Oracle WITH 子句与递归查询

适用版本：Oracle Database 9i / 11g R2 / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

WITH 子句（CTE）与递归查询[1]：

类型：

• 普通 CTE
• 递归 CTE（11g R2+）
• 内联

详细见：Oracle CTE 与递归查询。

---

2. 普通 CTE

2.1 基础

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM employees GROUP BY dept_id
)
SELECT e.name, e.salary, d.avg_sal
FROM employees e, dept_avg d
WHERE e.dept_id = d.dept_id AND e.salary > d.avg_sal;

2.2 多个 CTE

WITH 
high_paid AS (
  SELECT * FROM employees WHERE salary > 10000
),
dept_count AS (
  SELECT dept_id, COUNT(*) AS cnt FROM high_paid GROUP BY dept_id
)
SELECT d.dept_name, c.cnt
FROM dept_count c, departments d
WHERE c.dept_id = d.id;

2.3 引用

WITH 
a AS (SELECT 1 AS x FROM dual),
b AS (SELECT x + 1 AS y FROM a)
SELECT * FROM b;
-- y = 2

---

3. 递归 CTE

3.1 语法

WITH cte (cols) AS (
  -- 锚点
  SELECT ...
  UNION ALL
  -- 递归
  SELECT ... FROM cte WHERE ...
)
SELECT * FROM cte;

3.2 员工层级

WITH emp_tree (emp_id, emp_name, mgr_id, lvl) AS (
  -- 锚点：顶级
  SELECT employee_id, last_name, manager_id, 1
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- 递归：下级
  SELECT e.employee_id, e.last_name, e.manager_id, t.lvl + 1
  FROM employees e, emp_tree t
  WHERE e.manager_id = t.emp_id
)
SELECT LPAD(' ', lvl * 2) || emp_name AS hierarchy, lvl
FROM emp_tree
ORDER BY lvl, emp_name;

3.3 BOM 物料展开

WITH bom_tree (parent_id, child_id, qty, lvl) AS (
  -- 顶层
  SELECT parent_id, child_id, quantity, 1
  FROM bom WHERE parent_id = 100
  
  UNION ALL
  
  -- 递归
  SELECT b.parent_id, b.child_id, b.quantity, t.lvl + 1
  FROM bom b, bom_tree t
  WHERE b.parent_id = t.child_id
)
SELECT child_id, qty, lvl
FROM bom_tree;

---

4. 递归 CONNECT BY

4.1 基础

SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

详细见：Oracle 层次查询。

4.2 排序

SELECT LPAD(' ', LEVEL * 2) || last_name AS name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

4.3 过滤

-- WHERE：在树构建后过滤
SELECT ... FROM employees
WHERE salary > 5000
START WITH ...
CONNECT BY ...;

-- CONNECT BY 过滤（构造时）
SELECT ... FROM employees
START WITH ...
CONNECT BY PRIOR employee_id = manager_id AND salary > 5000;

---

5. CTE vs CONNECT BY

5.1 对比

特性	CTE	CONNECT BY
标准	ANSI	Oracle
性能	相当	相当
灵活	高	中
多递归	支持	不支持

5.2 选择

• 复杂：CTE
• 简单树：CONNECT BY

---

6. SYS_CONNECT_BY_PATH

6.1 路径

SELECT 
  employee_id,
  SYS_CONNECT_BY_PATH(last_name, '/') AS path,
  LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
-- /King/Smith/Jones

6.2 CTE 替代

WITH emp_tree (emp_id, name, path, lvl) AS (
  SELECT employee_id, last_name, '/' || last_name, 1
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.last_name, t.path || '/' || e.last_name, t.lvl + 1
  FROM employees e, emp_tree t
  WHERE e.manager_id = t.emp_id
)
SELECT * FROM emp_tree;

---

7. 循环检测

7.1 CONNECT BY

-- NOCYCLE 避免死循环
SELECT ... FROM employees
START WITH ...
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

7.2 CTE

-- 手动检测
WITH emp_tree (emp_id, name, path, lvl) AS (
  SELECT employee_id, last_name, '/' || TO_CHAR(employee_id), 1
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.last_name, 
    t.path || '/' || TO_CHAR(e.employee_id), t.lvl + 1
  FROM employees e, emp_tree t
  WHERE e.manager_id = t.emp_id
    AND INSTR(t.path, '/' || TO_CHAR(e.employee_id)) = 0
)
SELECT * FROM emp_tree;

---

8. 性能

8.1 索引

- 递归键加索引
- CONNECT BY PRIOR col = col
- manager_id 索引

8.2 限制层级

-- CTE
WHERE lvl <= 5

-- CONNECT BY
CONNECT BY PRIOR ... AND LEVEL <= 5

8.3 监控

EXPLAIN PLAN FOR ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY));
-- HASH JOIN SEMI / CONNECT BY

---

9. 应用场景

9.1 组织结构

- 员工层级
- 部门层级

9.2 BOM

- 物料展开
- 配方

9.3 评论树

- 评论回复
- 论坛

9.4 路径

- 路由
- 图遍历

---

10. 常见坑与排错

10.1 死循环

-- CTE 手动检测
-- CONNECT BY NOCYCLE

10.2 性能差

-- 1. 索引
-- 2. 限制层级
-- 3. 优化锚点

10.3 数据缺失

-- 检查起始条件
-- START WITH 正确

---

11. 最佳实践

1. CTE ANSI：标准
2. CONNECT BY：简单
3. 索引关键：性能
4. NOCYCLE：防死循环
5. 限制层级：性能
6. 路径：可视化
7. 监控执行计划：优化
8. 测试验证：完整
9. 业务理解：正确
10. 文档化：复杂

---

12. 参考资料

[1] Oracle Database SQL Language Reference 19c, “SELECT” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html