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

最新推荐文章于 2025-06-25 17:05:28 发布

原创最新推荐文章于 2025-06-25 17:05:28 发布 · 3.8k 阅读

0 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

8 篇文章

订阅专栏

本文详细介绍了DPK_HQ_POS_MOVE工作流程的自动化管理，包括启动、重启、日志记录等关键步骤，确保了系统运行的稳定性和效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;