Example of a Sample Function In PLSQL

Topic Introduction: This tutorial will give an Example of a Sample Function In PLSQL.

Function Structure & Example

CREATE OR REPLACE FUNCTION fun_get_grosssal (p_empid NUMBER)

    RETURN NUMBER

IS

    v_sal   NUMBER;

BEGIN

    SELECT salary + salary * NVL (commission_pct, 0) / 100 gross_salary

      INTO v_sal

      FROM employees

     WHERE employee_id = p_empid;

    RETURN v_sal;

EXCEPTION

    WHEN NO_DATA_FOUND

    THEN

        RETURN NULL;

    WHEN OTHERS

    THEN

        RETURN NULL;

END;

/

Invoking Function or Result Check

Method #1

SELECT fun_get_grosssal (101) gross_sal FROM DUAL;

Method #2

SELECT Last_name,

       salary,

       commission_pct,

       fun_get_grosssal (employee_id) gross_sal

  FROM employees;