PL/SQL Batch processing statement (i) BULK COLLECT

We know that it is expensive to run SQL statements in a PL/SQL program, because they are being submitted to the SQL engine for processing, and this control transfer between the PL/SQL engine and the SQL engine is called the context, and each time it is swapped, there is an additional overhead.
However, the forall and bulk collect can allow the PL/SQL engine to compress multiple contexts into one, which makes the time spent on SQL statement execution of multiple-row Records in PL/SQL drop sharply.

Example:
First, BULK collect binds the resulting set of results once (all)

--The following example uses bulk collect to bind the resulting set of results to a record variable DECLARE  TYPE Emp_rec_type is RECORD--declaring record types (empno emp.empno%TYPE, ename emp.ename%TYPE, HireDate emp.hiredate%TYPE);  TYPE Nested_emp_type is TABLE of Emp_rec_type; --declares 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 resulting set of results once to the record variable emp_tab from emp; For I in Emp_tab. First: Emp_tab. last LOOP dbms_output.put_line ('Current record is'|| Emp_tab (i). empno| | Chr9)|| Emp_tab (i). ename| | Chr9)||Emp_tab (i). HireDate); END LOOP;
END;--The above example can be achieved through a for loop and a normal select INTO, what is the difference between the two? --the difference is a for-loop select INTO line fetch and bind to the record variable, while bulk collect extracts all rows at once and binds to the record variable. That is, batch binding.

Second, limit the amount of fetch data (limit quantity) using limits
When using the bulk COLLECT clause, for collection types such as nested tables, union arrays are automatically initialized and extended (as in the example below). Therefore, if you use the bulk collect clause to manipulate the collection, you do not need to initialize and extend the collection. Because of the bulk characteristics of the bulk collect, if the amount of data is large and the collection is automatically extended at this time, to avoid the performance degradation caused by too large datasets, use the limit clause to limit the amount of data fetched at one time. The limit clause allows only the bulk of the fetch operation statement to appear.
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; -a record-based nested table Emp_tab Nested_emp_type is defined. -defines a collection variable that is not initialized at this time
v_limit Pls_integer: = 5; - defines a variable to be the value of limit v_counter Pls_integer:=0;   BEGIN OPEN emp_cur; LOOP FETCH emp_cur BULK COLLECT into Emp_tab-bulk Collect clause LIMIT V_limit is used for fetch ;  -To  limit the amount of data fetched by using the limit clause EXIT when emp_tab. COUNT=0; --note at this point the cursor exits using Emp_tab. COUNT, not emp_cur%NotFound V_counter:= V_counter +1; -records the number of fetch after using limit 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. Bulk binding using the returning clause
BULK collect can be used in conjunction with insert,delete,update statements in addition to bulk binding with Select,fetch. When combined with these several DML statements, we
You need to use the returning clause to implement bulk binding.

--Remove all deptno= from the table EMP in the following example20 Records 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 Nested_emp_type;
--V_limit Pls_integer: =3;--V_counter Pls_integer: =0; BEGIN DELETE from emp WHERE deptno= -returning empno, ename, HireDate-use returning to return these several columns BULK COLLECT into Emp_tab; -BULK INSERT data from the previously returned columns into the collection variable Dbms_output.put_line ('Deleted'|| Sql%rowcount | |'rows.' );      COMMIT; IF Emp_tab. COUNT>0Then--when the set variable is not empty, all 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. limitations of BULK Collect
1. You cannot use the bulk COLLECT clause on an associative array that uses a string type as a key.
2, only in the server-side program to use bulk COLLECT, if used on the client, it will produce an error that does not support this feature.
3. The target object of BULK COLLECT into must be a collection type.
4. Composite targets (such as object types) cannot be used in the returning into clause.
5, if there are multiple implicit data type conversions, multiple composite targets cannot be used in the bulk COLLECT into clause.
6. If there is an implicit data type conversion, a collection of compound targets, such as a collection of object types, cannot be used in the bulk collectinto clause.

PL/SQL Batch processing statement (i) BULK COLLECT