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.

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 for creating PL/SQL package specification:

CREATE [OR REPLACE] PACKAGE package_name
[ AUTHID { CURRENT_USER | DEFINER } ]
{ IS | AS }

   Code language: SQL (Structured Query Language) (sql)

[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 REPLACEkeyword instead of the CREATEkeyword. 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.

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;Code language: SQL (Structured Query Language) (sql)

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:

CREATE [OR REPLACE] PACKAGE BODY package_name
   { IS | AS }

   Code language: SQL (Structured Query Language) (sql)

[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 for the keyword BODYand 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 BEGINkeyword and end with the EXCEPTIONsection or ENDkeyword 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 personnelpackage:

/*
  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;Code language: SQL (Structured Query Language) (sql)

Referencing PL/SQL package elements

You reference to package elements by using dot notation:

package_name.package_elementCode language: SQL (Structured Query Language) (sql)

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

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;Code language: SQL (Structured Query Language) (sql)

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