Bulk Processing: BULK COLLECT and FORALL

27.1 Why Bulk Processing?

Row-by-row processing causes a context switch between the PL/SQL and SQL engines for every row. Bulk operations batch them.

27.2 BULK COLLECT

DECLARE
    TYPE emp_tab IS TABLE OF employees%ROWTYPE;
    l_emps emp_tab;
    CURSOR c IS SELECT * FROM employees;
BEGIN
    -- All at once (careful with memory)
    SELECT * BULK COLLECT INTO l_emps FROM employees WHERE department_id = 50;

    -- In batches with LIMIT (recommended for large sets)
    OPEN c;
    LOOP
        FETCH c BULK COLLECT INTO l_emps LIMIT 1000;
        EXIT WHEN l_emps.COUNT = 0;           -- not c%NOTFOUND!
        FOR i IN 1 .. l_emps.COUNT LOOP
            NULL; -- process
        END LOOP;
    END LOOP;
    CLOSE c;
END;
/

27.3 FORALL

FORALL i IN INDICES OF coll and VALUES OF idx_coll handle sparse collections.

RETURNING ... BULK COLLECT INTO.

The best approach of all: a single SQL statement (INSERT ... SELECT, MERGE) when possible.

DECLARE
    TYPE id_tab  IS TABLE OF employees.employee_id%TYPE;
    TYPE sal_tab IS TABLE OF employees.salary%TYPE;
    l_ids id_tab; l_sals sal_tab;
    e_dml_errors EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_dml_errors, -24381);
BEGIN
    SELECT employee_id, salary * 1.1 BULK COLLECT INTO l_ids, l_sals FROM employees WHERE department_id = 50;

    FORALL i IN 1 .. l_ids.COUNT SAVE EXCEPTIONS
        UPDATE employees SET salary = l_sals(i) WHERE employee_id = l_ids(i);

    DBMS_OUTPUT.PUT_LINE('Row 3 affected: ' || SQL%BULK_ROWCOUNT(3));
EXCEPTION
    WHEN e_dml_errors THEN
        FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('Index ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX || ': ' ||
                                 SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE));
        END LOOP;
END;
/