Collections can be used to more efficiently process DML commands by passing all the values to be used for repetitive execution of a DELETE, INSERT, or UPDATE command in one pass to the database server rather than re-iteratively invoking the DML command with new values. The DML command to be processed in such a manner is specifed with the FORALL statement. In addition, one or more collections are given in the DML command where different values are to be substituted each time the command is executed.
FORALL index IN lower_bound .. upper_bound { insert | update | delete };
index is the position in the collection given in the insert, update, or delete DML command that iterates from the integer value given as lower_bound up to and including upper_bound.
Note: If an exception occurs during any iteration of the FORALL statement, all updates that occurred since the start of the execution of the FORALL statement are automatically rolled back. This behavior is not Oracle compatible. Oracle allows explicit use of the COMMIT or ROLLBACK commands to control whether or not to commit or roll back updates that occurred prior to the exception.
The following example uses an INSERT command with the FORALL statement to insert three new employees into the emp table.
DECLARE TYPE empno_tbl IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER; TYPE ename_tbl IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; TYPE job_tbl IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; TYPE sal_tbl IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; TYPE deptno_tbl IS TABLE OF emp.deptno%TYPE INDEX BY BINARY_INTEGER; t_empno EMPNO_TBL; t_ename ENAME_TBL; t_job JOB_TBL; t_sal SAL_TBL; t_deptno DEPTNO_TBL; BEGIN t_empno(1) := 9001; t_ename(1) := 'JONES'; t_job(1) := 'ANALYST'; t_sal(1) := 3200.00; t_deptno(1) := 40; t_empno(2) := 9002; t_ename(2) := 'LARSEN'; t_job(2) := 'CLERK'; t_sal(2) := 1400.00; t_deptno(2) := 40; t_empno(3) := 9003; t_ename(3) := 'WILSON'; t_job(3) := 'MANAGER'; t_sal(3) := 4000.00; t_deptno(3) := 40; FORALL i IN t_empno.FIRST..t_empno.LAST INSERT INTO emp (empno,ename,job,sal,deptno) VALUES (t_empno(i),t_ename(i),t_job(i),t_sal(i),t_deptno(i)); END; SELECT * FROM emp WHERE empno > 9000; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+---------+-----+----------+---------+------+-------- 9001 | JONES | ANALYST | | | 3200.00 | | 40 9002 | LARSEN | CLERK | | | 1400.00 | | 40 9003 | WILSON | MANAGER | | | 4000.00 | | 40 (3 rows)
The following example updates the salary of these three employees in a FORALL statement.
DECLARE TYPE empno_tbl IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER; TYPE sal_tbl IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; t_empno EMPNO_TBL; t_sal SAL_TBL; BEGIN t_empno(1) := 9001; t_sal(1) := 3350.00; t_empno(2) := 9002; t_sal(2) := 2000.00; t_empno(3) := 9003; t_sal(3) := 4100.00; FORALL i IN t_empno.FIRST..t_empno.LAST UPDATE emp SET sal = t_sal(i) WHERE empno = t_empno(i); END; SELECT * FROM emp WHERE empno > 9000; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+---------+-----+----------+---------+------+-------- 9001 | JONES | ANALYST | | | 3350.00 | | 40 9002 | LARSEN | CLERK | | | 2000.00 | | 40 9003 | WILSON | MANAGER | | | 4100.00 | | 40 (3 rows)
The final example deletes these three employees in a FORALL statement.
DECLARE TYPE empno_tbl IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER; t_empno EMPNO_TBL; BEGIN t_empno(1) := 9001; t_empno(2) := 9002; t_empno(3) := 9003; FORALL i IN t_empno.FIRST..t_empno.LAST DELETE FROM emp WHERE empno = t_empno(i); END; SELECT * FROM emp WHERE empno > 9000; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-------+-----+-----+----------+-----+------+-------- (0 rows)
