What is Function and its examples

Function:-
            Function is named PL/SQL block. It must return a single value. We can call function in SELECT Statement.
           
Syntax:-
        CREATE OR REPLACE FUNCTION function_name
        RETURN data_type
        IS
        BEGIN
            STATEMENT;           
            RETURN (value/variables);
        END function_name;
   
After creation of FUNCTION, we call it for the output.

There are many different ways for execute the procedure
   
1.
    SELECT function_name FROM table_name;

2.   
    DECLARE
    variable_name data_type;
    BEGIN
        variable:= function_name;
    END;   
   
We can use different parameter mode in function

1. IN
2. OUT
3. IN OUT
4. OUT NOCOPY
5. IN OUT NOCOPY
   
Note:- *a) If we use OUT, IN OUT mode in function's parameter, we can not call that function in
                 SELECT statement.
             b) By default parameter mode is IN.
             c) NOCPOY pass the reference of variable not value. NOCOPY use only with OUT and
                 IN OUT mode.
       
Examples:-

1.    

    CREATE OR REPLACE FUNCTION XXC05_FUNC
    RETURN NUMBER
    IS
     ID NUMBER;
    BEGIN
        ID:=100;
    RETURN 100;
    END XXC05_FUNC;
   
   
    SELECT XXC05_FUNC FROM DUAL;
   
   
2.

    CREATE OR REPLACE FUNCTION XXC05_FUNC
    RETURN NUMBER
    IS
     L_SAL NUMBER;
    BEGIN
        SELECT SALARY
        INTO L_SAL
        FROM EMPLOYEES
        WHERE EMPLOYEE_ID=199;
    RETURN L_SAL;
    END XXC05_FUNC;
   
    SELECT XXC05_FUNC FROM DUAL;
   
   
3.

    CREATE OR REPLACE FUNCTION XXC05_FUNC(P_ID NUMBER)
    RETURN NUMBER
    IS
     L_SAL NUMBER;
    BEGIN
        SELECT SALARY
        INTO L_SAL
        FROM EMPLOYEES
        WHERE EMPLOYEE_ID=P_ID;
    RETURN L_SAL;
    END XXC05_FUNC;   
   
    SELECT XXC05_FUNC(198) FROM DUAL;
   

4.

    CREATE OR REPLACE FUNCTION XXC05_FUNC(P_ID NUMBER,P_DEPT_ID OUT NUMBER)
    RETURN NUMBER
    IS
     L_SAL NUMBER;
    BEGIN
        SELECT SALARY,DEPARTMENT_ID
        INTO L_SAL,P_DEPT_ID
        FROM EMPLOYEES
        WHERE EMPLOYEE_ID=P_ID;
    RETURN L_SAL;
    END XXC05_FUNC;   
   
Note:- This type of function can not call in SELECT Statement. This type of function call within BEGIN block;
For Example:-

    DECLARE
     L_DEPT_ID NUMBER;
     L_SAL NUMBER;
    BEGIN
        L_SAL:= XXC05_FUNC(199,L_DEPT_ID);
        DBMS_OUTPUT.PUT_LINE('Department id:- '||L_DEPT_ID||CHR(9)||'Salary:- '||L_SAL);
    END;

Filed Under: PL SQL Thursday 27 February 2014