區域平臺統計報表，省--市--區 匯總，還有各級醫院，匯總與列表要在一個列表顯示。
用到ORACLE 會話時臨時表??GLOBAL TEMPORARY TABLE ? ??ON COMMIT PRESERVE ROWS;
遞歸樹：?START WITH P.PARENTORG = ‘ROOT‘
CONNECT BY PRIOR P.ORGCODE = P.PARENTORG;
WITH 連續嵌套
記錄一下便于查閱。
CREATE OR REPLACE PACKAGE BODY PKG_JXKH_SHXBB AS
PROCEDURE MJZGH(P_REPORTID IN VARCHAR2,
P_UNITID IN VARCHAR2,--要查詢的行政區劃
P_SDATE IN VARCHAR2,--要查詢的開始日期
P_EDATE IN VARCHAR2,--要查詢的開始日期
V_CUR OUT REFCURSORTYPE) IS
PRAGMA AUTONOMOUS_TRANSACTION;
P_sql VARCHAR2(4000);
BEGIN
P_sql := ‘CREATE GLOBAL TEMPORARY TABLE TMP_MZJZGH (
PARENTCODE VARCHAR2(60),
PARENTUNITID VARCHAR2(60),
PARENTUNITNAME VARCHAR2(60),
A NUMBER,
B NUMBER,
C NUMBER
) ON COMMIT PRESERVE ROWS‘;
--EXECUTE IMMEDIATE P_sql;
EXECUTE IMMEDIATE ‘TRUNCATE TABLE TMP_MZJZGH‘;
COMMIT;
INSERT INTO TMP_MZJZGH
(PARENTCODE,PARENTUNITID,PARENTUNITNAME)
SELECT P.PARENTORG,P.ORGCODE,P.MANAGERORGNAME
FROM PMR005_ORG P
WHERE P.BELONGTO < 3
AND P.STATUS = ‘1‘
START WITH P.PARENTORG = ‘ROOT‘
CONNECT BY PRIOR P.ORGCODE = P.PARENTORG;
FOR CUR IN (
WITH TMP AS (SELECT A.PARENTORG,
A.ORGCODE,
A.MANAGERORGNAME,
TT.*
FROM PMR005_ORG A,
(SELECT T.UNITID,
SUM(CASE
WHEN T.MetaDATAID = ‘MZ_JZXX_GHRC‘ THEN
T.MetaDATAVALUE
ELSE
0
END) SUMGHRC,
SUM(CASE
WHEN T.MetaDATAID = ‘MZ_FY_JZRC‘ THEN
T.MetaDATAVALUE
ELSE
0
END) SUMJZRC,
SUM(CASE
WHEN T.MetaDATAID = ‘MZ_JZXX_LGRC‘ THEN
T.MetaDATAVALUE
ELSE
0
END) SUMLGRC
FROM JXKH_MetaDTAVALUE T
WHERE T.MetaDATAID IN
(‘MZ_JZXX_GHRC‘,‘MZ_FY_JZRC‘,
‘MZ_JZXX_LGRC‘)
AND INSTR(P_UNITID,UNITID) > 0
AND T.STATDATE >
TO_DATE(P_SDATE,‘YYYY-MM-DD‘)
AND T.STATDATE <
TO_DATE(P_EDATE,‘YYYY-MM-DD‘)
GROUP BY T.UNITID) TT
WHERE A.ORGCODE = TT.UNITID
AND A.BELONGTO < 4),
TMP2 AS (SELECT P2.PARENTORG,
P2.ORGCODE,
P2.MANAGERORGNAME,
SUM(SUMGHRC) A,
SUM(SUMJZRC) B,
SUM(SUMLGRC) C
FROM PMR005_ORG P2,TMP
WHERE P2.PARENTORG =
(SELECT ORGCODE
FROM PMR005_ORG
WHERE PARENTORG =
‘ROOT‘)
AND (P2.ORGCODE =
TMP.ORGCODE AND
P2.ORGTYPE = ‘2‘)
GROUP BY P2.PARENTORG,
P2.MANAGERORGNAME
UNION
SELECT P3.PARENTORG,
P3.ORGCODE,
P3.MANAGERORGNAME,
SUM(SUMGHRC),
SUM(SUMJZRC),
SUM(SUMLGRC)
FROM TMP,PMR005_ORG P3
WHERE (P3.ORGCODE =
TMP.PARENTORG OR
P3.ORGCODE =
TMP.ORGCODE)
AND P3.BELONGTO = ‘2‘
GROUP BY P3.PARENTORG,
P3.MANAGERORGNAME),
TMP3 AS (SELECT PARENTORG,
ORGCODE,
MANAGERORGNAME,
A,
B,
C
FROM TMP2
UNION
SELECT ‘ROOT‘,
‘14000000‘,
‘山西省‘,
SUM(A),
SUM(B),
SUM(C)
FROM TMP2
GROUP BY ‘ROOT‘,
‘山西省‘
UNION
SELECT P4.PARENTORG,
P4.ORGCODE,
P4.SHORTNAME,
SUM(TP.A),
SUM(TP.B),
SUM(TP.C)
FROM TMP2 TP,
PMR005_ORG P4
WHERE TP.PARENTORG =
P4.ORGCODE
AND P4.PARENTORG =
(SELECT ORGCODE
FROM PMR005_ORG
WHERE PARENTORG =
‘ROOT‘
AND ORGTYPE = ‘1‘)
GROUP BY P4.PARENTORG,
P4.SHORTNAME)
SELECT * FROM TMP3)
LOOP
UPDATE TMP_MZJZGH
SET A = CUR.A,B = CUR.B,C = CUR.C
WHERE PARENTCODE = CUR.PARENTORG
AND PARENTUNITID = CUR.ORGCODE
AND PARENTUNITNAME = CUR.MANAGERORGNAME;
END LOOP;
COMMIT;
OPEN V_CUR FOR
SELECT A.PARENTCODE,
A.PARENTUNITID,
A.PARENTUNITNAME,
A.A MZ_JZXX_GHRC,
A.B MZ_FY_JZRC,
A.C MZ_JZXX_LGRC
FROM TMP_MZJZGH A;
END;
END;
