Packages in PLSQL

A package is a Schema Object that groups logically related PL/SQL types, items, and subprograms.
 Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary.
The specification (spec for short) 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 spec.

The spec holds public declarations, which are visible to your application.

The body holds implementation details and private declarations, which are hidden from your application.


Advantages of PL/SQL Packages:
============================
Easier Application Design
------------------------
When designing an application, all you need initially is the interface information in the package specs.
 You can code and compile a spec without its body.

Information Hiding
-------------------------
With packages, you can specify which types, items, and subprograms are public (visible and accessible) or
 private (hidden and inaccessible). For example, if a package contains four subprograms,
three might be public and one private. The package hides the implementation of the private subprogram
so that only the package (not your application) is affected if the implementation changes.

Better Performance
--------------------------

When you call a packaged subprogram for the first time, the whole package is loaded into memory.
So, later calls to related subprograms in the package require no disk I/O.
Also, packages stop cascading dependencies and thereby avoid unnecessary recompiling


Example of a PL/SQL Package
======================

CREATE OR REPLACE PACKAGE emp_actions AS  -- spec
   TYPE EmpRecTyp IS RECORD (emp_id INT, salary REAL);
   CURSOR desc_salary RETURN EmpRecTyp;
   PROCEDURE hire_employee (
      ename  VARCHAR2,
      job    VARCHAR2,
      mgr    NUMBER,
      sal    NUMBER,
      comm   NUMBER,
      deptno NUMBER);
   PROCEDURE fire_employee (emp_id NUMBER);
END emp_actions;

CREATE OR REPLACE PACKAGE BODY emp_actions AS  -- body
   CURSOR desc_salary RETURN EmpRecTyp IS
      SELECT empno, sal FROM emp ORDER BY sal DESC;
   PROCEDURE hire_employee (
      ename  VARCHAR2,
      job    VARCHAR2,
      mgr    NUMBER,
      sal    NUMBER,
      comm   NUMBER,
      deptno NUMBER) IS
   BEGIN
      INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job,
         mgr, SYSDATE, sal, comm, deptno);
   END hire_employee;

   PROCEDURE fire_employee (emp_id NUMBER) IS
   BEGIN
      DELETE FROM emp WHERE empno = emp_id;
   END fire_employee;
END emp_actions;


Bodiless Package
----------------------

CREATE PACKAGE trans_data AS  -- bodiless package
   TYPE TimeRec IS RECORD (
      minutes SMALLINT,
      hours   SMALLINT);
   TYPE TransRec IS RECORD (
      category VARCHAR2,
      account  INT,
      amount   REAL,
      time_of  TimeRec);
   minimum_balance    CONSTANT REAL := 10.00;
   number_processed   INT;
   insufficient_funds EXCEPTION;
END trans_data;


Overloading  Package
-----------------------------

CREATE PACKAGE journal_entries AS
   ...
   PROCEDURE journalize (amount REAL, trans_date VARCHAR2);
   PROCEDURE journalize (amount REAL, trans_date INT);
END journal_entries;

CREATE PACKAGE BODY journal_entries AS
   ...
   PROCEDURE journalize (amount REAL, trans_date VARCHAR2) IS
   BEGIN
      INSERT INTO journal
         VALUES (amount, TO_DATE(trans_date, 'DD-MON-YYYY'));
   END journalize;

   PROCEDURE journalize (amount REAL, trans_date INT) IS
   BEGIN
      INSERT INTO journal
         VALUES (amount, TO_DATE(trans_date, 'J'));
   END journalize;
END journal_entries;


Eamples:
----------

  CREATE OR REPLACE PACKAGE "MFUND"."GLOBREFCURSOR"
authid current_user
IS
TYPE GREF IS REF CURSOR;
END;
/

Ur's

AmarAlam