Functions

A function is a named PL/SQL block that returns a value. Unlike procedures, functions use the RETURN keyword to send a result back to the caller. Functions can be called from SQL statements — a capability procedures do not have.

Creating a Function

CREATE OR REPLACE FUNCTION function_name(
    param1 IN datatype,
    param2 IN datatype DEFAULT default_value
)
RETURN return_datatype IS
    -- local variable declarations
BEGIN
    -- logic
    RETURN some_value;
EXCEPTION
    WHEN ... THEN
        RETURN fallback_value;  -- or RAISE
END function_name;
/

Simple Scalar Function

CREATE OR REPLACE FUNCTION full_name(
    p_first  IN employees.first_name%TYPE,
    p_last   IN employees.last_name%TYPE
)
RETURN VARCHAR2 IS
BEGIN
    RETURN TRIM(p_first) || ' ' || TRIM(p_last);
END full_name;
/

-- Call from PL/SQL
DECLARE
    v_name VARCHAR2(100);
BEGIN
    v_name := full_name('  Steven ', 'King  ');
    DBMS_OUTPUT.PUT_LINE(v_name);  -- Steven King
END;
/

-- Call directly from SQL
SELECT full_name(first_name, last_name) AS name, salary
FROM   employees
WHERE  department_id = 90;

Function with Business Logic

CREATE OR REPLACE FUNCTION get_annual_comp(
    p_salary    IN employees.salary%TYPE,
    p_comm_pct  IN employees.commission_pct%TYPE DEFAULT 0
)
RETURN NUMBER IS
    v_commission NUMBER;
BEGIN
    v_commission := NVL(p_salary * NVL(p_comm_pct, 0), 0);
    RETURN ROUND((p_salary + v_commission) * 12, 2);
END get_annual_comp;
/

-- Use in SQL
SELECT first_name,
       salary,
       commission_pct,
       get_annual_comp(salary, commission_pct) AS annual_comp
FROM   employees
WHERE  department_id = 80
ORDER BY annual_comp DESC
FETCH FIRST 5 ROWS ONLY;

RETURN in Exception Handler

Functions must always return a value or raise an exception on every code path:

CREATE OR REPLACE FUNCTION get_dept_budget(
    p_dept_id IN departments.department_id%TYPE
)
RETURN NUMBER IS
    v_budget NUMBER;
BEGIN
    SELECT SUM(salary) * 12
    INTO   v_budget
    FROM   employees
    WHERE  department_id = p_dept_id;

    RETURN NVL(v_budget, 0);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 0;  -- department has no employees
    WHEN OTHERS THEN
        -- Log error and re-raise
        DBMS_OUTPUT.PUT_LINE('Error in get_dept_budget: ' || SQLERRM);
        RAISE;
        -- No RETURN needed here — RAISE exits the function
END get_dept_budget;
/

Purity Rules — Calling Functions from SQL

Functions called from SQL must obey purity constraints to avoid side effects:

-- DETERMINISTIC: safe for function-based indexes, query caching
CREATE OR REPLACE FUNCTION format_salary(p_salary IN NUMBER)
RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
    RETURN '$' || TO_CHAR(p_salary, 'FM999,999.00');
END format_salary;
/

-- Create a function-based index using a deterministic function
CREATE INDEX idx_salary_fmt ON employees (format_salary(salary));

-- RESULT_CACHE: result cached in SGA — very fast for expensive lookups
CREATE OR REPLACE FUNCTION dept_headcount(p_dept_id IN NUMBER)
RETURN NUMBER RESULT_CACHE IS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM   employees WHERE department_id = p_dept_id;
    RETURN v_count;
END dept_headcount;
/

SELECT department_id, dept_headcount(department_id) AS headcount
FROM   departments;

Function vs Procedure

Calling a Function from Another PL/SQL Block

DECLARE
    v_budget   NUMBER;
    v_headcount NUMBER;
BEGIN
    -- Call in assignment
    v_budget    := get_dept_budget(80);
    v_headcount := dept_headcount(80);

    DBMS_OUTPUT.PUT_LINE(
        'Sales: ' || v_headcount || ' employees, $' ||
        TO_CHAR(v_budget, 'FM999,999,999') || ' total annual salary'
    );

    -- Call in IF condition
    IF get_dept_budget(90) > 500000 THEN
        DBMS_OUTPUT.PUT_LINE('Executive budget exceeds threshold.');
    END IF;
END;
/

Table Function (Returning a Collection)

Functions can return collections usable in SQL's TABLE() operator:

CREATE TYPE t_emp_name_list AS TABLE OF VARCHAR2(100);
/

CREATE OR REPLACE FUNCTION dept_employee_names(p_dept_id IN NUMBER)
RETURN t_emp_name_list IS
    v_names  t_emp_name_list := t_emp_name_list();
BEGIN
    SELECT first_name || ' ' || last_name
    BULK COLLECT INTO v_names
    FROM   employees
    WHERE  department_id = p_dept_id
    ORDER BY last_name;

    RETURN v_names;
END dept_employee_names;
/

-- Use in SQL with TABLE()
SELECT COLUMN_VALUE AS employee_name
FROM   TABLE(dept_employee_names(60));

Summary

• Functions return a value via RETURN — every code path must return or raise.
• Call functions from SQL, PL/SQL assignments, conditions, and expressions.
• DETERMINISTIC enables result caching and function-based indexes.
• RESULT_CACHE stores results in the SGA — ideal for expensive, frequently called lookups.
• Functions in SQL must not perform DML (ORA-14551) — move DML to procedures.
• Table functions return collections usable with TABLE() in SQL queries.