ORACLE 递归获取树形结构数据

1.原始数据结构

2.使用关键字：CONNECT BY NOCYCLE

2.1.ERROR：ORA-01436，可使用关键字“NOCYCLE”，自动跳过死循环代码
2.2.SYS_CONNECT_BY_PATH， 可以得到完整的目录树
2.3.START WITH: 递归开始条件
2.4.CONNECT_BY_ROOT: 指定字段根节点的值

WITH V_ALL AS (
    SELECT 'A' AS CODE, 'A1' AS ITEM_CODE FROM DUAL
    UNION ALL
    SELECT 'A' AS CODE, 'A2' AS ITEM_CODE FROM DUAL
    UNION ALL
    SELECT 'A' AS CODE, 'B' AS ITEM_CODE FROM DUAL
    UNION ALL
    SELECT 'B' AS CODE, 'B1' AS ITEM_CODE FROM DUAL
    UNION ALL
    SELECT 'B' AS CODE, 'B2' AS ITEM_CODE FROM DUAL
    UNION ALL
    SELECT 'B' AS CODE, 'C' AS ITEM_CODE FROM DUAL
    UNION ALL
    SELECT 'C' AS CODE, 'D' AS ITEM_CODE FROM DUAL
)
SELECT
    CONNECT_BY_ROOT(A.CODE) AS CODE
    , A.ITEM_CODE
    , LEVEL AS NODE_INDEX
    , REPLACE('0' || SYS_CONNECT_BY_PATH(CODE, '>'), '>' || CODE, '') PARENT_TREE --  树形目录: 上一级层级
    , '0' || SYS_CONNECT_BY_PATH(CODE, '>') CURRENT_TREE  -- 树形目录: 当前层级
    , '0' || SYS_CONNECT_BY_PATH(CODE, '>') || '>' || A.ITEM_CODE AS CURRENT_NODE --  树形目录: 完整层级
FROM
    V_ALL A
--START WITH CODE = 'A'
CONNECT BY NOCYCLE A.CODE = PRIOR A.ITEM_CODE
ORDER BY CODE, CURRENT_NODE
;

2.5.结果

3.递归函数脚本

3.1.ERROR：ORA-32039: 递归 WITH 子句必须具有列别名列表
3.1.1.解决方法 - 临时表后加上别名清单：RESULT(CODE, ITEM_CODE, NODE_INDEX, ROOT_CODE)

WITH V_ALL AS (
    SELECT 'A' AS CODE, 'A1' AS ITEM_CODE FROM DUAL
    UNION ALL
    SELECT 'A' AS CODE, 'A2' AS ITEM_CODE FROM DUAL
    UNION ALL
    SELECT 'A' AS CODE, 'B' AS ITEM_CODE FROM DUAL
    UNION ALL
    SELECT 'B' AS CODE, 'B1' AS ITEM_CODE FROM DUAL
    UNION ALL
    SELECT 'B' AS CODE, 'B2' AS ITEM_CODE FROM DUAL
    UNION ALL
    SELECT 'B' AS CODE, 'C' AS ITEM_CODE FROM DUAL
    UNION ALL
    SELECT 'C' AS CODE, 'D' AS ITEM_CODE FROM DUAL
), V_G AS (
    SELECT CODE FROM V_ALL GROUP BY CODE
)
, RESULT(CODE, ITEM_CODE, NODE_INDEX, ROOT_CODE) as (
    SELECT V_G.CODE, V_ALL.ITEM_CODE AS ITEM_CODE, 1 AS NODE_INDEX , V_G.CODE AS ROOT_CODE
    FROM V_ALL
    INNER JOIN V_G ON V_G.CODE = V_ALL.CODE
    UNION ALL
    SELECT RESULT.CODE, V_ALL.ITEM_CODE, RESULT.NODE_INDEX + 1 AS NODE_INDEX, RESULT.ROOT_CODE
    FROM V_ALL
    INNER JOIN RESULT ON RESULT.ITEM_CODE = V_ALL.CODE
)
SELECT * FROM result
ORDER BY CODE, NODE_INDEX, ITEM_CODE

;

3.2.结果

 

Reference:

https://www.cnblogs.com/Soprano/p/10659127.html
https://blog.csdn.net/m0_46636892/article/details/122984132
https://blog.csdn.net/wang_yunj/article/details/51040029

 

connect by 用于存在父子，祖孙，上下级等层级关系的数据表进行层级查询
oracle递归with
 https://oracle-base.com/articles/11g/recursive-subquery-factoring-11gr2