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:= 500000; 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 pname = DBMS_UTILITY.get_time; FORi IN 1..iterations LOOP INSERT INTO parts1 (pnum, pname) VALUES (pnums (I), pnames (I)); ENDLOOP; T2 pname = DBMS_UTILITY.get_time; FORALL i IN 1..iterations INSERT INTO parts2 (pnum, pname) VALUES (pnums (I), pnames (I)); T3V = 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 (10pm 20jue 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, dudes; 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