Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 38 ยท Cursors, Procedures and Functions

Stored Functions in Oracle PL/SQL

Write Oracle stored functions, call them from SQL, and learn the rules: RETURN, DETERMINISTIC, RESULT_CACHE, side effects, table functions and when to pick a function over a procedure.

A function is a named PL/SQL block that always returns exactly one value. That single difference from a procedure has a big consequence: because a function produces a value, you can use it anywhere an expression is allowed, including inside a SELECT, a WHERE clause or a CHECK constraint. Functions are how you package a calculation such as tax, a formatted name or a business-day count so every query uses the same rule.

Oracle cannot run on this site. Function code is shown read-only. The runnable examples use Python and SQLite, which let you register your own SQL functions, as stand-ins for the same idea.

Creating and calling a function

SQL
CREATE OR REPLACE FUNCTION annual_salary (
  p_monthly IN NUMBER,
  p_bonus   IN NUMBER DEFAULT 0
) RETURN NUMBER
IS
BEGIN
  RETURN p_monthly * 12 + p_bonus;
END annual_salary;
/

Notice RETURN NUMBER in the header (the type) and RETURN expression in the body (the value). Every execution path must end in a RETURN; a function that reaches END without one raises ORA-06503: PL/SQL: Function returned without value.

You can call it from PL/SQL, from SQL, or in a quick test against DUAL:

SQL
SELECT annual_salary(4000, 500) FROM dual;          -- 48500

SELECT first_name, annual_salary(salary) AS yearly
  FROM employees
 WHERE annual_salary(salary) > 60000;

DECLARE
  v_total NUMBER := annual_salary(3000);
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_total);                     -- 36000
END;
/

SQLite lets you register a function from the host language, which is the closest runnable analogue. Here Python provides annual_salary to SQL:

Python A function used inside SQL (Python and SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Rules for functions called from SQL

A function used in a SQL statement is held to stricter rules than one called from PL/SQL:

  • It may use only IN parameters (no OUT or IN OUT).
  • It cannot run COMMIT, ROLLBACK or DDL (unless it is an autonomous transaction).
  • Called from a SELECT, it must not modify database tables (ORA-14551: cannot perform a DML operation inside a query).
  • Its parameter and return types must be SQL types. BOOLEAN and PLS_INTEGER work in PL/SQL, but SQL (before 23ai) cannot see them. Oracle 23ai adds a native BOOLEAN SQL type, but returning NUMBER 0/1 is still the portable choice.

Calling a PL/SQL function once per row also costs a context switch per row (see bulk processing). On large tables a function in WHERE can be much slower than the same expression written inline, and it prevents Oracle from using a normal index on the column. A function-based index can fix that when the function is deterministic.

DETERMINISTIC and RESULT_CACHE

Two options help Oracle avoid redundant work:

SQL
CREATE OR REPLACE FUNCTION vat_amount (p_net IN NUMBER, p_rate IN NUMBER)
  RETURN NUMBER DETERMINISTIC
IS
BEGIN
  RETURN ROUND(p_net * p_rate / 100, 2);
END;
/

CREATE OR REPLACE FUNCTION department_name (p_id IN NUMBER)
  RETURN VARCHAR2 RESULT_CACHE
IS
  v_name departments.department_name%TYPE;
BEGIN
  SELECT department_name INTO v_name FROM departments WHERE department_id = p_id;
  RETURN v_name;
END;
/

DETERMINISTIC is a promise: same inputs, same output, always, with no dependence on tables or the clock. Oracle can then reuse results within a statement and allows the function in a function-based index. Oracle does not verify the promise, so a wrong one gives wrong answers silently. RESULT_CACHE stores results in a shared memory cache keyed by the arguments; Oracle automatically invalidates entries when the tables the function reads change. It suits lookup functions on slowly changing data.

The Python model below shows the idea behind both: a cache keyed by the arguments, cleared when the underlying data changes.

Python What RESULT_CACHE does (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Functions that return collections and rows

A function can return a record, a collection, or a REF CURSOR. A table function returns a collection that SQL can query like a table with TABLE(...). A pipelined table function streams rows out as they are produced instead of building the whole collection first:

SQL
CREATE TYPE num_list AS TABLE OF NUMBER;
/
CREATE OR REPLACE FUNCTION first_n (p_n NUMBER)
  RETURN num_list PIPELINED
IS
BEGIN
  FOR i IN 1 .. p_n LOOP
    PIPE ROW (i * i);
  END LOOP;
  RETURN;
END;
/

SELECT * FROM TABLE(first_n(5));      -- 1, 4, 9, 16, 25

A pipelined function ends with a bare RETURN; and each value goes out through PIPE ROW. The consumer starts getting rows immediately and memory use stays flat, which makes pipelined functions the standard tool for in-database ETL.

Function or procedure?

Question Function Procedure
Returns a value Exactly one, via RETURN None (use OUT parameters)
Usable inside SQL Yes, with restrictions No
Typical job Calculate, look up, format Perform an action, change data

A good rule: if it computes and has no side effects, make it a function; if it changes data, make it a procedure. Avoid functions that also have OUT parameters. They cannot be used in SQL and surprise readers.

Finally, declare helper functions with RETURN types tied to columns using %TYPE, handle NO_DATA_FOUND explicitly when a lookup may find nothing (see exception handling), and group related functions into a package.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens if a function reaches its END without executing RETURN?

    Every path must return a value. Oracle raises an error at run time if one does not.
  2. 2. Which of these may a function called from a SELECT statement NOT do?

    A function called from a query cannot modify tables (ORA-14551), unless it is an autonomous transaction.
  3. 3. What does DETERMINISTIC promise?

    The promise lets Oracle reuse results and allows function-based indexes. Oracle does not check that it is true.
  4. 4. How do you query a function that returns a collection as if it were a table?

    The TABLE operator turns a collection into rows. Pipelined functions return them as they are produced.

Summary

  • A function returns exactly one value with RETURN and can be used inside SQL expressions.
  • Functions called from SQL are restricted: IN parameters only, no commits, no DML inside queries.
  • DETERMINISTIC and RESULT_CACHE let Oracle reuse results; the first is a promise you must keep.
  • Pipelined table functions stream rows into TABLE(...) queries.
  • Compute with functions, act with procedures, and group both in packages.

Next: Packages: Specification and Body