bulk collect批量定期插入删除行（续）

create table t1 as select * from dba_objects where object_id is not null; 
create table t2 as select object_id from t1 where rownum<75201;
create index idx_t1_object_id on t1(object_id);
根据T2的字段去删除T表的内容，并定期按行数提交更新
declare
v_count number;
cursor c1 is select  object_id from t2;                 --定义一游标
type t_c1 is table of number index by binary_integer;   --定义存储数据类型为NUMBER，下标为整数的索引表
i_c1 t_c1;
begin
  v_count:=0;
open c1;
 loop
  fetch c1 bulk collect into i_c1 limit 500;            --每次fetch500行，根据资源情况分配
forall idx_c1 in 1..i_c1.count
   delete from t1 where object_id=i_c1(idx_c1);         --批量绑定
v_count:=v_count+1;
   if v_count=2 then                                    --达到1000行提交一次
    commit;
    v_count:=0;
   end if;
   exit when c1%NOTFOUND;                               --遍历完成退出，避免死循环！
 end loop;
 commit;                                                --剩余的提交
close c1;
exception                                               --异常处理
when others then
rollback;
end;
/

-------------------------------------------------------------------------

t1 t2
truncate table t1;
create table t1 as select * from dba_objects where object_id is not null; 
create table t2 as select * from dba_objects where object_id is not null;
将T2表的数据批量插入到T1表并定期按行数更新；
declare
v_count number;
cursor c1 is select  rowid from t2;
type t_c1 is table of rowid index by binary_integer;                    --定义索引表
i_c1 t_c1;
begin
  v_count:=0;
open c1;
 loop
  fetch c1 bulk collect into i_c1 limit 500;
forall idx_c1 in 1..i_c1.count
   insert into t1 select * from t2 where rowid=i_c1(idx_c1);            --插入整行数据
v_count:=v_count+1;
   if v_count=2 then
    commit;
    v_count:=0;
   end if;
   exit when c1%NOTFOUND;
 end loop;
 commit;
close c1;
exception 
when others then
rollback;
end;
/