What are PL SQL packages ? | PL SQL packages examples

In my previous article I have already given the information about the functions in PL SQL and stored procedure in PL SQL with its real life examples. One of the most important concept which is heavily used is PL SQL packages. In this article we want to give the details about PL SQL packages with example. I would like to explain detailed level information about packages with real life examples. We can encapsulate the multiple block structures of PL SQL with using the packages.

What you will see in this article?

What are PL SQL packages with examples?

What are Advantages of Packages?

What is Syntax and guidelines to use the packages?

What are PL SQL packages with examples?

The PL SQL packages are nothing but the database objects which is nothing but collection of multiple database objects bind together to achieve a specific purpose. These are group of logically related data items those are nothing but types, variables and subprograms.

The PL SQL packages always have two parts :

1. Package Specification
2. Package body

Package Specification : The PL SQL package specification is nothing but the skeleton of the package where you can mention the declaration of the types, variables, constants, exceptions, cursors and subprograms. These can be referenced from outside package.

Syntax for package specification :

Create or Replace Package package_name is/as

Public type and variable declaration;

subprogram specification;

End package_name;

1.The create or replace word will drop the package specification and re-create the new one.

2.Variables declared in package specification are initialized to NULL value by DEFAULT.

3.All constructs are declared in package specification.

Package body : The package body is nothing but the actual code of cursors or subprograms which needs to be encapsulated to achieve the specific purpose or business logic. It enables the oracle server to read multiple objects at once. The package body is nothing but the actual business logic written in code.

Create or Replace package body package_name is/as

All business code for variable declaration and subprograms exact code;

Begin

Business Logic;

End;

The package body will drop the package body and recreate the new one.

The identifiers defined is package body is private and it is not visible outside body.

Public constructs are visible to package body.

What are advantages of using packages?

In this section I would like to discuss about the multiple advantages of using packages . There are following advantages of packages :

1. Encapsulation : The encapsulation is best advantage of using PL SQL packages. You can encapsulate the multiple business logics together.
2. Reusability : You can reuse the package business logic again and again whenever necessary.
3. Easily maintainace : You can easily do the maintainace of the PL SQL package code.
4. Easy application design : There is package specification and package body so you can easily do the application design.
5. Hiding the important business logic : The key use of packages is to hide the business logic. Only package specification is visible and accessible to applications. The private construct in package body are hidden and inaccessible. The coding is hidden in package body. So the key advantage of packages are used for security.

How to Write the packages in PL SQL?

In this section I would like to give you some steps to write the packages in PL SQL :

1. You have to develop the packages for general purpose use.
2. Kindly define package specification before package body.
3. The package specification should contain only those constructs that you want to be public.
4. Package specification should contain as less as possible construct.
5. The dependency management reduces the need to recompile the referencing subprograms when package specification changes.
6. Just remember that when your subprograms are invalid the package also become invalid.

Real life example :

If we want to create a package to calculate area of different figures like circle,rectangle,square etc.

-First step is to create different functions for package

create or replace function F_square_area
( p_side number )
return number
is
begin

return p_side*p_side;
end;

How to call function ?

select F_square_area(4) from dual;

---

—-Create a function to calculate area of rectangle
create or replace function F_rectangle_area
( p_l number,p_w number )
return number
is
begin

return p_l*p_w;
end;

select F_rectangle_area(4) from dual;

–Now because theses 2 functions are logically grouped,
–it is better to use package
–The code will be more organized

create or replace package PKG_area
is

function F_square_area( p_side number )
return number;

function F_rectangle_area( p_l number,p_w number )
return number;

–we dont have begin in package specification
end;

create or replace package body area
is
function square_area( p_side number )
return number
is
begin
return p_side*p_side;
end;

select PKG_area.F_square_area(5) from dual;

select PKG_area.F_rectangle_area(5,6) from dual;

Package calling :

begin
DBMS_OUTPUT.PUT_LINE(PKG_area.F_square_area(4));
end;

The above is easiest way to call the package. Hope you like this article on packages. If you want more examples regarding the packages you can comment in comments section or send me mail on complexsql@gmail.com.