Pl/SQL: Writing and Compiling PL/SQL Packages.

A package is a database object that groups logically related PL/SQL types, objects, and subprograms. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. Thespecification is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the specification.
Unlike subprograms, packages cannot be called, parameterized, or nested. Still, the format of a package is similar to that of a subprogram:

CREATE PACKAGE name AS  -- specification (visible part)
   -- public type and object declarations
   -- subprogram specifications
END [name];

CREATE PACKAGE BODY name AS  -- body (hidden part)
   -- private type and object declarations
   -- subprogram bodies
[BEGIN
   -- initialization statements]
END [name];

The specification holds public declarations, which are visible to your application. The body holds implementation details and private declarations, which are hidden from your application. As shown in the following figure, you can think of the specification as an operational interface and of the body as a "black box":

You can debug, enhance, or replace a package body without changing the interface (package specification) to the package body.
For example, we want to create a simple package providing three functions: hire_employee, fire_employee and raise_salary.
First we created the package specification.

CREATE OR REPLACE PACKAGE test AS -- package spec
    TYPE list IS VARRAY(25) of NUMBER(3);

    PROCEDURE hire_employee (emp_id INTEGER, name VARCHAR2);
    PROCEDURE fire_employee (emp_id INTEGER);
    PROCEDURE raise_salary (emp_id INTEGER, amount REAL);
END test;
/

Then we created the package body.

CREATE OR REPLACE PACKAGE BODY test AS -- package body
    PROCEDURE hire_employee (emp_id INTEGER, name VARCHAR2) IS
    BEGIN
        INSERT INTO employee VALUES (emp_id, name, 1000);
    END hire_employee;

    PROCEDURE fire_employee (emp_id INTEGER) IS
    BEGIN
        DELETE FROM employee WHERE empno = emp_id;
    END fire_employee;

    PROCEDURE raise_salary (emp_id INTEGER, amount REAL) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Increase Salary :' || to_char(amou
nt));
        UPDATE employee SET sal = sal + amount WHERE empno = emp_id;
    END raise_salary;
END test;

/

To compile the package, we can either type them into SQL*Plus terminal. And Oracle server will compile and store the package, or save them into separate files and compile them from SQL*Plus. Assume the package spec is stored in a file named spec, and the body is stored in another file named body. The following shows how to compile the package and make the procedure call at SQL*Plus.

>SQL SET SERVEROUTPUT ON
>SQL VARIABLE num NUMBER

>SQL @spec

>SQL @body

>SQL exec test.raise_salary(1,1000);