Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of using BULK COLLECT to improve efficiency in batch queries, deletions and updates in Oracle. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

BULK COLLECT (batch aggregation type) and array collection type type is table of table% rowtype index by binary_integer usage notes.

Example 1: batch query the housing account information of the project capital account number "320001054663" and print them out.

DECLARE TYPE acct_table_type IS TABLE OF my_acct%ROWTYPE INDEX BY BINARY_INTEGER; v_acct_table acct_table_type; BEGIN SELECT * BULK COLLECT INTO v_acct_table FROM my_acct WHERE parent_fund='320001054663' FOR i IN 1..v_acct_table.COUNT LOOP-print dbms_output.put_line ('ACCT:' | | v_acct_table (I) .fund | |','| | v_acct_table (I) .bal | |','| | v_acct_table (I) .fund); END LOOP; END

Description section:

1. DECLARE states the following parts that you want to declare

2. The Type declaration is the name of the type acct_table_typ e

3. IS TABLE OF specifies the array type of the table that is a collection, which is simply a data type that can store multiple rows in a column. My_acct indicates on which table (existing table)% ROWTYPE refers to the data type of the rows on the table.

4. INDEX BY BINARY_INTEGER refers to the type of index organization

5. V_acct_table defines a variable to store collection data types

6. BULK COLLECT INTO refers to a batch aggregation type. To put it simply, it can store a multi-row and multi-column storage type, followed by an into that specifies where it comes from.

7. The number of v_acct_table.COUNT used in v_acct_table

8. (I) denotes the sublabel

Example 2: batch update the balance of the housing account whose project capital account number is "320001054663".

DECLARE TYPE fund_table_type IS TABLE OF acct.fund%TYPE; TYPE bal_table_type IS TABLE OF acct.bal%TYPE; v_fund_table fund_table_type; v_bal_table bal_table_type; BEGIN UPDATE acct SET bal=bal*1000 WHERE parent_fund='320001054663' RETURNING fund,bal BULK COLLECT INTO v_fund_table,v_bal_table FOR i IN 1..v_fund_table.COUNT LOOP dbms_output.put_line ('ACCT:' | | v_fund_table (I) | |','| | v_bal_table (I)); END LOOP; END

Description section:

1.% TYPE is the same as acct.fund data type

V_fund_table fund_table_type

2. V_bal_table bal_table_type; defines variables to store them.

3. RETURNING is used to specify the part to be returned, and BULK COLLECT INTO batch aggregation type

4. Print them out with for

On "Oracle batch query, delete, update the use of BULK COLLECT to improve efficiency example analysis" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.