高效的PL/SQL程序设计--批量处理

批量处理一般用在ETL操作, ETL代表提取(extract),转换(transform),装载(load), 是一个数据仓库的词汇!

 

类似于下面的结构：

for x (select * from...)
loop
    Process data;
    insert into table values(...);
end loop;

 

一般情况下, 我们处理大笔的数据插入动作, 有2种做法, 第一种就是一笔笔的循环插入

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;

 

第2种方法就是批量处理(insert全部字段)：

create or replace procedure Bulk_Test1(p_array_size in number)
as
 type array is table of user_tables%rowtype;
 l_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部分字段：

create or replace procedure Bulk_Test2(p_array_size in number)
as
 l_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;
 

在性能方面批量处理有着很大的优势, p_array_size一般默认都是100