How to Use FIRALL to process discontinuous arrays in Oracle 10 GB

In the past, we used Oracle-related nested tables to prevent the deletion of related elements in the middle of the array to be processed. Related array items must also 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