Oracle层级递归查询需求：遇到循环时终止分支并继续处理其他包

你遇到的核心问题是递归过程中没有跟踪已访问过的对象，导致出现循环调用时无法终止当前分支。要实现遇到循环就停止当前递归链、继续处理其他分支的需求，我们需要在递归函数中加入一个「已访问对象集合」来记录已经遍历过的包，每次递归前检查当前包是否已经在集合中——如果存在，就输出循环提示并终止当前分支；否则继续遍历。

修改后的完整代码 #

首先更新包定义，新增用于存储已访问对象的集合类型，同时给记录类型加一个状态字段便于标记循环：

CREATE OR REPLACE PACKAGE object_x is
    type ObjectRec is record(
        dName varchar2(250),
        level number,
        status varchar2(50) -- 标记正常遍历/循环终止状态
    );
    type ObjectTemp is table of ObjectRec;
    type VisitedObjects is table of varchar2(250); -- 存储已访问的包名
    function Referenced(
        dname VARCHAR2, 
        level NUMBER, 
        maxl NUMBER,
        visited VisitedObjects default VisitedObjects() -- 默认空集合，首次调用无需传入
    ) return ObjectTemp pipelined;
end;
/

然后修改包体，加入循环检测和已访问集合的处理逻辑：

CREATE OR REPLACE PACKAGE BODY object_x is
function Referenced(
    dname VARCHAR2, 
    level NUMBER, 
    maxl NUMBER,
    visited VisitedObjects
) return ObjectTemp pipelined is
    rData ObjectRec;
    new_visited VisitedObjects;
begin
    -- 先检查层级是否超出设定最大值
    if level >= maxl then 
        return; 
    end if;

    -- 循环检测：当前包是否已在访问链中
    if visited is not null and visited.exists(upper(dname)) then
        rData.dName := LPAD(' ', 3*(level-1), ' ') || 'LOOP!!!!';
        rData.level := level;
        rData.status := 'LOOP_BREAK';
        pipe row(rData);
        rData.dName := LPAD(' ', 3*(level-1), ' ') || '-----BREAK------';
        pipe row(rData);
        return; -- 终止当前递归分支，不再深入
    end if;

    -- 生成新的已访问集合，加入当前包
    new_visited := visited;
    new_visited.extend;
    new_visited(new_visited.count) := upper(dname);

    -- 输出当前包（按层级生成缩进，结构更清晰）
    rData.dName := LPAD(' ', 3*(level-1), ' ') || dname;
    rData.level := level;
    rData.status := 'NORMAL';
    pipe row(rData);

    -- 遍历当前包依赖的其他包，递归处理
    for r in (
        select referenced_name 
        from user_dependencies
        where name = upper(dname)
          and type = 'PACKAGE BODY'
          and referenced_type = 'PACKAGE'
          and referenced_name != upper(dname)
    ) loop
        -- 传递更新后的已访问集合，递归查询依赖
        for r2 in (select * from table(Referenced(r.referenced_name, level + 1, maxl, new_visited))) loop
            pipe row(r2);
        end loop;
    end loop;

    return;
end;
end;
/

关键逻辑说明 #

1. 已访问集合跟踪：每次递归都会传递包含所有已遍历包的集合，确保不会重复处理同一个包，从根源避免无限循环。
2. 循环终止处理：检测到循环时，输出你期望的提示信息，然后用return立即终止当前递归分支，转而处理其他未遍历的依赖包。
3. 缩进格式优化：用LPAD(' ', 3*(level-1), ' ')根据层级生成统一缩进，让输出的调用链结构更直观。
4. 状态标记：新增的status字段可以帮你快速区分正常遍历的记录和循环终止的提示，不需要的话可以直接删除该字段。

调用示例 #

你可以这样调用函数查看结果：

select level, dname 
from table(object_x.Referenced('PAC1', 1, 10));

修改后的代码会在遇到循环时自动终止当前分支并输出提示，同时继续处理其他未遍历的包，完全符合你的预期需求。

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