很多情况下我们需要分别删除数据表的一些记录分批来提交以此来减少对于Undo的使用下面我们提供一个简单的存储过程来实现此逻辑
SQL> create table test as select * from dba_objects;
Table created
SQL> create or replace procedure deleteTab
/**
** Usage: run the script to create the proc deleteTab
** in SQL*PLUS type exec deleteTab(FooID>=);
** to delete the records in the table Foo commit per records
** Condition with default value = and default Commit batch is
**/
(
p_TableName in varchar The TableName which you want to delete from
p_Condition in varchar default = Delete condition such as id>=
p_Count in varchar default Commit after delete How many records
)
as
pragma autonomous_transaction;
n_delete number:=;
begin
while = 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_OUTPUTPUT_LINE(Finished!);
DBMS_OUTPUTPUT_LINE(Totally ||to_char(n_delete)|| records deleted!);
end;
/
Procedure created
SQL> insert into test select * from dba_objects;
rows created
SQL> /
rows created
SQL> /
rows created
SQL> commit;
Commit complete
SQL> exec deleteTab(TESTobject_id >)
Finished!
Totally records deleted!
PL/SQL procedure successfully completed
注释在此实例中修正了一下增加了个缺省值以下是具体过程:
create or replace procedure deleteTab
(
p_TableName in varchar
The TableName which you want to delete from
p_Condition in varchar default =
Delete condition such as id>=
p_Count in varchar default
Commit after delete How many records
)
as
pragma autonomous_transaction;
n_delete number:=;
begin
while = 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_OUTPUTPUT_LINE(Finished!);
DBMS_OUTPUTPUT_LINE(Totally ||to_char(n_delete)|| records deleted!);
注释读者可以根据自己的实际情况来进行适当的调整
