熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

分別刪除數據表記錄的方法

2013-11-13 22:14:18  來源: Oracle 

  很多情況下我們需要分別刪除數據表的一些記錄分批來提交以此來減少對於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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.