PL SQL Packages

PL-SQL Packages – Learn how to declare variables, functions, procedures , pl-sql types , constants, exceptions, cursors in Package specification section and how to implement the logic in Package body section with example.

PL-SQL Packages

A package is like an interface in C#. Like interface, package groups logical declaration like variables , functions, procedures,pl-sql
types,variables,constants,exceptions,cursors into header section which can call as package specification.

Like interface implementation implementation done in class, Package implementation done in package body section, that defines the implementation part (code) for procedures, cursors, functions.

To create package specification, use the SQL statement CREATE PACKAGE

Syntax

CREATE [OR REPLACE] PACKAGE package_name

[ AUTHID { CURRENT_USER | DEFINER } ]
{ IS | AS }

[definitions of public TYPES
,declarations of public variables, types, and objects
,declarations of exceptions
,pragmas
,declarations of cursors, procedures, and functions
,headers of procedures and functions]

END [package_name];

Notes

• The CREATE PACKAGE statement  define a new package specification.
• To rebuild existing package you can use the REPLACE keyword instead of the CREATE keyword.
• In the package specification, you can define new types, declare global variables, types, objects, exceptions, cursors, procedures, and functions.

To create package Body, use the SQL statement CREATE PACKAGE BODY

CREATE [OR REPLACE] PACKAGE BODY package_name
   { IS | AS }

[definitions of private TYPEs
,declarations of private variables, types, and objects
,full definitions of cursors
,full definitions of procedures and functions]

[BEGIN
sequence_of_statements [EXCEPTION
exception_handlers ] ] END [package_name];

Notes

• The syntax is similar to  package specification except the keyword BODY and the implemented code of package specification.
• The package body can contain an optional package initialization section that appears at the end of the package body.
• The package initialization sections start with the BEGIN keyword and end with the EXCEPTION section or END keyword of the package.
• The package initialization section is executed when the application references to the package element at the first time.

PL-SQL PACKAGE Alteration.

You can update package code you just recompile the package body,
Package Alter Syntax 

ALTER PACKAGE package_name COMPILE BODY;

Recompile the already created/executed package code,

PL-SQL DROP PACKAGE

You can drop package using package DROP statement.

Package Drop Syntax :

DROP PACKAGE package_name;

drops the package that was created.

Advantages of PL/SQL Packages

Modularity-You can create package to store all related functions and procedures are grouped together into single unit called packages.
Information Hiding -Package is reliable to granting a privileges.
Added Functionality – Package are support overloading to overload functions and procedures.
Better Performance -Package  improves the performance of loading the multiple object into memory at once, therefore, subsequent calls to related program doesn’t required to calling physically I/O.
Package  reduce the traffic because all block execute all at once.

Example program to define PACKAGE specification

create or replace
PACKAGE PKG_CALCULATIONS
AS
 PROCEDURE ADD_PROC(A INT ,B INT ,C OUT INT );
 FUNCTION FACT_FUN(A IN INT  ) RETURN NUMBER;
END PKG_CALCULATIONS;

Example Program to define Package Body Implementation

create or replace
PACKAGE BODY PKG_CALCULATIONS
AS
PROCEDURE ADD_PROC(
    A INT ,
    B INT ,
    C OUT INT )
IS
BEGIN
  C :=A+B;
END;
FUNCTION FACT_FUN(
    A IN INT )
  RETURN NUMBER
IS
  FACT INT;
  I    NUMBER;
BEGIN
  I   :=1;
  FACT:=1;
  LOOP
        FACT:=FACT*I;
      EXIT
    WHEN I=A;
      I  :=I+1;
    END LOOP;
 RETURN FACT;   
END;
END PKG_CALCULATIONS;

Example program to call a function that implemented in package using anonymous block

DECLARE
   FACT INT;
BEGIN
  FACT:= PKG_CALCULATIONS.FACT_FUN(5);
  dbms_output.put_line('Factorial of Number 5 = '||FACT);
END;

Output
Factorial of Number 5 = 120

Example – program to call procedure and function from anonymous block

DECLARE
  A    NUMBER;
  B    NUMBER;
  C    NUMBER;
  FACT NUMBER;
BEGIN
  A := 4;
  B := 5;
  PKG_CALCULATIONS.ADD_PROC( A => A, B => B, C => C );
  /* Legacy output:
  DBMS_OUTPUT.PUT_LINE('SUM OF 2 NUMBERS = ' || C);
  */
  --:C := C;
  DBMS_OUTPUT.PUT_LINE('SUM OF 2 NUMBERS = ' || C);
  FACT:= PKG_CALCULATIONS.FACT_FUN(5);
  dbms_output.put_line('Factorial of Number 5 = '||FACT);
END;

Output
SUM OF 2 NUMBERS = 9
Factorial of Number 5 = 120

Example query to recompile package

ALTER PACKAGE PKG_CALCULATIONS COMPILE BODY

Example query to drop package

DROP PACKAGE PKG_CALCULATIONS;