procedure PRO_PG_RUN(new_time in VARCHAR2) --测试设备运行存储过程
as
monitoring_time DATE;
noExistent1 VARCHAR2(4);
---------游标模块
--重载设备
CURSOR CUR_OVERLOADING_DEVICE(monitoring_time DATE) IS -- 5分钟内检测出来的重载设备记录(来自 监测表和设备表)
select distinct t1.device_id as SB_device_id,t1.dt_device_name as SB_device_name,
t2.bureau_code,t2.province_code,o.org_code as bureau_name,o1.org_code as province_name,
t1.create_time,t2.voltage_level,t2.device_status,
SQRT(power(t1.active_power,2) + power(t1.active_power*0.9,2))/(t2.high_vol_rated_capacity) Load_Factor
from pmcc_dm.sgcm_device_dt_data t1,pmcc_dm.sgcm_pub_main_device t2
left join PMCC_DM.REF_PUB_ORG O
on o.org_code = t2.bureau_code
left join PMCC_DM.REF_PUB_ORG O1
on o1.org_code = t2.province_code
where t1.device_id = t2.device_id
and t1.create_time > (monitoring_time - 5/(24*60))
and t1.create_time <= monitoring_time
and SQRT(power(t1.active_power,2) + power(t1.active_power*0.9,2))/(t2.high_vol_rated_capacity) >= 0.8
and SQRT(power(t1.active_power,2) + power(t1.active_power*0.9,2))/(t2.high_vol_rated_capacity) < 1;
CURSOR CUR_OVERLOADING_DEVICE_EXIST(monitoring_time DATE) IS -- 目前处于重载设备 (来自 重过载表)
select * from pmcc_dw.TWB_PG_HEAVY_OVERLOAD_DETAILS t
where t.HEAVY_OVERLOAD_TYPE = 1 --重载设备标示。
and t.END_TIME is null --未结束 及处于重过载状态下。
and t.UPDATA_TIME > (monitoring_time - 10/(24*60));
-------数据操作模块
begin
monitoring_time := to_date(new_time,'yyyy-mm-dd hh24:mi:ss');
noExistent1 := 1; --赋值一个开关值
--重载设备
for overloading_device in CUR_OVERLOADING_DEVICE(monitoring_time) loop --重载设备
for overloading_device_exist in CUR_OVERLOADING_DEVICE_EXIST(monitoring_time) loop --监测表数据（重载设备）existing
if overloading_device_exist.device_id = overloading_device.SB_device_id then
if overloading_device_exist.IS_MONITORING = 2 then
update pmcc_dw.TWB_PG_HEAVY_OVERLOAD_DETAILS t
set
t.end_time = t.UPDATA_TIME --结束时间
where t.UPDATA_TIME > (monitoring_time - 10/(24*60))
and t.UPDATA_TIME <= monitoring_time - 5/(24*60)
and t.device_id = overloading_device_exist.device_id;
commit;
noExistent1 := 0; --开关值(0：存在 ,但不再进行监测)
exit;
end if;
update pmcc_dw.TWB_PG_HEAVY_OVERLOAD_DETAILS t
set
t.DEVICE_STATUS = overloading_device.device_status, --设备状态(更新)
t.MAX_LOAD_FACTOR = greatest(t.MAX_LOAD_FACTOR,overloading_device.Load_Factor), --最高负载率(更新)
t.TOTAL_LOAD_FACTOR = t.TOTAL_LOAD_FACTOR + overloading_device.load_factor, --累计负载率
t.TOTAL_NUM = t.TOTAL_NUM + 1, --累计统计次数
t.UPDATA_TIME = monitoring_time --更新时间
where t.UPDATA_TIME > (monitoring_time - 10/(24*60))
and t.UPDATA_TIME <= monitoring_time - 5/(24*60)
and t.device_id = overloading_device_exist.device_id;
commit;
noExistent1 := 0; --开关值(0：存在)
exit;
else
noExistent1 := 1; --开关值(1：不存在)
end if;
end loop;
if noExistent1 = 1 then
--插入一条新的记录
insert into pmcc_dw.TWB_PG_HEAVY_OVERLOAD_DETAILS
(UUID,ORG_CODE,ORG_NAME,BUREAU_CODE,BUREAU_NAME,VOLTAGE_LEVEL,DEVICE_ID,DEVICE_NAME,DEVICE_STATUS,
HEAVY_OVERLOAD_TYPE,START_TIME,MAX_LOAD_FACTOR,TOTAL_LOAD_FACTOR,TOTAL_NUM,UPDATA_TIME,IS_MONITORING)
values
(sys_guid(),overloading_device.province_code,overloading_device.province_name,
overloading_device.bureau_code,overloading_device.bureau_name,overloading_device.voltage_level,
overloading_device.SB_device_id,overloading_device.SB_device_name,overloading_device.device_status,
'1',monitoring_time,overloading_device.Load_Factor,overloading_device.Load_Factor,1,monitoring_time,1
);
commit;
end if;
end loop;
