使用for循环，批量删除历史数据

declare
  maxrows number default 500000;
begin
  for i in 1 .. 100 loop
    delete from TB_OPT_LOG
     where op_time < to_date('20181001', 'yyyy-mm-dd')
       and rownum <= maxrows;
    commit;
  end loop;
end;

 

 

create or replace procedure test as
 cursor delete_cursor is select table_name from dba_tables where owner='SCOTT' and table_name in('OBJ1','OBJ2');
 V_TABLE_NAME dba_tables.table_name%type;
 BEGIN open delete_cursor;
  fetch delete_cursor into v_table_name;
   IF v_table_name= 'OBJ1' THEN
  for i in 1 .. 100 loop
    delete from scott.OBJ1
     where CREATED < to_date('20181001', 'yyyy-mm-dd')
       and rownum <= 500000;
    commit;
  end loop;
 elsif v_table_name = 'OBJ2' then
  for i in 1 .. 100 loop
    delete from scott.OBJ2
     where CREATED < to_date('20191001', 'yyyy-mm-dd')
       and rownum <= 500000;
    commit;
  end loop;
 close delete_cursor;
 end if;
 end;

 

 declare
  job number;
BEGIN
  DBMS_JOB.SUBMIT(  
        JOB => job,  /*自动生成JOB_ID*/  
        WHAT => 'delete_log;',  /*需要执行的存储过程名称或SQL语句*/ 
        NEXT_DATE => sysdate,  /*初次执行时间-立即执行*/  
        INTERVAL => 'TRUNC(sysdate+1)+1/24' /*每天凌晨一点执行一次*/
      );  
--commit;
end;     
/