Oracle SYS_CONNECT_BY_PATH() 函数使用指南

在 Oracle 数据库中，SYS_CONNECT_BY_PATH() 是一个专门用于**层级查询（Hierarchical Queries）**的内置函数。它能够从树形结构的根节点开始，顺着层级关系逐层向下遍历，并将遍历过程中所经过的每个节点的指定列值，使用自定义的分隔符连接成一条完整的层级路径。

该函数仅在包含 START WITH ... CONNECT BY 的层级查询中有效。

语法

Oracle SYS_CONNECT_BY_PATH() 函数的标准语法如下：

SYS_CONNECT_BY_PATH(column, char)

参数说明

column：必需。表示要沿层级路径拼接的列名或标量表达式（通常为节点名称、代码或 ID）。
char：必需。用于分隔各个节点列值的字符串字面量（通常为 '/'、' -> ' 或 ',' 等）。
- 重要限制：分隔符 char 的内容不能出现在 column 的列值中，否则 Oracle 将抛出 ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have separator as part of column value 错误。

返回值

返回一个包含从根节点到当前节点完整路径的字符串。数据类型通常为 VARCHAR2（若路径超长，在扩展模式下支持最大 32,767 字节，或在复杂聚合中转为 CLOB）。

使用示例

以下通过一个典型的公司部门组织架构示例，展示 SYS_CONNECT_BY_PATH() 的实际用法。

准备示例数据

CREATE TABLE departments (
    dept_id     NUMBER PRIMARY KEY,
    dept_name   VARCHAR2(50),
    parent_id   NUMBER
);

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);
INSERT INTO departments VALUES (6, '华东销售部', 5);
COMMIT;

示例 1：获取每个部门的完整层级路径

使用 / 作为分隔符生成完整的部门树路径：

SELECT 
    dept_id,
    dept_name,
    LEVEL AS depth,
    SYS_CONNECT_BY_PATH(dept_name, '/') AS path
FROM departments
START WITH parent_id IS NULL
CONNECT BY PRIOR dept_id = parent_id
ORDER SIBLINGS BY dept_id;

输出结果：

DEPT_ID  DEPT_NAME       DEPTH  PATH
-------  -------------  ------  ---------------------------------------
      1  总公司              1  /总公司
      2  技术研发中心        2  /总公司/技术研发中心
      3  后端研发部          3  /总公司/技术研发中心/后端研发部
      4  前端研发部          3  /总公司/技术研发中心/前端研发部
      5  市场营销中心        2  /总公司/市场营销中心
      6  华东销售部          3  /总公司/市场营销中心/华东销售部

示例 2：去除路径开头的多余分隔符

默认情况下，SYS_CONNECT_BY_PATH 的输出总是以指定的分隔符开头。如果需要去掉开头的分隔符（如 / 或 ->），可以使用 LTRIM() 函数：

SELECT 
    dept_name,
    LTRIM(SYS_CONNECT_BY_PATH(dept_name, ' -> '), ' -> ') AS clean_path
FROM departments
START WITH parent_id IS NULL
CONNECT BY PRIOR dept_id = parent_id;

输出结果：

DEPT_NAME       CLEAN_PATH
-------------   ------------------------------------------
总公司          总公司
技术研发中心    总公司 -> 技术研发中心
后端研发部      总公司 -> 技术研发中心 -> 后端研发部
前端研发部      总公司 -> 技术研发中心 -> 前端研发部
市场营销中心    总公司 -> 市场营销中心
华东销售部      总公司 -> 市场营销中心 -> 华东销售部

示例 3：仅查询叶子节点的完整路径

结合 CONNECT_BY_ISLEAF 伪列，可以快速筛选出末级节点及其自顶向下的完整归属链路：

SELECT 
    dept_name AS leaf_dept,
    SYS_CONNECT_BY_PATH(dept_name, '/') AS full_path
FROM departments
WHERE CONNECT_BY_ISLEAF = 1
START WITH parent_id IS NULL
CONNECT BY PRIOR dept_id = parent_id;

常见注意事项

分隔符冲突 (ORA-30004)：若节点名称本身包含逗号（如 Apple, Inc.），而分隔符选用了 ','，会导致 ORA-30004。此时应更换为不会在数据中出现的字符（如 | 或 ~）。
循环引用导致死循环 (ORA-01436)：如果数据中存在自引用或环形回路（例如 A 的父节点是 B，B 的父节点是 A），必须在 CONNECT BY 后面加上 NOCYCLE 关键字。