oracle 触发器定时任务,ORACLE触发器，定时器。

ORACLE定时器是靠时间去触发JOB，而触发器是靠事件去触发JOB.从安全性上考虑，触发器的安全度要高于定时器。机制也优于定时器。

-- ORACLE通过标志表实现存储过程触发。数据执行完毕后更新记录。

create table TRI_ERMETL(object_name varchar2(100),data_date char(8),flag char(1),curr_time date);

insert into TRI_ERMETL values('ERMETL','20180324','N',sysdate);

CREATE OR REPLACE TRIGGER ETL_TRIGGER

AFTER INSERT ON TRI_ERMETL

DECLARE v_date date;

begin

select to_date(data_date,'yyyy-mm-dd') into v_date

from (select data_date,row_number ()over(order by curr_time desc) as rn from TRI_ERMETL

where flag ='N' and object_name='ERMETL'

) where rn=1 ;

commit;

erm_etl.main(v_date);

update TRI_ERMETL set flag='Y' where curr_time in (select max(curr_time) from TRI_ERMETL

where flag ='N' and object_name='ERMETL' and data_date=to_char(v_date,'YYYYMMDD'));

end;

--定时器

DECLARE

job_no_ NUMBER;

BEGIN

DBMS_JOB.SUBMIT(job_no_,

'ERM_ETL.MAIN(TRUNC(SYSDATE)-1);',             -- 调用包，参数为当前日期前一天

sysdate,

'TRUNC(sysdate)+1+12/24');     -- 12/24 每天中午2点

COMMIT;

END;

/**

查询JOB

select

job,last_date,last_sec,next_sec,total_time,interval,what

from user_jobs

*/