oracle批量删除的方法

来源：互联网发布：用vb编写计算器的程序编辑：程序博客网时间：2024/05/23 22:19

oracle删除大数据时会对undo造成很大压力，死锁也会经常产生，所以经常采取分批提交的方式。

下面的存储过程转自：http://www.eygle.com/archives/2005/02/ecioioaeoeeeioe.html

create or replace procedure deleteTab(                                                                                                  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;                                                                                             /

execute trswcmnew.deletetab('T1','id>20','2000');