有条件的分步删除数据表中的记录
--创建测试表
create table test as select * from dba_objects; Table created.
--创建删除表的存储过程
create or replace procedure deleteTab
--插入语句
SQL> insert into test select * from dba_objects; 6374 rows created. SQL> / 6374 rows created. SQL> / 6374 rows created. SQL> commit; --创建删除的存储过程
create or replace procedure deleteTab
/**
** Usage: run the script to create the proc deleteTab
** in SQL*PLUS, type "exec deleteTab('Foo','ID>=1000000','3000');"
** to delete the records in the table "Foo", commit per 3000 records.
** Condition with default value '1=1' and default Commit batch is 10000.
**/
(
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!');
end;
/ --执行语句
SQL> exec deleteTab('TEST','object_id >0','10000')
你看看执行结果我试验过，效果还可以 |