oracle存储过程的定时执行,oracle存储过程定时执行简单例子

/*插入天气语句*/

INSERT INTO ENERGY.WEATHER

VALUES(SEQ_WEATHER.nextVal,sysdate,'多云转晴','24','30','西南风','南风','微风','4','2','70','89','420','','适合旅游')

/*创建存储过程 insertWeather*/

CREATE OR REPLACE

PROCEDURE insertWeather IS

BEGIN

INSERT INTO ENERGY.WEATHER

VALUES(SEQ_WEATHER.nextVal,sysdate,'多云转晴','24','30','西南风','南风','微风','4','2','70','89','420','','适合旅游'); /*seq_weather.nextVal 获取序列下一个值*/

COMMIT;

END;

declare

job number; /*定时任务名*/

begin

dbms_job.submit(job, 'insertweather;', sysdate, 'TRUNC(sysdate,''mi'') + 1 / (24*60)'); /*每分钟执行一次*/

end

commit;

declare

job number;

begin

dbms_job.submit(job, 'insertweather;', sysdate, 'TRUNC(SYSDATE + 1) + (6*60+30)/(24*60)'); /*每天早上6:30执行一次*/

end

commit;

删除、查询任务

begin

dbms_job.remove(27);

commit;

end;

select * from dba_jobs