【指标统计】标记存量遥控（成功/失败）遥信（正确/错误）

【达梦】标记成功/失败的遥控

declareoccur_time_var TIMESTAMP;soe_time_var timestamp;obj_id_var bigint;status_var integer;content_var VARCHAR;cnt integer;Pcur CURSOR; beginOPEN Pcur FOR select distinct e.occur_time,e.obj_id,e.status,e.content from scada_event_yk e where rtu_type in (3,4,7,8) and alarm_type = 4 and status = 16 and result is null;LOOPFETCH Pcur INTO occur_time_var,obj_id_var,status_var,content_var;EXIT WHEN Pcur%NOTFOUND;execute immediate 'select count(*) from scada_event_yk where alarm_type = 4 and status = 17 and occur_time <=? and occur_time >= ? and obj_id = ?;' into cnt using occur_time_var+numtodsinterval(30,'second'),occur_time_var,obj_id_var;if (cnt < 1) thenexecute immediate 'update scada_event_yk set result = 1 where alarm_type = 4 and occur_time =? and obj_id = ? and status = ? and result is null;' using occur_time_var,obj_id_var,status_var;else execute immediate 'update scada_event_yk set result = 0 where alarm_type = 4 and occur_time =? and obj_id = ? and status = ? and result is null;' using occur_time_var,obj_id_var,status_var;end if;END LOOP;CLOSE Pcur; end; commit;

【达梦】标记正确/错误的遥信

declareoccur_time_var TIMESTAMP;soe_time_var timestamp;obj_id_var bigint;status_var integer;content_var VARCHAR;cnt integer;Pcur CURSOR; beginOPEN Pcur FOR select distinct e.occur_time,e.obj_id,e.status,e.content from scada_event_yx e where rtu_type in (3,4,7,8) and alarm_type = 1 and result is null;LOOPFETCH Pcur INTO occur_time_var,obj_id_var,status_var,content_var;EXIT WHEN Pcur%NOTFOUND;execute immediate 'select count(*) from scada_event_yx where alarm_type = 3 and soe_time <=? and soe_time >= ? and obj_id = ? and status = ?;' into cnt using occur_time_var,occur_time_var-numtodsinterval(15,'second'),obj_id_var,status_var;if (cnt < 1) thenexecute immediate 'update scada_event_yx set result = 1 where alarm_type = 1 and occur_time =? and obj_id = ? and status = ?;' using occur_time_var,obj_id_var,status_var;else execute immediate 'update scada_event_yx set result = 0 where alarm_type = 1 and occur_time =? and obj_id = ? and status = ?;' using occur_time_var,obj_id_var,status_var;end if;END LOOP;CLOSE Pcur; end; commit;

【金仓】标记成功/失败的遥控

--创建存储过程 CREATE or REPLACE PROCEDURE "SMARTSYS"."PROC_YK"() AS declareoccur_time_var TIMESTAMP;soe_time_var timestamp;obj_id_var bigint;status_var integer;content_var VARCHAR;cnt integer;Pcur refcursor; beginOPEN Pcur FOR select distinct e.occur_time,e.obj_id,e.status,e.content from scada_event_yk e where rtu_type in (3,4,7,8) and alarm_type = 4 and status = 16 and result is null;LOOPFETCH Pcur INTO occur_time_var,obj_id_var,status_var,content_var;EXIT WHEN Pcur%NOTFOUND;execute immediate 'select count(*) from scada_event_yk where alarm_type = 4 and status = 17 and occur_time <=$1 and occur_time >= $2 and obj_id = $3;' into cnt using occur_time_var+cast('30' as interval second),occur_time_var,obj_id_var;if (cnt < 1) thenexecute immediate 'update scada_event_yk set result = 1 where alarm_type = 4 and occur_time =$1 and obj_id = $2 and status = $3 and result is null;' using occur_time_var,obj_id_var,status_var;else execute immediate 'update scada_event_yk set result = 0 where alarm_type = 4 and occur_time =$1 and obj_id = $2 and status = $3 and result is null;' using occur_time_var,obj_id_var,status_var;end if;END LOOP;CLOSE Pcur; end; --执行存储过程 select PROC_YK(); --删除存储过程 drop PROCEDURE PROC_YK();

【金仓】标记正确/错误的遥信

--创建存储过程 CREATE or REPLACE PROCEDURE "SMARTSYS"."PROC_YX"() AS declareoccur_time_var TIMESTAMP;soe_time_var timestamp;obj_id_var bigint;status_var integer;content_var VARCHAR;cnt integer;Pcur refcursor; beginOPEN Pcur FOR select distinct e.occur_time,e.obj_id,e.status,e.content from scada_event_yx e where rtu_type in (3,4,7,8) and alarm_type = 1 and result is null;LOOPFETCH Pcur INTO occur_time_var,obj_id_var,status_var,content_var;EXIT WHEN Pcur%NOTFOUND;execute immediate 'select count(*) from scada_event_yx where alarm_type = 3 and soe_time <=$1 and soe_time >= $2 and obj_id = $3 and status = $4;' into cnt using occur_time_var,occur_time_var-cast('15' as interval second),obj_id_var,status_var;if (cnt < 1) thenexecute immediate 'update scada_event_yx set result = 1 where alarm_type = 1 and occur_time =$1 and obj_id = $2 and status = $3;' using occur_time_var,obj_id_var,status_var;else execute immediate 'update scada_event_yx set result = 0 where alarm_type = 1 and occur_time =$1 and obj_id = $2 and status = $3;' using occur_time_var,obj_id_var,status_var;end if;END LOOP;CLOSE Pcur; end; --执行存储过程 select PROC_YX(); --删除存储过程 drop PROCEDURE PROC_YX();

总结

以上是生活随笔为你收集整理的【指标统计】标记存量遥控（成功/失败）遥信（正确/错误）的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。