Oracle SQL实现员工项目层级递归查询 解决循环异常问题

现有员工项目分配表csm_assignments，表结构规则如下：

• 主键：assignment_id
• 核心业务字段：employee_id（员工ID）、project_id（项目ID）
• 关联关系：员工与项目为多对多，单个项目可分配多名员工，单名员工可参与多个项目

需求为传入指定员工ID，递归拉取全量关联分配记录，递归执行逻辑：

1. 第一步查询传入员工直接参与的所有项目
2. 第二步查询上述项目下的所有参与员工
3. 第三步查询上述员工参与的其他未被遍历过的项目
4. 逐层重复上述步骤，直到没有新的关联记录为止

验证示例：传入员工ID=2000时，最终需返回对应关联的8条分配记录。

现存问题：直接编写递归CTE添加CYCLE子句、或使用CONNECT BY语法均存在循环遍历问题，层级复杂时会生成超大量冗余结果集，要求优先使用Oracle SQL实现，其次可接受PL/SQL方案。

方案1：纯Oracle SQL实现（修正递归逻辑从根源避免循环） #

递归循环、结果冗余的核心原因是未区分遍历节点类型（员工/项目），导致同一条分配记录被反复匹配。以下修正版递归CTE通过集合标记已遍历的员工、项目，仅匹配未访问过的新节点，从根源避免循环，CYCLE子句作为兜底校验：

WITH traverse(
    assignment_id, 
    employee_id, 
    project_id, 
    visited_emps, 
    visited_projs,
    lvl
) AS (
    -- 锚点：查询初始传入员工的所有直接分配记录
    SELECT 
        a.assignment_id,
        a.employee_id,
        a.project_id,
        -- 初始化已访问员工集合，存入初始员工ID
        SYS.ODCINUMBERLIST(a.employee_id) AS visited_emps,
        -- 初始化已访问项目集合，存入初始员工关联的所有项目
        CAST(
            MULTISET(SELECT a2.project_id FROM csm_assignments a2 WHERE a2.employee_id = a.employee_id)
            AS SYS.ODCINUMBERLIST
        ) AS visited_projs,
        1 AS lvl
    FROM csm_assignments a
    WHERE a.employee_id = :input_emp_id -- 传入指定员工ID，示例传2000

    UNION ALL

    -- 递归分支：交替遍历新员工、新项目，仅匹配未访问节点
    SELECT
        next_a.assignment_id,
        next_a.employee_id,
        next_a.project_id,
        -- 发现新员工时更新已访问员工集合
        CASE WHEN next_a.employee_id NOT MEMBER OF t.visited_emps
             THEN t.visited_emps MULTISET UNION SYS.ODCINUMBERLIST(next_a.employee_id)
             ELSE t.visited_emps
        END AS visited_emps,
        -- 发现新项目时更新已访问项目集合
        CASE WHEN next_a.project_id NOT MEMBER OF t.visited_projs
             THEN t.visited_projs MULTISET UNION SYS.ODCINUMBERLIST(next_a.project_id)
             ELSE t.visited_projs
        END AS visited_projs,
        t.lvl + 1 AS lvl
    FROM traverse t
    JOIN csm_assignments next_a
      -- 匹配规则：要么是已遍历项目下的新员工，要么是已遍历员工参与的新项目
      ON (next_a.project_id MEMBER OF t.visited_projs AND next_a.employee_id NOT MEMBER OF t.visited_emps)
      OR (next_a.employee_id MEMBER OF t.visited_emps AND next_a.project_id NOT MEMBER OF t.visited_projs)
    -- 终止条件：匹配到的记录两端节点都已访问过则跳过
    WHERE NOT (next_a.employee_id MEMBER OF t.visited_emps AND next_a.project_id MEMBER OF t.visited_projs)
)
CYCLE assignment_id SET is_cycle TO 'Y' DEFAULT 'N'
-- 最终去重返回所有关联分配记录
SELECT DISTINCT assignment_id, employee_id, project_id
FROM traverse
WHERE is_cycle = 'N';

若使用的Oracle版本不支持嵌套表集合判断，可将visited_emps、visited_projs替换为逗号拼接的字符串，通过INSTR判断ID是否存在，逻辑完全一致。

方案2：PL/SQL实现（适合大数据量场景，性能更可控） #

如果纯SQL在超大数据量下存在性能波动，可通过PL/SQL用两个嵌套表分别存储已遍历的员工、项目ID，循环迭代直到无新节点产生，完全避免重复遍历：

CREATE OR REPLACE TYPE num_tab IS TABLE OF NUMBER;
/

CREATE OR REPLACE FUNCTION get_related_assignments(p_input_emp_id NUMBER)
RETURN SYS_REFCURSOR
IS
    v_visited_emps num_tab := num_tab(p_input_emp_id);
    v_visited_projs num_tab := num_tab();
    v_new_emps num_tab;
    v_new_projs num_tab;
    v_result SYS_REFCURSOR;
BEGIN
    -- 初始化初始员工关联的所有项目
    SELECT project_id BULK COLLECT INTO v_visited_projs
    FROM csm_assignments
    WHERE employee_id = p_input_emp_id;

    LOOP
        -- 拉取已遍历项目下的未登记员工
        SELECT DISTINCT employee_id BULK COLLECT INTO v_new_emps
        FROM csm_assignments
        WHERE project_id MEMBER OF v_visited_projs
          AND employee_id NOT MEMBER OF v_visited_emps;

        -- 拉取已遍历员工参与的未登记项目
        SELECT DISTINCT project_id BULK COLLECT INTO v_new_projs
        FROM csm_assignments
        WHERE employee_id MEMBER OF v_visited_emps
          AND project_id NOT MEMBER OF v_visited_projs;

        -- 无新节点时终止迭代
        EXIT WHEN v_new_emps.COUNT = 0 AND v_new_projs.COUNT = 0;

        -- 将新节点加入已访问集合
        v_visited_emps := v_visited_emps MULTISET UNION v_new_emps;
        v_visited_projs := v_visited_projs MULTISET UNION v_new_projs;
    END LOOP;

    -- 拼接返回所有关联的分配记录
    OPEN v_result FOR
    SELECT DISTINCT assignment_id, employee_id, project_id
    FROM csm_assignments
    WHERE employee_id MEMBER OF v_visited_emps
      AND project_id MEMBER OF v_visited_projs;

    RETURN v_result;
END;
/

-- 调用示例：查询员工2000的全量关联分配记录
SELECT * FROM TABLE(get_related_assignments(2000));

• 两种方案均从遍历逻辑上区分了员工、项目两类节点，仅将从未遍历过的节点加入迭代范围，不会出现同一条记录反复匹配的循环问题，也不会生成冗余结果
• 纯SQL方案无需创建额外数据库对象，适配大多数快速查询场景
• PL/SQL方案迭代逻辑更直观，百万级数据量下性能比递归CTE更稳定，不会出现递归深度过大的性能瓶颈

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