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:

The following example creates a function that calculates total sales by year:

CREATE OR REPLACE FUNCTION get_total_sales(
    in_year PLS_INTEGER
) 
RETURN NUMBER
IS
    l_total_sales NUMBER := 0;
BEGIN
    -- get total sales
    SELECT SUM(unit_price * quantity)
    INTO l_total_sales
    FROM order_items
    INNER JOIN orders USING (order_id)
    WHERE status = 'Shipped'
    GROUP BY EXTRACT(YEAR FROM order_date)
    HAVING EXTRACT(YEAR FROM order_date) = in_year;
    
    -- return the total sales
    RETURN l_total_sales;
END;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To compile the function in Oracle SQL Developer, you click the Run Statement button as shown in the picture below:

Once the function is compiled successfully, you can find it under the Functions node:

Calling a PL/SQL function #

You use a function anywhere that you use an expression of the same type. You can call a function in various places, such as:

PL/SQL block #

The following anonymous block calls the get_total_sales function to retrieve the total sales in 2017:

SET SERVEROUTPUT ON;

DECLARE
    l_sales NUMBER := 0;
BEGIN
    l_sales := get_total_sales (2017);
    DBMS_OUTPUT.PUT_LINE('Sales 2017: ' || l_sales);
END;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Sales 2017: 14408188.68Code language: CSS (css)

How it works:

First, declare a variable that will store the sales in 2017:

l_sales NUMBER := 0;

Second, call the get_total_sales function by passing 2017 to its parameter and assign the return value to the l_sales variable:

l_sales := get_total_sales (2017);

Third, display the value of the l_sales variable:

DBMS_OUTPUT.PUT_LINE('Sales 2017: ' || l_sales);Code language: JavaScript (javascript)

Boolean expressions #

The following example shows how to use the get_total_sales function in a Boolean expression:

SET SERVEROUTPUT ON;

BEGIN
    IF get_total_sales (2017) > 10000000 THEN
        DBMS_OUTPUT.PUT_LINE('Sales 2017 is above target');
    END IF;
END;

Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Sales 2017 is above target

How it works:

First, call the get_total_sales function to retrieve the sales in 2017 and compare its return value with 10,000,000. If the sales amount in 2017 is greater than 10 million, display a message:

IF get_total_sales (2017) > 10000000 THEN
   DBMS_OUTPUT.PUT_LINE('Sales 2017 is above target');
END IF;Code language: JavaScript (javascript)

SQL statements #

The following example calls the get_total_sales function in a SELECT statement:

SELECT
    get_total_sales(2017) sales_2017
FROM
    dual;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

SALES_2017
----------
14408188.7Code language: CSS (css)

Editing a function #

To edit and recompile an existing function, you follow these steps:

• First, click the function name that you want to edit
• Second, edit the code.
• Third, click the Compile menu option to recompile the code.

Removing a function #

The DROP FUNCTION deletes a function from the Oracle Database. Here’s the syntax for deleting a function:

DROP FUNCTION function_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, you specify the name of the function you want to drop after the DROP FUNCTION keywords.

For example, the following statement drops the GET_TOTAL_SALES function:

DROP FUNCTION get_total_sales;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Oracle issued a message indicating that the function GET_TOTAL_SALES has been dropped:

Function GET_TOTAL_SALES dropped.Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you want to drop a function using SQL Developer, you can follow these steps:

• First, right-click on the function name you want to delete.
• Second, choose the Drop… menu option.
• Third, click the Apply button to confirm the deletion.

Summary #

• A function is a reusable code block that performs a task and returns a single value.