PL/ SQL Package

PL/ SQL Package

A PL/SQL Package is a container which holds a set of procedures, types, functions etc into a single unit. A PL/SQL package contains 2 parts.

Package specification.
Package body.

Package Specification contains the signature of the various procedures and functions which are implemented in the package; the spec does not contain any implementation code.

Package Body contains the actual implementation of the procedures and functions declared in the package specification, any procedure/ function implemented in the package body should be declared in the spec, failing which the compiler will throw an error.

Syntax
CREATE OR REPLACE
PACKAGE    
IS
   /* Declaration goes here */
END ;

CREATE OR REPLACE
PACKAGE BODY  
IS
BEGIN
   /* Implementation goes here */
END;
End ;

Now let us see an example of how to create and use a PL/SQL package.

Example
CREATE OR REPLACE
PACKAGE   MathPackage
IS
            Procedure ProcedureAdd(Param1 In Number
                   ,Param2   In Number
                   , Param3   Out Number
                   );

            Procedure ProcedureSub(Param1 In Number
                   ,Param2   In Number
                   , Param3   Out Number
                   );
End MathPackage;

CREATE OR REPLACE
PACKAGE  BODY  MathPackage IS 
           Procedure ProcedureAdd(Param1 In Number
                             ,Param2   In Number
                             , Param3   Out Number
                             )
           IS
           /* Variable Declaration goes here */
          BEGIN
                    Param3   := Param1 + Param2;
          END ProcedureAdd;

          Procedure ProcedureSub(Param1 In Number
                             ,Param2   In Number
                             , Param3   Out Number
                             )
           IS
           /* Variable Declaration goes here */
          BEGIN
                    Param3   := Param1 - Param2;
          END ProcedureSub;
End MathPackage;

Executing the Package
We have defined both the Package Specification and the Package Body, we shall now try to execute the procedure in the package and get the result. We can execute the package either from the SQL prompt or by using editors like TOAD or Oracle SQL Developer.

DECLARE
  var_Number1  NUMBER;
  var_Number2  NUMBER;
  var_Number3  NUMBER;
BEGIN
var_Number1 := 10;
var_Number2 := 15;

  MathPackage.ProcedureAdd(
    Param1  => var_Number1,
    Param2  => var_Number2,
    Param3  => var_Number3
  );

  DBMS_OUTPUT.PUT_LINE('Sum = ' || TO_CHAR(var_Number3));

END;

Output
Sum = 25