PL/SQL Packages

PL/SQL Packages
A package is an encapsulated collection of related program objects (for example, procedures, functions, variables, constants, cursors, and exceptions) stored together in the database.

Using packages is an alternative to creating procedures and functions as standalone schema objects. Packages have many advantages over standalone procedures and functions. For example, they:

Let you organize your application development more efficiently.
Let you grant privileges more efficiently.
Let you modify package objects without recompiling dependent schema objects.
Enable Oracle Database to read multiple package objects into memory at once.
Can contain global variables and cursors that are available to all procedures and functions in the package.
Let you overload procedures or functions. Overloading a procedure means creating multiple procedures with the same name in the same package, each taking arguments of different number or datatype.

The specification part of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package. The body of a package defines the objects declared in the specification, as well as private objects that are not visible to applications outside the package.

Example of a PL/SQL Package Specification and Body
The following example shows a package specification for a package named Employee_management. The package contains one stored function and two stored procedures. The body for this package defines the function and the procedures:

CREATE PACKAGE BODY Employee_management AS
FUNCTION Hire_emp (Name VARCHAR2, Job VARCHAR2,
Mgr NUMBER, Hiredate DATE, Sal NUMBER, Comm NUMBER,
Deptno NUMBER) RETURN NUMBER IS
New_empno NUMBER(10);

-- This function accepts all arguments for the fields in
-- the employee table except for the employee number.
-- A value for this field is supplied by a sequence.
-- The function returns the sequence number generated
-- by the call to this function.

BEGIN
SELECT Emp_sequence.NEXTVAL INTO New_empno FROM dual;
INSERT INTO Emp_tab VALUES (New_empno, Name, Job, Mgr,
Hiredate, Sal, Comm, Deptno);
RETURN (New_empno);
END Hire_emp;

PROCEDURE fire_emp(emp_id IN NUMBER) AS

-- This procedure deletes the employee with an employee
-- number that corresponds to the argument Emp_id. If
-- no employee is found, then an exception is raised.

BEGIN
DELETE FROM Emp_tab WHERE Empno = Emp_id;
IF SQL%NOTFOUND THEN
Raise_application_error(-20011, 'Invalid Employee
Number: ' || TO_CHAR(Emp_id));
END IF;
END fire_emp;

PROCEDURE Sal_raise (Emp_id IN NUMBER, Sal_incr IN NUMBER) AS

-- This procedure accepts two arguments. Emp_id is a
-- number that corresponds to an employee number.
-- SAL_INCR is the amount by which to increase the
-- employee's salary. If employee exists, then update
-- salary with increase.

BEGIN
UPDATE Emp_tab
SET Sal = Sal + Sal_incr
WHERE Empno = Emp_id;
IF SQL%NOTFOUND THEN
Raise_application_error(-20011, 'Invalid Employee
Number: ' || TO_CHAR(Emp_id));
END IF;
END Sal_raise;
END Employee_management;


--------------------------------------------------------------------------------
Note:
If you want to try this example, then first create the sequence number Emp_sequence. Do this with the following SQL*Plus statement:

SQL> CREATE SEQUENCE Emp_sequence
> START WITH 8000 INCREMENT BY 10;


Creating Packages
Each part of a package is created with a different statement. Create the package specification using the CREATE PACKAGE statement. The CREATE PACKAGE statement declares public package objects.

To create a package body, use the CREATE PACKAGE BODY statement. The CREATE PACKAGE BODY statement defines the procedural code of the public procedures and functions declared in the package specification.

You can also define private, or local, package procedures, functions, and variables in a package body. These objects can only be accessed by other procedures and functions in the body of the same package. They are not visible to external users, regardless of the privileges they hold.

It is often more convenient to add the OR REPLACE clause in the CREATE PACKAGE or CREATE PACKAGE BODY statements when you are first developing your application. The effect of this option is to drop the package or the package body without warning. The CREATE statements would then be the following:

CREATE OR REPLACE PACKAGE Package_name AS ...


and

CREATE OR REPLACE PACKAGE BODY Package_name AS ...


Creating Packaged Objects
The body of a package can contain include:

Procedures and functions declared in the package specification.
Definitions of cursors declared in the package specification.
Local procedures and functions, not declared in the package specification.
Local variables.
Procedures, functions, cursors, and variables that are declared in the package specification are global. They can be called, or used, by external users that have EXECUTE permission for the package or that have EXECUTE ANY PROCEDURE privileges.

When you create the package body, make sure that each procedure that you define in the body has the same parameters, by name, datatype, and mode, as the declaration in the package specification. For functions in the package body, the parameters and the return type must agree in name and type.

Privileges to Create or Drop Packages
The privileges required to create or drop a package specification or package body are the same as those required to create or drop a standalone procedure or function.