PROCEDURE_监测系统_告警信息存储过程—产生告警信息插入告警表
create or replace procedure proc_alarmlog(in_id in number, --采集器编码
ip_code in number, --采集器ip
in_time varchar2, --采集时间
t_arr num_array, --温度数据
h_arr num_array, --湿度数据
c_arr num_array) --二氧化碳数据
is
min_table num_type := num_type(0); --仓库监控阈值范围最小值
max_table num_type := num_type(0); --仓库监控阈值范围最大值
state_table num_type := num_type(0); --阈值范围对应的仓库状态
v_value number; --采集数据的临时变量
v_wid number; --仓库编码
v_count number; --临时变量，用于判断仓库某个采集区域是否存在警告或异常
v_state number; --临时变量，用于判断仓库某个采集区域的状态
v_reason varchar2(100):='';--临时变量，用于存储异常信息
v_err varchar2(200);--临时变量，用于存储异常信息
begin
--根据采集器编码获取仓库编码
begin
select wid into v_wid from warehouse_new t where t.client_id = in_id;
exception
when no_data_found then
return; --无法找到对应的仓库，返回
end;
--根据仓库编码获得仓库温度配置
--按告警状态倒序查询
--便于判断温度值状态
select min_value, max_value, state bulk collect
into min_table, max_table, state_table
from temperature t
where t.wid = v_wid
and t.state != 1
order by t.state desc;
--循环校验温度十个字段值，如果为255跳出循环
for i in 1 .. 10 loop
if t_arr(i) = 255 then
exit;
else
v_state := 1;
for j in 1 .. state_table.count loop
if t_arr(i) >= min_table(j) and t_arr(i) <= max_table(j) then
v_state := state_table(j);
if v_state = 3 then
v_reason:='异常，范围：'||min_table(j)||'到'||max_table(j);
elsif v_state = 2 then
v_reason:='告警，范围：'||min_table(j)||'到'||max_table(j);
else
v_reason:='';
end if;
exit; --退出内层循环
end if;
end loop;
select count(1)--判断该区域是否已存在未解决的告警信息
into v_count
from alarmlog t
where t.solvestate = 0
and t.jurisdictionid = i
and t.warehouseid = v_wid;
if v_count > 0 then
update alarmlog t
set t.state = decode(v_state,1,t.state,v_state),--如果状态是正常，则不改变告警信息记录的状态
t.solvetime = decode(v_state,1,to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),''),
t.solvestate = decode(v_state,1,1,0)--如果状态是正常则改变告警信息的解决状态为1：已解决
where t.solvestate = 0
and t.jurisdictionid = i
and t.indicator = 1--温度
and t.warehouseid = v_wid;
elsif v_state!= 1 then
insert into alarmlog
(id,--主键
time,--采集时间
reason,--告警原因
warehouseid,--所属仓库编码
jurisdictionid,--异常区域（1-10）
solvestate,--异常解决状态
state,--发生异常时仓库状态
value,--发生异常的值
ipcode,--采集器编码
indicator)--异常种类(温度、适度、二氧化碳)
values
(alarmlog_id_seq.nextval, in_time, v_reason,v_wid,i,0,v_state,t_arr(i),in_id,1);
end if;
end if;
end loop;
--根据仓库编码获得仓库湿度配置
--按告警状态倒序查询
--便于判断温度值状态
select min_value, max_value, state bulk collect
into min_table, max_table, state_table
from humidity t
where t.wid = v_wid
and t.state != 1
order by t.state desc;
--循环校验湿度十个字段值，如果为255跳出循环
for i in 1 .. 10 loop
if h_arr(i) = 255 then
exit;
else
v_state := 1;
for j in 1 .. state_table.count loop
if h_arr(i) >= min_table(j) and h_arr(i) <= max_table(j) then
v_state := state_table(j);
if v_state = 3 then
v_reason:='异常，范围：'||min_table(j)||'到'||max_table(j);
elsif v_state = 2 then
v_reason:='告警，范围：'||min_table(j)||'到'||max_table(j);
else
v_reason:='';
end if;
exit; --退出内层循环
end if;
end loop;
select count(1)--判断该区域是否已存在未解决的告警信息
into v_count
from alarmlog t
where t.solvestate = 0
and t.jurisdictionid = i
and t.warehouseid = v_wid;
if v_count > 0 then
update alarmlog t
set t.state = decode(v_state,1,t.state,v_state),--如果状态是正常，则不改变告警信息记录的状态
t.solvetime = decode(v_state,1,to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),''),
t.solvestate = decode(v_state,1,1,0)--如果状态是正常则改变告警信息的解决状态为1：已解决
where t.solvestate = 0
and t.indicator = 2--湿度
and t.jurisdictionid = i
and t.warehouseid = v_wid;
elsif v_state!= 1 then
insert into alarmlog
(id,--主键
time,--采集时间
reason,--告警原因
warehouseid,--所属仓库编码
jurisdictionid,--异常区域（1-10）
solvestate,--异常解决状态
state,--发生异常时仓库状态
value,--发生异常的值
ipcode,--采集器编码
indicator)--异常种类(温度、适度、二氧化碳)
values
(alarmlog_id_seq.nextval, in_time, v_reason,v_wid,i,0,v_state,h_arr(i),in_id,2);
end if;
end if;
end loop;
-- commit;
exception when others then
v_err:=sqlerrm;
insert into operationlog
(id, time, behavior, state, reason)
values
(operationlog_id_seq.nextval,
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
'生成告警信息发生数据库异常',
1,
v_err);
-- commit;
end proc_alarmlog;
