Oracle函数:sys_connect_by_path

Oracle函数:sys_connect_by_path 主要用于树查询(层次查询) 以及 多列转行。其语法一般为：

       select ... sys_connect_by_path(column_name,'connect_symbol')  from table

       start with ... connect by ... prior

依托于该语法，我们可以将一个表形结构以树的顺序列出来。在下面列述了Oracle中树型查询的常用查询方式以及经常使用的 与树查询相关的Oracle特性函数等，在这里用oracle自带的emp数据表做测试。

 

1. 查找一个员工的所有下属员工。

   在树形结构中即查找一个节点的所有直属子节点(所有后代)。

 

select sys_connect_by_path(ename,'/') tree from emp start with ename='KING' connect by mgr=prior empno;

 以上sql语句是：查找姓名为'KING'的员工的所有下属员工。

 

2. 查找一个员工的所有上司经理。

   在树形结构中即查找一个节点的所有直属父节点(祖宗)。

 

select sys_connect_by_path(ename,'/') tree from emp start with ename='SMITH' connect by empno=prior mgr;

 以上sql语句是：查找姓名为'SMITH'的员工的所有上司经理。

 

上面列出两条树型查询语句之间的区别在于prior关键字的位置不同，所以决定了查询的方式不同。 

当mgr=prior empno时，数据库会根据当前的empno迭代出mgr与该empno相同的记录，所以查询的结果是迭代出了所有的子类记录；

而empno=prior mgr时，数据库会跟据当前的mgr来迭代出empno与该mgr相同的记录，所以查询出来的结果就是所有的父类记录。

 

对于数据库来说，根节点并不一定是在数据库中设计的顶级节点，而是start with开始的地方。

 

sys_connect_by_path函数就是从start with开始的地方开始遍历，并记下其遍历到的节点，start with开始的地方被视为根节点，将遍历到的路径根据函数中的分隔符，组成一个新的字符串，这个功能还是很强大的。

 

sys_connect_by_path函数用connect by来寻找下一条记录，直到迭代找不到相应记录为止。概念与递归类似，connect by指定递归(连接)条件，如果条件不满足则递归结束。

 

 

level: 在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2， 依此类推。 

connect_by_root：用在列名之前，记录的是当前层的根节点内容。 
connect_by_isleaf：判断当前节点是否包含下级节点，如果包含的话，说明不是叶子节点，这里返回0；反之，如果不包含下级节点，这里返回1。

 

 

多列转行应用

   将emp数据表的所有字段名输出，以逗号分隔(field1,field2.....)

 

select max(ltrim(sys_connect_by_path(column_name,','),',')) tree 
from (
   select column_name,rownum rn from user_tab_columns where table_name='EMP'
) 
start with rn=1 connect by rn=rownum