ORACLE之 层次查询

ORACLE 层次查询

层次查询子句 CONNECT BY，用于构造层次结果集的查询。也称树形结构查询。

语法
SELECT ...
LEVEL,
[ CONNECT_BY_ROOT ],
[ SYS_CONNECT_BY_PATH( column, ' character ' ) ],
[ CONNECT_BY_ISCYCLE ],
[ CONNECT_BY_ISLEAF ]
FROM ...
[ START WITH condition ]
CONNECT BY [ NOCYCLE]PRIORcondition | conditionPRIOR
ORDER [ SIBLINGS ] BY column

KEY WORDS:
start with
用来标示哪个节点作为根节点开始查找并构造结果集。该节点返回记录中最高节点。

 

connect by
说明每行数据将是按层次顺序检索，并规定将表中的数据连入树形结构的关系中。

 

prior
必须放置在连接关系两列中某一列的前面，等号左右均可。对于父子关系的节点， prior所在的一侧表示父节点；从而确定查找结构的顺序。

 

level
伪列 level 的作用是显示当前节点的级别（层数）。

 

connect_by_root[()]小括号为可选项
作用在一个列上，并返回当前行最顶级（top）或者最根部（root）的值。

 

sys_connect_by_path(column, '除逗号之外的特殊字符')
显示当前节点的详细路径。

 

connect_by_iscycle
伪列 connect_by_iscycle 如果当前行是循环的一部分则返回1，否则返回0。

 

connect_by_isleaf
伪列 connect_by_isleaf 与 connect_by_iscycle 相比，connect_by_isleaf 更轻量一些。作用是当 connect_by_isleaf=1 时，表示该行为叶子节点。
注： connect_by_isleaf 是 Oracle 10g 以上提供的，9i 中没有 connect_by_isleaf 这个伪列。 

select e.*, connect_by_isleaf isleaf, sys_connect_by_path(ename,'\') path, level
from emp e
where not exists(select null from emp where mgr=e.empno)
start with mgr is null
connect by prior empno=mgr;

以上代码可替代 connect_by_isleaf 这个伪列。为了显示是否为叶子节点在查询中显示了 connect_by_isleaf 这个字段。当 where 条件中 not exists 表示当前查询的是叶子节点即 connect_by_isleaf=1，当 exists 则表示不是叶子节点即 connect_by_isleaf=0

 

nocycle
伪列 connect_by_iscycle 必须要和关键字 nocycle 结合使用，否者出现 ORA-30930:  CONNECT_BY_ISCYCLE 伪列要求 NOCYCLE 关键字异常。

connect by nocycle 是指查询下一行的条件，其中 nocycle 是当递归出现环的时候终止该分支查询。如果不用 nocycle，当查询出现环时，oracle会出现如上图异常。

 

order siblings by
用于返回同一父节点之间的各个子节点的排序。

 

example:

select ename, connect_by_root (ename) as root, sys_connect_by_path(ename, ':') as chain, level,connect_by_iscycle as iscycle,connect_by_isleaf as isleaf
from emp
start with mgr is null
connect by nocycle prior empno =  mgr
order siblings by sal;