oracle存储过程游标中传参数，实现动态查询
procedure PRO_PG_RUN(new_time in VARCHAR2) --测试设备运⾏
as
monitoring_time DATE;
noExistent1 VARCHAR2(4);
---------模块
--重载设备
etc什么意思CURSOR CUR_OVERLOADING_DEVICE(monitoring_time DATE) IS -- 5分钟内检测出来的重载设备记录(来⾃ 监测表和设备表) lect distinct t1.device_id as SB_device_id,t1.dt_device_name as SB_device_name,
t2.bureau_code,t2.province_code,o_code as bureau_name,o1_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
on o_code = t2.bureau_code
left join PMCC_DM.REF_PUB_ORG O1
on 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 -- ⽬前处于重载设备 (来⾃ 重过载表)
lect * 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; --赋值⼀个开关值
patriot
--重载设备
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 thenauthorware
if overloading_device_exist.IS_MONITORING = 2 then
update pmcc_dw.TWB_PG_HEAVY_OVERLOAD_DETAILS t
t
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
t
t.DEVICE_STATUS = overloading_device.device_status, --设备状态(更新)
consumptiont.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, --累计统计次数
we are one什么意思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;
el
noExistent1 := 1; --开关值(1：不存在)
end if;
end loop;
if noExistent1 = 1 thenovertherainbow
--插⼊⼀条新的记录
inrt 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;
