Oracle Forall

FORALL

Note:[from official document]

Example 12-7DELETE Statement in FOR LOOP Statement

DROP TABLE employees_temp;CREATE TABLE employees_temp AS SELECT *FROM employees; DECLARE TYPE NumList IS VARRAY(20) OF NUMBER; depts NumList := NumList(10, 30, 70); -- department numbersBEGIN  FORi IN depts.FIRST..depts.LAST LOOP   DELETE FROM employees_temp   WHERE department_id = depts(i);  ENDLOOP;END;/

Example 12-9Time Difference for INSERT Statement in FOR LOOP and FORALL Statements

DROP TABLE parts1;CREATE TABLE parts1 ( pnum INTEGER, pname VARCHAR2(15)); DROP TABLE parts2;CREATE TABLE parts2 ( pnum INTEGER, pname VARCHAR2(15)); DECLARE TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER; TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER; pnums   NumTab; pnames  NameTab; iterations  CONSTANT PLS_INTEGER:= 50000; t1  INTEGER; t2  INTEGER; t3  INTEGER;BEGIN  FORj IN 1..iterations LOOP  -- populatecollections   pnums(j) := j;   pnames(j) := 'Part No. ' || TO_CHAR(j);  ENDLOOP;   t1:= DBMS_UTILITY.get_time;   FORi IN 1..iterations LOOP   INSERT INTO parts1 (pnum, pname)   VALUES (pnums(i), pnames(i));  ENDLOOP;   t2:= DBMS_UTILITY.get_time;  FORALL i IN 1..iterations   INSERT INTO parts2 (pnum, pname)   VALUES (pnums(i), pnames(i));   t3:= DBMS_UTILITY.get_time;  DBMS_OUTPUT.PUT_LINE('Execution Time (secs)'); DBMS_OUTPUT.PUT_LINE('---------------------'); DBMS_OUTPUT.PUT_LINE('FOR LOOP: ' || TO_CHAR((t2 - t1)/100)); DBMS_OUTPUT.PUT_LINE('FORALL:   '|| TO_CHAR((t3 - t2)/100)); COMMIT;END;/Result is similar to: Execution Time (secs)---------------------FOR LOOP: 2.16FORALL:  .11 PL/SQL procedure successfully completed.

Example 12-10FORALL Statement for Subset of Collection

DROP TABLE employees_temp;CREATE TABLE employees_temp AS SELECT *FROM employees; DECLARE TYPE NumList IS VARRAY(10) OF NUMBER; depts NumList := NumList(5,10,20,30,50,55,57,60,70,75);BEGIN FORALL j IN 4..7   DELETE FROM employees_temp WHERE department_id = depts(j);END;/

Example 12-26DELETE with RETURN BULK COLLECT INTO in FORALL Statement

DROP TABLE emp_temp;CREATE TABLE emp_temp ASSELECT * FROM employeesORDER BY employee_id, department_id; DECLARE TYPE NumList IS TABLE OF NUMBER; depts  NumList :=NumList(10,20,30);  TYPE enum_t IS TABLE OF employees.employee_id%TYPE; e_ids  enum_t;  TYPE dept_t IS TABLE OF employees.department_id%TYPE; d_ids  dept_t; BEGIN FORALL j IN depts.FIRST..depts.LAST   DELETE FROM emp_temp   WHERE department_id = depts(j)   RETURNING employee_id, department_id   BULK COLLECT INTO e_ids, d_ids;  DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:');   FORi IN e_ids.FIRST .. e_ids.LAST LOOP   DBMS_OUTPUT.PUT_LINE (     'Employee #' || e_ids(i) || ' from dept #' || d_ids(i)   );  ENDLOOP;END;/

Deleted 9 rows:

Employee #200 from dept #10

Employee #201 from dept #20

Employee #202 from dept #20

Employee #114 from dept #30

Employee #115 from dept #30

Employee #116 from dept #30

Employee #117 from dept #30

Employee #118 from dept #30

Employee #119 from dept #30