層次樹展開問題

在資料倉儲的層次建模時，常用遞迴的方式表示一顆層次樹，但有些BI工具的前端不支援遞迴，所以為了實現資料下鑽，可以把一棵遞迴樹進行擴充。

-- 建立原始樹表，並產生資料CREATE TABLE TREE(  C_PARENT  INTEGER,  C_CHILD   INTEGER);Insert into TREE (C_PARENT, C_CHILD) Values (NULL, 1002);Insert into TREE (C_PARENT, C_CHILD) Values (1002, 1003);Insert into TREE (C_PARENT, C_CHILD) Values (1002, 1004);Insert into TREE (C_PARENT, C_CHILD) Values (1002, 1005);Insert into TREE (C_PARENT, C_CHILD) Values (1003, 1006);Insert into TREE (C_PARENT, C_CHILD) Values (1003, 1007);Insert into TREE (C_PARENT, C_CHILD) Values (1003, 1008);COMMIT;-- 建立擴充的樹表CREATE TABLE TREE_EXPLODE(  C_PARENT    INTEGER,                          -- 父節點  C_CHILD     INTEGER,                          -- 子節點  C_LEVEL     INTEGER,                          -- 父節點所在層級  C_DISTANCE  INTEGER,                          -- 父節點到子節點的層數  C_BOTTOM    CHAR(1 BYTE),                     -- 是否葉子節點  C_SEQ       VARCHAR2(100 BYTE),               -- 從樹根到子節點的路徑  EFF_DT      DATE,                             -- 生效日期，用於維護曆史資訊  EXP_DT      DATE                              -- 失效日期，用於維護曆史資訊);-- 建立預存程序產生擴充樹表資料CREATE OR REPLACE PROCEDURE p_tree_explodeISBEGIN   FOR x IN (    SELECT c_child, LEVEL c_level                   FROM tree             START WITH c_parent IS NULL             CONNECT BY PRIOR c_child = c_parent)   LOOP      INSERT INTO tree_explode (c_parent,                                c_child,                                c_level,                                c_distance,                                c_bottom,                                c_seq,                                eff_dt,                                exp_dt)             SELECT CONNECT_BY_ROOT a.c_child,                    a.c_child,                    x.c_level,                    LEVEL - 1,                    DECODE (CONNECT_BY_ISLEAF, 1, 'Y', 'N'),                    (    SELECT SYS_CONNECT_BY_PATH (c_child, '/')                           FROM tree                          WHERE c_child = a.c_child                     START WITH c_parent IS NULL                     CONNECT BY PRIOR c_child = c_parent),                    TRUNC (SYSDATE),                    TO_DATE ('9999-12-31', 'yyyy-mm-dd')               FROM tree a         START WITH a.c_child = x.c_child         CONNECT BY PRIOR a.c_child = a.c_parent;   END LOOP;   COMMIT;END;/-- 測試TRUNCATE TABLE tree_explode;EXEC p_tree_explode;SELECT * FROM tree_explode;