Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)

Part Number E10472-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

FORALL Statement

The FORALL statement runs one DML statement multiple times, with different values in the VALUES and WHERE clauses. The different values come from existing, populated collections. The FORALL statement is usually much faster than an equivalent FOR loop.

Topics:

Syntax

forall_statement ::=

forall_statement
Description of the illustration forall_statement.gif

bounds_clause ::=

bounds_clause
Description of the illustration bounds_clause.gif

Semantics

forall_statement

index_name

An identifier for the implicitly declared integer variable that is local to the FORALL statement. Statements outside the FORALL statement cannot reference index_name. Statements inside the FORALL statement can reference index_name as an index variable, but cannot use it in expressions or change its value. After the FORALL statement runs, index_name is undefined.

dml_statement

A static or dynamic INSERT, UPDATE, or DELETE statement that references at least one collection in its VALUES or WHERE clause. Performance benefits apply only to collection references that use index_name as a subscript.

Every collection that dml_statement references must have subscripts that match the values of index_name. If you apply the DELETE, EXTEND, or TRIM method to one collection, apply it to the other collections also, so that all collections have the same set of subscripts. If any collection lacks a referenced element, an exception is raised.

Restrictions on dml_statement 

SAVE EXCEPTIONS

This option enables the FORALL loop to continue even if some of its DML statements fail. Instead of raising an exception immediately, the program raises a single exception after the FORALL statement finishes. For information about the exceptions, use the implicit cursor attribute SQL%BULK_EXCEPTIONS.

bounds_clause

Specifies the collection element subscripts that provide values for the variable index_name. For each value, the SQL engine runs dml_statement once.

lower_bound .. upper_bound

Both lower_bound and upper_bound are numeric expressions that PL/SQL evaluates once, when the FORALL statement is entered, and rounds to the nearest integer if necessary. The resulting integers must be the lower and upper bounds of a valid range of consecutive index numbers. If an element in the range is missing or was deleted, an exception is raised.

INDICES OF collection [ BETWEEN lower_bound AND upper_bound ]

Specifies that the values of index_name correspond to the subscripts of the elements of the specified collection. The subscripts need not be consecutive.

Both lower_bound and upper_bound are numeric expressions that PL/SQL evaluates once, when the FORALL statement is entered, and rounds to the nearest integer if necessary. The resulting integers are the lower and upper bounds of a valid range of index numbers, which need not be consecutive.

Restriction on collection If collection is an associative array, it must be indexed by integer.

VALUES OF index_collection

Specifies that the values of index_name are the elements of index_collection, a collection of PLS_INTEGER elements that is indexed by PLS_INTEGER. The subscripts of index_collection need not be consecutive. If index_collection is empty, an exception is raised and the FORALL statement does not run.

Usage

You can use the FORALL statement only in server-side programs, not in client-side programs.

Examples

Related Topics

In this chapter:

In other chapters: