Oracle 19c递归查询父节点及最优路径问题排查与解决

一、解决ORA-32044循环错误 #

递归查询触发循环错误，是因为数据存在环状依赖（如子节点→父节点→子节点的闭环），或递归过程重复访问同一节点。可以在递归CTE中通过记录已访问节点列表彻底避免循环：

基础递归CTE写法（防循环） #

WITH recursive_path (child, parent, ownership, full_path, visited_nodes) AS (
    -- 锚点成员：指定起始子节点（可关联其他表批量获取）
    SELECT 
        cr.child, 
        cr.parent, 
        cr.ownership,
        TO_CHAR(cr.child) || '->' || TO_CHAR(cr.parent) AS full_path,
        ',' || TO_CHAR(cr.child) || ',' || TO_CHAR(cr.parent) || ',' AS visited_nodes
    FROM P_C_REL cr
    -- 示例：关联其他表获取目标子节点，需替换为实际表名和条件
    JOIN TARGET_TABLE t ON cr.child = t.target_child
    UNION ALL
    -- 递归成员：仅访问未在已访问列表中的父节点
    SELECT 
        rp.child, 
        cr.parent, 
        rp.ownership + cr.ownership, -- 若所有权非累加，可调整为MAX(rp.ownership, cr.ownership)等逻辑
        rp.full_path || '->' || TO_CHAR(cr.parent) AS full_path,
        rp.visited_nodes || TO_CHAR(cr.parent) || ',' AS visited_nodes
    FROM recursive_path rp
    JOIN P_C_REL cr ON rp.parent = cr.child
    -- 通过字符串匹配排除已访问节点，避免循环
    WHERE INSTR(rp.visited_nodes, ',' || TO_CHAR(cr.parent) || ',') = 0
)
-- 查询所有到最终父节点的完整路径
SELECT child, full_path, ownership AS total_ownership
FROM recursive_path
WHERE NOT EXISTS (SELECT 1 FROM P_C_REL cr WHERE cr.child = recursive_path.parent);

说明：visited_nodes用逗号包裹节点ID，通过INSTR判断节点是否已被访问；full_path可根据需求调整分隔符（如/或|）。

二、需求1：查询指定子节点的所有递归父节点完整路径 #

只需调整锚点成员的子节点来源即可：

• 单个指定子节点：直接用WHERE cr.child = '目标子节点ID'
• 批量子节点：保持和其他表的关联逻辑

单个子节点查询示例 #

WITH recursive_path (child, parent, ownership, full_path, visited_nodes) AS (
    SELECT 
        cr.child, 
        cr.parent, 
        cr.ownership,
        TO_CHAR(cr.child) || '->' || TO_CHAR(cr.parent) AS full_path,
        ',' || TO_CHAR(cr.child) || ',' || TO_CHAR(cr.parent) || ',' AS visited_nodes
    FROM P_C_REL cr
    WHERE cr.child = 'CHILD_001' -- 指定目标子节点
    UNION ALL
    SELECT 
        rp.child, 
        cr.parent, 
        rp.ownership + cr.ownership,
        rp.full_path || '->' || TO_CHAR(cr.parent) AS full_path,
        rp.visited_nodes || TO_CHAR(cr.parent) || ',' AS visited_nodes
    FROM recursive_path rp
    JOIN P_C_REL cr ON rp.parent = cr.child
    WHERE INSTR(rp.visited_nodes, ',' || TO_CHAR(cr.parent) || ',') = 0
)
SELECT full_path, total_ownership
FROM (
    SELECT 
        full_path, 
        ownership AS total_ownership,
        CASE WHEN NOT EXISTS (SELECT 1 FROM P_C_REL cr WHERE cr.child = rp.parent) THEN 'Y' ELSE 'N' END AS is_final
    FROM recursive_path rp
)
WHERE is_final = 'Y';

三、需求2：筛选所有权最高的路径 #

假设“所有权最高”指路径总所有权最大，可通过窗口函数RANK()筛选：

WITH recursive_path (child, parent, ownership, full_path, visited_nodes) AS (
    SELECT 
        cr.child, 
        cr.parent, 
        cr.ownership,
        TO_CHAR(cr.child) || '->' || TO_CHAR(cr.parent) AS full_path,
        ',' || TO_CHAR(cr.child) || ',' || TO_CHAR(cr.parent) || ',' AS visited_nodes
    FROM P_C_REL cr
    WHERE cr.child = 'CHILD_001' -- 指定目标子节点
    UNION ALL
    SELECT 
        rp.child, 
        cr.parent, 
        rp.ownership + cr.ownership,
        rp.full_path || '->' || TO_CHAR(cr.parent) AS full_path,
        rp.visited_nodes || TO_CHAR(cr.parent) || ',' AS visited_nodes
    FROM recursive_path rp
    JOIN P_C_REL cr ON rp.parent = cr.child
    WHERE INSTR(rp.visited_nodes, ',' || TO_CHAR(cr.parent) || ',') = 0
),
final_paths AS (
    SELECT 
        full_path, 
        ownership AS total_ownership
    FROM recursive_path rp
    WHERE NOT EXISTS (SELECT 1 FROM P_C_REL cr WHERE cr.child = rp.parent)
),
ranked_paths AS (
    SELECT 
        full_path,
        total_ownership,
        -- 按总所有权降序排名，若多个路径所有权相同，保留所有
        RANK() OVER (ORDER BY total_ownership DESC) AS path_rank
    FROM final_paths
)
SELECT full_path, total_ownership
FROM ranked_paths
WHERE path_rank = 1;

补充：若“所有权最高”指路径中单个节点所有权最大值，只需将递归中的ownership计算改为GREATEST(rp.ownership, cr.ownership)，后续排序逻辑对应调整即可。

四、替代方案：Oracle原生CONNECT BY语法 #

Oracle自带的CONNECT BY语法支持自动循环检测，写法更简洁：

-- 查询所有最终路径
SELECT 
    SYS_CONNECT_BY_PATH(child, '->') || '->' || parent AS full_path,
    SUM(ownership) OVER (PARTITION BY CONNECT_BY_ROOT child ORDER BY LEVEL) AS total_ownership
FROM P_C_REL
START WITH child = 'CHILD_001' -- 指定目标子节点
CONNECT BY NOCYCLE PRIOR parent = child
-- 筛选最终父节点（无上级节点）
AND NOT EXISTS (SELECT 1 FROM P_C_REL cr WHERE cr.child = P_C_REL.parent);

说明：NOCYCLE关键字自动跳过循环；CONNECT_BY_ROOT获取起始子节点；LEVEL表示递归层级。筛选最高所有权路径的逻辑可叠加之前的窗口函数写法实现。

内容的提问来源于stack exchange，提问作者sharan jain