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;
