Oracle 递归函数及 其他数据库 CTE 使用小计

最近做 Oracle 数据存储的时候用到了递归，简单查询了一下 Oracle 的递归函数与 CTE 的区别，略作记录，仅供参考。

1. 使用 CTE（公共表表达式）
   CTE 适用于支持标准 SQL 的数据库，例如 PostgreSQL、MySQL 8.0、SQL Server 等。由锚成员（初始查询）和递归成员（循环部分）构成，二者通过 UNION ALL 连接。递归成员中要包含能结束递归的条件，防止出现无限循环。处理大量数据时，递归查询可能会影响性能，这种情况下可以考虑对数据进行预处理。

WITH RECURSIVE DepartmentTree AS (
    -- 初始查询（锚成员）
    SELECT id, name, parent_id, 1 AS level
    FROM departments
    WHERE id = 1  -- 根部门 ID
    
    UNION ALL
    
    -- 递归查询（递归成员）
    SELECT d.id, d.name, d.parent_id, dt.level + 1
    FROM departments d
    JOIN DepartmentTree dt ON d.parent_id = dt.id
)
SELECT * FROM DepartmentTree;

2. Oracle 递归查询的函数 CONNECT BY

SELECT id, name, parent_id, LEVEL
FROM departments
START WITH id = 1  -- 根部门 ID
CONNECT BY PRIOR id = parent_id
ORDER SIBLINGS BY name;

SQL 中使用 CTE（公共表表达式）和特定数据库函数（如 Oracle 的 CONNECT BY）实现递归查询的主要区别：
CTE：
迭代逻辑：通过在递归成员中引用 CTE 自身来实现迭代，每次迭代生成新的结果集
灵活性：支持更复杂的递归逻辑，例如在递归过程中进行条件过滤、聚合计算
临时结果存储：每次递归生成的结果集通常存储在临时表中，适合处理中等规模数据
优化支持：部分数据库（如 PostgreSQL）可对 CTE 进行优化，避免重复计算
循环检测：部分数据库（如 PostgreSQL）支持 WHERE NOT CYCLE 子句检测循环引用
聚合操作：可在递归过程中使用聚合函数（如 SUM、COUNT）
跨数据库开发，需要保证代码兼容性
递归逻辑复杂，需要灵活控制迭代过程

CONNECT BY：
特定数据库函数，属于特定数据库的专有语法（如 Oracle、DB2），不具备跨数据库移植性，使用 START WITH 和 CONNECT BY 子句，语法更简洁，但仅适用于支持该语法的数据库
路径遍历：基于父子关系直接遍历整个层级结构，隐式构建路径。
深度控制：可通过 LEVEL 伪列直接获取节点深度，但无法像 CTE 那样灵活调整递归条件。
路径缓存：Oracle 等数据库会缓存遍历路径，对于大规模层级数据可能具有更好的性能。
索引依赖：性能高度依赖父子关系字段的索引优化。
路径展示：支持通过 SYS_CONNECT_BY_PATH 函数直接生成节点路径。
排序控制：通过 ORDER SIBLINGS BY 子句控制同级节点的排序。