解决生产问题--oracle篇

oracle判断定时任务是否正常执行的sql

---

前言

业务场景：由于这套系统的某些定时任务（每天早上8点整执行）会不定时挂掉（服务器内存时不时满了，甲方还不给扩容），导致当天定时任务没有执行，也存在定时任务执行出现异常的场景，导致系统出现异常，给用户带来很大不便。因此需要写个语句由第三方定时执行，判断是否给管理员发出邮件告警。
因此本次查询条件是：
1、如果当天没有执行定时任务（日志表task_log没有记录），则需要发出警告。
2、如果当天执行异常，且异常内容不是“今天没有将要到期的测试卡”，则需要发出警告。
表数据如下，表名：task_log，关键字段results，finish_date

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、列举出两种场景

1、判断当天没有执行定时任务（日志表task_log没有记录），如果没有if_exist = false：
select 1 as r,n1, case when n1 = 1 then ‘ture’ else ‘false’ end as if_exist
from (select count(*) as n1
from scyx.task_log a
where a.cfg_task_id = 1000000289
and a.finish_date > sysdate - 1);

2、如果当天执行异常，且异常内容不是“今天没有将要到期的测试卡”，则if_ok = false：
select 1 as r,n2, case when n2 > 0 then ‘false’ else ‘true’ end as if_ok from(
select count(*) n2
from scyx.task_log a
where a.cfg_task_id = 1000000289
and a.finish_date > sysdate - 1
and a.results <> ‘ok’
and a.results not like ‘%今天没有将要到期的测试卡%’);

二、关联查询，将两条数据放到一个临时表里

select n1, n2, if_exist, if_ok
from (select 1 as r,
n1,
case
when n1 = 1 then
‘ture’
else
‘false’
end as if_exist
from (select count() as n1
from task_log a
where a.cfg_task_id = 1000000289
and a.finish_date > sysdate - 1)) t1
left join (select 1 as r,
n2,
case
when n2 > 0 then
‘false’
else
‘true’
end as if_ok
from (select count() n2
from task_log a
where a.cfg_task_id = 1000000289
and a.finish_date > sysdate - 1
and a.results <> ‘ok’
and a.results not like ‘%今天没有将要到期的测试卡%’)) t2
on t1.r = t2.r

三、得出结果

– if_alarm = 1 需要告警， if_alarm = 0 不需要告警
select case
when if_exist = ‘false’ or if_ok = ‘false’ then
1
else
0
end as if_alarm
from (select if_exist, if_ok
from (select 1 as r,
n1,
case
when n1 = 1 then
‘ture’
else
‘false’
end as if_exist
from (select count() as n1
from task_log a
where a.cfg_task_id = 1000000289
and a.finish_date > sysdate - 1)) t1
left join (select 1 as r,
n2,
case
when n2 > 0 then
‘false’
else
‘true’
end as if_ok
from (select count() n2
from task_log b
where b.cfg_task_id = 1000000289
and b.finish_date > sysdate - 1
and b.results <> ‘ok’
and b.results not like ‘%今天没有将要到期的测试卡%’)) t2
on t1.r = t2.r) t;

---

总结

sql写的比较长，不知道有没有大佬指正修改。sql再复杂的条件，只要拆分成一个个简单的条件，问题就迎刃而解了。