如何用Oracle编写循环查询获取层级包含关系的最终结果

原数据表（假设表名为 inclusion_relation） #

预期结果 #

解决方案1：递归CTE（推荐，高效简洁） #

Oracle 11gR2及以上版本支持递归公用表表达式（CTE），可直接通过SQL实现层级遍历，无需显式循环：

WITH recursive_relation AS (
    -- 锚点成员：初始化起始节点与直接上级节点
    SELECT 
        id,
        i1 AS i,
        i2 AS current_parent,
        i2 AS candidate_final
    FROM inclusion_relation
    UNION ALL
    -- 递归成员：向上遍历上级节点的包含关系，直到无更高层级
    SELECT 
        rr.id,
        rr.i,
        ir.i2 AS current_parent,
        ir.i2 AS candidate_final
    FROM recursive_relation rr
    JOIN inclusion_relation ir 
        ON rr.id = ir.id AND rr.current_parent = ir.i1
)
-- 筛选出最终叶子节点（无上级节点的节点）
SELECT 
    id,
    i,
    candidate_final AS i_final
FROM recursive_relation rr
WHERE NOT EXISTS (
    SELECT 1 
    FROM inclusion_relation ir 
    WHERE ir.id = rr.id AND ir.i1 = rr.candidate_final
)
ORDER BY id, i, i_final;

代码解释 #

1. 锚点成员：从原始表中提取所有初始包含关系，将i1作为起始节点i，i2作为当前上级节点，同时标记为候选最终节点。
2. 递归成员：将当前上级节点作为新的起始节点，继续关联原始表查找其上级，不断更新候选最终节点，直到无法找到更高层级的节点。
3. 最终筛选：通过NOT EXISTS判断候选节点是否为叶子节点（即该节点不再作为被包含对象出现在表中），得到每个起始节点的所有最终包含节点。

解决方案2：PL/SQL显式循环 #

如果需要用显式循环实现，可通过存储过程完成：

CREATE OR REPLACE PROCEDURE get_final_inclusion_result
IS
    CURSOR c_initial_relations IS
        SELECT id, i1 AS i, i2 AS current_parent
        FROM inclusion_relation;
    v_id VARCHAR2(10);
    v_i VARCHAR2(10);
    v_current_parent VARCHAR2(10);
    v_next_parent VARCHAR2(10);
BEGIN
    -- 创建会话级临时表存储结果，提交后保留数据
    CREATE GLOBAL TEMPORARY TABLE temp_final_results (
        id VARCHAR2(10),
        i VARCHAR2(10),
        i_final VARCHAR2(10)
    ) ON COMMIT PRESERVE ROWS;
    
    -- 清空临时表，避免重复执行时有残留数据
    DELETE FROM temp_final_results;
    
    -- 遍历所有初始包含关系，递归查找最终节点
    FOR rec IN c_initial_relations LOOP
        v_id := rec.id;
        v_i := rec.i;
        v_current_parent := rec.current_parent;
        
        -- 循环查找上级节点，直到无更高层级
        LOOP
            BEGIN
                SELECT i2 INTO v_next_parent
                FROM inclusion_relation
                WHERE id = v_id AND i1 = v_current_parent;
                
                v_current_parent := v_next_parent;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    EXIT; -- 无上级节点，退出循环
            END;
        END LOOP;
        
        -- 将最终结果插入临时表
        INSERT INTO temp_final_results (id, i, i_final)
        VALUES (v_id, v_i, v_current_parent);
    END LOOP;
    
    -- 输出结果
    DBMS_OUTPUT.PUT_LINE('id | i  | i_final');
    DBMS_OUTPUT.PUT_LINE('---|----|--------');
    FOR res IN (SELECT * FROM temp_final_results ORDER BY id, i, i_final) LOOP
        DBMS_OUTPUT.PUT_LINE(res.id || ' | ' || res.i || ' | ' || res.i_final);
    END LOOP;
END;
/

使用方式 #

执行存储过程并查看输出：

SET SERVEROUTPUT ON;
EXEC get_final_inclusion_result;

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