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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)