Procedures and Functions

23.1 Procedures

Parameter modes: IN (default, read-only), OUT, IN OUT, and NOCOPY (pass by reference hint).

Notation: positional, named (=>), mixed.

CREATE OR REPLACE PROCEDURE raise_salary (
    p_emp_id   IN  employees.employee_id%TYPE,
    p_pct      IN  NUMBER DEFAULT 10,
    p_new_sal  OUT employees.salary%TYPE
) AS
BEGIN
    UPDATE employees SET salary = salary * (1 + p_pct / 100)
    WHERE employee_id = p_emp_id
    RETURNING salary INTO p_new_sal;

    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20010, 'Employee ' || p_emp_id || ' not found');
    END IF;
END raise_salary;
/

DECLARE v NUMBER; BEGIN raise_salary(p_emp_id => 100, p_new_sal => v); DBMS_OUTPUT.PUT_LINE(v); END;
/
EXEC raise_salary(100, 5, :v)

23.2 Functions

Functions called from SQL can't run DML (unless autonomous), can't use OUT parameters meaningfully, and must return SQL types.

PRAGMA UDF (12c) reduces the SQL ↔ PL/SQL context-switch cost.

Scalar subquery caching: (SELECT f(x) FROM dual).

CREATE OR REPLACE FUNCTION get_annual_salary (p_emp_id NUMBER) RETURN NUMBER
    DETERMINISTIC        -- same input → same output (enables caching and function-based indexes)
    PARALLEL_ENABLE
    RESULT_CACHE         -- cross-session function result cache
IS
    v_sal NUMBER;
BEGIN
    SELECT salary * 12 * (1 + NVL(commission_pct, 0)) INTO v_sal FROM employees WHERE employee_id = p_emp_id;
    RETURN v_sal;
EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN NULL;
END;
/
SELECT last_name, get_annual_salary(employee_id) FROM employees;

23.3 Managing Subprograms

SELECT object_name, object_type, status FROM user_objects WHERE object_type IN ('PROCEDURE','FUNCTION','PACKAGE');
SELECT text FROM user_source WHERE name = 'RAISE_SALARY' ORDER BY line;
SELECT * FROM user_errors;   SHOW ERRORS
ALTER PROCEDURE raise_salary COMPILE;
DROP PROCEDURE raise_salary;
SELECT * FROM user_dependencies WHERE referenced_name = 'EMPLOYEES';
EXEC UTL_RECOMP.RECOMP_SERIAL('HR');   -- recompile invalid objects (or utlrp.sql)

23.4 Invoker's vs Definer's Rights

AUTHID DEFINER (default): runs with the owner's privileges.

AUTHID CURRENT_USER: runs with the caller's privileges.

ACCESSIBLE BY (package_x) (12c) restricts who can call a unit.

Code-based access control: grant roles to PL/SQL units (GRANT role TO PROCEDURE p).