Oracle 对表中的记录进行大批量删除

以下是一些对大批量删除进行分批删除逐次提交的代码,可根据自己的实际情况测试修改后实施.

--对无关联的单表中的记录按条件删除
declare
?n_count number;
?n_rownum number:=10000;
?begin
?select count(*) into n_count from tb_detail where createdate?for i in 1..ceil(icount/irownum) loop
? ? delete from? tb_detail a
? ? where? createdate? ? commit;
?end loop;
?end;

--对有关联的表按条件删除
declare
? type ridArray is table of rowid index by binary_integer;
? type dtArray is table of varchar2(50) index by binary_integer;
? v_rowid? ? ? ? ridArray;
? v_fid_to_delete dtArray;
? n_delete? ? ? ? number;
? n_rownum? ? ? ? number:=10000;
?begin
? select count(*)
? ? into n_delete
? ? from tb_main
? ? where createdate < to_date('20140101', 'yyyymmdd');
? for i in 1 .. ceil(n_delete / n_rownum) loop
? ? select fid, rowid BULK COLLECT
? ? ? INTO v_fid_to_delete, v_rowid
? ? ? from tb_main
? ? ? where createdate < to_date('20140101', 'yyyymmdd')
? ? ? ? and rownum <= n_rownum;
? ? forall j in 1 .. v_fid_to_delete.COUNT
? ? ? delete from tb_detail where fid = v_fid_to_delete(j);
? ? forall k in 1 .. v_rowid.COUNT
? ? ? delete from tb_main where rowid = v_rowid(k);
? ? commit;
? end loop;
?end;

--对有关联的表按条件删除子表或主表
declare
? type dtArray is table of varchar2(50) index by binary_integer;
? v_fid_to_delete dtArray;
? n_delete? ? ? ? number;
? n_rownum? ? ? ? number := 10000;
?begin
? select fid BULK COLLECT
? ? INTO v_fid_to_delete
? ? from tb_main
? ? where createdate < to_date('20140601', 'yyyymmdd');
? for i in 1 .. ceil(v_fid_to_delete.COUNT / n_rownum) loop
? ? forall j in (i - 1) * n_rownum + 1 .. least(i * n_rownum,v_fid_to_delete.COUNT)
? ? ? delete from tb_detail where fid = v_fid_to_delete(j);
? ? commit;
? end loop;
?end;