如何有条件的分步删除数据表中的记录

文章作者 100test 发表时间 2008:01:11 12:23:29
来源 100Test.Com百考试题网

如何有条件的分步删除数据表中的记录作者:eygle出处:http://blog.eygle.com日期:February 22, 2005

　　有时候我们需要分配删除数据表的一些记录，分批提交以减少对于Undo的使用，本文提供一个简单的存储过程用于实现该逻辑。
　　你可以根据你的需要进行适当调整，本例仅供参考:

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