教你快速掌握分别删除数据表记录的方法

　　很多情况下我们需要分别删除数据表的一些记录，分批来提交以此来减少对于Undo的使用，下面我们提供一个简单的存储过程来实现此逻辑。

　　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.

　　注释：在此实例中修正了一下,增加了2个缺省值,以下是具体过程:

　　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!');

　　注释：读者可以根据自己的实际情况来进行适当的调整。