Scribd Logo
Upload

Welcome to Scribd!

  • 174 views

    Stored Procedures

    Uploaded by

    Surya Vivek
    This document discusses stored procedures in PL/SQL. It defines a stored procedure as a type of subprogram that can be stored in a database for repeated execution, providing reusability and a programming environment. It provides the structure for creating procedures, including parameter lists, and examples of creating procedures with different parameter modes. It also demonstrates how to call a stored procedure and remove one. Stored procedures allow reusable code blocks to be stored in a database for repeated execution from functions, triggers, and select statements.

    Copyright:

    © All Rights Reserved
    Download as PPT, PDF, TXT or read online from Scribd

    Stored Procedures

    Uploaded by

    Surya Vivek
    174 views15 pages
    This document discusses stored procedures in PL/SQL. It defines a stored procedure as a type of subprogram that can be stored in a database for repeated execution, providing reusability and a programming environment. It provides the structure for creating procedures, including parameter lists, and examples of creating procedures with different parameter modes. It also demonstrates how to call a stored procedure and remove one. Stored procedures allow reusable code blocks to be stored in a database for repeated execution from functions, triggers, and select statements.

    Original Description:

    stored procedures of sql

    Original Title

    stored procedures

    Copyright

    © © All Rights Reserved

    Available Formats

    PPT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document discusses stored procedures in PL/SQL. It defines a stored procedure as a type of subprogram that can be stored in a database for repeated execution, providing reusability and a programming environment. It provides the structure for creating procedures, including parameter lists, and examples of creating procedures with different parameter modes. It also demonstrates how to call a stored procedure and remove one. Stored procedures allow reusable code blocks to be stored in a database for repeated execution from functions, triggers, and select statements.

    Copyright:

    © All Rights Reserved
    Download as PPT, PDF, TXT or read online from Scribd
    Download as ppt, pdf, or txt
    174 views15 pages

    Stored Procedures

    Uploaded by

    Surya Vivek
    This document discusses stored procedures in PL/SQL. It defines a stored procedure as a type of subprogram that can be stored in a database for repeated execution, providing reusability and a programming environment. It provides the structure for creating procedures, including parameter lists, and examples of creating procedures with different parameter modes. It also demonstrates how to call a stored procedure and remove one. Stored procedures allow reusable code blocks to be stored in a database for repeated execution from functions, triggers, and select statements.

    Copyright:

    © All Rights Reserved
    Download as PPT, PDF, TXT or read online from Scribd
    Download as ppt, pdf, or txt
    of 15

    PL/SQL: Stored

    Procedures

    Presenter: Surya Vivek.B


    [email protected]

    Objectives
    Uses

    of procedures
    Create procedures
    Features of parameter modes
    Procedures with parameters
    Invoke a procedure
    Remove a procedure
    Examples

    Stored Procedures

    What is stored procedure?

    A procedure is a type of subprogram that performs an action.


    A procedure can be stored in the database, as a schema object,
    for repeated execution.

    Advantages

    Reusability: do not need to write the code again and again


    Programming language-like environment

    Assignment, Loop, For, IF statements

    Call it whenever needed

    From select statement, another procedure, or another function

    Creating A Stored Procedure


    If exists, then drop it and create it again

    IS or AS both are valid

    CREATE [OR REPLACE] PROCEDURE <procedureName> (<paramList>) [IS| AS]


    <localDeclarations>
    Begin
    <procedureBody>;
    End;
    /

    A parameter in the paramList is specified as:


    <name> <mode> <type>
    Mode:
    IN input parameter (default)
    OUT output parameter
    INOUT input and output parameter
    cs3431

    Creating Procedures with


    Parameters

    General Structure
    CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]
    [IS | AS]
    [declaration_section]
    BEGIN
    executable_section
    [EXCEPTION
    exception_section]
    END [procedure_name];

    Optional section for exception handling

    Example I
    Define a variable

    By default, it is IN

    You can use the procedure name


    before the parameter name

    In PL/SQL a ; ends a line without execution


    Execute the command and
    create the procedure

    Developing Procedures

    Calling a Stored Procedure

    SQL> exec <procedureName> [(<paramList>)];

    SQL > exec remove_emp (10);

    EXAMPLE - II
    CREATE OR REPLACE PROCEDURE raise_salary
    (p_id IN employees.employee_id%TYPE)
    IS
    BEGIN
    UPDATE employees SET salary = salary * 1.10
    WHERE employee_id = p_id;
    END raise_salary;
    /
    To invoke a procedure in iSQL*Plus, use the EXECUTE command.
    EXECUTE raise_salary (176)
    To invoke a procedure from another procedure, use a direct call. At the location of
    calling the new procedure, enter the procedure name and actual parameters.
    raise_salary (176);

    Example - III
    CREATE OR REPLACE PROCEDURE query_emp
    ( p_id IN employees.employee_id%TYPE,
    p_name OUT employees.last_name%TYPE,
    p_salary OUT employees.salary%TYPE,
    p_comm OUT employees.commission_pct%TYPE)
    IS
    BEGIN
    SELECT last_name, salary, commission_pct
    INTO p_name, p_salary, p_comm
    FROM employees
    WHERE employee_id = p_id;
    END query_emp;
    /

    Procedure created

    continue.

    Load and run the emp_query.sql script file to create the


    QUERY_EMP procedure.
    Declare host variables, execute the QUERY_EMP
    procedure, and print the value of the global G_NAME variable.
    VARIABLE g_name VARCHAR2(25)
    VARIABLE g_sal NUMBER
    VARIABLE g_comm NUMBER
    EXECUTE query_emp(171, :g_name, :g_sal, :g_comm)

    Removing Procedures
    Drop a procedure stored in the database.
    Syntax:

    Summary of Stored Procedures

    Code modules that are stored inside the DBMS

    Used and called repeatedly

    Powerful programing language style

    Can be called from other procedures, functions, triggers, or


    from select statement (only functions)

    THANK YOU

    Presenter: Surya Vivek.B


    [email protected]

    You might also like