Using the FORALL statement v16

Suggest edits

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 invoke 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 each time the command is executed.

Syntax

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.

How it works

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.

Note

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 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.

Using FORALL with CREATE

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;

Using FORALL with UPDATE

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;
Output
 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)

Using FORALL with DELETE

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;
Output
 empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)
← Prev

Using the MULTISET operators

Next →

Using the BULK COLLECT clause