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 asf_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);?
