oracle树结构统计,ORACLE 递归树型结构统计汇总

区域平台统计报表，省--市--区 汇总，还有各级医院，汇总与列表要在一个列表显示。

用到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.ORGCODE,

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.ORGCODE,

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',

'14000000',

'山西省'

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.ORGCODE,

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;