Packages

Database objects that group logically related PL/SQL types, objects and subprograms
They cannot be called, passed parameters to or nested
There are two parts
–        Specification
–        Body

Advantages of Packages
Modularity
–        allows encapsulation of related types, objects and subprograms in a named PL/SQL module
–        easy to understand with simple, clear and well defined interfaces
–        helps in application development
Easier Application Design
–        when designing applications, only interface information in package specifications initially required
–        can code and compile specification without body
–        stored subprograms referencing the package can be compiled as well
–        need not define package bodies until ready to complete the application
Information Hiding
–        can specify which types, items, and subprograms are public or private
–        definition of private subprograms is hidden so that only the package (not the application) is affected if the definition changes
–        simplifies maintenance and enhancement and protects integrity of the package
Better performance
–        when a packaged subprogram is called for the first time, the whole package is loaded into memory later calls to related subprograms in the package require no disk I/O

Package Specification
Is an interface to the applications
Declares the types, variables, constants, exceptions, cursors and subprograms available for use
Holds public declarations, visible to the application
Can be thought of as an operational interface
Scope of the declarations are local to the database schema and global to the package
Lists the package resources available to applications
Created using CREATE PACKAGE command

Syntax for Package Specification –

CREATE [OR REPLACE] PACKAGE AS
Global variables declaration;
Procedure specifications;
Function specifications;
Type Definitions;
Cursor Declarations
END [];

Package Body
Implements the package specification
Fully defines cursors and subprograms
Holds implementation details and private declarations, hidden from the application
Can be thought of as a ‘black body’
Can be replaced, enhanced or replaced without changing the interface
Can be changed without recompiling calling programs
Scope of the declarations are local to the package body
Declared types and objects are inaccessible except from within the package body
Initialization part of a package is run only once, the first time the package is referenced

Syntax for Package Body –

CREATE [OR REPLACE] PACKAGE BODY AS
Private members (variables and procedure/functions/cursors/types)
Procedure Code;
Function Code;
Implementation of Types;
Use of Cursors;
Using Global variables in the members of the package.
END [];

Referencing Package Objects
Packaged objects and subprograms must be referenced using the dot notation
packagename.typename
packagename.objectname
packagename.subprogramname

E.g – DBMS_OUTPUT.PUT_LINE

Maintaining a Package
Can drop a package using the DROP command

DROP PACKAGE
Example
DROP PACKAGE airlines;
To drop just one construct, remove it from the package and then recompile the package

Examples of Packages –

1)          Creating a package of 3 procedures –

Package Specification –

create or replace package pk1 is
procedure x(a number);
procedure y(b number);
procedure z(c number);
end PK1;

Package Body –
create or replace package body pk1
is
procedure x(a number)
is
Begin
dbms_output.put_line(‘Procedure p1’);
End x;
procedure y(b number)
is
Begin
dbms_output.put_line(‘Procedure p2’);
End y;
/*Suppose in the package body if all the procedures are not written then it will give error.*/
/*procedure z(c number)
is
Begin
dbms_output.put_line(‘Procedure p3’);
End z; */
End pk1;

——————————

 Using the Package pk1-
SQL >  Execute PK1.X(4);

2)  Use of global variable in a function and procedure –

Package Specification –
create or replace package pk2
as
g number;
function m(a number) return number;
procedure n;
end pk2;

Package Body –

create or replace package body pk2
as
function m(a number) return number
is
Begin
g := a;
return g;
End m;

procedure n
is
Begin
if g >= 100 then
dbms_output.put_line(‘Discount is 20%’);
else
dbms_output.put_line(‘Discount is 10%’);
end if;
end n;
End pk2;

Using the package in a PL/SQL block –
Declare
x number;
Begin
x := pk2.m(700);
pk2.n;
End;

3)  Use of Type in a Procedure –
Package Specification –
create or replace package pk3 as
Type t1 is RECORD
(e1 Emp.Empno %Type,
e2 Emp.Ename%Type,
e3 Emp.Sal%Type);
Procedure p1;
end pk3;

