Create a Package and call its members

Packages encapsulates related functionality into one self-contained unit by grouping procedures and functions together Packages are typically made up of two components: a specification and a body. The specification lists the available procedures, functions, types, and objects.

Creating a Package Specification

You create a package specification using the CREATE PACKAGE statement. The simplified syntax for the CREATE PACKAGE statement is as follows:


CREATE [OR REPLACE] PACKAGE package_name
{IS | AS}
    package_specification
END package_name;

The following example creates a specification for a package named emp_package:


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));

SQL> CREATE PACKAGE emp_package AS
  2     TYPE t_ref_cursor IS REF CURSOR;
  3     FUNCTION get_emp_ref_cursor RETURN t_ref_cursor;
  4     PROCEDURE update_salary (p_id IN emp.empno%TYPE,p_factor IN NUMBER);
  5  END emp_package;
  6  /

Package created.

SQL>

Creating a Package Body

You create a package body using the CREATE PACKAGE BODY statement. The simplified syntax for the CREATE PACKAGE BODY statement:


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

The following example creates the package body for emp_package:


SQL> CREATE PACKAGE BODY emp_package AS
  2     FUNCTION get_emp_ref_cursor RETURN t_ref_cursor IS v_emp_ref_cursor t_ref_cursor;
  3     BEGIN
  4         -- get the REF CURSOR
  5         OPEN v_emp_ref_cursor FOR SELECT empno, ename, sal FROM emp;
  6         -- return the REF CURSOR
  7         RETURN v_emp_ref_cursor;
  8     END get_emp_ref_cursor;
  9
 10     PROCEDURE update_salary(p_id IN emp.empno%TYPE,p_factor IN NUMBER ) AS v_count INTEGER;
 11     BEGIN
 12        SELECT COUNT(*) INTO v_count FROM emp WHERE empno = p_id;
 13        IF v_count = 1 THEN
 14           UPDATE emp SET sal = sal * p_factor WHERE empno = p_id;
 15           COMMIT;
 16        END IF;
 17     EXCEPTION
 18        WHEN OTHERS THEN
 19        ROLLBACK;
 20     END update_salary;
 21  END emp_package;
 22  /

Package body created.

SQL>

Calling Functions and Procedures in a Package


SQL> SELECT emp_package.get_emp_ref_cursor FROM dual;

GET_EMP_REF_CURSOR
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

no rows selected


SQL>
SQL> SELECT sal FROM emp WHERE empno = 3;

no rows selected

SQL>
SQL> CALL emp_package.update_salary(3, 1.25);

Call completed.

SQL>
SQL> SELECT sal FROM emp WHERE empno = 3;

no rows selected

SQL>
SQL>

Home »
Oracle »
PL/SQL »

Packages:

Create a Package and call its members
Information on Functions and Procedures in a Package
Dropping a Package