Package : Collection of objects like procedures, functions etc., is called package
Advantages: Security, Hiding the informations, easy maintain
Process of creating the package:
- Package specification : It will have the name of objects / list of objects
- 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:
- Develop a package called distribution with below procedures and functions
- Procedure to show inventory items (Item_id, Item Name, Description, Creation date)
- Function to show number of purchase orders created in between given date range
- Procedure to show all purchase orders of selected supplier
- Procedue to show suppliers
- Function to show number of suppliers exist
- Develop a package called Finance (Invoice, Payments etc.,)
- 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
