RUI个人博客 首页>>Oracle Development>>[原]Oracle sys_connect_by_path 函数

[原]Oracle sys_connect_by_path 函数

sys_connect_by_path     必须和connect by 子句合用。
SYS_CONNECT_BY_PATH(column, char)

Purpose

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 CONNECT BY condition.

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

See Also:

"Hierarchical Queries" for more information about hierarchical queries and CONNECT BY conditions

Examples

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/Greenberg/Chen
     /Kochhar/Greenberg/Faviet
     /Kochhar/Greenberg/Popp
     /Kochhar/Greenberg/Sciarra
     /Kochhar/Greenberg/Urman
     /Kochhar/Higgins/Gietz
   /Kochhar/Baer
   /Kochhar/Greenberg
   /Kochhar/Higgins
   /Kochhar/Mavris
   /Kochhar/Whalen
 /Kochhar
第一个参数是  形成树形式的字段,
第二个参数是父级和其子级分割显示用的分隔符
START  WITH 代表你要开始遍历的节点!
CONNECT BY PRIOR 标识父子关系的对应!
例子1:
SELECT ename   
FROM scott.emp    
START WITH ename = 'KING'    
CONNECT BY PRIOR empno = mgr; 

select sys_connect_by_path(ename,'>>') "newename"
    from emp
    start with ename='KING'
    connect by prior empno=mgr;

例子2:
将一个表的所有列连成一行,用逗号分隔
select max(substr(sys_connect_by_path(column_name,','),2))
    from (select column_name,rownum rn from user_tab_columns where table_name='EMP')
   start with rn=1 connect by rn=rownum

昵  称:
邮  箱:
评论内容:
验 证 码:
可用[code][/code]插入代码
点击刷新验证码