This topic describes how to use the FORALL statement.
Terms
Collections can be used to process data manipulation language (DML) statements in a more efficient manner. They can pass all values that are required for the repetitive execution of the DELETE, INSERT, or UPDATE statement in one pass to the database server rather than re-iteratively invoke DML statement with new values. The DML statement to be processed in such a manner is specified by using the FORALL statement. One or more collections are given in the DML statement where different values are to be substituted each time the statement is executed.
FORALL index IN lower_bound .. upper_bound
{ insert_stmt | update_stmt | delete_stmt };
The index parameter specifies the position in the collection that is given in the insert_stmt, update_stmt, or delete_stmt DML statement. The statement iterates from the integer value that is given as lower_bound up to and including upper_bound.
Examples
The FORALL statement creates a loop. Each iteration of the loop increments the index variable. You can use the index within the loop to select a member of a collection. The number of iterations is controlled by the lower_bound .. upper_bound clause. The loop is executed once for each integer from lower_bound up to and including upper_bound, and the index is incremented by one for each iteration. The following code snippet provides an example:
FORALL i IN 2 .. 5
This example creates a loop that executes four times: In the first iteration, the index (i) is set to the value 2. In the second iteration, the index is set to the value 3. The loop continues until it executes for the value 5 and then terminates.
The following example creates a table that is named as emp_copy. This table is an empty duplicate of the emp table. In this example, a type emp_tbl is declared to be an array where each element in the array is of composite type and composed of the column definitions that are used to create the emp table. An index is also created on the emp_tbl type.
t_emp is an associative array of the emp_tbl type. The SELECT statement uses the BULK COLLECT INTO statement 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;
The following example shows how to execute the 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)
The following example shows how to execute the FORALL statement to delete three employees:
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)