Oracle 遍历下级

在 Oracle 中遍历下级节点（如树形结构数据），可以通过 递归查询 或 CONNECT BY 子句 实现。以下是两种方法的详细说明和示例：

---

方法 1：使用 CONNECT BY 子句

CONNECT BY 是 Oracle 中专门用于处理层次化查询的语法，适用于树形结构数据的遍历。

步骤说明

1. 表结构示例
   假设表名为 Departments，包含部门ID和父部门ID：

   CREATE TABLE Departments (
       DepartmentID INT PRIMARY KEY,
       ParentDepartmentID INT,
       DepartmentName VARCHAR2(50)
   );
   

2. 查询逻辑

   • 使用 START WITH 指定起始节点（如根节点）。
   • 使用 CONNECT BY PRIOR 关联父节点与子节点。

示例代码

-- 创建示例数据
INSERT INTO Departments VALUES (1, NULL, '总部');
INSERT INTO Departments VALUES (2, 1, '技术部');
INSERT INTO Departments VALUES (3, 2, '开发组');
INSERT INTO Departments VALUES (4, 2, '测试组');
INSERT INTO Departments VALUES (5, 1, '市场部');

-- 查询所有下级（包含自身）
SELECT 
    DepartmentID,
    DepartmentName,
    ParentDepartmentID,
    LEVEL AS Level  -- 层级从1开始
FROM Departments
START WITH DepartmentID = 1  -- 指定根节点
CONNECT BY PRIOR DepartmentID = ParentDepartmentID
ORDER SIBLINGS BY DepartmentID;

结果示例

DepartmentID	DepartmentName	ParentDepartmentID	Level
1	总部	NULL	1
2	技术部	1	2
3	开发组	2	3
4	测试组	2	3
5	市场部	1	2

关键参数说明

• START WITH：指定查询的起始节点。
• CONNECT BY PRIOR：定义父子关系，PRIOR 表示父节点。
• LEVEL：表示节点层级（根节点为1，子节点递增）。
• ORDER SIBLINGS BY：按兄弟节点排序。

---

方法 2：使用递归 CTE

Oracle 从 11g R2 版本开始支持递归 CTE，语法与其他数据库（如 MySQL、SQL Server）类似。

示例代码

-- 递归查询所有下级（包含自身）
WITH DepartmentCTE (DepartmentID, DepartmentName, ParentDepartmentID, Level) AS (
    -- 锚定成员：选择根节点
    SELECT 
        DepartmentID, 
        DepartmentName, 
        ParentDepartmentID,
        1 AS Level  -- 层级从1开始
    FROM Departments
    WHERE DepartmentID = 1  -- 指定根节点

    UNION ALL

    -- 递归成员：逐层查找下级
    SELECT 
        d.DepartmentID, 
        d.DepartmentName, 
        d.ParentDepartmentID,
        cte.Level + 1  -- 每层深度+1
    FROM Departments d
    INNER JOIN DepartmentCTE cte 
        ON d.ParentDepartmentID = cte.DepartmentID
)
SELECT * FROM DepartmentCTE;

结果示例

DepartmentID	DepartmentName	ParentDepartmentID	Level
1	总部	NULL	1
2	技术部	1	2
5	市场部	1	2
3	开发组	2	3
4	测试组	2	3

---

扩展场景

1. 仅查询直接下级
   使用 CONNECT BY：

   SELECT * 
   FROM Departments
   WHERE ParentDepartmentID = 1;  -- 获取总部的直接下级
   

2. 限制递归深度
   使用 WHERE 条件限制递归层数：

   WITH DepartmentCTE AS (
       SELECT 
           DepartmentID, 
           DepartmentName, 
           ParentDepartmentID,
           1 AS Level
       FROM Departments
       WHERE DepartmentID = 1
   
       UNION ALL
   
       SELECT 
           d.DepartmentID, 
           d.DepartmentName, 
           d.ParentDepartmentID,
           cte.Level + 1
       FROM Departments d
       INNER JOIN DepartmentCTE cte 
           ON d.ParentDepartmentID = cte.DepartmentID
       WHERE cte.Level < 2  -- 限制递归深度为2层
   )
   SELECT * FROM DepartmentCTE;
   

3. 排除根节点
   修改锚定成员为直接下级：

   WITH DepartmentCTE AS (
       SELECT 
           DepartmentID, 
           DepartmentName, 
           ParentDepartmentID,
           1 AS Level
       FROM Departments
       WHERE ParentDepartmentID = 1  -- 从总部的直接下级开始
       UNION ALL
       ...
   )
   

---

注意事项

1. 性能优化：为 ParentDepartmentID 字段创建索引以提高查询效率。
2. 递归深度限制：默认递归深度为 1000 层，超过会报错。可通过 MAX_RECURSION 参数调整。
3. 选择方法：
   • 如果数据层级较深且结构复杂，推荐使用 递归 CTE。
   • 如果数据层级较浅且需要简单查询，推荐使用 CONNECT BY。

---

通过以上方法，可以在 Oracle 中高效遍历树形结构数据，适用于部门层级、分类目录等场景。