Firall processing of discontinuous arrays in Oracle 10g

It is time-consuming to perform data manipulation language (DML) in a pl/sql loop because each loop is diverted from the Pl/sql engine to the SQL engine. Applying ForAll is a better way to submit a set of temporary values to a SQL statement at a one-time.
Before Oracle 10g, the syntax of a forall statement can only handle an array element of continuity: ForAll index_name in Lower_bound. Upper_bound sql_statement;

This means that, previously, nested tables using forall cannot delete elements in the middle of the pending array, and the array items must be continuously processed. Oracle 10g solves both problems and adds the indices of and values of clauses.

The INDICES of clause replaces the Lower_bound. Upper_bound, which describes all valid index values to be processed, even if there is an interval between those values. It's like this:

ForAll index_name in INDICES of collection_name

BETWEEN Lower_bound and Upper_bound

sql_statement;

You can still apply the between syntax to limit the scope to be processed, which is an optional content.

The VALUES of clauses help you handle the primary collection in different order. You can create another collection that contains only the index numbers that you want to process, in the order in which you want to process them. The statement then becomes:

ForAll index_name in VALUES of index_collection

sql_statement;

Listing A is an example of applying an HR sample pattern. I loaded the department name into a nested table in memory, and then searched for it-related departments inside. For each search to one, I saved its table entry index. The VALUES of clause combines this set of indexes to handle INSERT statements for each department in the table. (This is just an example; you can do the same with a separate SQL statement.) In list B is the result of the output.