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]; -- 按兄弟节点排序

关键元素解释：

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 标准，可移植性好