PL/SQL Function

Summary: In this tutorial, you’ll learn how to develop a PL/SQL function to perform a specific task and return a value.

Creating a PL/SQL function #

In PL/SQL, a function is a reusable code block that performs a specific task and returns a single value.

Here’s the syntax for creating a function:

CREATE [OR REPLACE] FUNCTION function_name (
    parameter1 datatype
    parameter2 datatype
) RETURN return_type
IS
   -- declarative section
BEGIN
   -- executable section
    
   RETURN value;

[EXCEPTION]
    [exception-handling section]
END;
/Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

A function consists of a header and body:

Function header consists of a function name and a RETURN clause specifying the returned value’s datatype. Each parameter of the function can be either in the IN, OUT, or INOUT mode. For more information on the parameter mode, check out the PL/SQL procedure tutorial

The function body is the same as the procedure’s body, which has three sections: declaration, execution, and exception handler.

• Declaration section is where you declare variables, constants, cursors, and user-defined types. It the IS and BEGIN keywords.
• Execution section is where you place the executable statements. It’s between the BEGIN and END keywords. Unlike a procedure, you must have at least one RETURN statement in the execution section.
• Exception-handling section is where you put the exception handler code.

Only the executable section is mandatory, whereas the other sections are optional.

PL/SQL function example #

We’ll use the orders and order_items from the sample database: