Using the FORALL statement v16
You can use collections to process DML commands more efficiently by passing all the values to be used for repetitive execution of a
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.
index is the position in the collection given in the
delete_stmt DML command that iterates from the integer value given as
lower_bound up to and including
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
ROLLBACK commands to control whether to commit or roll back updates that occurred prior to the exception.
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
upper_bound (inclusive), and the index increments by one for each iteration.
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
t_emp is an associative array of type
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
This example uses a
FORALL statement to update the salary of three employees:
This example deletes three employees in a