Oracle快速删除海量数据的方法

create or replace procedure delBigTab
(
p_TableName       in    varchar2,
p_Condition       in    varchar2,
p_Count        in    varchar2
)
as
  pragma autonomous_transaction;
  n_delete number:=0;
begin
     while 1=1 loop
       EXECUTE IMMEDIATE
               'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'
       USING p_Count;
       if SQL%NOTFOUND then
          exit;
       else
           DBMS_OUTPUT.PUT_LINE('Finished!' || to_char(p_count)||sysdate);
           n_delete:=n_delete + SQL%ROWCOUNT;
           DBMS_OUTPUT.PUT_LINE('Finished!' || to_char(n_delete)||sysdate);          
       end if;
       commit;
     end loop;
     commit;
DBMS_OUTPUT.PUT_LINE('Finished!');
DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');
end;

SQL> set serveroutput on
SQL> exec delbigtab('EQUIPMENTCODE','1=1','10000')三个参数的名字分别是：表名，条件，删除记录数

执行效果是:

Finished!1000007-6月 -07
Finished!1000007-6月 -07
Finished!1000007-6月 -07
Finished!2000007-6月 -07
Finished!1000007-6月 -07
Finished!3000007-6月 -07
Finished!1000007-6月 -07
Finished!3506807-6月 -07
Finished!
Totally 35068 records deleted!

PL/SQL procedure successfully completed