ORACLE 递归获取树形结构数据

1.原始数据结构

2.使用关键字：CONNECT BY NOCYCLE

2.1.ERROR：ORA-01436，可使用关键字“NOCYCLE”

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 * FROM V_ALL;
SELECT
    CONNECT_BY_ROOT(A.CODE) AS CODE
    , A.ITEM_CODE
    , LEVEL
FROM
    V_ALL A
CONNECT BY NOCYCLE A.CODE = PRIOR A.ITEM_CODE
;

2.2.结果

3.递归函数脚本

3.1.ERROR：ORA-32039: 递归 WITH 子句必须具有列别名列表

3.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://blog.csdn.net/m0_46636892/article/details/122984132

https://blog.csdn.net/wang_yunj/article/details/51040029

oracle递归with

 https://oracle-base.com/articles/11g/recursive-subquery-factoring-11gr2