分段提交删除大批量数据

分段提交删除大批量数据[@more@]

例子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
--每次将表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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/77311/viewspace-1025183/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/77311/viewspace-1025183/