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

Oracle 实现递归有两种核心方式也是面试 / 工作高频考点CONNECT BY传统树形查询适合层级结构递归 WITH11gR2 支持标准 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树形查询向下递归查所有子节点需求查询总公司下所有子部门包含层级、路径、根节点方法 1CONNECT BY 树形查询最常用sqlSELECT 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标准 SQLsqlWITH 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向上递归查所有父节点需求查询海淀办事处的所有上级部门代码sqlSELECT 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过滤递归结果剪枝需求查询华北分公司及其下属部门排除海淀办事处代码sqlSELECT 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 关键字表格关键字作用START WITH递归起始行PRIOR代表上一行数据递归关联条件LEVEL当前递归层级根 1SYS_CONNECT_BY_PATH拼接层级路径CONNECT_BY_ROOT获取根节点数据NOCYCLE避免死循环2. 递归 WITH 结构sqlWITH 递归名称 AS ( 锚点成员起始数据 UNION ALL 递归成员自连接查询子节点 ) SELECT * FROM 递归名称;总结树形结构部门 / 员工 / 菜单优先用CONNECT BY语法简洁复杂递归多表关联、累计计算用递归 WITH向下递归PRIOR 子ID 父ID向上递归PRIOR 父ID 子ID死循环必加NOCYCLE