PACKAGE and PACKAGE BODY in Oracle

PACKAGE and PACKAGE BODY in Oracle

Packages: A Package is an Oracle object, which holds other objects within it. Objects commonly held within a package are procedures, functions, variables, constants, cursors and exceptions.

A Package consists of two components

- Specification

- Body

A Package Specification declares the types (variable of the Record type), memory variables, constants, exceptions, cursors and subprograms that are available for use.

A Package body fully defines cursor, functions and procedure and thus implements the specification.


Why Use Package

Packages offer the following advantages:

1) Packages enable the organization applications into efficient modules. Each package is easily understood, and the interfaces between packages are simple, clear and well defined.

2) Package allows granting of privileges efficiently.

3) A Package's public variables and cursor persist for the duration of the session. Therefore all cursors and procedures that execute in the environment can store them.

4) Packages enable the overloading of procedures and functions when required.

5) Package improves performance by loading multiple objects into memory at once. Therefore, subsequent calls to related sub programs in the package require no I/O.

6) Package promotes code reuse through the use of libaries that contain shared procedures and functions. Thereby reducing redundant coding.

Creating the Package Specification

Syntax
CREATE [Or Replace] PACKAGE package_name
IS | AS
public type and item declarations
subprogram specifications
End package_name;

Creating the Package Body
Syntax
CREATE [Or Replace] PACKAGE BODY package_body_name
IS | AS
private type and item declarations
subprogram body
End package_body_name;

Some Sample Program using Package and Package Body

1) create or replace package emp_proc as
Procedure emp_pr (mempno number) ;
Procedure dept_pr (mdeptno number) ;
end;

create or replace package body emp_proc as
Procedure emp_pr (mempno number)
as
msal number ;
begin
select sal into msal from emp where empno=mempno ;
dbms_output.put_line('sal is '|| msal) ;
end;
Procedure dept_pr (mdeptno number) as
mdname varchar2(10) ;
begin
select dname into mdname from dept where deptno=mdeptno;
dbms_output.put_line('dname is '|| mdname) ;
end;
end;

To execute the previous program
--------------------------------

declare
begin
dbms_output.put('the emp ') ;
emp_proc.emp_pr(7369) ;
dbms_output.put('the dept ') ;
emp_proc.dept_pr(20) ;
END;

2) create or replace package p2
as
function f1(x number) return number ;
procedure pro (x number,y number) ;
end ;

create or replace package body p2
as
function f1(x number) return number
as
y number;
begin
y:=x*(30/100);
return y;
end f1;
procedure pro (x number, y number)
as
z number;
begin
z:=x*y;
dbms_output.put_line('the multiplication of'||x ||'and'||y||':='||z);
end pro;
end;

To execute this Program

exec p2.pro(45,3);

declare
x number ;
begin
x:=p2.f1(10);
dbms_output.put_line(x);
end;


Using Cursors in Packages

1) create or replace package pack_cur
IS
cursor c1 is select empno from emp ;
procedure proc_emp1 ;
procedure proc_emp2 ;
end ;

create or replace package body pack_cur
IS
mempno number;
procedure proc_emp1 is
begin
open c1;
loop
fetch c1 into mempno ;
dbms_output.put_line(mempno) ;
exit when c1%rowcount >=3 ;
end loop ;
end proc_emp1 ;
procedure proc_emp2 IS
begin
loop
fetch c1 into mempno ;
dbms_output.put_line(mempno) ;
exit when c1%rowcount >=6 ;
end loop ;
end proc_emp2 ;
end ;

2) create or replace package pack_cur
IS
cursor c1 is select empno from emp ;
cursor c2 is select dname from dept ;
procedure proc_emp ;
procedure proc_dept;
end ;

create or replace package body pack_cur
IS
mempno emp.empno%type;
mdname dept.dname%type;
procedure proc_emp is
begin
open c1;
loop
fetch c1 into mempno ;
dbms_output.put_line(mempno) ;
exit when c1%rowcount >=3 ;
end loop ;
close c1;
end proc_emp ;
procedure proc_dept IS
begin
open c2;
loop
fetch c2 into mdname ;
dbms_output.put_line(mdname) ;
exit when c2%rowcount >=2 ;
end loop ;
close c2;
end proc_dept ;
end ;

Declaring a Bodiless Package

CREATE or REPLACE PACKAGE global_consts IS
mile_2_kilo CONSTANT NUMBER := 1.6093;
kilo_2_mile CONSTANT NUMBER := 0.6214;
yard_2_meter CONSTANT NUMBER := 0.9144;
meter_2_yard CONSTANT NUMBER := 1.0936;
end global_consts;

CREATE OR REPLACE PROCEDURE meter_to_yaard
(p_meter IN number, p_yard out Number)
IS
BEGIN
p_yard := p_meter + global_consts.meter_2_yard ;
End ;

Droppimg/ Removing Package and Package Body

DROP PACKAGE package_name ;

DROP PACKAGE BODY package_body_name ;