oracle定时任务

 

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即可。