PL-SQL - Package

PACKAGES

A package is a object where you put in all the related procedures and functions together in one object. Packages has two parts, one is Package Spec and another one is package body.
In Package Spec is nothing but an object in which you declare the procedure and function names which you are going to group together, arguments for the procedure ie declaration part of procedures and functions. In Package Body we write the code for all the procedures and functions we declare in the package spec.

You should have same number of procedure and functions in body as same as package spec, otherwise you will get an error when try to save the body.

Syntax to create the Package Spec
CREATE OR REPLACE PACKAGE SPEC <spec_name > 
declare variables here so that any procedure or function with in this package can use it. 
Subprograms declartion Example
CREATE PROCEDURE invoice_monthly_report ( var_mnthyear char(4) );
CREATE FUNCTION check_invoice_balance ( var_invno number(4) ) RETURN number;
END <spec_name>

Syntax to create the Package body
CREATE OR REPLACE PACKAGE BODY <spec_name > 
     CREATE PROCEDURE invoice_monthly_report ( var_mnthyear char(4) ) AS
     declare variables 
     BEGIN
                write the pl/sql code
     EXCEPTION
                handle the exceptions
      END invoice_monthly_report 

      CREATE FUNCTION check_invoice_balance (var_invno number(4))RETURN number   AS
       declare the variables
       BEGIN
                  write pl/sql code
                  return the value to the calling program
       EXCEPTION
                  handle the exception
                  return the value (may be -1 if the program failed)
       END check_invoice_balance;
  END <spec_name>

Package Example

create or replace package pck_product is 
  procedure proc_insert_product 
                    ( var_prd_rec product%ROWTYPE, 
                      var_err_code OUT number,
                      var_err_msg OUT varchar2 ); 
  function check_product 
                    ( var_prod_id product.product_id%TYPE ) return number; 
end pck_product;

create or replace package body pck_product is 
    procedure proc_insert_product 
                    ( var_prd_rec product%ROWTYPE, 
                      var_err_code OUT number, 
                      var_err_msg OUT varchar2 ) IS 
    begin 
    -- select the next sequence number rom the product sequence 
    select prod_seq.nextval into var_prd_rec.product_id from dual; 
    -- insert into the product table. 
    insert into product 
               ( product_id, product_name, qty_on_hand, reorder_level) 
             values 
               ( var_prd_rec.product_id, var_prd_rec.product_name, var_prd_rec.qty_on_hand, var_prd_rec.reorder_level ); 
     exception 
            when others then 
            var_err_code := SQL%SQLCODE; 
            var_err_msg := SQL%ERRMSG; 
    end proc_insert_product;

    function check_product ( var_prod_id product.product_id%TYPE ) IS 
         var_prod_hold product.product_id%TYPE; 
    begin 
        select product_id into var_prod_id from product where product_id = var_prod_id; 
        return 1; 
    exception 
        when no_Data_found then 
        return -1; 
    end check_product;
end pck_product;
Advantages of Package
You can divide the procedures and functions to the relative tables, 
for example if you are dealing with HR application we can write related
sub programs which access employee, dept, payroll together. 
When you try to execute a stored procedure or function with in a package
all the procedures and functions will be in memory so that the next procedure
or function call will be faster.
Packged variables and cursors exists for the session, they can be shared
by all subprograms that execute in the environment.

How you call a package body programs.
You need to call the programs as packagename.procedurename

EXECUTE invoice_pack.invoice_monthly_report ('1099');