Function

Syntax

CREATE [OR REPLACE] FUNCTION <function_name> [(<parameter1> [IN | OUT | IN OUT] [, <parameter2> [IN | OUT | IN OUT] ]) ]
RETURN <data_type>
IS/AS
   [<declaration_section>]
BEGIN
   <executable_section>
[EXCEPTION
   <exception_section>]
END <function_name>;

Example

CREATE OR REPLACE FUNCTION retired_employee (p_emp_no IN NUMBER) RETURN VARCHAR2
IS
   v_age NUMBER;
BEGIN
   SELECT TRUNC(SYSDATE) – TRUNC(birth_date)
     INTO v_age
     FROM employees
    WHERE employee_number = p_emp_no;

   IF NVL(v_age, 0) > 58 THEN
      return ('Employee is retired.');
   ELSE
      return ('Employee is still an employee.');
   END IF;
EXCEPTION
   WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR (-20001, 'Error occurred while checking for retirement - '||p_emp_no);
END retired_employee;