PL/SQL - Packages

A Package is a schema object that groups logically related PL/SQL types, items and subprograms like procedures and functions. Package can hold other constructs too, such as exceptions, variables, cursors and type declarations.

Package have two parts:

• Package specification
• Package body

Advantages of packages:

• Modularity: packages let us encapsulate logically related types, items  and subprogrmas in a named PL/SQL module.
• Easier application design : when designing an application, initially need to mention only interface information in the package specs, we can define the detailed body code later.
• Information hiding : with packages, we can specify which types , items and subprograms are public or private.
• Better pefformance: when we call a packaged subprogram for the first time, the whole package is loaded into the memory. So, later calls to related subprograms in the package require no disk I/O.
• Added functionality: packaged public variables and cursors persist for the duration of a session so, they cab be shared by all the subprograms that execute in the environment.

Package Specification:

The package specification tells a user of the package what it cam do rather than how it will do it. The spec contains only the header of the program units rather any executable code. Its kind of like a declaration section for program units.

Syntax:

CREATE OR REPLACE PACKAGE package_name

AS

Program1_header;

Program2_header;

Program3_header;

END package_name;

/

Example:

CREATE OR REPLACE PACKAGE CUSTOMER_PACKAGE

AS

PROCEDURE ADD_CUSTOMER

(

c_id IN NUMBER,

c_fname IN VARCHAR2,

c_lname IN VARCHAR2,

c_addr IN VARCHAR2,

c_date_added IN DATE

);

PROCEDURE DISPLAY_NAMES;

PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

);

PROCEDURE show_customer

(

customer_in IN customer%ROWTYPE

);

FUNCTION get_names

(

custid IN NUMBER

)

RETURN SYS_REFCURSOR;

FUNCTION find_salescount

(

p_sales_date IN date

)

RETURN NUMBER;

END CUSTOMER_PACKAGE;

Package Body:

The package body contains the program unit bodies - the executable statements that correspond with the headers in the package specification. 

Syntax:

CREATE OR REPLACE PACKAGE BODY package_name

AS

-- complete subprogram code

program1_code;

program2_code;

program3_code;

END package_name;

/

Example:

CREATE OR REPLACE PACKAGE BODY customer_package

AS

PROCEDURE ADD_CUSTOMER

(

c_id IN OUT NUMBER,

c_fname IN VARCHAR2,

c_lname IN VARCHAR2,

c_addr IN VARCHAR2,

c_date_added IN DATE

)

AS

BEGIN

INSERT INTO CUSTOMER(customer_id, first_name, last_name, address, date_added) VALUES (c_id,c_fname,c_lname,c_address,c_date_added);

COMMIT;

dbms_output.put_line('Data successfully Inserted');

SELECT COUNT(1) INTO c_id FROM CUSTOMER;

END ADD_CUSTOMER;

PROCEDURE DISPLAY_NAMES

IS

C_REC SYS_REFCURSOR;

FNAME VARCHAR2(50);

LNAME VARCHAR2(50);

BEGIN

C_REC := GET_NAMES(10);

LOOP

FETCH C_REC INTO FNAME , LANME;

EXIT WHEN C_REC%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(FNAME);

DBMS_OUTPUT.PUT_LINE(LNAME);

END LOOP;

CLOSE C_REC;

END;

PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

c_name customer.first_name%type;

c_country customer.country%type;

ex_customer_id EXCEPTION;

BEGIN

IF c_id <=0 THEN

RAISE ex_customer_id;

SELECT first_name, country INTO c_name, c_country FROM customer WHERE customer_id = c_id;

dbms_output.put_line('Name: '|| c_name);

dbms_output.put_line('Country: '|| c_country);

EXCEPTION

WHEN ex_customer_id THEN

dbms_output.put_line('ID must be greater than zero!');

WHEN no_data_found THEN

dbms_output.put_line('No such customer!');

WHEN too_many_rows THEN

dbms_output.put_line('You got more than 1 row!');

WHEN others THEN

dbms_output.put_line('Error!');

END;

PROCEDURE show_customer

(

customer_in IN customer%ROWTYPE

)

IS

BEGIN

Update customer set row =customer_in where customer_id =customer_in.customer_id;

COMMIT;

END;

FUNCTION find_salescount

( p_sales_date IN date

) RETURN NUMBER

AS

no_of_sales number := 0;

BEGIN

SELECT count(*) INTO no_of_sales from sales where sales_date = p_sales_date;

RETURN no_of_sales;

END find_salescount;

FUNCTION get_names

(

custid IN NUMBER

)

RETURN SYS_REFCURSOR;

IS

l_return SYS_REFCURSOR;

BEGIN

OPEN l_return FOR

SELECT first_name, last_name FROM customer WHERE customer_id = custid;

RETURN l_return;

END get_names;

END customer_package;

How to execute sub programs present in the packages:

execute <package_name>.<resource_name>

Example:

execute.customer_package.display_names;

execute customer_package.get_customer(16);