Oracle函数sys_connect_by_path()

这个函数是oracle9i新提出来的，用来显示分层查询的路径，从跟节点到子节点的路径。

参数：sys_connect_by_path(字段名, 2个字段之间的连接符号)

注意：sys_connect_by_path()函数必须和connect by 关键字一起使用。

具体路径怎么显示，来看一个例子：

--create table
create table a (id varchar2(10),name varchar2(20));
create table b (id1 varchar2(10),id2 varchar2(10));
--insert
insert into a
select 1,'a' from dual union all
select 2,'b' from dual union all
select 3,'c' from dual union all
select 4,'d' from dual union all
select 5,'e' from dual ;
insert into b
select null,1 from dual union all
select 1,2 from dual union all
select 2,3 from dual union all
select 2,4 from dual union all
select 3,5 from dual ;
commit;

select  * from a;
select  * from b;

--查询
select  b.*,a.name,sys_connect_by_path(a.name,'/') path
from b right join a on b.id2 = a.id
start with b.id1 is null
connect by b.id1= prior b.id2
order by 2,1

结果： 

ID1 ID2 NAME PATH
1  1 a /a
2 1 2 b /a/b
3 2 3 c /a/b/c
4 2 4 d /a/b/d
5 3 5 e /a/b/c/e