This post here shows us the Oracle function syntax and example.
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>;
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;
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 email@example.com. Again I appreciate your visit. Hope to see you again and again!