oracle sys_connect_by_root,[原]Oracle sys_connect_by_path 函数

SYS_CONNECT_BY_PATH(column, char)

Purpose

SYS_CONNECT_BY_PATHis valid only in hierarchical queries(级联查询). It returns the path of a column value from root to node, with column values separated bycharfor each row returned byCONNECTBYcondition.

Bothcolumnandcharcan be any of the data typesCHAR,VARCHAR2,NCHAR, orNVARCHAR2. The string returned is ofVARCHAR2data type and is in the same character set ascolumn.

See Also:

"Hierarchical Queries"for more information about hierarchical queries andCONNECTBYconditions

Examples

The following example returns the path of employee names from employeeKochharto all employees ofKochhar(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

第一个参数是  形成树形式的字段，