FORALL statement (PL/SQL)

The FORALL statement executes a data change statement for all elements of an array or for a range of elements of an array.

Invocation

This statement can only be specified in a PL/SQL block.

Authorization

The privileges held by the authorization ID of the statement must include all of the privileges necessary to invoke the data change statement that is specified in the FORALL statement.

Syntax

Read syntax diagramSkip visual syntax diagramFORALLindex-variableINlower-bound..upper-boundINDICES OFindexing-arrayVALUES OFindexing-arrayinsert-statementsearched-delete-statementsearched-update-statementexecute-immediate-statement

Description

index-variable
Identifies a name to be used as an array index. It is implicitly declared as an INTEGER and it can only be referenced in the FORALL statement.
lower-bound .. upper-bound
Identifies a range of index values that are assignable to the index-variable with lower-bound less than upper-bound. The range represents every integer value starting with lower-bound and incrementing by 1 up to and including upper-bound.
INDICES OF indexing-array
Identifies the set of array index values of the array identified by indexing-array. If indexing-array is an associative array, array index values must be assignable to index-variable and could be a sparse set.
VALUES OF indexing-array
Identifies the set of element values of the array identified by indexing-array. The element values must be assignable to index-variable and could be an unordered sparse set.
insert-statement
Specifies an INSERT statement that is effectively executed for each index-variable value.
searched-delete-statement
Specifies a searched DELETE statement that is effectively executed for each index-variable value.
searched-update-statement
Specifies a searched UPDATE statement that is effectively executed for each index-variable value.
execute-immediate-statement
Specifies an EXECUTE IMMEDIATE statement that is effectively executed for each index-variable value.

Notes

  • FORALL statement processing is not atomic. If an error occurs while iterating in the FORALL statement, any data change operations that have already been processed are not implicitly rolled back. An application can use a ROLLBACK statement to roll back the entire transaction when an error occurs in the FORALL statement.

Example

The following example shows a basic FORALL statement:
FORALL x 
  IN in_customer_list.FIRST..in_customer_list.LAST
  DELETE FROM customer 
    WHERE cust_id IN in_customer_list(x);