How to Use FIRALL to process some non-continuous arrays in Oracle 10 Gb?

What we want to introduce today is the operating language (DML) for Oracle to use FIRALL to process some non-consecutive arrays and execute relevant data in PL/SQL loops) in fact, it is a very time-consuming task, because every cycle needs to be switched from the PL/SQL engine to the SQL engine. Applying FORALL is a better way to submit a set of temporary values to SQL statements at a time.

Before Oracle 10 Gb, The FORALL statement syntax can only process continuous array elements:

 
 

  
  
FORALL index_name IN lower_bound ..upper_bound sql_statement; 
 
 

This means: Previously, the nested tables using FORALL cannot delete the elements in the middle of the array to be processed, and the array items must be continuously processed. Oracle 10 Gb solves these two problems and adds the indices of and values of clauses.

The indices of clause replaces lower_bound... upper_bound, indicating all valid index values to be processed, even if there is an interval between these values. It is 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. This is an optional content.

The values of Clause helps you process major sets in different order. Create another set that contains only the index numbers you want to process. These numbers are arranged in the order you want to process. Then the statement is changed:

 
 

  
  
FORALL index_name IN VALUES OF index_collection  
  
  
sql_statement;  
 
 

List A is an example of applying the HR sample model. I load the Department name to a nested table in the memory and search for IT-related departments. Each time one is found, the index of the input items in the table where I saved it. The values of clause combines this set OF indexes to perform INSERT statement processing for each department in the table. (This is just an example. You can use a separate SQL statement to perform the same processing .) List B is the output result.

The above content introduces how to use FIRALL to process non-continuous arrays in Oracle 10 Gb. I hope you will get some benefits.

Article by: http://database.ctocio.com.cn/280/9001780.shtml