oracle中定时任务的应用。如:定时更新
业务表log中字段为:id,adate(日期型),状态status
当插入log表数据后,如果日期加上5天后,小于当前日期,就改变status
1.触发器。
create or replace trigger jg_log_a_i
after insert or delete or update on jg_log
REFERENCING OLD AS OLD NEW AS NEW
for each row
declare
-- local variables here
begin
if inserting and :new.jcl_day is not null then
insert into sf_temp_log
(id, cqday)
values
(:new.id, :new.adate+To_number(:new.day));
end if;
if deleting then
delete from sf_temp_log tl where tl.id=:old.id;
end if;
if updating then
update sf_temp_log tl1 set tl1.cqday=:new.adate+To_number(:new.day)
where tl1.id=:old.id;
end if;
end jg_log_a_i;
2.过程:
procedure updateJdcheckLog is
v_status jg_log.status%type;
begin
for p in(select id, cqday from sf_temp_log) loop
select status into v_status from jg_log t where t.jcl_id=p.jcl_id;
if v_status ='1' and p.cqday<sysdate then
update sf_log jcl set jcl.status='0'
where jcl.id=p.id;
delete from sf_temp_log tl where tl.id=p.id;
end if;
end loop;
end updateJdcheckLog;
3.创建DBMS_jobs
在What栏输入:包名.过程名(如:test.updateJdcheckLog)
在Interval栏输入:TRUNC(sysdate,'mi') + 1/ (24*60);根据需要输入时间间隔。
点击Apply即可。