Oracle batch query, delete, update use bulk collect to improve efficiency

Time:2020-2-12

Use notes for bulk aggregate and array collection type is table of table% rowtype index by binary_integer.

Example 1: batch query the house account information with project fund account no. “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 
      ---Circular printing 
      dbms_output.put_line('ACCT:'||v_acct_table(i).fund|| ','||v_acct_table(i).bal||','||v_acct_table(i).real_nmbr); 
  END LOOP; 
END;

Description:

1. Declare describes the following parts you want to declare

2. The typ e declaration is the name of type acct table typ e

3. Is table of specifies the array type of a set of tables, which is simply a data type that can store a column and multiple rows. My ABCD indicates which table (the existing table)% rowtype refers to the data type of the rows in the table

4. Index by binary “refers to index organization type

5. V ABCD acct table defines a variable to store the set data type

6. Bulk collect into refers to a batch aggregation type. Simply speaking, it can store a multi row and multi column storage type. After the into, specify where to come from,

7. The number of v-acct-table.count is used for the number of v-acct-table

8. (I) indicates the lower label

Example 2: batch update the balance of the house account with the project fund account no. “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:

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

  v_fund_table fund_table_type;

2. Define variables to store them

3. Returning specifies the part to be returned, bulk collect into batch aggregation type

4. Print them out with for

Conclusion:

  • Set serveroutput on during the experiment
  • The purpose of the above example is to improve the performance. The performance here mainly refers to the speed
  • Speed refers to batch insertion, update and deletion. Why does it increase speed? The extracted data are processed in memory, because it is relatively fast in memory. This is common sense

The above is the Oracle batch query, deletion and update introduced by Xiaobian to you. We hope it can help you. If you have any questions, please leave a message to me and Xiaobian will reply to you in time. Thank you very much for your support of the developepaer website!