20.Packages

PL/SQL

Package : Collection of objects like procedures, functions etc., is called package

Advantages: Security, Hiding the informations, easy maintain

Process of creating the package:

  1. Package specification : It will have the name of objects / list of objects
  2. Package Body : It will have the body (code) of the object specified in the package spec.

Syntax:

Package Specification:

Create or replace package <package name> as
   procedure1  procedure_name (parameters) is
   function1 function_name  (parametesr) return return_type is
   procedure2 procedure_name (parameters) is
   function2 function_name  (parametesr) return return_type is
end;

Package body:

Create or replace package <package name> as
    procedure1 procedure_name (parameters) is
    begin
        ---
        ---
    end;

    function1  function_name (parameters)   return type is
    begin
        ---
        --
    end;
end;

Example: Create a package spec for a procedure and functon

create or replace package bank_package as
procedure show_bank_account
function show_bank_bal(p_acno number) return number;
end;

SQL> @ filename.sql

Package created

SQL> ed filename.sql

create or replace package body bank_package as

/* procedure */

procedure show_bank_account is
cursor bank_cur is select * from banks;
begin
for bank_rec in bank_cur
loop
dbms_output.put_line (bank_rec.acno|| ‘ ‘||bank_rec.amount||’ ‘||bank_rec.ttype);
end loop;
end;

/* function */

function show_bank_bal(p_acno number) return number is

tot_dep number;
tot_wit number;
bal number;
begin

select sum(amount) 
into   tot_dep
    from   banks 
    where  ttype='d' 
     and   acno = p_acno;


select sum(amount) 
into   tot_wit
    from   banks 
    where  ttype='w' 
     and   acno = p_acno;

bal:=tot_dep - tot_wit;

return(bal);

end;

end;

/

SQL> @ filename.sql
package body created

Executing objects belongs above package

function execution

SQL> SELECT bank_package.SHOW_BANK_BAL(10001) FROM DUAL;

Procedure execution

SQL> EXECUTE bank_package.show_bank_account;

Task:

  1. Develop a package called distribution with below procedures and functions
  2. Procedure to show inventory items (Item_id, Item Name, Description, Creation date)
  3. Function to show number of purchase orders created in between given date range
  4. Procedure to show all purchase orders of selected supplier
  5. Procedue to show suppliers
  6. Function to show number of suppliers exist
  7. Develop a package called Finance (Invoice, Payments etc.,)
  8. Develop a package called Bank (banks, bank branches, bank accunts, Customers, Bank balances)
    procedure to show banks
    procedure to show bank branchs of given bank
    procedure to show bank accounts
    function to show bank balance of a customer