–1. 創建待刪除記錄特征表

CREATE TABLE "SMARTSYS"."scada_event_yx_mark" ( "OCCUR_TIME" TIMESTAMP(0) NOT NULL, "OBJ_ID" BIGINT NOT NULL, "STATUS" INTEGER NOT NULL, "ALARM_TYPE" INTEGER NOT NULL, "CONTENT" VARCHAR(512), "GRAND_ID" BIGINT, "ISDEL" INTEGER default 0) STORAGE(ON "SMART_HISTORY", CLUSTERBTR);

–2. 創建查找容器的函數

CREATE OR REPLACE FUNCTION get_dev_container(OBJ_ID IN BIGINT, CONT_TAB IN VARCHAR) RETURN BIGINT AS DECLAREid_var bigint;cont_id_var bigint;cont_tab_var varchar(64);sql_str VARCHAR(1024); BEGINIF (OBJ_ID IS NULL or OBJ_ID < 2 or cont_tab IS NULL or length(cont_tab)=0) THENRETURN NULL;ELSEsql_str := 'select id, container_id, container_tablename from '||cont_tab||' where id = '||OBJ_ID;execute immediate sql_str into id_var, cont_id_var, cont_tab_var;IF (cont_tab = 'obj_dev') THENRETURN cont_id_var;END IF;return get_dev_container(cont_id_var, cont_tab_var);END IF; END;

–3. 更新容器

update scada_event_yx_mark set grand_id = get_dev_container(obj_id, 'pnt_dig');

–4. 連續幾條變位來自同一個設備的容器（饋線或者環網柜）

declareoccur_time_var TIMESTAMP;obj_id_var bigint;status_var integer;grand_id_var bigint;old_occur_time_var TIMESTAMP;old_obj_id_var bigint;old_status_var integer;old_grand_id_var bigint;cnt integer;Pcur CURSOR; beginold_occur_time_var := '1900-01-01';old_obj_id_var := -1;old_status_var := -1;old_grand_id_var := -1;update scada_event_yx_mark set isdel = 0;OPEN Pcur FOR select occur_time,obj_id,status,grand_id from scada_event_yx_mark order by occur_time;LOOPFETCH Pcur INTO occur_time_var,obj_id_var,status_var,grand_id_var;EXIT WHEN Pcur%NOTFOUND;if old_grand_id_var = grand_id_var THENupdate scada_event_yx_mark set isdel = 1 where occur_time = occur_time_var and obj_id = obj_id_var and status = status_var and grand_id = grand_id_var;update scada_event_yx_mark set isdel = 1 where occur_time = old_occur_time_var and obj_id = old_obj_id_var and status = old_status_var and grand_id = old_grand_id_var;end if;old_occur_time_var := occur_time_var;old_obj_id_var := obj_id_var;old_status_var := status_var;old_grand_id_var := grand_id_var;END LOOP;CLOSE Pcur;update scada_event_yx_mark set isdel = 1 where grand_id is null; end;

總結

以上是生活随笔為你收集整理的【指标统计】统计装置总招遥信的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。