set serveroutput on declare i number:=0; rows number:=10000; begin loop delete TABLENAME where rownum<=rows and ...; i := SQL%ROWCOUNT+i; dbms_output.put_line('delete '||i||' rows'); commit; exit when i<>rows; end loop; end; /
对大数据量用bulk collect实现批量删除,效率会高很多,下面在9i下测试通过CREATE OR REPLACE PROCEDURE bulkdelete AS TYPE T_DATE_t IS TABLE OF TAB1.T_DATE%TYPE; T_DATE_array T_DATE_t; CURSOR c1 IS SELECT T_DATE FROM tab1 WHERE extract(MONTH FROM T_DATE) = 10;BEGIN OPEN c1; LOOP FETCH c1 BULK COLLECT INTO T_DATE_array LIMIT 1000; --一次删除1000条 FORALL i IN T_DATE_array.FIRST .. T_DATE_array.LAST DELETE FROM tab1 WHERE T_DATE = T_DATE_array(i);
EXIT WHEN c1%NOTFOUND; END LOOP; CLOSE c1; COMMIT;EXCEPTION WHEN OTHERS THEN ROLLBACK; END bulkdelete;
declare
i number:=0;
rows number:=10000;
begin
loop
delete TABLENAME where rownum<=rows and ...;
i := SQL%ROWCOUNT+i;
dbms_output.put_line('delete '||i||' rows');
commit;
exit when i<>rows;
end loop;
end;
/
SELECT T_DATE FROM tab1 WHERE extract(MONTH FROM T_DATE) = 10;BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO T_DATE_array LIMIT 1000; --一次删除1000条
FORALL i IN T_DATE_array.FIRST .. T_DATE_array.LAST
DELETE FROM tab1 WHERE T_DATE = T_DATE_array(i);
EXIT WHEN c1%NOTFOUND;
END LOOP; CLOSE c1; COMMIT;EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END bulkdelete;