oracle存储过程游标中传参数，实现动态查询
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 as bureau__code as province_name,
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
_code = t2.bureau_code
left join PMCC_DM.REF_PUB_ORG O1
_code = t2.province_code
where t1.device_id = t2.device_id
ate_time > (monitoring_time - 5/(24*60))
ate_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  --重载设备标⽰。
oracle游标的使用
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
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_MONITORIN      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;
end;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。