PACKAGES

A package is a collection of PL/SQL objects grouped together under one

package name. Packages include procedures, functions, cursors, decla-rations,

types, and variables. There are numerous benefits in collecting

objects into a package. In this chapter, you learn what these benefits are,

and how to use them.

THE BENEFITS OF USING PACKAGES

There are numerous benefits of using packages as a method to bundle

your functions and procedures, the first being that a well-designed pack-age

is a logical grouping of objects—such as functions, procedures, global

variables, and cursors. All of the code (parse tree and pseudocode

[p-code]) is loaded on the first call of the package. This means that the

first call to the package is very expensive, but all subsequent calls will re-sult

in an improved performance. Packages are therefore often used in ap-plications

where procedures and functions are used repeatedly.

There is also an additional level of security using packages. When a user

executes a procedure in a package (or stored procedures and functions),

the procedure operates with the same permissions as its owner. Packages

also allow the creation of private functions and procedures, which can

only be called from other functions and procedures in the package. This

enforces information hiding.

THE PACKAGE SPECIFICATION

The package specification contains information about the contents of the

package, but not the code for the procedures and functions. It also con-tains

declarations of global/public variables. Anything placed in the

declarative section of a PL/SQL block may be coded in a package

specification. All objects placed in the package specification are called

public objects. Any function or procedure not in the package specification

but coded in a package body is called a private function or procedure.

THE PACKAGE BODY

The package body contains the actual executable code for the objects de-scribed

in the package specification. The package body contains code for

all procedures and functions described in the specification and may addi-tionally

contain code for objects not declared in the specification; the lat-ter

type of packaged object is invisible outside the package and is referred

as being hidden. When creating stored packages, the package specifica-tion

and body can be compiled separately.

RULES FOR THE PACKAGE BODY

There are a number of rules that must be followed in package body code:

(1) There must be an exact match between the cursor and module head-ers

and their definitions in package specification.

(2) Do not repeat decla-ration

of variables, exceptions, type, or constants in the specification

again in the body.

(3) Any element declared in the specification can be ref-erenced

in the body.

REFERENCING PACKAGE ELEMENTS

Use the following notation when calling packaged elements from outside

of the package: package_name.element.

You do not need to qualify elements when declared and referenced inside

the body of the package or when declared in a specification and refer-enced

inside the body of the same package.

FOR EXAMPLE

--The Head

CREATE OR REPLACE PACKAGE manage_customers

AS

PROCEDURE find_cust_name

(i_cust_no IN customers.cust_no%TYPE,

o_cust_name OUT customers.cust_name%TYPE);

FUNCTION id_is_good

(i_cust_no IN customers.cust_no %TYPE)

RETURN BOOLEAN;

END manage_customers;

--The Body

CREATE OR REPLACE PACKAGE BODY manage_customers

AS

PROCEDURE find_cust_name

(i_cust_no IN customers.cust_no%TYPE,

o_cust_name OUT customers.cust_name%TYPE)

IS

BEGIN

SELECT cust_name

INTO o_cust_name

FROM customers

WHERE cust_no = i_cust_no;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE('Error in finding: cust_no '|| i_cust_no );

END find_cust_name;

FUNCTION id_is_good

(i_cust_no IN customers.cust_no%TYPE)

RETURN BOOLEAN

IS

v_id_cnt number;

BEGIN

SELECT COUNT(*)

INTO v_id_cnt

FROM customers

WHERE cust_no = i_cust_no;

RETURN 1=v_id_cnt;

EXCEPTION

WHEN OTHERS

THEN

RETURN FALSE;

END id_is_good;

END manage_customers;

Now we will use elements of the manage_customers package in another

code block..

I DECLARE

v_cust_name customers.cust_name%TYPE;

BEGIN

IF manage_customers.id_is_good(&v_id)

THEN

manage_customers.find_cust_name (&&v_id,v_cust_name);

DBMS_OUTPUT.PUT_LINE('Customer No. '||&&v_id||' is '||v_cust_name);

ELSE

DBMS_OUTPUT.PUT_LINE('Customer ID: '||&&v_id||' is not in the database.');

END IF;

END;

STORED PACKAGES