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.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

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.