PL/SQL - SPLessons

PL/SQL Package

Chapter 27

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PL/SQL Package

PL/SQL Package

shape Description

PLSQL Package is a collection of functions and procedures. Once a package is defined, the function and procedure can be called by using the package name with Dot(.). And, dbms_output.put_line is a predefined package name.

A PLSQL Package will have 2 categories, they are:

Package declaration

shape Description

The PLSQL Package declaration is the interface to the package and declares constants, variables, cursors, subprograms and exceptions that can be referenced from outside the package.

The public objects are the objects that are placed inside the declaration. And, if any subprogram that doesn’t exist in the declaration of package, but, if the code exist in the package, then the code is said to be private object.

PLSQL Package Syntax

shape Syntax

Create or replace package is

Function Declaration

Procedure Declaration

End package_name;


Function declaration => Declaration a function.

Procedure declaration => Declaration a procedure.

End package_name => End of the package.

Package Examples

shape Examples

The below example describes the creation of package declaration:

SQL> create package cust_sal as
  2  procedure find_sal(c_id;
  3  end cust_sal;
  4  /
PL/SQL package is successfully created.

The above example will create the package declaration with cust_sal and package identifier as customer_id(c_id).

Package body

shape Description

The package body actualizes the package declaration. It encloses the usage of each sub program and cursor declared inside the package declaration. The sub programs characterized in a package body are attainable over an extreme end of the package if and only if the declaration simultaneously emerges in the package declaration.

Package Syntax

shape Syntax

Create or replace package body package_name is

Function Definition

Procedure Definition

End package_name;


Package_name => Name of the package

Function definition => Declaration of function

Procedure definition => Declaration of procedure

End package_name => End of the package body

PLSQL Package Examples

shape Examples

The below example describes the package body program.

SQL> create or replace package body pack2 is
  2  Function calcsum(a number,b number)
  3  return number is
  4  s number(10);
  5  begin
  6  s:a+b;
  7  returns;
  8  end calcsum;
  9  procedure calcmax(a IN number,b IN number,m OUT number)is
 10  begin
 11  if a>b then
 12  m:=a;
 13  else
 14  m:=b;
 15  end if;
 16  end calcmax;
 17  end pack1;
 18  /
Package body successfully created.
Select pack2.calcsum(10,20) from dual;
SQL>variable x number
SQL>execute pack2.calcmax(10,20:x)
SQL>print x;

In the above example, the package body is created with the name pack2 and calculates the sum value & maximum values.

Package advantages

shape Description

Package provides numerous advantages such as:

Better execution

The entire package is stacked in the memory while calling the package sub program.

Information hiding

Information hiding describes the item type in the subprograms both visible and invisible.

Included functionality

Cursors and open variables enclosed for the continuation of a session are shared and executed in every subprogram.

Measured quality

Package will encapsulate consistently related sorts such as subprograms and items in the module named PL/SQL. Every package is seen effectively and the interfaces among the package are well defined, and clear.

Easier application design

While designing an application all the interface information should be characterized inside the package declaration. And, without package body, one can code and execute the PL/SQL package.

Package alter

shape Description

The Alter package statement will explicitly recompile a package declaration and package body. Explicit recompilation wipes out the requirement for accurate standard run time recompilation and anticipates related run time arrangement errors & execution overhead.

Every single item inside the package will be stored in a different way in units. Alter package items will recompile the package statements together. It will change the work and adjust system articulation by performing recompilation announcements exclusively.

shape Syntax

ALTER PACKAGE[schema. ]package


Schema => Contains the schema object.
Package => Determines the term of the packages to be recompiled.
Compile => Is a key word for executing the program.
Package => Contains package specification and body.
Body => Specify to recompile the packages.
Specification => Analyse for compilation errors.

Package drop

shape Description

The package drop articulation will drop a stored package from the database. The package drop explanation will drop the declaration and body of a package.

shape Syntax

DROP PACKAGE[BODY][schema. ]package;

Drop => Is used to drop a package.
Package =>Contains functions and procedures.
Body => Specify to drop the packages.
Schema => Indicates the schema involving packages.

shape Examples

The below example describes the drop package.

SQL>DROP Package customer1
dropped customer1 successfully
SQL>select * from customer1;
No such table exists

The package customer1 is successfully dropped.


shape Key Points

  • PLSQL Package – It is a collection of functions and procedures.
  • Package declaration – Is the interface to the package.
  • Package body – It actualizes the declaration packages.
  • Package alter – Alters the declaration of package and package body.
  • Package drop – Will drop a stored package from the database.