PLSQL PLSQL Packages Package Specification Body PLSQL Packages

PL/SQL

PL/SQL Packages Package = Specification + Body

PL/SQL Packages Specification Public declaration (Public objects) Visible to application Info about the package What can be part of it? Types, variables, constants, exceptions, cursors, and subprograms Body Private declaration (Private Object) Hidden from application

PL/SQL Packages Example

PL/SQL Packages Example Specification CREATE OR REPLACE PACKAGE c_package AS -- Adds a customer PROCEDURE add. Customer(c_id customers. id%type, c_name customers. name%type, c_age customers. age%type, c_addr customers. address%type, c_sal customers. salary%type); -- Removes a customer - PROCEDURE del. Customer(c_id customers. id%TYPE); --Lists all customers END c_package; / PROCEDURE list. Customer;

PL/SQL Packages Example Body CREATE OR REPLACE PACKAGE BODY c_package AS PROCEDURE add. Customer(c_id customers. id%type, c_name customers. name%type, c_age customers. age%type, c_addr customers. address%type, c_sal customers. salary%type) IS BEGIN INSERT INTO customers (id, name, age, address, salary) VALUES(c_id, c_name, c_age, c_addr, c_sal); END add. Customer; PROCEDURE del. Customer(c_id customers. id%type) IS BEGIN DELETE FROM customers WHERE id = c_id; END del. Customer; END c_package;

Why SQL Packages? . . .

How to use it? Input DECLARE code customers. id%type: = 8; BEGIN c_package. addcustomer(7, 'Rajnish', 25, 'Chennai', 3500); c_pack age. addcustomer(8, 'Subham', 32, 'Delhi', 7500); c_package. listcustomer; c_package. delcustomer(8); c_package. listcustomer; END;

How to use it? Output Customer(1): Ramesh Customer(2): Khilan Customer(3): kaushik Customer(4): Chaitali Customer(5): Hardik Customer(6): Komal Customer(7): Rajnish Customer(8): Subham Customer(1): Ramesh Customer(2): Khilan Customer(3): kaushik Customer(4): Chaitali Customer(5): Hardik Customer(6): Komal Customer(7): Rajnish

Package Call <Package. Name>. <Procedure/Function Name> (Parameters)

Benefits? Modularity Easier application design Information hiding Public Private Added functionality Initially can give spec only Sharing Better performance. Loading

Existing packages DBMS_ALERT DBMS_OUTPUT enables you to display output from PL/SQL blocks and subprogram DBMS_PIPE lets you use triggers to alert an application when specific database values change. allows different sessions to communicate over named pipes. UTL_FILE allows your PL/SQL programs to read and write operating system (OS) text files.

Function vs Procedure Function Must return value Can be used in SQL statement. Procedure May or may not return Can return more than one value.

Trigger Stored procedure Fired on DML operation Types System Trigger Before, After, For each row, for each statement When DB start up & shut down Schema Trigger On DDL

Types of Joins Equi Join Self Joined without join Inner Join If join in one table. Cartesian When primary-foreign relationship. Include rows which satisfy condition. Outer Non-satisfying rows

Other Terminology Indexes DB object To improve performance of data retrieval. Create index x on table. X. column. X Mutating Table under process.

Other Terminology Cursor Area for multi-row operation in PL/SQL program Types Implicit – declared automatically Explicit Syntax Declare C 1 is Select SAL, EMPNO from EMP X number; Y vacrchar 2(30); Begin Open C 1; Loop Fetch C 1 INTO x, y; EXIT when c 1%NOTFOUND End Loop; End;

Thank you.