sys_connect_by_path

最新推荐文章于 2022-02-14 11:04:04 发布

黑色的熊猫

最新推荐文章于 2022-02-14 11:04:04 发布

阅读量303

点赞数

CC 4.0 BY-SA版权

文章标签： path

本文链接：https://blog.youkuaiyun.com/xiongli0204/article/details/7991046

本文展示了如何使用Oracle的SYS_CONNECT_BY_PATH函数来构建组织结构或层级关系的路径字符串。通过两个具体例子，分别从元数据表和员工表中获取带有层级关系的数据，展示了如何设置起始节点并递归连接子节点。

select substr(sys_connect_by_path(meta.code, '.'), 2)
from T_META meta where meta.code=#{metaCode}
start with meta.parent = '@ROOT@'
connect by nocycle prior meta.code = meta.parent

SELECT substr(SYS_CONNECT_BY_PATH(ename, '>'),2) "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