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;
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;
1.% type is the same as acct.fund data 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
- 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!