sys_connect_by_path函数是为了配合递归查询的函数, sys_connect_by_path函数是将递归查到的数据加上特定的符号,看起来更明显
connect by递归查询配合sys_connect_by_path基本语法是:
select sys_connect_by_path([字段],[分隔符号]) from [表格] start with ... connect by prior id = pId
start with:表示以什么为根节点,不加限制可以写1=1,要以id为123的节点为根节点,就写为start with id =123
connect by:connect by是必须的,start with有些情况是可以省略的,或者直接start with 1=1不加限制
prior:prior关键字可以放在等号的前面,也可以放在等号的后面,表示的意义是不一样的,比如 prior id = pid,就表示pid就是这条记录的根节点了
sys_connect_by_path第一个参数是形成树形式的字段,第二个参数是父级和其子级分隔显示用的分隔符!
然后给个例子,scott用户登录
select sys_connect_by_path(ename, '>')
from emp
start with ename = 'KING'
connect by prior empno = mgr;
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiI0gTMx81dsQWZ4lmZf1GLlpXazVmcvwFciV2dsQXYtJ3bm9CX9s2RkBnVHFmb1clWvB3MaVnRtp1XlBXe0xCMy81dvRWYoNHLwEzX5xCMx8FesU2cfdGLwMzX0xiRGZkRGZ0Xy9GbvNGLpZTY1EmMZVDUSFTU4VFRR9Fd4VGdsYTMfVmepNHLrJXYtJXZ0F2dvwVZnFWbp1zczV2YvJHctM3cv1Ce-cmbw5SMyMTN1IzM0Q2Y1EzM5UmYyYzXxIDNyQTMxAzLcdDMyIDMy8CXn9Gbi9CXzV2Zh1WavwVbvNmLvR3YxUjLyM3Lc9CX6MHc0RHaiojIsJye.png)
可能遇到的报错,ORA-30004:使用SYS_CONNECT_BY_PATH函数时,不能将分隔符作为列值的一部分
select sys_connect_by_path(ename, '@')
from emp
start with ename = 'KING'
connect by prior empno = mgr;