Package Body –
create or replace package body pk3 as
procedure p1
is
v   t1; /*Using the type of the package directly inside the procedure.*/
Begin
select empno,ename,sal into v
from emp
where ename = ‘SMITH’;
dbms_output.put_line(v.e1 || ‘-‘ || v.e2 || ‘-‘ || v.e3);
End;
End pk3;
4)  Use of Cursor in  Procedure –

Package Specification –
create or replace package pk4
as
cursor cf is select * from emp
where job = ‘MANAGER’;
m cf%rowtype;
procedure CP;
End pk4;

Package Body –

create or replace package body pk4 as
procedure CP
is
Begin
Open cf;
Loop
fetch cf into m;

/*Showing the first entry of manager*/
if cf%rowcount = 1 then
dbms_output.put_line(m.empno || ‘-‘ || m.ename || ‘-‘ || m.sal);
else
exit;
end if;
End Loop;
Close cf;
End CP;
End pk4;

5) Example of a body less package  –
(Persistent state of variables)

create or replace package bodyless
is
x  number := 200;
y  number := 100;
/*Variables needs to be initialized if it is in a body less package*/
end;

begin
bodyless.x :=  bodyless.x 100;
dbms_output.put_line(‘Now the value of the x variable of bodyless package is ‘ || bodyless.x);
bodyless.y :=  bodyless.y 500;
dbms_output.put_line(‘Now the value of the y variable of bodyless package is ‘ || bodyless.y);
end;

/* Note– Execute the block 3-4 times, you will see that the values of x and y are getting changed.
But disconnect or close the session and start the session again.
The original values of the x and y from the package gets initialized.
*/

6) Example of private members in package body –

create or replace package prvmembers
is
procedure m1;
end;

create or replace package body prvmembers
is
/*Creating Private Procedure in the body*/
procedure m2
is
begin
dbms_output.put_line(‘This is the private member of the package body’);
end m2;

procedure m1
is
begin
m2;
end;
end;

Example of forward declaration of a private member

create or replace package body prvmembers
is
–procedure m2;       /* Forward declaration of m2 */
procedure m1
is
begin
m2;
end;

/*Creating Private Procedure in the body*/
procedure m2
is
begin
dbms_output.put_line(‘This is the private member of the package body’);
end m2;
end;

If the private member is not getting called in any global members then forward declaration is not required.
create or replace package body prvmembers
is
procedure m1
is
begin
dbms_output.put_line(‘abc’);
end;
/*Creating Private Procedure in the body*/
procedure m2
is
begin
dbms_output.put_line(‘This is the private member of the package body’);
end m2;
end;
/

4)       Example of one time only procedure (Procedure in package body)
Useful for setting remote connectivity for front ends.
In the session for the first time any of the members of the package is called then the procedure will be implicitly invoked.
Then for the future calls of the members of the same package in the same session the procedure will not get executed.

create or replace package pk1
is
procedure p1;
procedure p2;
end;

create or replace package body pk1
is
procedure p1
is
begin
dbms_output.put_line(‘p1’);
end p1;
procedure p2
is
begin
dbms_output.put_line(‘p2’);
end p2;
begin
dbms_output.put_line(‘Welcome to my package’);
end pk1;

create or replace package pk1
is
type namelist is table of emp.ename%type index by binary_integer;
v namelist;
cursor cf is select ename from emp;
M cf%rowtype;
procedure first_five;
procedure next_five;
end;

create or replace package body pk1
is
i Number;
procedure first_five
is
begin
for i in 1..5
loop
dbms_output.put_line(v(i));
end loop;
end first_five;
procedure next_five
is
begin
for i in 6..10
loop
dbms_output.put_line(v(i));
end loop;
end next_five;
begin
i := 0;
open cf;

loop
i :=  i 1;
fetch cf into M;
v(i) := M.ename;
exit when cf%notfound;
end loop;
end

To initialize the global variable by taking value from table            
create or replace package pk1
is
average_sal number;
function calc_sal_deviation(s number) return number;
procedure sal_status(s number);
end;

