1、编写定时任务存储过程(别的存储过程太长,贴个触发器冒充下)
CREATE OR REPLACE TRIGGER TRU_ACCEPT_ABNORMAL_STATE
--状态更新触发器,当已整改时,级联更新本条记录的24小时或48小时状态
BEFORE UPDATE ON ACCEPT_ABNORMAL
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
IF :OLD.STATE24H IS NOT NULL AND :NEW.STATE = 3 THEN
:NEW.STATE24H :=3;
END IF;
IF :OLD.STATE48H IS NOT NULL AND :NEW.STATE = 3 THEN
:NEW.STATE48H := 3;
END IF;
END;
2.创建调度任务
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
JOB_NAME => 'CHANGE_STATE', --任务名称
JOB_TYPE => 'STORED_PROCEDURE', --任务类型
JOB_ACTION => 'TRU_ACCEPT_ABNORMAL_STATE',--任务执行的程序名称
START_DATE => '', --开始执行时间
REPEAT_INTERVAL => 'FREQ=MINUTELY;INTERVAL=10',--执行频率:每分钟,间隔10秒
END_DATE => NULL, --结束时间
ENABLED => TRUE, --任务创建完毕后是否自动激活
AUTO_DROP => FALSE, --自动删除???
COMMENTS => '案例任务调度'); --备注,任务说明
END;
3.运行调度任务
BEGIN
DBMS_SCHEDULER.RUN_JOB('CHANGE_STATE');
END;
4、任务调度查询
select * from user_SCHEDULER_jobs;
另一种方法,貌似简单点:
DECLARE
v_job NUMBER;
BEGIN
DBMS_JOB.SUBMIT(v_job,'PROC_ZBSJB_JSSJ;',TRUNC(sysdate)+1/24,'TRUNC(sysdate)+1+1/24'); --- 每天凌晨1点执行一次, 从今天开始
commit;
END;
查看开始的任务:
SELECT * from user_jobs;
删除任务:
begin
dbms_job.remove(70);--在user_jobs中查到的id
commit;
--:job可以用dba_jobs.job的值代替如:1198
end;