Oracle 递归函数练习（CONNECT BY + 递归 WITH）

Oracle 实现递归有两种核心方式，也是面试 / 工作高频考点：

1. CONNECT BY（传统树形查询，适合层级结构）
2. 递归 WITH（11gR2 + 支持，标准 SQL，功能更强）

我给你整理3 个经典实战练习，带完整表数据、代码、详解，直接复制运行即可练习。

准备测试表（所有练习通用）

先创建部门层级表和员工上下级表，插入测试数据：

sql

-- 1. 部门层级表（父部门->子部门） CREATE TABLE dept_tree ( dept_id NUMBER PRIMARY KEY, -- 部门ID dept_name VARCHAR2(50), -- 部门名称 parent_id NUMBER -- 父部门ID ); -- 插入数据（顶级部门parent_id为NULL） INSERT INTO dept_tree VALUES (1, '总公司', NULL); INSERT INTO dept_tree VALUES (2, '华北分公司', 1); INSERT INTO dept_tree VALUES (3, '华南分公司', 1); INSERT INTO dept_tree VALUES (4, '北京事业部', 2); INSERT INTO dept_tree VALUES (5, '深圳事业部', 3); INSERT INTO dept_tree VALUES (6, '海淀办事处', 4); -- 2. 员工上下级表（领导->下属） CREATE TABLE emp_tree ( emp_id NUMBER PRIMARY KEY, -- 员工ID emp_name VARCHAR2(50), -- 员工姓名 manager_id NUMBER -- 领导ID ); INSERT INTO emp_tree VALUES (100, '董事长', NULL); INSERT INTO emp_tree VALUES (101, '总经理', 100); INSERT INTO emp_tree VALUES (102, '销售总监', 101); INSERT INTO emp_tree VALUES (103, '华北经理', 102); INSERT INTO emp_tree VALUES (104, '华南经理', 102); INSERT INTO emp_tree VALUES (105, '北京销售', 103); COMMIT;

练习 1：树形查询（向下递归：查所有子节点）

需求

查询总公司下所有子部门（包含层级、路径、根节点）

方法 1：CONNECT BY 树形查询（最常用）

sql

SELECT dept_id, dept_name, parent_id, LEVEL AS 层级, -- 递归层级（根节点=1） SYS_CONNECT_BY_PATH(dept_name, '->') AS 部门路径, -- 拼接层级路径 CONNECT_BY_ROOT dept_name AS 根部门 -- 根节点数据 FROM dept_tree START WITH parent_id IS NULL -- 递归起始点（顶级部门） CONNECT BY PRIOR dept_id = parent_id; -- 递归条件：上一行的部门ID = 当前行父ID

方法 2：递归 WITH（标准 SQL）

sql

WITH recu_dept AS ( -- 1. 锚点成员（起始数据：根节点） SELECT dept_id, dept_name, parent_id, 1 AS 层级 FROM dept_tree WHERE parent_id IS NULL UNION ALL -- 2. 递归成员（循环查询子节点） SELECT d.dept_id, d.dept_name, d.parent_id, rd.层级 + 1 FROM dept_tree d INNER JOIN recu_dept rd ON d.parent_id = rd.dept_id ) SELECT * FROM recu_dept;

练习 2：向上递归（查所有父节点）

需求

查询海淀办事处的所有上级部门

代码

sql

SELECT dept_id, dept_name, LEVEL AS 层级, SYS_CONNECT_BY_PATH(dept_name, '<-') AS 上级路径 FROM dept_tree START WITH dept_name = '海淀办事处' -- 起始点：最末级部门 CONNECT BY PRIOR parent_id = dept_id; -- 向上递归：上一行的父ID = 当前行部门ID

练习 3：过滤递归结果（剪枝）

需求

查询华北分公司及其下属部门，排除海淀办事处

代码

sql

SELECT dept_id, dept_name, LEVEL AS 层级 FROM dept_tree START WITH dept_name = '华北分公司' CONNECT BY PRIOR dept_id = parent_id AND dept_name != '海淀办事处'; -- 递归条件中过滤（剪枝）

练习 4：循环检测（处理死循环）

需求

模拟数据死循环（部门 6 的父部门设为自己），查询时避免无限递归

sql

-- 制造死循环 UPDATE dept_tree SET parent_id = 6 WHERE dept_id = 6; COMMIT; -- 查询 + 死循环检测（NOCYCLE + CONNECT_BY_ISCYCLE） SELECT dept_id, dept_name, CONNECT_BY_ISCYCLE AS 是否循环节点, -- 1=循环节点，0=正常 SYS_CONNECT_BY_PATH(dept_name, '->') AS 路径 FROM dept_tree START WITH parent_id IS NULL CONNECT BY NOCYCLE PRIOR dept_id = parent_id; -- NOCYCLE：遇到循环停止递归

核心语法速记

1. CONNECT BY 关键字

表格

2. 递归 WITH 结构

sql

WITH 递归名称 AS ( 锚点成员（起始数据） UNION ALL 递归成员（自连接查询子节点） ) SELECT * FROM 递归名称;

总结

1. 树形结构（部门 / 员工 / 菜单）优先用CONNECT BY，语法简洁
2. 复杂递归（多表关联、累计计算）用递归 WITH
3. 向下递归：PRIOR 子ID = 父ID；向上递归：PRIOR 父ID = 子ID
4. 死循环必加NOCYCLE