[Oracle]高效的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