Oracle递归查询详解

原创于 2025-08-29 14:09:53 发布 · 848 阅读

23 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

数据库同时被 2 个专栏收录

114 篇文章

订阅专栏

Oracle

38 篇文章

订阅专栏

递归查询是 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]; -- 按兄弟节点排序

关键元素解释：

LEVEL 伪列：
- 这是一个系统自动生成的伪列，它表示当前行在树形结构中的层级。
- 根节点的 LEVEL 为 1，根节点的直接子节点为 2，以此类推。
START WITH 子句：
- 用于指定递归开始的根节点（一行或几行）。
- 例如：START WITH employee_id = 100 表示从员工 ID 为 100 的 CEO 开始构建树。
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。这可以用于从子节点向上遍历到根节点（自下而上）。
ORDER SIBLINGS BY 子句：
- 在保持层次结构完整性的前提下，对同一父节点下的兄弟节点进行排序。
- 比直接在最后用 ORDER BY 更合理，因为它不会打乱树的显示顺序。

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

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

EMPLOYEE_ID	NAME	MANAGER_ID	JOB_TITLE
100	King	(null)	President
101	Kochhar	100	VP
102	De Haan	100	VP
103	Hunold	102	Manager
104	Ernst	103	Analyst
…	…	…	…

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

查询语句（自上而下）：

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; -- 同一经理下的员工按名字排序

查询结果可能如下：

LEVEL	Indented_Name	EMPLOYEE_ID	NAME	MANAGER_ID	JOB_TITLE
1	King	100	King	(null)	President
2	De Haan	102	De Haan	100	VP
3	Hunold	103	Hunold	102	Manager
4	Ernst	104	Ernst	103	Analyst
2	Kochhar	101	Kochhar	100	VP
…	…	…	…	…	…

从这个结果可以清晰地看出 King 是根节点，De Haan 和 Kochhar 向他汇报，Hunold 向 De Haan 汇报，Ernst 向 Hunold 汇报。

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

CONNECT_BY_ROOT：
- 用于获取当前行所在树的根节点的某列值。
- SELECT CONNECT_BY_ROOT NAME AS Top_Manager, NAME ... 会为 Ernst 显示 Top_Manager 是 King。
SYS_CONNECT_BY_PATH：
- 显示从根节点到当前节点的完整路径。
- SELECT SYS_CONNECT_BY_PATH(NAME, ' -> ') AS Path ... 对于 Ernst，会显示 -> King -> De Haan -> Hunold -> Ernst。
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;

五、两种方法的对比

特性	`START WITH ... CONNECT BY` (Oracle专用)	递归 CTE `WITH` (ANSI 标准)
语法简洁性	更简洁，专为层次查询设计	稍显冗长，但逻辑清晰
功能强大性	非常强大，有专属伪列和函数(`LEVEL`, `SYS_CONNECT_BY_PATH`等)	功能同样强大，但需要自己实现类似功能（如用字段记录Path）
可读性	对熟悉 Oracle 的人可读性高	遵循声明式编程，递归逻辑更标准，对来自其他数据库的用户可读性高
性能	通常性能更优，Oracle 对其有深度优化	性能也很好，但可能不如原生语法
标准性	Oracle 私有语法	ANSI SQL 标准，可移植性好