📦 Bulk Processing
BULK COLLECT and FORALL for fast set-based PL/SQL.
| Snippet | |
|---|---|
| ☆BULK COLLECT — Fetch all rows into a collection in one shot |
DECLARE TYPE t_ids IS TABLE OF NUMBER; v_ids t_ids; BEGIN SELECT id BULK COLLECT INTO v_ids FROM employees; END;
|
| ☆BULK COLLECT LIMIT — Chunked bulk fetch to bound memory |
OPEN c; LOOP FETCH c BULK COLLECT INTO v_rows LIMIT 100; EXIT WHEN v_rows.COUNT = 0; END LOOP; CLOSE c;
|
| ☆FORALL INSERT — Single context switch for N inserts |
FORALL i IN 1..v_ids.COUNT INSERT INTO audit_log (emp_id) VALUES (v_ids(i));
|
| ☆FORALL UPDATE — Bulk updates without row-by-row overhead |
FORALL i IN 1..v_ids.COUNT UPDATE employees SET salary = salary * 1.1 WHERE id = v_ids(i);
|
| ☆%ROWTYPE Record — Record variable matching a table row |
DECLARE v_emp employees%ROWTYPE; BEGIN SELECT * INTO v_emp FROM employees WHERE id = 100; END;
|