Packages in Oracle

 Packages:
 - It is an collection of related variables,cursors,
   procedures and functions.
 - It is stored in "User_source" system table
 - It can be shared with other users
 - They will improve performance of Oracle while         accessing sub programs from client                  environment.
 - They support OOPS features like Encapsulation,
    Data Hiding and Function Over Loading. (8.0)
    It has 2 parts:
 i> Package Specification      It holds the declaration of Variables, Cursors,           Procedures and Functions.
 ii> Package Body      It holds the code of sub programs
---------------------------------------------------------------
 

 Ex: 1  Package Specification   create or replace package pack1 as
   result number(12);
   procedure add_num( a number, b number );
   function mul_num(x number, y number)
   return number;
   end pack1;

  Package Body  create or replace package body pack1 as
  procedure add_num( a number,b number) is
  begin
   result := a + b;
  dbms_output.put_line(' Sum of numbers is:    '||result);
  end add_num;
  function mul_num(x number,y number) return     number is
  begin
   result := x * y;
   return(round(result));
   end mul_num;
  end pack1;
 
 Using Package elements: desc pack1
 exec pack1.add_num(2099,8908);
 select Pack1.mul_num(5000,22),
 pack1.mul_num(7766,10) from dual;
 select ename, job, pack1.mul_num(sal,.15) PF,        pack1.mul_num(sal,2) BONUS  from emp;
---------------------------------------------------------------
 Ex: 2
 create or replace package epack as
 function gross(vsal number) return number;
 function experiance(doj date) return number;
 end epack;
 /

 create or replace package body epack as
 -- Local Sub program (Valid in Package Body only )
 function mul_nos(a number,b number) return    number  is
 begin
 return(round(a * b));
 end mul_nos;

 function gross(vsal number) return number is
 da number(12);
 hra number(12);
 pf number(12);
 gpay number(14);
 begin
 -- Using local sub program
 da := mul_nos(vsal,.25);
 hra := mul_nos(vsal,.35);
 pf := mul_nos(vsal,.15);
 gpay := vsal + da + hra - pf;
 return(gpay);
 end gross;
 function experiance(doj date) return number is
 begin
 return (round(months_between(sysdate,doj))/12);
 end experiance;
 end epack;
 /

 Select empno, ename, job,
 round(epack.experiance(hiredate)) experiance,
 epack.gross(sal) "Gross Pay" from emp;

 select ename, epack.mul_nos(sal,2) "bonus" from    emp;  -- error
 Local sub programs cannot be accessed from    package.
---------------------------------------------------------------
 

 * Package with " Function over loading " feature: create or replace package load_pack as
 function add_data(a number,b number) return     number;
 function add_data(a varchar2,b varchar2) return      varchar2;
 end load_pack;
 /
 create or replace package body load_pack as
 function add_data(a number,b number) return     number   is
 begin
 return( a + b );
 end add_data;
 function add_data(a varchar2,b varchar2) return      varchar2 is
 begin
 return(a||'  '||b);
 end add_data;
 end load_pack;
 /

 >desc load_pack
 >select load_pack.add_data(5453,5675) total,
  load_pack.add_data('iLOGIC','Technologies') name
  from dual;
---------------------------------------------------------------  Removing Package:
 drop package <pack name>; drop package pack1;
 -- Removes package specification & Body at a time

 Sharing Package: ( scott ) >grant execute on load_pack to user1;

 User1: > select scott.load_pack.add_data(5453,5675)            total from dual;
 > desc scott.load_pack

 To View the Existing Package Body: ( scott ) > select text from user_source where name =            'PACK1';
---------------------------------------------------------------
 

 Ex: 4
 create table bankmaster(accno number(4),
 name varchar2(20), acc_type char(1),
 curr_bal number(12,2));

 create table transaction(accno number(4),
 tran_type char(1),tran_date date, amt   number(12,2));

 * Package Updates the current balance in   bankmaster table with a procedure & checks for    minimum balance with a function:
 create or replace package bpack as
 cbal bankmaster.curr_bal%type;
 procedure upd_bal(vaccno number,vamt number,
 vtype char);
 function chk_bal(vaccno number,vamt number)
 return boolean ;
 end bpack;
 /

 create or replace package body bpack as
 procedure upd_bal(vaccno number,vamt number,
 vtype char) is
 begin
 select curr_bal into cbal from bankmaster 
 where accno = vaccno;
 if vtype = 'D' then
 cbal := cbal + vamt;
 elsif vtype = 'W' then
 cbal := cbal - vamt;
 end if;
 update bankmaster set curr_bal = cbal
 where accno = vaccno;
 end upd_bal;

 function chk_bal(vaccno number,vamt number)
 return boolean is
 begin
 select curr_bal into cbal from bankmaster
 where accno = vaccno;
 cbal := cbal - vamt;
 if cbal < 5000 then
 return(true);
 else
 return(false);
 end if;
 end chk_bal;
 end bpack;
 /

 * Trigger Updates the Current balance       automatically by using sub programs in package. create or replace trigger btrig
 before insert on transaction for each row
 begin
 if :new.tran_type = 'D' then
 bpack.upd_bal(:new.accno,:new.amt,'D');
 elsif :new.tran_type = 'W' then
 IF bpack.chk_bal(:new.accno,:new.amt) THEN
 raise_application_error(-20888, 'Account holder is    not having enough Balance');
 ELSE
 bpack.upd_bal(:new.accno,:new.amt,'W');
 END IF;
 end if;
 end;
 /

 insert into bankmaster values(101, 'SRIRAM', 'S',      50000);
 insert into transaction values(101, 'D', sysdate,     25000);
 insert into transaction values(101, 'W', sysdate,     5000);
 insert into transaction values(101, 'W', sysdate,      75000);
---------------------------------------------------------------
 

 Using Cursors in Package: create or replace package cpack as
 cursor c1 is select * from stu_info;
 i c1%rowtype;
 procedure tot_fee ;
 pragma serially_reusable;
 end cpack;

 create or replace package body cpack as
 pragma serially_reusable;
 procedure tot_fee is
 tot number(12) := 0;
 begin
 for i in c1 loop
 dbms_output.put_line(i.roll||'  '||i.sname
 ||'   '||i.course ||'  '||i.fee_paid);
 tot := tot + i.fee_paid;
 end loop;
 dbms_output.put_line
 (' Total fee collected is  : '||tot);
 end tot_fee;
 end cpack;

 exec cpack.tot_fee;

 * Pragma Serially_Reusable : It clears the global variables and cursors defined in
 package after executing sub programs .
 It will save resources and improve performance.
---------------------------------------------------------------
 

 Advantages of Package: Used to store related sub programs at a common     location.
 Package elements can be used any where in    database.
 Improves the performance while accessing Sub        programs from other s/w tools.
---------------------------------------------------------------