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,

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;    
/

Result

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.

PL/SQL Package Alter

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

Syntax

ALTER PACKAGE package_name COMPILE BODY; 

Recompile the already created/executed package code,

Example

PL/SQL Package Drop

You can drop package using package DROP statement,

Syntax

DROP PACKAGE package_name; 

Example

• « Previous
• Next »