首页 > 代码库 > FORALL与BULK COLLECT是实现批量SQL

FORALL与BULK COLLECT是实现批量SQL

2014年11月20日,星期四 更新

1. 示例1
declare
/*    type card_rec_type is record
    ( dn_no channel.dn_no%type,
        channel_id channel.channel_id%type);
    type nested_card_type is table of card_rec_type;
    card_tab nested_card_type; */
    cursor card_rec is
        select dn_no,channel_id from channel;
    type nested_card_type is table of card_rec%rowtype;-->基于游标的嵌套表类型
    card_tab nested_card_type;
begin
    select dn_no,channel_id
    bulk collect into card_tab
    from channel
    where lan_id=7;
    forall i in card_tab.first .. card_tab.last
        insert into (select dn_no,channel_id from tb_card)
        values card_tab(i);
    commit;
    dbms_output.put_line(‘The total ‘||card_tab.count||‘ has insert into tb_card‘);
end;
/
--如果我们在forall抛出异常的时候进行捕获,在Exception处理语句中进行commit的话,我们成功更新的记录是可以保存下来

示例2
declare
  cursor cur is select object_id,object_name from dba_objects a where a.OBJECT_TYPE=‘TABLE‘;
  l_count number :=0;
  l_commit number :=100;
  
  type t_target is table of cur%rowtype;
  l_target t_target;
  l_limit number default 10;
  
begin
  open cur;
  loop
    fetch cur bulk collect into l_target limit l_limit;
    exit when l_target.count=0;
         forall i in l_target.first .. l_target.last
         insert into tmp_lbx (id,name) values(l_target(i).object_id,l_target(i).object_name);
         
         l_count :=l_count + l_target.count;
         if l_count >= l_commit then
           commit;
           l_count :=0;
         end if;  
     end loop;
     commit;    
     close cur;
end;

2. 异常处理示例 Forall中Bulk_Exceptions捕获
declare
type id_list is table of t.object_id%type index by binary_integer;
id_info id_list;
i number;
ERRORS_NUM number;
begin
    select object_id
    bulk collect into id_info from t;
 
    forall i in id_info.first .. id_info.last save exceptions
        update t set subobject_name=object_type||timestamp
        where object_id=id_info(i);
    commit;
    exception
        when others then
            errors_num :=sql%bulk_exceptions.count;
            dbms_output.put_line(‘Total Exception is :‘||to_char(errors_num));
 
            if errors_num > 10 then
                errors_num := 10;
            end if;
 
            for i in 1 .. errors_num loop
                dbms_output.put_line(‘Error #‘||i||‘ is ‘||sql%bulk_exceptions(i).error_index||‘ ‘ ||sqlerrm(-sql%bulk_exceptions(i).error_code));
            end loop;
 
            rollback;
end;
/

--异常记录入库
--创建测试表
create table   tb_bulk_test(p_id number(10) primary key,p_name varchar2(50));
insert into tb_bulk_test (p_id,p_name)
select a.OBJECT_ID,a.OBJECT_NAME from dba_objects a where a.OBJECT_ID>500 and rownum<=1000;

--生成随机数表
create table tb_test  as
SELECT trunc(DBMS_RANDOM.value(10,10000)) id FROM DUAL connect by level <1001;
--创建异常记录表
create table log_error (err_ind varchar2(10),err_code varchar2(50), insert_date date default sysdate);

declare 
cursor c1 is select id from tb_test;
type c1_type is table of c1%rowtype;
c1_tab c1_type;
bulk_errors exception;
pragma exception_init(bulk_errors,-24381);

type type_errorindex is table of number index by binary_integer;
err_ind type_errorindex;
type type_errorcode is table of varchar2(50) index by binary_integer;
err_code type_errorcode;

begin
  open c1 ;
    fetch c1 bulk collect into c1_tab ;
    forall i in c1_tab.first .. c1_tab.last save exceptions
    insert into (select p_id from tb_bulk_test ) values c1_tab(i);
    commit;
    exception
      when bulk_errors then
        for j in 1 .. sql%bulk_exceptions.count() 
          loop
            err_ind(j) :=sql%bulk_exceptions(j).error_index;
            err_code(j) := -sql%bulk_exceptions(j).error_code;
            execute immediate ‘insert into log_error(err_ind,err_code) values(:1,:2)‘
            using err_ind(j),err_code(j);
            commit;
          end loop;   
end;
   
3. forall与rowcount
declare
type id_list is table of t.object_id%type index by binary_integer;
id_info id_list;
i number;
j number;
ERRORS_NUM number;
begin
    select object_id
    bulk collect into id_info from t;
 
    forall i in id_info.first .. id_info.last save exceptions
        update t set subobject_name=object_type||timestamp
        where object_id=id_info(i);
 
 
    for j in id_info.first .. id_info.last loop
        dbms_output.put_line(‘Iteration# ‘||j||‘ : ‘||sql%bulk_rowcount(j)||‘ rows.‘);
    end loop;
    commit;
end;
/





参考:
http://otn.itpub.net/17203031/viewspace-777035/



来自为知笔记(Wiz)


FORALL与BULK COLLECT是实现批量SQL