Oracle数据库递归查询示例完整指南

平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“Oracle数据库递归查询示例”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。

前言

递归查询是 SQL 中很强大的一种功能，它用来处理具有层次结构或树形结构实际处理时，的数据。在 Oracle 中，递归查询主要借助 START WITH 和 CONNECT BY 子句实现，这是 Oracle 的传统方法。从 Oracle 11g Release 2 开始，它也兼容 ANSI SQL 标准的 WITH 子句（即公共表表达式 CTE）形式的递归查询。

结合项目来看，我将重点介绍最常用、也是 Oracle 特色的 START WITH ... CONNECT BY 语法，同时简要对比标准的递归 CTE 方法。

一、核心概念与语法 (START WITH …… CONNECT BY)

这种语法专门为处理层次查询而设计，很直观和高效。

基本语法结构：

SELECT [LEVEL], column1, column2, ...
FROM table_name
[WHERE ...]
START WITH condition -- 指定层次结构的根节点（起点）
CONNECT BY [PRIOR] condition -- 定义父节点和子节点之间的关系
[ORDER SIBLINGS BY column_name]; -- 按兄弟节点排序

关键元素解释：

1. LEVEL 伪列：

   • 这是一个系统自动生成的伪列，它表示当前行在树形结构中的层级。
   • 根节点的 LEVEL 为 1，根节点的直接子节点为 2，以此类推。

2. START WITH 子句：

   • 用来指定递归开始的根节点（一行或几行）。
   • 比如：START WITH employee_id = 100 表示从员工 ID 为 100 的 CEO 开始构建树。

3. CONNECT BY 子句：

   • 这是递归查询的核心，它定义了父行和子行之间的关系。
   • PRIOR 运算符：至关重要。它引用的是父行的列。
     • CONNECT BY PRIOR child_id = parent_id：表示上一行的 child_id 等于当前行的 parent_id。这通常用来从父节点向下遍历到子节点（自上而下）。
     • CONNECT BY child_id = PRIOR parent_id：表示当前行的 child_id 等于上一行的 parent_id。这能够用来从子节点向上遍历到根节点（自下而上）。

4. ORDER SIBLINGS BY 子句：

   • 在保持层次结构完整性的前提下，对同一父节点下的兄弟节点进行排序。
   • 比直接在最后用 ORDER BY 更合理，因为它不会打乱树的显示顺序。

二、经典示例：员工组织架构图

假设我们有一个 employees 表，结构如下所示：

需求： 查询所有员工，并显示他们的汇报层级关系。

查询语句（自上而下）：

SELECT
    LEVEL,
    LPAD(' ', (LEVEL-1)*4, ' ') || NAME AS Indented_Name, -- 用缩进直观显示层级
    EMPLOYEE_ID,
    NAME,
    MANAGER_ID,
    JOB_TITLE
FROM employees
START WITH MANAGER_ID IS NULL -- 从最大的老板开始（没有经理的人）
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID -- 上一行的员工ID = 当前行的经理ID
ORDER SIBLINGS BY NAME; -- 同一经理下的员工按名字排序

查询结果可能如下所示：

从实现思路看，从这个结果能够清晰地看出 King 是根节点，De Haan 和 Kochhar 向他汇报，Hunold 向 De Haan 汇报，Ernst 向 Hunold 汇报。

三、其他有用的运算符和函数

1. CONNECT_BY_ROOT：

   • 用来拿到当前行所在树的根节点的某列值。
   • SELECT CONNECT_BY_ROOT NAME AS Top_Manager, NAME ... 会为 Ernst 显示 Top_Manager 是 King。

2. SYS_CONNECT_BY_PATH：

   • 显示从根节点到当前节点的完整路径。
   • SELECT SYS_CONNECT_BY_PATH(NAME, ' -> ') AS Path ... 对于 Ernst，会显示 -> King -> De Haan -> Hunold -> Ernst。

3. CONNECT_BY_ISLEAF：

   • 判断当前行是否是叶子节点（即没有子节点）。是叶子节点则得到 1，否则得到 0。

四、ANSI SQL 标准方法：递归公用表表达式 (CTE)

Oracle 也兼容采用 WITH 子句进行递归查询，语法更符合其他数据库（如 PostgreSQL, SQL Server）的标准。

语法结构：

WITH cte_name (column_list) AS (
    -- 锚定成员 (Anchor Member)：定义根节点
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition -- 类似于 START WITH

    UNION ALL

    -- 递归成员 (Recursive Member)：引用CTE自身，进行递归join
    SELECT t.column1, t.column2, ...
    FROM table_name t
    JOIN cte_name c ON t.parent_id = c.child_id -- 类似于 CONNECT BY
)
-- 主查询
SELECT * FROM cte_name;

用递归 CTE 实现上面的例子：

WITH Employee_Tree (LEVEL, EMPLOYEE_ID, NAME, MANAGER_ID, JOB_TITLE) AS (
    -- 锚定成员：找到根节点
    SELECT
        1 AS LEVEL,
        EMPLOYEE_ID,
        NAME,
        MANAGER_ID,
        JOB_TITLE
    FROM employees
    WHERE MANAGER_ID IS NULL

    UNION ALL

    -- 递归成员：连接员工表和CTE自身
    SELECT
        p.LEVEL + 1, -- 层级增加
        e.EMPLOYEE_ID,
        e.NAME,
        e.MANAGER_ID,
        e.JOB_TITLE
    FROM employees e
    INNER JOIN Employee_Tree p ON e.MANAGER_ID = p.EMPLOYEE_ID
)
SELECT * FROM Employee_Tree
ORDER BY LEVEL, NAME;

五、两种方法的对比

总结

• 对于 Oracle 环境下的开发，START WITH ... CONNECT BY 是处理递归查询的首选，因为它语法简洁、功能专一且性能优异。
• 如果你需编写跨数据库理解这一步时，兼容的 SQL，或者希望递归逻辑更符合通用的编程思维（先锚定再递归），那么应该采用递归 CTE (WITH 子句)。

结合项目来看，无论是哪种方法，递归查询都是操作树形结构数据（如组织架构、菜单、分类目录、BOM物料清单）的利器。

到此这篇关于Oracle递归查询的文章就介绍到这了,更多相关Oracle递归查询内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多兼容脚本之家！