PL/SQL Package

Summary: in this tutorial, you will learn how to create a simple PL/SQL package that is a group of related functions, procedures, types, etc.

Introducing to PL/SQL Package

PL/SQL package is a group of related functions, procedures, types, cursors, etc. PL/SQL package is like a library once written stored in the Oracle database and can be used by many applications.

A PL/SQL package has two parts: package specification and package body.

• A package specification is the public interface of your applications. The public means the stored function, procedures, types, etc., are accessible from other applications.
• A package body contains the code that implements the package specification.

PL/SQL Package

Creating PL/SQL Package Specification

The package specification is required when you create a new package. The package specification lists all the objects which are publicly accessible from other applications. The package specification also provides the information that developers need to know in order to use the interface. In short, package specification is the package’s API.

If the package specification does not contain any stored functions, procedures and no private code is needed, you don’t need to have a package body. These packages may contain only type definition and variables declaration. Those variables are known as package data. The scope of package data is global to applications. It is recommended that you should hide as much as package data as possible and use get and set functions to read and write that data. By doing this, you can prevent your package data changed unintentionally.

It is important to note that you must compile the package specification before package body.

Here is the syntax of creating PL/SQL package specification:

1 2 3 4 5 6 7 8 9 10 11

CREATE [OR REPLACE] PACKAGE package_name [ AUTHID { CURRENT_USER | DEFINER } ] { IS | AS }      [definitions of public TYPES    ,declarations of public variables, types, and objects    ,declarations of exceptions    ,pragmas    ,declarations of cursors, procedures, and functions    ,headers of procedures and functions] END [package_name];

The CREATE PACKAGE statement is used to define a new package specification. If you want to rebuild existing package you can use the REPLACE keyword instead of the CREATE keyword. In the package specification, you can define new types, declare global variables, types, objects, exceptions, cursors, procedures, and functions.

Below is an example of creating a new package specification called personnel. The personnelpackage contains two functions: get_fullname() and get_salary() based on employee’s ID.

1 2 3 4 5 6 7 8

CREATE OR REPLACE PACKAGE personnel AS   -- get employee's fullname   FUNCTION get_fullname(n_emp_id NUMBER)     RETURN VARCHAR2;   -- get employee's salary   FUNCTION get_salary(n_emp_id NUMBER)     RETURN NUMBER; END personnel;

Creating PL/SQL Package Body

PL/SQL package body contains all the code that implements stored functions, procedures, and cursors listed in the package specification.

The following illustrates the syntax of creating package body:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

CREATE [OR REPLACE] PACKAGE BODY package_name    { IS | AS }      [definitions of private TYPEs    ,declarations of private variables, types, and objects    ,full definitions of cursors    ,full definitions of procedures and functions] [BEGIN    sequence_of_statements   [EXCEPTION    exception_handlers ] ]   END [package_name];

The syntax is similar to the package specification except the keyword BODY and the implemented code of package specification.

The package body can contain an optional package initialization section that appears at the end of the package body. The package initialization sections start with the BEGIN keyword and end with the EXCEPTION section or END keyword of the package. The package initialization section is executed when the application references to the package element at the first time.

The following illustrates package body of the personnel package:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

/*   Package personnel body */ CREATE OR REPLACE PACKAGE BODY personnel AS   -- get employee's fullname   FUNCTION get_fullname(n_emp_id NUMBER) RETURN VARCHAR2 IS       v_fullname VARCHAR2(46);   BEGIN     SELECT first_name || ',' ||  last_name     INTO v_fullname     FROM employees     WHERE employee_id = n_emp_id;       RETURN v_fullname;     EXCEPTION   WHEN NO_DATA_FOUND THEN     RETURN NULL;   WHEN TOO_MANY_ROWS THEN     RETURN NULL;   END; -- end get_fullname     -- get salary   FUNCTION get_salary(n_emp_id NUMBER) RETURN NUMBER IS     n_salary NUMBER(8,2);   BEGIN     SELECT salary     INTO n_salary     FROM employees     WHERE employee_id = n_emp_id;       RETURN n_salary;       EXCEPTION       WHEN NO_DATA_FOUND THEN         RETURN NULL;       WHEN TOO_MANY_ROWS THEN         RETURN NULL;   END; END personnel;

Referencing PL/SQL package elements

You reference to package elements by using dot notation:

1	package_name.package_element

The following code illustrates how to use personnel package by calling its functions:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SET SERVEROUTPUT ON SIZE 1000000; DECLARE   n_salary NUMBER(8,2);   v_name   VARCHAR2(46);   n_emp_id NUMBER := &emp_id; BEGIN     v_name   := personnel.get_fullname(n_emp_id);   n_salary := personnel.get_salary(n_emp_id);     IF v_name  IS NOT NULL AND     n_salary IS NOT NULL   THEN     dbms_output.put_line('Employee: ' || v_name);     dbms_output.put_line('Salary:' || n_salary);   END IF; END;

In this tutorial, you’ve learned how to create a complete PL/SQL package and how to use its functions in another program.