PostgreSQL 存储过程
来源：互联网
-- Function: tms_org_score_statis()
-- DROP FUNCTION tms_org_score_statis();
CREATE OR REPLACE FUNCTION tms_org_score_statis()
RETURNS character varying AS
$BODY$
declare
r record;
statis_sql "varchar";
execute_sql "varchar";
num int4 := 0;
total_score numeric(10,2);
begin
statis_sql := 'select tms.* from
(SELECT ORG.ID ORG_ID, COALESCE(AB.participation_legal, 0) participation_legal, COALESCE(AB.participation_leader, 0) participation_leader,
COALESCE(AB.participation_cadre, 0) participation_cadre, COALESCE(GH.participation_staff, 0) participation_staff,
COALESCE(AB.qualified_legal, 0) qualified_legal, COALESCE(AB.qualified_leader, 0) qualified_leader, COALESCE(AB.qualified_cadre, 0)qualified_cadre,
COALESCE(GH.qualified_staff, 0) qualified_staff, COALESCE(GH.avg_answer_time, 0) avg_answer_time,
CASE WHEN TSC.ORG_ID > 0 THEN TRUE ELSE FALSE END AS EXIST
FROM PUBLIC.T_ORG ORG
LEFT OUTER JOIN
(
SELECT BB.ORG_ID,
SUM(participation_legal) participation_legal,
SUM(participation_leader) participation_leader,
SUM(participation_cadre) participation_cadre,
SUM(qualified_legal) qualified_legal,
SUM(qualified_leader) qualified_leader,
SUM(qualified_cadre) qualified_cadre
FROM
(
SELECT AA.ORG_ID,
CASE WHEN AA.ROLE_ID = 1 THEN 5 ELSE 0 END AS participation_legal,
CASE WHEN AA.ROLE_ID = 2 THEN 3 ELSE 0 END AS participation_leader,
CASE WHEN AA.ROLE_ID = 3 THEN 2 ELSE 0 END AS participation_cadre,
CASE WHEN AA.ROLE_ID = 1 AND AA.AR = 10 THEN 5 ELSE 0 END AS qualified_legal,
CASE WHEN AA.ROLE_ID = 2 AND AA.AR = 10 THEN 3 ELSE 0 END AS qualified_leader,
CASE WHEN AA.ROLE_ID = 3 AND AA.AR = 10 THEN 2 ELSE 0 END AS qualified_cadre
FROM
(
SELECT US.ORG_ID, UR.ROLE_ID, MIN(ANSWER_RESULT) AR
FROM PUBLIC.T_USER US
LEFT OUTER JOIN PUBLIC.T_USER_ROLE UR
ON US.ID = UR.USER_ID
LEFT OUTER JOIN PUBLIC.T_QUESTION_PAPER QP
ON US.ID = QP.USER_ID
where ur.role_id in (1, 2, 3)
GROUP BY US.ORG_ID, UR.ROLE_ID
) AA
) BB
GROUP BY BB.ORG_ID
) AB
ON ORG.ID = AB.ORG_ID
left outer JOIN
(
select org_id, (all_real_qty::numeric * 30 / (participator_qty)) participation_staff,
(total_SCORE / all_real_qty) * 0.5 qualified_staff, total_answer_time / all_real_qty avg_answer_time
from
(
SELECT ORG_ID, COUNT(*) all_real_qty,MIN(participator_qty) participator_qty,
SUM(COALESCE(QP.score,0)) total_SCORE, COALESCE(sum(extract(epoch from (END_TIME - START_TIME))), 0) total_answer_time
FROM PUBLIC.V_USER_INFO VI
LEFT OUTER JOIN PUBLIC.T_QUESTION_PAPER QP
ON VI.USER_ID = QP.USER_ID
GROUP BY ORG_ID
) GG
) GH
ON ORG.ID = GH.ORG_ID
LEFT OUTER JOIN PUBLIC.t_competition_score TSC
ON ORG.ID = TSC.ORG_ID
) tms';
for r in execute statis_sql loop
total_score := r.participation_legal + r.participation_leader + r.participation_cadre + r.participation_staff + r.qualified_legal + r.qualified_leader + r.qualified_cadre + r.qualified_staff;
num := num + 1;
IF R.EXIST = false THEN
execute_sql := 'insert into public.t_competition_score(org_id,participation_legal,participation_leader, participation_cadre, participation_staff,
qualified_legal,qualified_leader,qualified_cadre,qualified_staff,avg_answer_time, total_score,create_time,status,last_update_time) values
(' || r.org_id || ',' || r.participation_legal || ',' || r.participation_leader || ',' || r.participation_cadre || ',' || r.participation_staff || ',' ||
r.qualified_legal || ',' || r.qualified_leader || ',' || r.qualified_cadre || ',' || r.qualified_staff || ',' || r.avg_answer_time || ',' ||
total_score || ', ''' || now() || ''',' || 20 || ',''' || now() || ''')';
END IF;
IF R.EXIST = true THEN
execute_sql := 'UPDATE public.t_competition_score set participation_legal = ' || r.participation_legal || ', participation_leader = ' || r.participation_leader || ', participation_cadre = '
|| r.participation_cadre || ', participation_staff = ' || r.participation_staff || ', qualified_legal = ' || r.qualified_legal || ', qualified_leader = ' || r.qualified_leader
|| ', qualified_cadre = ' || r.qualified_cadre || ', qualified_staff = ' || r.qualified_staff || ', avg_answer_time = ' || r.avg_answer_time || ', total_score = ' || total_score
|| ', last_update_time = ''' || now() || ''' where org_id = ' || r.org_id;
END IF;
RAISE NOTICE '%', 'sql: ' || execute_sql;
EXECUTE execute_sql;
end loop;
RETURN num;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
-- Function: tms_org_score_statis()
-- DROP FUNCTI
