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,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;

end;