Bulk collect clause for batch SQL

The bulk collect clause will batch retrieve the results, that is, bind the result set to a collection variable at a time, and send the results from the SQL engine to the PL/SQL engine. Generally,
Use bulk collect in the fetch into and returning into clauses. This article describes the usage of bulk collect in these situations one by one.
For usage of forall statements, see forall statements for batch SQL statements.

 

I. Bulk collect batch binding example

-- The following example uses bulk collect to bind the result set to the record variable declare type emp_rec_type is record -- declare the record type (empno EMP. empno % type, ename EMP. ename % type, hiredate EMP. hiredate % type); Type nested_emp_type is table of emp_rec_type; -- declare the record type variable emp_tab nested_emp_type; begin select empno, ename, hiredate bulk collect into emp_tab -- use Bulk collect to bind the result set to the record variable emp_tab from EMP; for I in emp_tab.first .. emp_tab.last loop D Bms_output.put_line ('current record is '| emp_tab (I ). empno | CHR (9) | emp_tab (I ). ename | CHR (9) | emp_tab (I ). hiredate); End loop; end; -- the above example can be implemented through the for loop and the normal select into. What is the difference between the two? -- The difference is that the select into statement of the For Loop is extracted row by row and bound to the record variable. Bulk collect extracts all rows at a time and binds them to the record variable. That is, batch binding.

Ii. Use Limit to limit the amount of fetch data
When the bulk collect clause is used, the set types, such as nested tables and associated arrays, are automatically initialized and extended (as shown in the following example ). Therefore, if bulk is used
Collect clause is used to operate a set, so you do not need to initialize and expand the set. Due to bulk collect's batch feature, if the data volume is large and the set is automatically expanded at this time
The performance of a large dataset is not reduced. Therefore, the limit clause is used to limit the data size extracted at a time. The limit clause can only appear in batches of fetch operation statements.

Usage:
Fetch... bulk collect into... [limit rows]

Declare cursor emp_cur is select empno, ename, hiredate from EMP; Type emp_rec_type is record (empno EMP. empno % type, ename EMP. ename % type, hiredate EMP. hiredate % type); Type nested_emp_type is table of emp_rec_type; --> defines the record-based nested table emp_tab nested_emp_type; --> defines the set variable. v_limit pls_integer: = 5 is not initialized at this time; --> A variable is defined as the limit value v_counter pls_integer: = 0; begin open emp_cur; loop fetch emp_cur bulk collect into emp_tab --> the bulk collect clause limit v_limit is used for Fetch; --> use the limit clause to limit the extracted data volume exit when emp_tab.count = 0; --> note that emp_tab.count is used for the cursor exit, instead of emp_cur % notfound v_counter: = v_counter + 1; --> record the number of fetch times after limit is used for I in emp_tab.first .. emp_tab.last loop dbms_output.put_line ('current record is '| emp_tab (I ). empno | CHR (9) | emp_tab (I ). ename | CHR (9) | emp_tab (I ). hiredate); End loop; end loop; close emp_cur; dbms_output.put_line ('the v_counter is '| v_counter); end;

Iii. Batch binding of returning clauses
In addition to batch binding with select and fetch, bulk collect can also be used with insert, delete, and update statements. When combined with these DML statements, we
You need to use the returning clause for batch binding.

-- In the following example, declare type emp_rec_type is record (empno EMP. empno % type, ename EMP. ename % type, hiredate EMP. hiredate % type); Type nested_emp_type is table of emp_rec_type; emp_tab values; -- v_limit pls_integer: = 3; -- v_counter pls_integer: = 0; begin Delete from EMP where deptno = 20 returning empno, ename, hiredate --> Use returning to return these columns bulk collect into emp_tab; --> batch insert data from the previously returned column to the Collection variable dbms_output.put_line ('deleted' | SQL % rowcount | 'rows. '); Commit; If emp_tab.count> 0 then --> when the set variable is not empty, all the deleted elements are output for I in emp_tab.first .. emp_tab.last loop dbms_output. put_line ('current record '| emp_tab (I ). empno | CHR (9) | emp_tab (I ). ename | CHR (9) | emp_tab (I ). hiredate | 'has been deleted'); End loop; end if; end;

Iv. comprehensive use of forall and bulk collect
Forall and bulk collect are two important ways to implement batch SQL. We can combine them to improve performance. The following example shows the sum of the two.

Drop table tb_emp; Create Table tb_emp as --> Create Table tb_emp select empno, ename, hiredate from EMP where 1 = 2; declare cursor emp_cur is --> declare the cursor select empno, ename, hiredate from EMP; Type nested_emp_type is table of emp_cur % rowtype; --> the cursor-based nested table Type emp_tab nested_emp_type; --> declare the nested variable begin select empno, ename, hiredate bulk collect into emp_tab --> bulk collect batch extract data from EMP where SAL> 1000; forall I in 1 .. emp_tab.count --> use the forall statement to insert data in the variable to the table tb_emp insert into (select empno, ename, hiredate from tb_emp) values emp_tab (I); Commit; dbms_output.put_line ('the total '| emp_tab.count | 'rows has been inserted to tb_emp'); end;

5. Restrictions on bulk collect
1. The bulk collect clause cannot be used for associated arrays that use string-type keys.
2. You can only use Bulk collect in the server program. If you use it on the client, an error that does not support this feature will be generated.
3. the target object of bulk collect into must be of the collection type.
4. Compound targets (such as object types) cannot be used in the returning into clause.
5. If multiple implicit data types are converted, multiple composite targets cannot be used in the bulk collect into clause.
6. If there is an implicit data type conversion, the composite target set (such as the object type set) cannot be used in the bulk collectinto clause.

 

6. More references

Batch SQL forall statements

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan