Oracle与H2递归查询循环问题：如何实现跨数据库统一报错？

针对生产用Oracle、本地开发用H2时，递归查询遇到循环的兼容问题——Oracle会报错但H2无限循环，以下是两种能统一检测循环的方案：

方案1：终止递归并返回有效结果 #

通过在递归CTE中新增路径追踪字段，每次递归前检查当前ID是否已被访问，避免进入循环。这种方案会返回循环出现前的所有层级数据，不会无限执行。

WITH ANCESTORS (ID, SUPERVISOR_ID, PATH) AS (
    -- 初始查询：记录当前ID的初始路径
    SELECT 
        ID, 
        SUPERVISOR_ID,
        ',' || ID || ',' AS PATH
    FROM EMPLOYEE
    WHERE ID = :id

    UNION ALL
    
    -- 递归查询：拼接新路径并检查循环
    SELECT 
        E2.ID, 
        E2.SUPERVISOR_ID,
        E1.PATH || E2.ID || ',' AS PATH
    FROM ANCESTORS E1
    JOIN EMPLOYEE E2 ON E1.SUPERVISOR_ID = E2.ID
    -- 用逗号包裹ID避免部分匹配（如ID 1和11混淆），检查当前ID是否已在路径中
    WHERE NOT E1.PATH LIKE '%,' || E2.ID || ',%'
)
SELECT ID, SUPERVISOR_ID
FROM ANCESTORS;

方案2：主动触发错误（两边统一报错） #

如果需要在检测到循环时直接报错，而非返回部分结果，可以在递归步骤中加入错误触发逻辑。利用数据库内置的错误函数，让Oracle和H2在检测到循环时都抛出明确错误：

WITH ANCESTORS (ID, SUPERVISOR_ID, PATH) AS (
    SELECT 
        ID, 
        SUPERVISOR_ID,
        ',' || ID || ',' AS PATH
    FROM EMPLOYEE
    WHERE ID = :id

    UNION ALL
    
    SELECT 
        -- 检测到循环时触发错误，兼容H2和Oracle的错误函数
        CASE 
            WHEN E1.PATH LIKE '%,' || E2.ID || ',%' THEN
                COALESCE(
                    RAISE_ERROR('检测到员工层级循环引用'), -- H2错误函数
                    RAISE_APPLICATION_ERROR(-20001, '检测到员工层级循环引用') -- Oracle错误函数
                )
            ELSE E2.ID 
        END AS ID,
        E2.SUPERVISOR_ID,
        E1.PATH || E2.ID || ',' AS PATH
    FROM ANCESTORS E1
    JOIN EMPLOYEE E2 ON E1.SUPERVISOR_ID = E2.ID
)
SELECT ID, SUPERVISOR_ID
FROM ANCESTORS;

说明 #

• 路径字段用',' || ID || ','格式，是为了避免ID部分匹配的误判（比如ID=1和ID=11，直接用LIKE '%1%'会误判）。
• 方案1适合需要获取有效层级数据的场景，方案2适合必须严格避免循环、需主动报错的场景。

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