层次树展开问题

        在数据仓库的层次建模时，常用递归的方式表示一颗层次树，但有些 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_explode
IS
BEGIN
   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;