SYS_CONNECT_BY_PATH函数用法

SELECT enameFROM scott.emp START WITH ename = 'KING' CONNECT BY PRIOR empno = mgr; --得到结果为：KINGJONESSCOTTADAMSFORDSMITHBLAKEALLENWARDMARTINTURNERJAMES而：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 是标示父子关系的对应！

如下例子：

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、带层次关系

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、行列转换

如把一个表的所有列连成一行，用逗号分隔:

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