Oracle用定时任务存储过程将数据转存到历史表，提高查询速度

一、定义存储过程

CREATE OR REPLACE PROCEDURE Sync_INFO_HISTORY IS
BEGIN
  insert into depart_passenger_info_history
    select * from depart_passenger_info where flt_date<trunc(sysdate-30);
  delete from depart_passenger_info where flt_date<trunc(sysdate-30);
  commit;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      rollback;
    WHEN OTHERS THEN
      rollback;
      RAISE;
END Sync_INFO_HISTORY;

二、Oracle定时任务执行存储过程

begin
  sys.dbms_job.submit(job => :job,
                      what => 'begin
  <span style="white-space:pre">				</span>sync_info_history;
<span style="white-space:pre">			</span>       end;',
                      next_date => to_date('01-01-4000', 'dd-mm-yyyy'),
                      interval => 'sysdate+1');
  commit;
end;
/