剛一個人在南京過完了元宵佳節，和同學去了一趟書店幫他選了一本三思的塗抹oracle，個人來看對他還是很不錯的，不然直接看eygle的深入淺出 dba3手記那是太吃力了，塗抹oracle還是很不錯的，至少能認認真真把那本書啃完是非常受益的。

即將迎來自己的未知的第二份工作，當然首先必須跟oracle相關，如果可以有管理類的工作那最好了，不過自己在技術和經驗方面還存在遠遠不足，雖然這一年來自己很努力的去肯文件做測試但是知識漏洞還是太多太多。

不瞎扯了，同事這些天需要刪除大量的資料，如果直接刪除對回滾段衝擊太大，一個執行半天delete很可能因為ora-01555而以悲劇告終。分批批次刪除提交則可以很有效的避免ora-01555錯誤而高效的刪除大量資料。

Sql>Select count(*) from test_objects01;

Count(*)

3330987

Sql>select count(*) from test_objects01 where object_id<63792;

Count(*)

697867

此時需要只保留object_id>=63792,當然由於刪除資料較多，可以利用create table test01保留不需要刪除的資料，然後truncate原表段，再insert到test_object01中。

不過需要保留的資料依然佔用很大比例，就不適用了！還是利用遊標的特性來對批次刪除進行控制吧！

Create or replace procedure del_proc01

As

Begin

loop

Delete from test01 where object_id<=63792 and rownum<=100000;

Exit when sql%notfound;--dml語句中隱式遊標屬性進行控制

Commit;

End loop;

Commit;

End;

/

注意dml語句中才存在隱式遊標，利用常用的sql%found sql%notfound sql%rowcount sql%isopen(隱式遊標中isopen查詢任何時間都返回false，所以一般不用於控制迴圈)。

同樣也可以利用sql%rowcount來控制迴圈。

Create or replace procedure del_proc02

As

Begin

Loop

Delete from test01 where object_id<=63792 and rownum<=100000;

Exit when sql%rowcount=0;

Commit;

End loop;

Commit;

End;

/

這裡摘要以下eygle的blog關於批次刪除的一個procedure，和上述自己所寫procedure加入了引數控制table_name 、where檢索條件、rownum批次刪除行數，然後後續輸出刪除的行數。（關於for all刪除的可實現性還未有清晰的理解）

SQL> create or replace procedure delBigTab

  2  (

  3    p_TableName       in    varchar2,

  4    p_Condition       in    varchar2,

  5    p_Count        in    varchar2

  6  )

  7  as

  8   pragma autonomous_transaction;

  9   n_delete number:=0;

 10  begin

 11   while 1=1 loop

 12     EXECUTE IMMEDIATE

 13       'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'

 14     USING p_Count;

 15     if SQL%NOTFOUND then

 16        exit;

 17     else

 18              n_delete:=n_delete + SQL%ROWCOUNT;

 19     end if;

 20     commit;

 21   end loop;

 22   commit;

 23   DBMS_OUTPUT.PUT_LINE('Finished!');

 24   DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');

 25  end;

 26  /

[@more@]

利用隱式遊標分批刪除資料的procedure

相關文章