Explain the FORALL statement in PL/SQL

How it works

FORALL sends a single DML statement (INSERT/UPDATE/DELETE) to the SQL engine once per element of a bind collection, in one batched round trip, instead of looping and issuing that DML once per row — BULK COLLECT's write-side counterpart. The bound collection must be referenced with a single subscript expression (e.g. v_ids(v_i)); FORALL isn't a general-purpose loop and can't contain any other statement inside it.

DECLARE
  TYPE t_id_tab IS TABLE OF employees.employee_id%TYPE;
  v_ids t_id_tab := t_id_tab(100, 101, 102);
BEGIN
  FORALL v_i IN v_ids.FIRST..v_ids.LAST
    UPDATE employees SET salary = salary * 1.05 WHERE employee_id = v_ids(v_i);

  DBMS_OUTPUT.PUT_LINE('Rows updated: ' || SQL%ROWCOUNT);
END;
/

Related Questions