oracle 存储过程完整-动态sql&动态游标双重游标

create or replace procedure sgcm_work_plan_history IS
  TYPE ref_cursor_type IS REF CURSOR;  --定义一个动态游标
  usrs ref_cursor_type;
  str_classify_id VARCHAR2(250); ---------类别id
  v_sql varchar2(1000);
  device_id VARCHAR2(250);
  device_name VARCHAR2(250);
  center_substation_id VARCHAR2(250);
  center_name VARCHAR2(250);
  substation_id VARCHAR2(250);
  substation_name VARCHAR2(250);
  p_msg VARCHAR2(250);
    --获取设备类别编码
    CURSOR cur_device_type_en is SELECT t.dictionary_key,t.dictionary_value FROM sgcm_dictionary t WHERE dictionary_type = 'key_decision_lib_device_type';
    --活设备类别编号
    CURSOR cur_device_type_num IS SELECT t.dictionary_key,t.dictionary_value FROM sgcm_dictionary t WHERE t.dictionary_type = 'main_device_type';
BEGIN
  -- 清空关键设备指标表数据
  delete from ECMS_KD_TARGET_DATA;
   COMMIT;
  for cur_row_en in cur_device_type_en LOOP
    for cur_row_num in cur_device_type_num LOOP
      -- 拆分运行编号
      if cur_row_en.dictionary_value = cur_row_num.dictionary_value  then
         str_classify_id := cur_row_num.dictionary_key;
         v_sql := 'SELECT dd.device_id,dd.device_name,dd.center_substation_id,dd.center_name,dd.substation_id,dd.substation_name FROM sgcm_pub_main_device dd WHERE  classify_id IN ('||str_classify_id||')';
            OPEN usrs FOR v_sql;
            LOOP            
              fetch usrs into device_id,device_name,center_substation_id,center_name,substation_id,substation_name;

                exit when usrs%notfound;  --循环条件 （如果这行与fetch into位置反了，不管动态sql'有没有结果，都会进去）
                    INSERT INTO ECMS_KD_TARGET_DATA(uuid,Device_Id,Device_Name,Center_Substation_Id,center_substation_name,Substation_Id,substation_name,device_type)
                 VALUES(sys_guid(),device_id,Device_Name,Center_Substation_Id,center_name,Substation_Id,substation_name,cur_row_en.dictionary_key);
                COMMIT;
            end loop;
            close usrs;
      end if;
    END LOOP;
  end loop;
EXCEPTION
  WHEN OTHERS THEN
     --回滚
    ROLLBACK;
    --打印异常消息
    p_msg := sqlcode || sqlerrm;
    dbms_output.put_line(p_msg);
    --存储过程执行失败，记录失败信息
    INSERT INTO ECMS_PROC_LOG
      (LOGID, PROC_NAME, DES, EXETIME, ISSUCCESS, ENDTIME)
    VALUES
      (SYS_GUID(),
       'ECMS_KD_TARGET_DATA',
       '插入关键设备指标数据失败，异常信息:' || p_msg,
       SYSDATE,
       -1,
       SYSDATE);
    COMMIT;
end;