用存储过程实现删除数据表的部分记录

SQL> create table test as select * from dba_objects;Table created.SQL> create or replace PRocedure deleteTab  2  /**  3   ** Usage: run the script to create the proc deleteTab  4   **        in SQL*PLUS, type "exec deleteTab('Foo','ID>=1000000','3000');"  5   **        to delete the records in the table "Foo", commit per 3000 records.  6   **       Condition with default value '1=1' and default Commit batch is 10000.  7   **/  8  (  9    p_TableName    in    varchar2,    -- The TableName which you want to delete from 10    p_Condition    in    varchar2 default '1=1',    -- Delete condition, such as "id>=100000" 11    p_Count        in    varchar2 default '10000'    -- Commit after delete How many records 12  ) 13  as 14   pragma autonomous_transaction; 15   n_delete number:=0; 16  begin 17   while 1=1 loop 18     EXECUTE IMMEDIATE 19       'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn' 20     USING p_Count; 21     if SQL%NOTFOUND then 22     exit; 23     else 24          n_delete:=n_delete + SQL%ROWCOUNT; 25     end if; 26     commit; 27   end loop; 28   commit; 29   DBMS_OUTPUT.PUT_LINE('Finished!');  30   DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!'); 31  end; 32  /Procedure created.SQL> insert into test select * from dba_objects;6374 rows created.SQL> /6374 rows created.SQL> /6374 rows created.SQL> commit;Commit complete.SQL> exec deleteTab('TEST','object_id >0','3000')Finished!Totally 19107 records deleted!PL/SQL procedure successfully completed.

create or replace procedure deleteTab(                                                                                                  p_TableName    in    varchar2,    -- The TableName which you want to delete from                 p_Condition    in    varchar2 default '1=1',    -- Delete condition, such as "id>=100000"                      p_Count        in    varchar2 default '10000'    -- Commit after delete How many records                      )                                                                                                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                                                                                                  n_delete:=n_delete + SQL%ROWCOUNT;                                                          end if;                                                                                          commit;                                                                                        end loop;                                                                                        commit;                                                                                          DBMS_OUTPUT.PUT_LINE('Finished!');                                                               DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');