PLSQL Package

Good programming practice involves organizing coding elements and reusing the existing codes for minimizing efforts and reducing project development duration. For this, grouping the procedures and functions is one of the best ways of coding. PLSQL provides the capability of creating PLSQL package wherein the programmers can create a logical group called package for storing PL/SQL functions, procedures, global variables and cursors to be used in the front end coding tools and programs.

Creating packages has the advantage that once the package is created, compiled and saved in the Shared Global Area, subsequent calls to its elements or objects will be time saving. The logical organization of individual code blocks makes it easier to call and use them.

Another advantage is that you can impose security on your code as the procedures and functions of a package can only be used if a user has been assigned rights on it. You can grant permission to users on your created package.  Encapsulation and data hiding can be implemented even after PLSQL is not Object Oriented Programming Language.

A PLSQL package is created in two steps-

Define Package Specification

In this step you need to define the contents of the package. Here you just specify what the package will have. It is basically declaration of the procedures, functions, global variables and cursors which can be used by including a package in other PLSQL blocks. It tells what all its contents will be without informing how it will be done. In other words package specification contains no details about code of the functions, procedure or cursors.

CREATE OR REPLACE PACKAGE  packageName
IS 
[ Declare  variables and types ] 
[Specify the Cursors] 
[ Specify the Modules ] 
END [packageName ];

Whatever you include in the package specification is public and can be called with the name of the package. A package specification is the information required by a developer to call a function or procedure or use the variables or the cursors.

Define Package Body

In this step you specify what the package elements will do.  If package specification is declaration, then package body is the implementation of the declared objects of the package specification. The procedures and functions declared in the specification get their executable code in the package body. Package body will tell how it is done for the functions and procedures.

If you create a procedure or function in body that is not declared in package specification, such an object becomes a private object. Such a private object can be called in other public procedures and functions. It also means that you can create and use private variables and cursors for your functions and procedures coded in the package body.

CREATE OR REPLACE PACKAGE BODY packageName 
IS 
[declare  variables and types ] 
[specify the SELECT statement of cursors ] 
[specify details  and code details of modules ]
 [BEGIN executable statements ]
 [EXCEPTION exception block ] 
END [ packageName ];

Rules for creating PLSQL Packages

• For each package body there must be a corresponding package specification.
• The cursors, procedures and functions headers must be exactly matched in both package Specification and Body.
• Variables, Exceptions, Data types or Constants declared in specification must not be re-declared in package body.
• If you need private elements for your package, those must be defined in only in the Package Body.
• To refer elements of one package in another package, call them the name of the container package.

Calling the PLSQL Package objects

The package objects or elements can be called by specifying the name of the object with name of the package joined by . (dot ).

packageName.ObjectName  

PLSQL Package Example

Data Table used

Package Specification

create or replace package my_pkg
as
procedure disp_book_by_Author(aname IN books.AUTHOR%type);
function count_book_by_type(btyp IN books.BTYPE%type) return number;
end my_pkg;

Package Body

create or replace package body my_pkg
as
procedure disp_book_by_Author(aname IN books.AUTHOR%type)
is
v_bt books.title%type;
v_bp books.price%type;
CURSOR br IS SELECT title, price from books where AUTHOR=aname;
begin
OPEN br;
FETCH br INTO v_bt, v_bp;
WHILE br%FOUND LOOP
    dbms_output.put_line(v_bt||'   '||v_bp);
    FETCH br INTO v_bt, v_bp;
END LOOP; 
end disp_book_by_Author;

function count_book_by_type(btyp IN books.BTYPE%type) return number
is
cnt number;
begin
select count(id) into cnt from books where btype=btyp;
RETURN cnt;
end count_book_by_type;
end my_pkg;

Calling the package elements in a PLSQL block

declare
counter number;
begin
my_pkg.disp_book_by_Author('Sir Quackalot');
counter:=my_pkg.count_book_by_type('ebook');
dbms_output.put_line('Count of Books of type ebook '||counter);
end;

admin