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

Source: Internet
Author: User

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:

 
 
  1. 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:

 
 
  1. FORALL index_name IN INDICES OF collection_name  
  2. BETWEEN lower_bound AND upper_bound  
  3. 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:

 
 
  1. FORALL index_name IN VALUES OF index_collection  
  2. 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.