Oracle SQL Function with Examples

PL/SQL function is a reusable program unit stored as a schema object in the Oracle Database. The following illustrates the syntax for creating a function:

CREATE [OR REPLACE] FUNCTION function_name (parameter_list)
    RETURN return_type
IS
    Code language: SQL (Structured Query Language) (sql)

[declarative section]

BEGIN

[executable section]

[EXCEPTION]

[exception-handling section]

END;

PL/SQL function example

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;

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:

1) In an assignment statement:

DECLARE
    l_sales_2017 NUMBER := 0;
BEGIN
    l_sales_2017 := get_total_sales (2017);
    DBMS_OUTPUT.PUT_LINE('Sales 2017: ' || l_sales_2017);
END;
Code language: SQL (Structured Query Language) (sql)

2) In a Boolean expression

BEGIN
    IF get_total_sales (2017) > 10000000 THEN
        DBMS_OUTPUT.PUT_LINE('Sales 2017 is above target');
    END IF;
END;
Code language: SQL (Structured Query Language) (sql)

3) In an SQL statement

SELECT
    get_total_sales(2017)
FROM
    dual;