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 numbers
BEGIN
  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.16
FORALL:  .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 AS
SELECT * FROM employees
ORDER 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