Using the FORALL statement v14
You can use collections to process DML commands more efficiently 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. The alternative is to reiteratively invoking the DML command with new values. Specify the DML command to process this way with the FORALL
statement. In addition, provide one or more collections in the DML command where you want to subsitute different values are each time the command is executed.
FORALL <index> IN <lower_bound> .. <upper_bound> { <insert_stmt> | <update_stmt> | <delete_stmt> };
index
is the position in the collection given in the insert_stmt
, update_stmt
, or delete_stmt
DML command that iterates from the integer value given as lower_bound
up to and including upper_bound.
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 rolled back. This behavior isn't compatible with Oracle databases. Oracle allows explicit use of the COMMIT
or ROLLBACK
commands to control whether to commit or roll back updates that occurred prior to the exception.
The FORALL
statement creates a loop. Each iteration of the loop increments the index
variable. You typically use the index
in the loop to select a member of a collection. Control the number of iterations with the lower_bound .. upper_bound
clause. The loop is executes once for each integer between the lower_bound
and upper_bound
(inclusive), and the index increments by one for each iteration.
For example:
FORALL i IN 2 .. 5
This expression creates a loop that executes four times. In the first iteration, index (i)
is set to the value 2
. In the second iteration, the index is set to the value 3
, and so on. The loop executes for the value 5
and then terminates.
This example creates a table emp_copy
that's an empty copy of the emp
table. The example declares a type emp_tbl
that's an array. Each element in the array is of composite type, composed of the column definitions used to create the table emp
. The example also creates an index on the emp_tbl
type.
t_emp
is an associative array of type emp_tbl
. The SELECT
statement uses the BULK COLLECT INTO
command to populate the t_emp
array. After the t_emp
array is populated, the FORALL
statement iterates through the values (i)
in the t_emp
array index and inserts a row for each record into emp_copy
.
CREATE TABLE emp_copy(LIKE emp); DECLARE TYPE emp_tbl IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER; t_emp emp_tbl; BEGIN SELECT * FROM emp BULK COLLECT INTO t_emp; FORALL i IN t_emp.FIRST .. t_emp.LAST INSERT INTO emp_copy VALUES t_emp(i); END;
This example uses a FORALL
statement to update the salary of three employees:
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)
This example deletes 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)