How to Create Function in PL/SQL?

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • PLSQL
  • 2 mins read

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: