oracle 定时任务实例

1.权限-----使用oa帐号设置权限
grant select on oa.t_App_Jfbx_Cwbxforxm to cwjk;
grant update on oa.t_App_Jfbx_Cwbxforxm to cwjk;

grant select on oa.t_app_jfbx_rcfybx to cwjk;
grant update on oa.t_app_jfbx_rcfybx to cwjk;

2.存储过程

create or replace procedure updateCwshState as
f_ywlxdm_temp varchar2(2);
f_ywid_forkyxxw_temp number(10);
f_cwsh_state_temp varchar2(2);
f_id_temp number(10);
begin
        for i in(select t.id,t.f_ywlxdm,t.f_ywid_forkyxxw,t.f_cwsh_state from t_ywbltx t where (t.f_ywlxdm='04' or t.f_ywlxdm='07') and t.f_js_readstate<>'1' ) loop
          f_ywlxdm_temp:=i.f_ywlxdm;
          f_ywid_forkyxxw_temp:=i.f_ywid_forkyxxw;
          f_cwsh_state_temp:=i.f_cwsh_state;
          f_id_temp:=i.id;
          if(f_ywlxdm_temp='04') then--课题
             update oa.t_app_jfbx_cwbxforxm t set t.F_CWSHSTATE=DECODE(f_cwsh_state_temp,'0','财务正在审核','1','财务审核完毕')  where t.id=f_ywid_forkyxxw_temp;
          else--日常
             update oa.t_app_jfbx_rcfybx t set t.F_CWSHSTATE=DECODE(f_cwsh_state_temp,'0','财务正在审核','1','财务审核完毕')  where t.id=f_ywid_forkyxxw_temp;
          end if;
          if(f_cwsh_state_temp='1') then
             update t_ywbltx t set t.f_js_readstate='1' where t.id=f_id_temp;
          end if;
      end loop;
end ;


3.任务-----使用cwjk设置定时任务

定义一个任务
variable cwjk_job number;  
begin 
     dbms_job.submit(:cwjk_job,'updateCwshState;',sysdate,'sysdate+1/1440');
end;

运行
exec dbms_job.run(:cwjk_job);
终止任务运行
exec dbms_job.broken(:cwjk_job,true);
删除任务
exec dbms_job.remove(:cwjk_job);