树状循环,在代码里叫做递归循环,递归循环就是函数自己调用自己。当sql里的一行数据有子节点和父节点,它就开始了循环;
1对多循环,类似for I=1 to max。表里要有最大max,并且连续;
当sql长到一定长度,直接放进sql developer非常不方便,推荐一款notepad++插件 sqlformat
树状循环sql
查找目录的sql
SELECT DISTINCT
rtrim(reverse (sys_connect_by_path(reverse (portal_label), ' > ')), ' > ') path
FROM SYSADM.PSPRSMDEFN
WHERE portal_name = 'EMPLOYEE'
AND portal_prntobjname = 'PORTAL_ROOT_OBJECT'
START WITH portal_objname like '%COMPONENT Name%'
CONNECT BY PRIOR portal_prntobjname = portal_objname
sql用到三个字段:
当前节点
它的父节点
节点label
执行结果:
1对多循环sql
select
ROLEUSER as USER_ID,
reverse(SYS_CONNECT_BY_PATH(reverse(ROLENAME), ','))
from
(
select
ROLEUSER,
ROLENAME,
count(*) OVER ( partition by ROLEUSER ) as ROLECOUNT,
ROW_NUMBER () OVER ( partition by ROLEUSER order by ROLENAME) ROLESEQUENCE
from
PSROLEUSER
where
ROLEUSER is not null)
where
ROLECOUNT=ROLESEQUENCE
start with
ROLESEQUENCE = 1
connect by prior
ROLESEQUENCE+1=ROLESEQUENCE
and prior
ROLEUSER=ROLEUSER;
执行结果:
SqlFormat tools
这个工具是notepad++的插件,下载地址:
https://www.sqlinform.com/download-free-notepad-plugin/
我是下载SQLinFormNpp.dll(我本人notepad++是32位的),创建folder放进dll文件
C:\Program Files (x86)\Notepad++\plugins\SQLinFormNpp
使用前
使用后
更多sql资料:
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/SYS_CONNECT_BY_PATH.html#GUID-D25A0F86-B559-4090-9164-7A2C84D1E11E