ORACLE 中的SYS_CONNECT_BY_PATH 函数

最新推荐文章于 2024-03-21 16:21:56 发布

haha00007

最新推荐文章于 2024-03-21 16:21:56 发布

阅读量527

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/haha00007/article/details/9021835

Oracle 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了Oracle SQL中的SYS_CONNECT_BY_PATH函数使用方法，该函数用于在层级查询中生成从根节点到当前节点路径的字符串。通过具体示例展示了如何使用此函数构建雇员层级结构。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

语法

用法

SYS_CONNECT_BY_PATH is valid only in hierarchical queries. It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECTBY condition.

Both column and char can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in the same character set as column.

范例

The following example returns the path of employee names from employee Kochhar to all employees of Kochhar (and their employees):

SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   START WITH last_name = 'Kochhar'
   CONNECT BY PRIOR employee_id = manager_id;

Path
---------------------------------------------------------------
/Kochhar
   /Kochhar/Greenberg
     /Kochhar/Greenberg/Faviet
     /Kochhar/Greenberg/Chen
     /Kochhar/Greenberg/Sciarra
     /Kochhar/Greenberg/Urman
     /Kochhar/Greenberg/Popp
   /Kochhar/Whalen
   /Kochhar/Mavris
   /Kochhar/Baer
   /Kochhar/Higgins
     /Kochhar/Higgins/Gietz