很多情況下我們需要分別刪除數據表的一些記錄分批來提交以此來減少對於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!);
注釋讀者可以根據自己的實際情況來進行適當的調整
From:http://tw.wingwit.com/Article/program/Oracle/201311/18562.html
