orcle 批量提取和批量插入 BULK COLLECT and FORALL

朱雀 2022-08-13 08:28 162阅读 0赞

oracle 可以从数据库中一次检索出一条数据，也可以一次检索一批记录，或者说检索一组记录。 批量提取是指一次单独的提取操作中从oracle检索多条记录的机制，批量提取检索记录可以减少发送到数据库服务器的请求次数，也可以降低网络流量和逻辑io的开销。

如下2个例子实现批量提取与插入操作

declare  
   type recstartyp is table of emp%rowtype index by BINARY\_INTEGER;  
   rec\_tab recstartyp;  
   cursor temp is select \* from emp;  
begin  
   open temp;  
   fetch temp bulk collect into rec\_tab;       \--批量提取所有记录  
   FORALL i in rec\_tab.first..rec\_tab.last  
      insert into emp1 values rec\_tab(i);  
   commit;  
   close temp;  
end;  
/  
  
declare  
    type tntbl\_array is table of table\_source%rowtype;  
    vntbl\_array tntbl\_array;  
    cursor cur\_source is select \* from table\_source;  
i\_arraysize    integer := 100;  
err\_num number;  
err\_msg varchar2(1000);  
  
begin  
    open cur\_source;  
    loop    
        fetch cur\_source   
         bulk collect   
         into vntbl\_array  
        limit i\_arraysize;     --每次批量提取一部分记录 100条（limit字句用来设置批量提取的数量）

forall i in 1..vntbl\_array.count      --批量插入  
        insert into table\_target  
        values vntbl\_array(i);          
\--2万条提交一次  
        if ( mod(cur\_source%rowcount, 20000) = 0) then  
            commit;  
        end if;             
        exit when cur\_source%notfound;             
    end loop;  
    commit;  
    if ( cur\_source%isopen ) then  
        close cur\_source;  
    end if;

end;