To create a function in PL/SQL, use CREATE OR REPLACE FUNCTION statement. Below are the syntax details and an example.
Syntax
CREATE [OR REPLACE] FUNCTION function_name [(parameters)] Return data_type is /* declare variables here */ Begin /* write program logic here */ Return data_type; End;
Clause OR REPLACE is optional, but it is better to use because we used to compile our code multiple times after making the changes. If your function requires parameters, then provide it in parenthesis. Specify the data type for Return clause, because a function must return a value.
Example
The following is an example of a PL/SQL function to add two numbers and return the total of it. This function takes two parameters of number type and will return a number.
CREATE OR REPLACE FUNCTION sum_two_numbers (p_n1 IN NUMBER, p_n2 IN NUMBER) RETURN NUMBER IS BEGIN RETURN (p_n1 + p_n2); END; /
Test it using Select Statement:
SELECT sum_two_numbers (2, 3) total FROM DUAL;
Output:
TOTAL ---------- 5 1 row selected.
Test it using PL/SQL Block:
SET SERVEROUTPUT ON;
DECLARE
n_total NUMBER;
BEGIN
n_total := sum_two_numbers (2, 3);
DBMS_OUTPUT.put_line ('Total is :' || n_total);
END;
/Output:
Total is :5 PL/SQL procedure successfully completed.
Get more details about the Oracle PL/SQL Functions from Oracle Docs.
See also:
- Oracle Function Example
- Oracle Row_Number Function Example
