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!

PL/SQL: Functions in Oracle

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

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Overview of Stored Functions

    A Function is a named PL/SQL subprogram, which when called from SQL or PL/SQL statement, mandatorily returns one and only one value. Functions are used to implement computation based logic. They are stored as schema objects in database. They promote reusability of logic and modularity.
    [​IMG]

    2. Syntax

    Code (Text):
    CREATE [OR REPLACE] FUNCTION [NAME][PARAMETER LIST] RETURN [DATA TYPE]
    [IS | AS]

    BEGIN
    STATEMENT 1;
    STATEMENT 2;

    RETURN [VALUE]
    STATEMENT N;
    END;
    REPLACE preserves the object privileges, during change in definition.
    Parameters are passed into Function in any of three modes IN, OUT, IN OUT.
    RETURN clause specifies the data type of the value returned by the function. A Function may contain multiple RETURN statements in the body, but must logically return a single value

    3. Procedures vs Functions

    [TABLE]Procedure Function
    Part of PL/SQL statement Part of an expression
    No RETURN in header RETURN keyword specified in header with the data type
    May or may not return values to host Must return single value to the host
    Executable section may or may not contain RETURN statement Executable section compulsorily contains one logical RETURN statement
    Objective is to store series of logical actions for serial execution Objective is to perform computational tasks and return result to the calling environment[/TABLE]

    Stored Function: Example

    Code (SQL):
    CREATE OR REPLACE FUNCTION F_AVG_SALARY RETURN NUMBER IS
     L_AVG NUMBER;
    BEGIN
     SELECT AVG(SALARY)
     INTO L_AVG
     FROM EMPLOYEE;
     
     RETURN L_AVG;
    END;
    4. Function Executing Methods

    1. Using DBMS_OUTPUT.PUT_LINE

    Code (SQL):
    SQL> EXECUTE   DBMS_OUTPUT.PUT_LINE(F_AVG_SALARY);
    2. Capturing function output in a variable

    Code (SQL):
    SQL> VARIABLE G_AVG NUMBER;

    SQL> EXEC :G_AVG := F_AVG_SALARY;
    PL/SQL PROCEDURE successfully completed.

    SQL> PRINT :G_AVG
    4500
     
    3. Calling within a PL/SQL block

    Code (SQL):
    DECLARE
     L_AVG NUMBER;
    BEGIN
    L_AVG := F_AVG_SALARY;
    DBMS_OUTPUT.PUT_LINE('Avg Salary:'||L_AVG);
    END;
    4. Calling from SQL statement

    Code (SQL):
    SQL> SELECT F_AVG_SALARY FROM DUAL;
    4500
    5. Calling User-Defined Functions in SQL Statements

    A user defined function can be called from WHERE, HAVING, ORDER BY and GROUP BY clause and in hierarchical query clauses i.e. START WITH and CONNECT BY clauses in a SELECT statement.

    Restrictions when calling functions from SQL Expressions:
    • Stored function must be in valid state
    • All parameter must be of IN mode and should be of SQL supported data type and not a BOOLEAN or COLLECTION type
    • Function must return value of SQL supported data type
    Below SELECT query lists the employee details whose salary is same as the average salary of the company.

    Code (SQL):
    SELECT EMPLOYEE_ID, ENAME, DEPARTMENT_ID, JOB_ID
    FROM EMPLOYEE
    WHERE SAL = F_AVG_SALARY;
    6. Calling functions from SQL

    A function called from a SQL must ensure the purity of database state. Below are the database states, which must be respected to ensure the purity level.
    • A function cannot make changes in tables, if called from SELECT query
    • A function cannot query a table, if called from INSERT, UPDATE or DELETE

    Example,

    Code (SQL):
    CREATE OR REPLACE FUNCTION F_CALC_SAL(P_RAISE_SAL NUMBER)
    RETURN NUMBER IS
       L_SAL NUMBER;
    BEGIN
       SELECT SALARY + P_RAISE_SAL
       INTO L_SAL
       FROM EMPLOYEE
       WHERE EMPLOYEE_ID = 170;
       
       RETURN (L_SAL);
     END;
    Below UPDATE statement calls above function F_CALC_SAL to update the incremented salary. It violates the second point as it queries EMPLOYEE table which is getting updated in below statement.

    Code (SQL):
    UPDATE EMPLOYEE
    SET SALARY = F_CALC_SAL(100)
    WHERE EMPLOYEE_ID = 170;

    UPDATE EMPLOYEE
           *
    ERROR at line 1:
    ORA-04091: TABLE SCOTT.EMPLOYEE IS mutating, TRIGGER/FUNCTION may NOT see it
    ORA-06512: at "SCOTT.F_CALC_SAL", line 5
    7. Removing Functions using the DROP statement

    A function can be removed from the schema using DROP command.

    Syntax:

    Code (Text):
    DROP FUNCTION [function name]
    Example,

    Code (SQL):
    SQL> DROP FUNCTION F_AVG_SALARY;

    FUNCTION dropped.
     

    Attached Files:

    • FUN.JPG
      FUN.JPG
      File size:
      24.1 KB
      Views:
      6,762