ORACLE数据库删除大数据量表过程(原创)

CREATE OR REPLACE PROCEDURE exec_delete_vpndata as
  T_STARTDATE DATE;
  T_ENDDATE   DATE;
  t_count     number;
  i           number;
BEGIN
  BEGIN
    i           := 0;
    T_STARTDATE := TO_DATE(to_char(sysdate - 18, 'yyyy-mm-dd') ||
                           ' 00:00:00',
                           'YYYY-MM-DD HH24:MI:SS');
    T_ENDDATE   := TO_DATE(to_char(sysdate - 18, 'yyyy-mm-dd') ||
                           ' 23:59:59',
                           'YYYY-MM-DD HH24:MI:SS');
    select TRUNC(count(*)/200000)+1
      into t_count
      from svr_pre_ivpn_billinfo
     where inserttime between T_STARTDATE and T_ENDDATE;
  EXCEPTION
    WHEN others THEN
      null;
  END;
  for i in 1 .. t_count loop
    delete from svr_pre_ivpn_billinfo
     where inserttime between T_STARTDATE and T_ENDDATE
       and rownum < =200000;
    commit;
  end loop;
end;