Eric Campbell Sep 11, 2025 0 min read Oracle

Oracle PL/SQL: Practical Stored Procedures

PL/SQL stored procedures encapsulate business logic close to the data. Here are patterns that keep them maintainable.

Key Insights

Use packages to group related procedures and maintain state
Exception handling with named exceptions makes error logic readable
Bulk operations (FORALL, BULK COLLECT) dramatically outperform row-by-row processing

Basic Procedure

CREATE OR REPLACE PROCEDURE update_salary(
    p_employee_id IN employees.employee_id%TYPE,
    p_increase IN NUMBER
) AS
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_increase / 100)
    WHERE employee_id = p_employee_id;

    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
    END IF;
END;

Bulk Operations

DECLARE
    TYPE id_table IS TABLE OF employees.employee_id%TYPE;
    l_ids id_table;
BEGIN
    SELECT employee_id BULK COLLECT INTO l_ids
    FROM employees WHERE department_id = 10;

    FORALL i IN l_ids.FIRST..l_ids.LAST
        UPDATE salaries SET bonus = 1000 WHERE employee_id = l_ids(i);
END;