PL/SQL Packages

PL/SQL Packages is schema object and collection of related data type (variables, constants), cursors, procedures, functions are defining within a single context. Package are divide into two part,

  1. Package Specification
  2. Package Body

Package specification block you can define variables, constants, exceptions and package body you can create procedure, function, subprogram.

PL/SQL Package Advantages

  1. You can create package to store all related functions and procedures are grouped together into single unit called packages.
  2. Package are reliable to granting a privileges.
  3. All function and procedure within a package can share variable among them.
  4. Package are support overloading to overload functions and procedures.
  5. Package are improve the performance to loading the multiple object into memory at once, therefore, subsequent calls to related program doesn't required to calling physically I/O.
  6. Package are reduce the traffic because all block execute all at once.

PL/SQL Package Syntax

PL/SQL Specification: This contain the list of variables, constants, functions, procedure names which are the part of the package. PL/SQL specification are public declaration and visible to a program.

Defining Package Specification Syntax

 CREATE [OR REPLACE] PACKAGE package_name     IS | AS     [variable_declaration ...]     [constant_declaration ...]     [exception_declaration ...]      [cursor_specification ...]     [PROCEDURE [Schema..] procedure_name         [ (parameter {IN,OUT,IN OUT} datatype [,parameter]) ]     ]     [FUNCTION [Schema..] function_name         [ (parameter {IN,OUT,IN OUT} datatype [,parameter]) ]         RETURN return_datatype     ] END [package_name];

PL/SQL Body: This contains the actual PL/SQL statement code implementing the logics of functions, procedures which are you already before declare in "Package specification".

Creating Package Body Syntax

 CREATE [OR REPLACE] PACKAGE BODY package_name     IS | AS     [private_variable_declaration ...]     [private_constant_declaration ...]     BEGIN         [initialization_statement]         [PROCEDURE [Schema..] procedure_name             [ (parameter [,parameter]) ]             IS | AS                 variable declarations;                 constant declarations;                               BEGIN                 statement(s);             EXCEPTION                 WHEN ...             END         ]         [FUNCTION  [Schema..] function_name                 [ (parameter [,parameter]) ]                 RETURN return_datatype             IS | AS                 variable declarations;                 constant declarations;                       BEGIN                 statement(s);             EXCEPTION                 WHEN ...             END         ]     [EXCEPTION          WHEN built-in_exception_name_1 THEN             User defined statement (action) will be taken;     ]    END; /

PL/SQL Package Example

PL/SQL Package example step by step explain to you, you are create your own package using this reference example. We have emp1 table having employee information,

1 Forbs ross Web Developer 45k
2 marks jems Program Developer 38k
3 Saulin Program Developer 34k
4 Zenia Sroll Web Developer 42k

Package Specification

Create Package specification code for defining procedure, function IN or OUT parameter and execute package specification program.

 CREATE or REPLACE PACKAGE pkg1     IS | AS     PROCEDURE pro1         (no in number, name out varchar2);     FUNCTION fun1         (no in number)         RETURN varchar2;     END;                     /

Package Body

Create Package body code for implementing procedure or function that are defined package specification. Once you implement execute this program.

 CREATE or REPLACE PACKAGE BODY pkg1 IS     PROCEDURE pro1(no in number,info our varchar2)         IS         BEGIN             SELECT * INTO temp FROM emp1 WHERE eno = no;         END;          FUNCTION fun1(no in number) return varchar2         IS         name varchar2(20);         BEGIN             SELECT ename INTO name FROM emp1 WHERE eno = no;             RETURN name;         END; END; /

Pl/SQL Program calling Package

Now we have a one package pkg1, to call package defined function, procedures also pass the parameter and get the return result.

 DECLARE     no number := &no;     name varchar2(20); BEGIN     pkg1.pro1(no,info);     dbms_output.put_line('Procedure Result');     dbms_output.put_line(info.eno||'     '||                          info.ename||'   '||                          info.edept||'   '||                          info.esalary||' '||);     dbms_output.put_line('Function Result');                              name := pkg1.fun1(no);     dbms_output.put_line(name); END;     /


Now execute the above created pkg_prg.sql program to asking which user information you want to get, you put user id and give information.

no number &n=2
Procedure Result
2    marks jems    Program Developer    38K
Function Result
marks jems

PL/SQL procedure successfully completed.

PL/SQL Package Alter

You can update package code you just recompile the package body,



Recompile the already created/executed package code,



Package body Altered.

PL/SQL Package Drop

You can drop package using package DROP statement,


 DROP PACKAGE package_name; 



Package dropped.