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;
/
bulk collect批量定期插入删除行(续)
最新推荐文章于 2021-04-11 04:00:28 发布