Oracle 10g using FORALL to deal with non-consecutive array

Posted on 2009-06-29 filed under: Oracle (RSS) 18 views

In PL / SQL implementation of cycle data manipulation language (DML) is a time-consuming, because each cycle should be from PL / SQL engine SQL engine turning. FORALL is a better application of the method, which a one-time to a group of SQL statements submitted to the provisional value.

Oracle 10g in the former, FORALL statement can only deal with the continuity of the syntax of the array elements:

FORALL index_name IN lower_bound .. upper_bound sql_statement;

This means that: in the past, the use of the FORALL nested table can not be processed to remove the middle element of the array, and an array of processing must be carried out continuously. Oracle 10g solve these two problems, and to increase the INDICES OF and VALUES OF clauses.

INDICES OF clause to replace lower_bound .. upper_bound, all have to deal with the effective index values, even if the interval between these values. Like this:

FORALL index_name IN INDICES OF collection_name
BETWEEN lower_bound AND upper_bound
sql_statement;

You can still BETWEEN syntax can be applied to deal with the scope of restrictions, this is an optional content.

VALUES OF clause to help you deal with a different sequence of the main collection. You then create another set, which contains only the index you want to deal with numbers, these numbers you wish to order. The statement then becomes:

FORALL index_name IN VALUES OF index_collection
sql_statement;

A list of applications is a model example of HR samples. I will load into memory the name of the department in a nested table, and then search inside the relevant departments and IT. Each to a search, I save it the index table entry. VALUES OF clauses combined index for this group of table INSERT statement for each department to deal with. (This is just an example; with a single SQL statement can also be treated in the same.) Lists B is output.