1、函数简介:
首先SYS_CONNECT_BY_PATH这个函数是oracle9i新提出的!
然后这个函数要和:START WITH(非必须) 和 CONNECT BY PRIOR(必须)联合使用;
START WITH 开始遍历的的节点
CONNECT BY PRIOR 父子关系的对应
LEVEL 是树的深度
函数有俩个参数(形成树的字段名,层级分隔符)
2、示例代码:
SELECT T.IDS,T.P_ID,SYS_CONNECT_BY_PATH(T.REF_ID,'->') AS "PATH",LEVEL FROM ORGPOST_TREE T START WITH T.P_ID='0' CONNECT BY PRIOR T.IDS=T.P_ID
3、注意事项:
带WHERE条件的查询语句
错误写法:
SELECT T.IDS,T.P_ID,SYS_CONNECT_BY_PATH(T.REF_ID,'->') AS "PATH" FROM ORGPOST_TREE T WHERE T.ORG_ID='012010007' AND T.WFC_VERSION='1' --或导致sql执行异常 START WITH T.P_ID='0' CONNECT BY PRIOR T.IDS=T.P_ID
正确写法:作为子查询(本人没有验证效率问题)
SELECT T.IDS,T.P_ID,SYS_CONNECT_BY_PATH(T.REF_ID,'->') AS "PATH",LEVEL FROM (SELECT * FROM ORGPOST_TREE WHERE ORG_ID='012010007' AND WFC_VERSION='1') T START WITH T.P_ID='0' CONNECT BY PRIOR T.IDS=T.P_ID
4、查询结果:
ab08c0ed37bf40fdb5fe84dab3df8b1c 0 ->DG001 1 64857309603e44bbb9e8e9345a5ce91e ab08c0ed37bf40fdb5fe84dab3df8b1c ->DG001->CXY0002 2 93973cb3a3fb4a568ffe8d0fe9353a5f 64857309603e44bbb9e8e9345a5ce91e ->DG001->CXY0002->CXY004 3
根据这个结果可以实现很多有意思的查询