罗兰小筑@NetFire(Fire.Rolland.Han)

–每500条数据提交一次
DECLARE CNT NUMBER(10):=0;
I NUMBER(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM ep_arrearage_bak
WHERE TO_CHAR(DF_DATE,‘MM’)=‘01′;
FOR I IN 1..TRUNC(CNT/500)+1
LOOP
DELETE FROM ep_arrearage_bak WHERE TO_CHAR(DF_DATE,‘MM’)=‘01′ AND ROWNUM<=500;
COMMIT;
END LOOP;
END;

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

–创建测试表
create table test as select * from dba_objects;

–创建删除表的存储过程 
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′)