create or replace procedure p_log_builder
as
cursor c_abs is
select t.bh,t.cgqdate cgqdate,t.createdat,tt.title,tt.cgqdate cgqdate1 from comm_tempcgqdate t inner join comm_sbwdbjcsgl tt
on f_getbh(t.bh) = tt.bdzid
and to_number(t.cgqdate)>to_number(tt.cgqdate)
and tt.title = '绝对值告警' ;
cursor c_dif is
select ttt.*,tttt.title from
(select tt.*
from
(
select max(to_number(cgqdate)) as maxtemp,min(to_number(cgqdate)) as mintemp,t.limit_temp,t.name1,bdzid,min(t.createdat) as createdat
from
(
select t.id,t.bh,t.name,t.bdzid,tt.id id1,tt.bh bh1,tt.cgqdate,ttt.cgqdate as limit_temp,substr(t.name,0,length(t.name)-1) name1,tt.createdat from sys_cgq t
inner join comm_tempcgqdate tt
on t.bh=tt.bh
left join comm_sbwdbjcsgl ttt on t.bdzid = ttt.bdzid and title = '差值告警'
)t
group by t.name1,t.limit_temp,bdzid)tt
where tt.maxtemp-mintemp>=limit_temp)ttt
inner join comm_sbwdbjcsgl tttt on tttt.bdzid = ttt.bdzid and tttt.title='差值告警' ;
begin
for r in c_abs
loop
begin
insert into sys_log_absolute(bh,cgqdate,createdat,title,cgqdate1) values(r.bh,r.cgqdate,r.createdat,r.title,r.cgqdate1);
commit;
EXCEPTION
WHEN OTHERS THEN
null;
end;
end loop;
for r in c_dif
loop
begin
insert into SYS_LOG_DIFFERENCE(
maxtemp,
mintemp,
limit_temp,
name1,
bdzid,
createdat,
title
)
values(
r.maxtemp,
r.mintemp,
r.limit_temp,
r.name1,
r.bdzid,
r.createdat,
r.title
);
commit;
EXCEPTION
WHEN OTHERS THEN
null;
end;
end loop;
end;
oracle 存储过程
最新推荐文章于 2024-06-05 16:17:52 发布