分段提交删除大批量数据

分段提交删除大批量数据

例子1：

declare
i integer;
begin
loop
execute immediate 'delete from tb_name f_a=xxx and f_b=yyy and romnum<5000';
commit;
select count(*) into i from tb_name f_a=xxx and f_b=yyy;
exit when i=0;
end loop;
end;

例子2：

set serveroutput on
Declare
 rcode integer;
 begin
 loop
 --每10000次将表a删除10000条记录
 delete from a
 where exists (select usercode from b where a.usercode = b.usercode)
 and rownum < 10000;
--每10000条提交一次
 commit;
 exit when sql%rowcount = 0;
 end loop;
 rcode := 1;
 --delete操作完成后，rcode值为1
 dbms_output.put_line('结果是：'||to_char(rcode));
 exception
 when others then
 rollback;
 rcode := 0;
 --delete操作失败后，rcode值为0
 dbms_output.put_line('结果是：'||to_char(rcode));
 end;

例子3:

分批写入数据

create or replace procedure sp_insert_data
as
begin
for i in 1..1000000 loop
insert into tb_hxl_test
values(i,to_char(i)||chr(i));
if mod(i,100000)= 0 then
insert into tb_hxl_log values (i,'commit');
commit;
end if;
end loop;
commit;
exception
when NO_Data_Found then dbms_output.put_line('no data found');
when others then rollback;
end sp_insert_data;