一、定义存储过程
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;
/