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

PL/SQL Architecture

PL/SQL Architecture

A PL/SQL block contains both SQL statements and Procedural PL/SQL statements. The PL/SQL statements are executed by a PL/SQL execution engine, while the SQL statements are executed by the SQL Engine in the Oracle database.

The PL/SQL engine can reside either in the Oracle server or in a different server. When a PL/SQL block is submitted for execution, it first reached the PL/SQL engine. The PL/SQL engine executes the PL/SQL statements and sends the SQL statements to the SQL engine in the Oracle Database.

By submitting SQL and PL/SQL in blocks, we can reduce the number of round trips between the front end application and the Oracle database. Without PL/SQL we will have to perform the procedural and control statement execution in the front end and perform multiple round trips to the Oracle database to execute the SQL statements.