SYS_CONNECT_BY_PATH 函數用法 ORACLE

SELECT ename  
FROM scott.emp   
START
WITH ename = 'KING'   
CONNECT BY PRIOR empno = mgr;   
 
--
得到結果為：  
 
KING  
JONES  
SCOTT  
ADAMS  
FORD  

SMITH  
BLAKE  
ALLEN  
WARD  
MARTIN  
TURNER  

JAMES  
 
 
 
而：  
 
 
 
SELECT
SYS_CONNECT_BY_PATH(ename, '>') "Path"   
FROM scott.emp   
START
WITH ename = 'KING'   
CONNECT BY PRIOR empno = mgr;  
 
 
 

--得到結果為：  
 
 
 
>KING  
>KING>JONES  

>KING>JONES>SCOTT  
>KING>JONES>SCOTT>ADAMS  

>KING>JONES>FORD  
>KING>JONES>FORD>SMITH  

>KING>BLAKE  
>KING>BLAKE>ALLEN  
>KING>BLAKE>WARD  

>KING>BLAKE>MARTIN  
>KING>BLAKE>TURNER  
>KING>BLAKE>JAMES  

>KING>CLARK  
>KING>CLARK>MILLER 
SELECT
ename
FROM scott.emp
START WITH ename = 'KING'
CONNECT BY
PRIOR empno = mgr;

--得到結果為：

KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES

 

而：

 

SELECT SYS_CONNECT_BY_PATH(ename, '>') "Path"
FROM scott.emp
START
WITH ename = 'KING'
CONNECT BY PRIOR empno = mgr;

 

--得到結果為：

 

>KING
>KING>JONES
>KING>JONES>SCOTT
>KING>JONES>SCOTT>ADAMS
>KING>JONES>FORD
>KING>JONES>FORD>SMITH
>KING>BLAKE
>KING>BLAKE>ALLEN
>KING>BLAKE>WARD
>KING>BLAKE>MARTIN
>KING>BLAKE>TURNER
>KING>BLAKE>JAMES
>KING>CLARK
>KING>CLARK>MILLER

其實SYS_CONNECT_BY_PATH這個函數是oracle9i才新提出來的！
它一定要和connect by子句合用！
第
一個參數是形成樹形式的欄位，第二個參數是父級和其子級分隔顯示用的分隔字元！

START WITH 代表你要開始遍曆的的節點！

CONNECT BY PRIOR 是標示父子關係的對應！

如下例子：

view plaincopy to clipboardprint?
select max(  
substr(  
sys_connect_by_path(column_name,',')  

,2)  
)  
from (select column_name,rownum rn from
user_tab_columns where table_name ='AA_TEST')  
start with rn=1
connect by rn=rownum ; 
select max(
substr(
sys_connect_by_path(column_name,',')
,2)
)
from
(select column_name,rownum rn from user_tab_columns where table_name
='AA_TEST')
start with rn=1 connect by rn=rownum ;

 

是將列用,進行分割成為一行，然後將首個，去掉，只取取最大的那個資料。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

下面是別人的例子：

1、帶層次關係

view plaincopy to clipboardprint?
SQL> create table dept(deptno
number,deptname varchar2(20),mgrno number);  
 
Table
created.  
 
SQL> insert into dept values(1,'總公司',null);  
 

1 row created.  
 
SQL> insert into dept
values(2,'浙江分公司',1);  
 
1 row created.  
 
SQL>
insert into dept values(3,'杭州分公司',2);  
 
1 row created.  
 

SQL> commit;  
 
Commit complete.  
 
SQL>
select max(substr(sys_connect_by_path(deptname,','),2)) from dept
connect by prior deptno=mgrno;  
 
MAX(SUBSTR(SYS_CONNECT_BY_PATH(DEPTNAME,','),2))  

--------------------------------------------------------------------------------  

總公司,浙江分公司,杭州分公司 
SQL> create table dept(deptno
number,deptname varchar2(20),mgrno number);

Table created.

SQL> insert into dept values(1,'總公司',null);

1 row created.

SQL> insert into dept values(2,'浙江分公司',1);

1 row created.

SQL> insert into dept values(3,'杭州分公司',2);

1 row created.

SQL> commit;

Commit complete.

SQL> select max(substr(sys_connect_by_path(deptname,','),2)) from
dept connect by prior deptno=mgrno;

MAX(SUBSTR(SYS_CONNECT_BY_PATH(DEPTNAME,','),2))
--------------------------------------------------------------------------------
總
公司,浙江分公司,杭州分公司

2、行列轉換

如把一個表的所有列連成一行，用逗號分隔:

view plaincopy to clipboardprint?
SQL> select
max(substr(sys_connect_by_path(column_name,','),2))  
from (select
column_name,rownum rn from user_tab_columns where table_name ='DEPT')  
start
with rn=1 connect by rn=rownum ;  
 
MAX(SUBSTR(SYS_CONNECT_BY_PATH(COLUMN_NAME,','),2))  

--------------------------------------------------------------------------------  

DEPTNO,DEPTNAME,MGRNO