1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Stored Procedures in PL/SQL

Discussion in 'SQL PL/SQL' started by SBH, Dec 15, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Introduction

    A Procedure is a named PL/SQL subprogram that can be optionally parameterized and is used to implement business logic. It is permanently stored in the database as schema object and can be invoked any number of times on specified set of data.

    Advantages of procedures
    • Maintain the business layer
    • Promote reusability of business logic and reduce code redundancy

    2. Syntax

    Code (Text):
    CREATE [OR REPLACE] PROCEDURE [NAME][(parameter)]
    [IS | AS]
    <Declare Section>
    BEGIN
     Statement1……
     Statement2……
     StatementN……
    END [procedure name];
    REPLACE preserves the object privileges upon the redefinition of the object.

    3. Notes

    • User must have CREATE [ANY] PROCEDURE privilege to create a stored procedure.
    • Parameters are declared in the subprogram header, after the name and before the declaration section for local variables.

    Code (Text):
    PARAMETER1 [IN | OUT | IN OUT] [DATA TYPE] [DEFAULT],
    Parameters can be passed to a subprogram in three modes
    • IN – It is the default pass mode.
    • OUT - Used as assignment variable
    • IN OUT - Used as assignment variable

    Example

    Code (SQL):
    CREATE OR REPLACE PROCEDURE P_UPD_JOBID(P_EMPID NUMBER,
                          P_JOB VARCHAR2)
    IS
    BEGIN
    UPDATE EMPLOYEE
    SET JOB_ID = P_JOB
    WHERE EMPLOYEE_ID=P_EMPID;
    END;
    /
    4. Execution of the procedure

    In SQL* Plus, procedures can be executed using EXECUTE command.

    Code (SQL):
    SQL> EXEC P_UPD_JOBID(100, 'HR');

    PL/SQL PROCEDURE successfully completed.
    It can be invoked as PL/SQL statement inside an anonymous PL/SQL block.

    Code (SQL):
    BEGIN
    .
    P_UPD_JOBID(100,'HR');
    .
    END;
    Using the OUT Parameters: Example

    Below procedure P_GET_SALARY returns the salary of an employee as OUT parameter.

    Code (SQL):
    CREATE OR REPLACE PROCEDURE P_GET_SALARY
    (P_EMPID IN NUMBER,
     P_SAL OUT NUMBER)
    IS
    BEGIN
     SELECT SALARY
     INTO P_SAL
     FROM EMPLOYEE
     WHERE EMPID=P_EMPID;
    END;
    /
    Executing the above procedure from SQL* Plus requires a bind value to capture the OUT parameter result. Note that it must of data type compatible with the OUT parameter.

    Code (SQL):
    SQL> VARIABLE G_SAL NUMBER;
    Now, the program must be executed giving actual parameters.

    Code (SQL):
    SQL> EXEC P_GET_SALARY(20,:G_SAL);

    PL/SQL PROCEDURE successfully completed.
    Please note that the actual parameters are fixed values. They can be passed through session bind variables, but need to be declared and assigned in SQL* Plus.

    Code (SQL):
    SQL> PRINT G_SAL
    2500
    5. Syntax for Passing Parameters

    Actual Parameters can be passed following three ways
    • Positional
    • Named
    • Mixed

    Positional: All the actual parameters are listed in the same sequence as formal parameters. Error is thrown, if there is any change in the parameter sequence.
    Example: P_UPD_JOB (100, 'HR');

    Named: Each format parameter is associated with the actual parameter using ‘=>’. Order may or may not be maintained. It is easy to maintain and convenient to read also.
    Example: P_UPD_JOB (P_EMPID => 100, P_JOB => 'HR');

    Mixed: This is combination of positional and named ways of passing parameters. Proper flow of notation must be maintained while passing the parameters.
    Example: P_UPD_JOB (100, P_JOB => 'HR');

    6. DEFAULT values for parameters

    Parameters can be assigned with default values during procedure definition. If procedure is invoked without passing any actual value, oracle takes the default value and resumes the execution of the procedure. Note that it can be assigned only to IN mode parameters.

    Default value can be assigned in two ways

    • The assignment operator :)=)
    • The DEFAULT keyword option

    It is suggested from coding point of view to have parameters with default value must at the end of the parameters listing.

    For example, above procedure P_UPD_JOB is redefined with P_JOB carrying a default value as below.

    Code (SQL):
    CREATE OR REPLACE PROCEDURE P_UPD_JOBID
    (P_EMPID IN NUMBER, P_JOB IN VARCHAR2 DEFAULT 'DEV')


    END;
    Above procedure takes two parameters department id and job id and updates in the EMP table. It can be invoked in below ways.

    Code (SQL):
    SQL> EXEC P_UPD_JOBID(20);
    Above call assigns 20 to P_DEPT_ID and takes default value of P_JOBID

    Code (SQL):
    SQL> EXEC P_UPD_JOBID (30, 'HR');
    Above call assigns actual value to both the parameters.

    7. Removing Procedures

    A stored procedure can be dropped from database using DROP command. Note that all dependent objects are rendered to INVALID state.

    Syntax:
    Code (Text):
    DROP PROCEDURE [procedure name]