Bulk collect for batch processing of multi-row data

Bulk collect for batch processing of multi-row data

When writing pl/SQL statements, a typical mode is often used to define a cursor and process the values cyclically from the cursor.
Similar to the following format
Declare
Cursor xxxx is xxxxx;
Begin
Loop cur in xxxxx loop
Xxxxx
End loop;
End;
/

If the cursor contains too much data, there may be performance problems. The main consideration of performance is the switch between the pl/SQL engine and the SQL engine, which is similar to the Context Environment in programming.
In this case, you can use bulk collect to directly read the data island cache at one time and further process it from the cache.
For example, if you take a new person to complete a task, he may ask you 100 questions a day, you want him to ask you every few minutes, or let him accumulate his questions and set a time to answer them in a centralized manner. Maybe you are busy with another thing. He asks you a question. At this time, there will be a switch in the Context Environment. When you answer the question, another question comes when you continue working, at this time, you have to perform another switch ....
For example, if we set a table test, we want to selectively Insert the data in test into test_all.
The original Pl/SQL is as follows:
Declare
Cursor test_cursors is select object_id, object_name from test;
Begin
For test_cursor in test_cursors loop
Dbms_output.put_line ('object _ id: '| test_cursor.object_id );
Insert into test_all values (test_cursor.object_id, test_cursor.object_name );
End loop;
Commit;
End;
/

If bulk collect is used, the following method is used:
Declare
Type id_t is table of test. object_id % type;
Type name_t is table of test. object_name % type;
Object_id id_t;
Object_name name_t;
Cursor test_cursors is select object_id, object_name from test;
Begin
Open test_cursors;
Fetch test_cursors bulk collect into object_id, object_name;
Close test_cursors;
For I in object_id.FIRST... object_id.LAST loop
Dbms_output.put_line ('object _ id: '| object_id (I ));
Insert into test_all values (object_id (I), object_name (I ));
End loop;
Commit;
End;
/

Or use an implicit cursor:
Declare
Type id_t is table of test. object_id % type;
Type name_t is table of test. object_name % type;
Object_id id_t;
Object_name name_t;
Begin
Select object_id, object_name bulk collect into object_id, object_name from test where rownum <20;
For I in object_id.FIRST... object_id.LAST loop
Dbms_output.put_line ('object _ id: '| object_id (I ));
Insert into test_all values (object_id (I), object_name (I ));
End loop;
Commit;
End;
/