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.

Note If an exception occurs during an iteration of the FORALL statement, all updates that have occurred since the start of the execution of the FORALL statement are automatically rolled back. This behavior is not compatible with Oracle databases. Oracle databases allow you to explicitly use the COMMIT or ROLLBACK statements to control whether to commit or roll back updates that occurred prior to the exception.

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)