Scribd Logo
Upload

Welcome to Scribd!

  • 77 views

    Introducing Stored Procedures and Functions

    Uploaded by

    Issa Samb

    Copyright:

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

    Introducing Stored Procedures and Functions

    Uploaded by

    Issa Samb
    77 views18 pages

    Original Title

    D80182GC10_les10

    Copyright

    © © All Rights Reserved

    Available Formats

    PPT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

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

    Introducing Stored Procedures and Functions

    Uploaded by

    Issa Samb

    Copyright:

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

    10

    Introducing Stored Procedures and Functions

    Copyright © 2013, Oracle and/or its affiliates. All rights reserved.


    Objectives

    After completing this lesson, you should be able to do the


    following:
    • Differentiate between anonymous blocks and subprograms
    • Create a simple procedure and invoke it from an
    anonymous block
    • Create a simple function
    • Create a simple function that accepts a parameter
    • Differentiate between procedures and functions

    10 - 2 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.


    Agenda

    • Introducing procedures and functions


    • Previewing procedures
    • Previewing functions

    10 - 3 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.


    Procedures and Functions

    • Are named PL/SQL blocks


    • Are called PL/SQL subprograms
    • Have block structures similar to anonymous blocks:
    – Optional declarative section (without the DECLARE keyword)
    – Mandatory executable section
    – Optional section to handle exceptions

    10 - 4 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.


    Differences Between Anonymous
    Blocks and Subprograms

    Anonymous Blocks Subprograms


    Unnamed PL/SQL blocks Named PL/SQL blocks
    Compiled every time Compiled only once
    Not stored in the database Stored in the database

    Cannot be invoked by other Named and, therefore, can be invoked by


    applications other applications
    Do not return values If functions, must return values

    Cannot take parameters Can take parameters

    10 - 5 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.


    Agenda

    • Introducing procedures and functions


    • Previewing procedures
    • Previewing functions

    10 - 6 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.


    Procedure: Syntax

    CREATE [OR REPLACE] PROCEDURE procedure_name


    [(argument1 [mode1] datatype1,
    argument2 [mode2] datatype2,
    . . .)]
    IS|AS
    procedure_body;

    10 - 7 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.


    Creating a Procedure

    ...
    CREATE TABLE dept AS SELECT * FROM departments;
    CREATE PROCEDURE add_dept IS
    v_dept_id dept.department_id%TYPE;
    v_dept_name dept.department_name%TYPE;
    BEGIN
    v_dept_id:=280;
    v_dept_name:='ST-Curriculum';
    INSERT INTO dept(department_id,department_name)
    VALUES(v_dept_id,v_dept_name);
    DBMS_OUTPUT.PUT_LINE(' Inserted '|| SQL%ROWCOUNT
    ||' row ');
    END;

    10 - 8 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.


    Invoking a Procedure

    ...
    BEGIN
    add_dept;
    END;
    /
    SELECT department_id, department_name FROM dept
    WHERE department_id=280;

    10 - 10 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.


    Agenda

    • Introducing procedures and functions


    • Previewing procedures
    • Previewing functions

    10 - 11 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.


    Function: Syntax

    CREATE [OR REPLACE] FUNCTION function_name


    [(argument1 [mode1] datatype1,
    argument2 [mode2] datatype2,
    . . .)]
    RETURN datatype
    IS|AS
    function_body;

    10 - 12 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.


    Creating a Function
    CREATE FUNCTION check_sal RETURN Boolean IS
    v_dept_id employees.department_id%TYPE;
    v_empno employees.employee_id%TYPE;
    v_sal employees.salary%TYPE;
    v_avg_sal employees.salary%TYPE;
    BEGIN
    v_empno:=205;
    SELECT salary,department_id INTO v_sal,v_dept_id FROM
    employees
    WHERE employee_id= v_empno;
    SELECT avg(salary) INTO v_avg_sal FROM employees WHERE
    department_id=v_dept_id;
    IF v_sal > v_avg_sal THEN
    RETURN TRUE;
    ELSE
    RETURN FALSE;
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RETURN NULL;
    END;

    10 - 13 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.


    Invoking a Function

    BEGIN
    IF (check_sal IS NULL) THEN
    DBMS_OUTPUT.PUT_LINE('The function returned
    NULL due to exception');
    ELSIF (check_sal) THEN
    DBMS_OUTPUT.PUT_LINE('Salary > average');
    ELSE
    DBMS_OUTPUT.PUT_LINE('Salary < average');
    END IF;
    END;
    /

    10 - 14 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.


    Passing a Parameter to the Function

    DROP FUNCTION check_sal;


    CREATE FUNCTION check_sal(p_empno employees.employee_id%TYPE)
    RETURN Boolean IS
    v_dept_id employees.department_id%TYPE;
    v_sal employees.salary%TYPE;
    v_avg_sal employees.salary%TYPE;
    BEGIN
    SELECT salary,department_id INTO v_sal,v_dept_id FROM employees
    WHERE employee_id=p_empno;
    SELECT avg(salary) INTO v_avg_sal FROM employees
    WHERE department_id=v_dept_id;
    IF v_sal > v_avg_sal THEN
    RETURN TRUE;
    ELSE
    RETURN FALSE;
    END IF;
    EXCEPTION
    ...

    10 - 15 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.


    Invoking the Function with a Parameter
    BEGIN
    DBMS_OUTPUT.PUT_LINE('Checking for employee with id 205');
    IF (check_sal(205) IS NULL) THEN
    DBMS_OUTPUT.PUT_LINE('The function returned
    NULL due to exception');
    ELSIF (check_sal(205)) THEN
    DBMS_OUTPUT.PUT_LINE('Salary > average');
    ELSE
    DBMS_OUTPUT.PUT_LINE('Salary < average');
    END IF;
    DBMS_OUTPUT.PUT_LINE('Checking for employee with id 70');
    IF (check_sal(70) IS NULL) THEN
    DBMS_OUTPUT.PUT_LINE('The function returned
    NULL due to exception');
    ELSIF (check_sal(70)) THEN
    ...
    END IF;
    END;
    /

    10 - 16 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.


    Quiz

    Subprograms:
    a. Are named PL/SQL blocks and can be invoked by other
    applications
    b. Are compiled only once
    c. Are stored in the database
    d. Do not have to return values if they are functions
    e. Can take parameters

    10 - 17 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.


    Summary

    In this lesson, you should have learned to:


    • Create a simple procedure
    • Invoke the procedure from an anonymous block
    • Create a simple function
    • Create a simple function that accepts parameters
    • Invoke the function from an anonymous block

    10 - 18 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.


    Practice 10: Overview

    This practice covers the following topics:


    • Converting an existing anonymous block to a procedure
    • Modifying the procedure to accept a parameter
    • Writing an anonymous block to invoke the procedure

    10 - 19 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

    You might also like