2021年11月5日
pg的一个存储过程备份下
1.注意游标的处理
2.注意存储过程也可以打印输出，方便调试
CREATE OR REPLACE PROCEDURE "bsspm"."calculate_warning_detail"("sqlstr" text, "datestr" varchar)
AS $BODY$
DECLARE
rec RECORD; -- 计算结果遍历
oldrec RECORD; -- 之前保存的结果遍历
handlerec RECORD; -- 之前保存的处置结果遍历
records refcursor; -- 本次计算结果
oldrecords refcursor; -- 之前保存的结果
sqlxx varchar; -- 拼接的计算sql
recordkey varchar; -- 判断是否存在的key，新数据
recordkey1 varchar; -- 判断是否存在的key，老数据
existkey INTEGER DEFAULT 0; -- 数据存在与否
begin
-- 参数化拼接计算红黄灯sql
sqlxx:='select * from (SELECT
bpii."ID",
bpii."PROJECT_ID",
bpii."CONTRACT_ID",
bpii."ENTERPRISE_ID",
bpii."ENTERPRISE_NAME",
bpii."MANAGEMENT_UNIT",
bpii."ENTERPRISE_TYPE",
bpii."INDICATOR_ID",
bpii."INDICATOR_CODE",
bpii."INDICATOR_VALUE",
bpii."BELONG_ENTERPRISE_ID",
bpii."CREATED_TS",
'
|| sqlstr ||
'from "BSS_PPM_INDICATOR_INFO" as bpii
where DATE_FORMAT ( bpii."CREATED_TS", ''%Y-%m-%d'') = ''' || datestr || '''
) tt where caculateIndicatorLight in (''红灯'',''黄灯'')
';
-- 查询新老数据
open oldrecords for EXECUTE('select * from "BSS_PPM_IND_SUMMARY" where "INDICATOR_END_TIME" = ''9999-12-31''');
open records FOR EXECUTE(sqlxx);
-- **** 遍历新数据，如果老数据中没有对应的，直接插入新的计算结果 ****
LOOP
-- 遍历新数据
FETCH records INTO rec;
EXIT WHEN NOT FOUND;
recordkey:= rec."INDICATOR_CODE" || '_' || rec."ENTERPRISE_ID" || '_' || rec."ENTERPRISE_TYPE" || '_' || rec."CONTRACT_ID" || '_' || rec."PROJECT_ID";
LOOP
-- 遍历老数据
FETCH oldrecords INTO oldrec;
EXIT WHEN NOT FOUND;
recordkey1:= oldrec."INDICATOR_CODE" || '_' || oldrec."ENTERPRISE_ID" || '_' || oldrec."ENTERPRISE_TYPE" || '_' || oldrec."CONTRACT_ID" || '_' || oldrec."PROJECT_ID";
if recordkey = recordkey1 then
existkey:= 1;
end if;
END LOOP;
-- ******* 游标归位
MOVE BACKWARD ALL from oldrecords;
-- 新记录有，原来的记录没有，增加一条记录
if existkey = 0 then
RAISE NOTICE 'insert %', rec."ID";
INSERT INTO "BSS_PPM_IND_SUMMARY"
(
"ID",
"BELONG_ENTERPRISE_ID",
"CONTRACT_ID",
"ENTERPRISE_ID",
"ENTERPRISE_NAME",
"ENTERPRISE_TYPE",
"INDICATOR_CODE",
"INDICATOR_ID",
"MANAGEMENT_UNIT",
"PROJECT_ID",
"INDICATOR_END_TIME",
"INDICATOR_START_TIME",
"INDICATOR_LEVEL"
)
VALUES(
random_string(64),
rec."BELONG_ENTERPRISE_ID",
rec."CONTRACT_ID",
rec."ENTERPRISE_ID",
rec."ENTERPRISE_NAME",
rec."ENTERPRISE_TYPE",
rec."INDICATOR_CODE",
rec."INDICATOR_ID",
rec."MANAGEMENT_UNIT",
rec."PROJECT_ID",
'9999-12-31',
rec."CREATED_TS",
rec.caculateIndicatorLight
);
end if;
existkey:= 0;
END LOOP;
-- 游标归位
MOVE BACKWARD ALL from oldrecords;
MOVE BACKWARD ALL from records;
-- 重置 existkey
recordkey:= '';
recordkey1:= '';
existkey:= 0;
-- **** 遍历老数据，1.如果有状态不同的，流转一下，2.如果有之前报警现在计算没有的，处置一下 ****
LOOP
FETCH oldrecords INTO oldrec;
EXIT WHEN NOT FOUND;
recordkey:= oldrec."INDICATOR_CODE" || '_' || oldrec."ENTERPRISE_ID" || '_' || oldrec."ENTERPRISE_TYPE" || '_' || oldrec."CONTRACT_ID" || '_' || oldrec."PROJECT_ID";
LOOP
FETCH records INTO rec;
EXIT WHEN NOT FOUND;
recordkey1:= rec."INDICATOR_CODE" || '_' || rec."ENTERPRISE_ID" || '_' || rec."ENTERPRISE_TYPE" || '_' || rec."CONTRACT_ID" || '_' || rec."PROJECT_ID";
if recordkey = recordkey1 then
existkey:= 1;
-- 新老数据状态不一致，修改老记录、新增一条记录
if oldrec."INDICATOR_LEVEL" != rec.caculateIndicatorLight and oldrec."INDICATOR_LEVEL" != '已处置' then
-- 关闭老记录
update "BSS_PPM_IND_SUMMARY"
set "INDICATOR_END_TIME" = TO_DATE(datestr, 'yyyy-MM-dd hh24:mi:ss')
where "ID" = oldrec."ID";
-- 插入新记录
INSERT INTO "BSS_PPM_IND_SUMMARY"
(
"ID",
"BELONG_ENTERPRISE_ID",
"CONTRACT_ID",
"ENTERPRISE_ID",
"ENTERPRISE_NAME",
"ENTERPRISE_TYPE",
"INDICATOR_CODE",
"INDICATOR_ID",
"MANAGEMENT_UNIT",
"PROJECT_ID",
"INDICATOR_END_TIME",
"INDICATOR_START_TIME",
"INDICATOR_LEVEL"
)
VALUES(
random_string(64),
rec."BELONG_ENTERPRISE_ID",
rec."CONTRACT_ID",
rec."ENTERPRISE_ID",
rec."ENTERPRISE_NAME",
rec."ENTERPRISE_TYPE",
rec."INDICATOR_CODE",
rec."INDICATOR_ID",
rec."MANAGEMENT_UNIT",
rec."PROJECT_ID",
'9999-12-31',
rec."CREATED_TS",
rec.caculateIndicatorLight
);
end if;
END IF;
END LOOP;
-- 游标归位
MOVE BACKWARD ALL from records;
-- 老记录有，新记录没有，关闭原来的状态，增加一条记录，预警等级为已处置
if existkey = 0 then
if oldrec."INDICATOR_LEVEL" = '已处置' THEN
RETURN;
END IF;
-- 关闭原来的记录
update "BSS_PPM_IND_SUMMARY"
set "INDICATOR_END_TIME" = TO_DATE(datestr, 'yyyy-MM-dd hh24:mi:ss')
where "ID" = oldrec."ID";
select * into handlerec from "BSS_PPM_IND_SUMMARY"
where
"INDICATOR_CODE" = oldrec."INDICATOR_CODE"
and "PROJECT_ID" = oldrec."PROJECT_ID"
and "CONTRACT_ID" = oldrec."CONTRACT_ID"
and "ENTERPRISE_TYPE" = oldrec."ENTERPRISE_TYPE"
and "INDICATOR_LEVEL" = '已处置' and DATE_FORMAT ( "INDICATOR_START_TIME", '%Y-%m-%d') = datestr;
IF handlerec."ID" is null then
-- 新增一条处置的记录
INSERT INTO "BSS_PPM_IND_SUMMARY"
(
"ID",
"BELONG_ENTERPRISE_ID",
"CONTRACT_ID",
"ENTERPRISE_ID",
"ENTERPRISE_NAME",
"ENTERPRISE_TYPE",
"INDICATOR_CODE",
"INDICATOR_ID",
"MANAGEMENT_UNIT",
"PROJECT_ID",
"INDICATOR_END_TIME",
"INDICATOR_START_TIME",
"INDICATOR_LEVEL"
)
VALUES(
random_string(64),
oldrec."BELONG_ENTERPRISE_ID",
oldrec."CONTRACT_ID",
oldrec."ENTERPRISE_ID",
oldrec."ENTERPRISE_NAME",
oldrec."ENTERPRISE_TYPE",
oldrec."INDICATOR_CODE",
oldrec."INDICATOR_ID",
oldrec."MANAGEMENT_UNIT",
oldrec."PROJECT_ID",
'9999-12-31',
TO_DATE(datestr, 'yyyy-MM-dd hh24:mi:ss'),
'已处置'
);
end if;
existkey:= 0;
end if;
END LOOP;
CLOSE oldrecords;
CLOSE records;
-- COMMIT;
end;
$BODY$
LANGUAGE plpgsql