以下是我做批量刪除的時(shí)候用到的幾種方法，刪除和ＵＰＤＡＴＥ，ＩＮＳＥＲＴ差不多，可以依葫蘆畫瓢，所以我就不改了，ＬＺ自己看看吧

create or replace procedure delete_table

is

i number(10);

begin

for x in (select * from emp where DEPTNO like 'a%')

loop

delete emp where emp.id = x.id

i:=i+1;

if i>1000 then

commit;

i:=0;

end if;

end loop;

exception

when others then

dbms_out.put_line(sqlcode);

rollback;

end delete_table;

或者

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;

或者

有條件的分步刪除數(shù)據(jù)表中的記錄

--創(chuàng)建測試表

create table test as select * from dba_objects;

Table created.

--創(chuàng)建刪除表的存儲(chǔ)過程

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

--創(chuàng)建刪除的存儲(chǔ)過程

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;

/

--執(zhí)行語句

SQL> exec deleteTab('TEST','object_id >0','10000')

你看看執(zhí)行結(jié)果我試驗(yàn)過，效果還可以

總結(jié)

以上是生活随笔為你收集整理的oracle存储过程多分支怎样写,如何写一个分段提交的存储过程啊的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。