Function – Syntax and Example

This post here shows us the Oracle function syntax and example.

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;

You can also Subscribe here to stay updated on latest posts of Oraclemine.com.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.