create or replace package body pk1
is
/*function calc_sal_deviation(s number) return number;
procedure sal_status(s number);*/
function calc_sal_deviation(s number) return number
is
begin
if average_sal > s then
return (average_sal – s);
else
return 0;
end if;
end;
procedure sal_status(s number)
is
begin
if average_sal > s then
dbms_output.put_line(‘On Lower Side’);
else
dbms_output.put_line(‘On Higher Side’);
end if;
end;
begin
select avg(sal) into average_sal
from emp;
end;

exec pk1.SAL_STATUS(800);
exec pk1.SAL_STATUS(4000);
select sal, pk1.CALC_SAL_DEVIATION(sal) “Deviation” from emp;

8) Example of Overloaded Members

create or replace package pk1
is
procedure p(a number, b number);
procedure p(x varchar2,y varchar2);
procedure p(z number);
procedure p;
end pk1;

create or replace package body pk1
is
procedure p(a number, b number)
is
c number;
begin
c := a b;
dbms_output.put_line(c);
end p;

procedure p(x varchar2, y varchar2)
is
begin
dbms_output.put_line(x || y);
end p;
procedure p(z number)
is
begin
if z > 0 then
dbms_output.put_line(‘The number is positive’);
else
dbms_output.put_line(‘The number is negative’);
end if;
end p;

  procedure p
is
begin
dbms_output.put_line(‘No parameter is passed’);
end p;
end pk1;

Example of overloaded members –
Parameter data types can be same, but names should be different.

create or replace package pk2
as
procedure p(salary number,  commission number);
procedure p(salary number, tax number);
end pk2;

create or replace package body pk2
as
procedure p(salary number, commission number)
is
total number;
begin
total := salary commission;
dbms_output.put_line(total);
end;
procedure p(salary number, tax number)
is
take_home number;
begin
take_home  := salary  – tax;
dbms_output.put_line(take_home);
end;
end pk2;

While executing such procedure the parameters need to be called by named method only.
exec pk2.p(salary => 4000, commission => 500);
exec pk2.p(salary => 4000, tax=> 200);

Example of Persistent Cursors –

create or replace package pc
is
cursor cf is select * from emp;
procedure pp1;
procedure pp2;
c cf%rowtype;
end ;

create or replace package body pc is
procedure pp1 is
begin
open cf;
loop
fetch cf into c;
dbms_output.put_line(c.empno||’-‘||c.ename||’-‘||c.sal);
exit when cf%rowcount >= 3;
end loop;
end pp1;
procedure pp2 is
begin
loop
fetch cf into c;
dbms_output.put_line(c.empno||’-‘||c.ename||’-‘||c.sal);
exit when cf%rowcount >= 6;
end loop;
close cf;
end pp2;
end;

PURITY LEVELS

PRAGMA RESTRICT_REFERENCES
WNDS   –   Write No Database State
RNDS   –    Read No Database State
WNPS  –   Write No Package State
RNPS   –   Read No Package State

Example of WNDS and RNDS

create table pt1(a number);
Insert into pt1 values(1);

Package Specification Code –

create or replace package pragma_package
as
function getTotal(commission in number) return number;
PRAGMA RESTRICT_REFERENCES (getTotal, WNDS, RNDS);
end;

Package Body Code –
create or replace package body pragma_package
as
function getTotal(commission in number)
return number
is
salary number := 0;
begin
select a into salary from pt1 where a = 1; — Throws error
return (commission salary);
end;
end;

PLS-00452: Subprogram ‘GETTOTAL’ violates its associated pragma

Example of WNPS
Package Code –

create or replace package purity_levels
as
pi number := 3.14;
function circle_area(radius number) return number;
PRAGMA RESTRICT_REFERENCES (circle_area, WNPS);
end;

Package Body code –

create or replace package body purity_levels
as
function circle_area(radius number)
return number
is
area number;
begin
pi := pi 5.4; — Throws error
area := pi *radius *radius;
return area;
end;
end;

Example of RNPS

Package Specification Code –

create or replace package purity_level1
as
selling_price number := 4000;
tax number := 50;
procedure cust_details;
PRAGMA RESTRICT_REFERENCES (cust_details, RNPS);
end;

Package Body Code —
create or replace package body purity_level1
as
procedure cust_details
is
begin
Insert into pt1 values(purity_level1.selling_price);
end;
end;

—————————–

Posted in UncategorizedTagged