Oracle中job的启动和job挂掉后得重启和写日志问题
create or replace package body dpk_hq_job_relation is
procedure RunJobRelation is
begin
   job_start;
   restart;
   Write_Job_Log;
end RunJobRelation;
                       
procedure JobStart is
  job_num number;
  n_job number;
begin
  select count(*) into job_num from user_jobs where what in('DPK_HQ_POS_MOVE.RunPosMove(null,null,null);',
                                                            'DPK_HQ_POS_MOVE.Del_PosData(15);',
                                                            'DPK_HQ_POS_MOVE.Del_PosDataLog(60);');
if job_num=3 then
   dbms_output.put_line('全部jog已经启动');
else
   begin
      select count(*) into job_num from user_jobs where what in('DPK_HQ_POS_MOVE.RunPosMove(null,null,null);');
      if job_num=0 then
         sys.dbms_job.submit(job => n_job,
                             what => 'DPK_HQ_POS_MOVE.RunPosMove(null,null,null);',
                             interval => 'sysdate+30/(24*60)');
         dbms_output.put_line('DPK_HQ_POS_MOVE.RunPosMove(null,null,null);已经启动');
      end if;
      select count(*) into job_num from user_jobs where what in('DPK_HQ_POS_MOVE.Del_PosData(15);');
      if job_num=0 then
         sys.dbms_job.submit(job => n_job,
                             what => 'DPK_HQ_POS_MOVE.Del_PosData(15);',
                             interval => 'sysdate+1');
      dbms_output.put_line('DPK_HQ_POS_MOVE.Del_PosData(15);已经启动');
      end if;
      select count(*) into job_num from user_jobs where what in('DPK_HQ_POS_MOVE.Del_PosDataLog(60);');
      if job_num=0 then
         sys.dbms_job.submit(job => n_job,
                             what => 'DPK_HQ_POS_MOVE.Del_PosDataLog(60);',
                             interval => 'sysdate+1');
      dbms_output.put_line('DPK_HQ_POS_MOVE.Del_PosDataLog(60);已经启动');
      end if;
   end;
commit;
end if;
end JobStart;

procedure ReStart is
jobno user_jobs.JOB%Type;
n_job number;
v_job_str varchar(1000);
  cursor crjob is
         select JOB from user_jobs where WHAT in('DPK_HQ_POS_MOVE.RunPosMove(null,null,null);',
                                                 'DPK_HQ_POS_MOVE.Del_PosData(15);',
                                                 'DPK_HQ_POS_MOVE.Del_PosDataLog(60);')
                                                  and (BROKEN='Y' or NEXT_DATE+6<sysdate);
begin
  open crjob;
  loop
    fetch crjob
       into jobno;
    exit when crjob%Notfound;
    select WHAT into v_job_str from user_jobs where JOB=jobno;
    dbms_job.remove(jobno);
    commit;    
    case (v_job_str)
       when 'DPK_HQ_POS_MOVE.RunPosMove(null,null,null);' then
          sys.dbms_job.submit(job => n_job,
                      what => 'DPK_HQ_POS_MOVE.RunPosMove(null,null,null);',
                      interval => 'sysdate+30/(24*60)');
         when 'DPK_HQ_POS_MOVE.Del_PosData(15);' then
             sys.dbms_job.submit(job => n_job,
                      what => 'DPK_HQ_POS_MOVE.Del_PosData(15);',
                      interval => 'sysdate+1');
          when  'DPK_HQ_POS_MOVE.Del_PosDataLog(60);'  then
             sys.dbms_job.submit(job => n_job,
                      what => 'DPK_HQ_POS_MOVE.Del_PosDataLog(60);',
                      interval => 'sysdate+1');
     end case;
  end loop;
  commit;
  close crjob;
end ReStart;

procedure Write_Job_Log is
jobrow user_jobs%ROWTYPE;
cursor crjob is
         select * from user_jobs where WHAT in('DPK_HQ_POS_MOVE.RunPosMove(null,null,null);',
                                                 'DPK_HQ_POS_MOVE.Del_PosData(15);',
                                                 'DPK_HQ_POS_MOVE.Del_PosDataLog(60);');
begin
  open crjob;
  loop
    fetch crjob
       into jobrow;
    exit when crjob%Notfound;
    insert into HQ_JOB_LOG
      (JOB, WHAT, FAILURES)
    values
      (jobrow.JOB, jobrow.WHAT, jobrow.FAILURES);
    if jobrow.FAILURES > 10 then
       dbms_output.put_line('job:'||jobrow.JOB||'调用的存储过程:'||jobrow.WHAT||'有问题,必须手动修改!');
    end if;
  end loop;
  commit;
  close crjob;
end Write_Job_Log;



end dpk_hq_job_relation;
阅读更多
个人分类: Oracle
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

Oracle中job的启动和job挂掉后得重启和写日志问题

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