[Oracle] efficient PL/SQL programming (4)-Batch Processing

 

Batch Processing is generally used in ETL operations. ETL stands for extract, transform, and load. It is the word of a data warehouse!

Similar to the following structure:

For X (select * from ...)
Loop
Process data;
Insert into table values (...);
End loop;

 

In general, there are two ways to deal with large data insertion actions. The first is to insert data cyclically.

Create Table T1 as select * From user_tables where 1 = 0;
Create Table T2 as select * From user_tables where 1 = 0;
Create Table T3 as select table_name from user_tables where 1 = 0;

 

Create or replace procedure nor_test
As
Begin
For X in (select * From user_tables)
Loop
Insert into T1 values X;
End loop;
End;

The 2nd method is batch processing (insert all fields ):

Create or replace procedure bulk_test1 (p_array_size in number)
As
Type array is table of user_tables % rowtype;
Rochelle data array;
Cursor C is select * From user_tables;
Begin
Open C;
Loop
Fetch C bulk collect into l_data limit p_array_size;

Forall I in 1 .. l_data.count
Insert into T2 values l_data (I );

Exit when C % notfound;
End loop;
End;

Insert fields:

Create or replace procedure bulk_test2 (p_array_size in number)
As
Rochelle tablename dbms_ SQL .varchar2_table;
Cursor C is select table_name from user_tables;
Begin
Open C;
Loop
Fetch C bulk collect into l_tablename limit p_array_size;

Forall I in 1 .. l_tablename.count
Insert into T3 values (l_tablename (I ));

Exit when C % notfound;
End loop;
End;

Batch Processing has a great advantage in terms of performance. p_array_size is generally 100 by default.