connect by 的用法
一、首先从一个经典的查询序列数例子入手：ITPUB个人空间 Y{1V:K{
#Ep LB!m#p6?8^0 select level from dual connect by level <= 10ITPUB个人空间 vh NM6Y3_t0w
%S K#jE.{(j0 select level from dual connect by 1 = 1ITPUB个人空间)y,t q w IJ
上例均可查询得到1 .. N 的序列(但最多100行)
x+H;sX)jN0
edw:G9x[$D"u.uS}y0 我们来分析一下其工作原理，level<=10用来控制循环的次数，即要重复多少次扫描表dual中的内容。第一次扫描得出的结果集的level都是1,第二次扫描的结果集的level都是2,依此类推。可能用文字描述的不太容易懂，下面我们通过试验来说明：ITPUB个人空间2ln2{-n R
ITPUB个人空间GG}2B {
with x asITPUB个人空间(Y!^:gO7LTO}
D HU E P"~'Q0 ( select 'aa' chr from dual
F0~hwy#|] vQ7x+m0
}@ [(Jg0 union all
&n;bu\] vB0ITPUB个人空间(vc'`d?.qJ6G
select 'bb' chr from dual)ITPUB个人空间SE,_@$DQCV)Z
ITPUB个人空间 Lsj1X V,IvE#eV/R
select level ,chr,lpad( ' ' ,( level - 1 )* 5 , '-' )||chr other from x connect by level <= 3
g!D&g2z4}5T0ITPUB个人空间F*L7`[ x5Q
LEVEL CHR OTHERITPUB个人空间5f J-@ RW0H
1 aa aaITPUB个人空间2Z ["m ?x"f-p+fR
2 aa ---- aa
ggj M*FTg;B0 3 aa --------- aaITPUB个人空间 G\'?t)Vut&C2EAo
3 bb --------- bbITPUB个人空间L FJ2I jb@3{3^1?&@
2 bb ---- bbITPUB个人空间7r y KCe Sc
3 aa --------- aa
(]:u%IxI J%L him KA0 3 bb --------- bbITPUB个人空间J4tZ}7U4w C?-e
1 bb bb
z)g Q;IJ1M0 2 aa ---- aa
f X%B`nLz@"O!l0 3 aa --------- aaITPUB个人空间iD&dIJ
3 bb --------- bbITPUB个人空间c2g/NE7u H
2 bb ---- bbITPUB个人空间5R l.`6O(W
3 aa --------- aa
U w6d;B1mp0 3 bb --------- bb
t M3]KY J3D g0 可见是全部level的树形结构，当扫描对象是dual时，即一个level只生成一条记录.
KE UA pc%[/J.J0 ITPUB个人空间 ^Ko)FM!C#| H
二、如何解决from dual只显示100行的问题：ITPUB个人空间 xmH,}}4td
selectlevel from dual connectbylevel <=300
R z0Tg"T/V?Z0 只显示100行，但据说只是9i的显示问题，解决方法如下：
xu*p-Y$I Z0 select * from (level from dual connectbylevel <=300)ITPUB个人空间0sfz;_Q(A
即可显示300行！用以解决无法多行显示问题
(s!w:?L9KY/Kvd0
~.` d)ou+v6~:j5us0 应用举例如下：
{+u'qXm kaX0ITPUB个人空间 g {1H j6bR2Q
select to_date( 2008 || '0101' , 'yyyymmdd' ) + rownum - 1 rq,ITPUB个人空间&I]&H!c h2u
Yi5\g+`:x4^0 to_char(to_date( 2008 || '0101' , 'yyyymmdd' ) + rownum - 1 , 'day' ) dayITPUB个人空间3P8Sh;?Ou L&d
'\7w F,jut1R0 from ( select rownum from dual
E9}2H6t9LJ2WY9k9mi0 connect by rownum <= to_date( 2008 || '1231' , 'yyyymmdd' ) - to_date( 2008 || '0101' , 'yyyymmdd' )+ 1 );ITPUB个人空间$R+Cy a-Qy)r
作用：列出所有日期及星期几，可用于查询工作日
*pPg6?-| e~0 ITPUB个人空间 zH3? } S(J \'w*b
ZqFl)A8ZF6d\0 ITPUB个人空间~-y,r#{:NF"fi
三、start with ... connect by 用法讲解：ITPUB个人空间0F'{m rU@Dw
构建如下table：ITPUB个人空间q5]VU'J he1e9sE-y
ID NAME PIDITPUB个人空间M+e#|Z b G(EP&^Q
1 10 0
L7FJ7dzy0 2 11 1
!|uM0J}H0 3 20 0
]}P YI9}0 4 12 1ITPUB个人空间X't2H j XD+x$N+]
5 121 2ITPUB个人空间 xU/MF9q,p
code example1：ITPUB个人空间]4X I+Pl{v Z
"C)c Y`/GH0 select TBL_TEST.*, level from TBL_TEST
u[ o9xUy`x0ITPUB个人空间&ujV$AJW h B
start with pid= 1 -- 可写到 connect by 后面
LXIu#]3D9F2q0
G%\FBr/e0 connect by prior pid = idITPUB个人空间)iBP(O"{.P
yW9] rH6K.N5h V-X0 ID NAME PID LEVELITPUB个人空间jfCht1?8Y
2 11 1 1ITPUB个人空间v5lQHKA S+O%Z
1 10 0 2ITPUB个人空间m2r*w$w1q6{(\/e#q
4 12 1 1ITPUB个人空间(M[5vn-eYO
1 10 0 2ITPUB个人空间,N2B1{9o8G
2I5i_Tr W0 code example2：ITPUB个人空间 n-o!d?!Y.z Rk1g#}
*\,L}1?;PoZ N0 select TBL_TEST.*, level from TBL_TEST
"V n Eq$y%ss0
G!CBg8nUH o!k G0 start with id = 5 -- 可写到 connect by 后面ITPUB个人空间 Z:zJ xLU ]/nZ
](L5u YpW2gms[0 connect by prior pid = idITPUB个人空间$G2M:X*}2k Y5k'?
ITPUB个人空间5k _"~ ` g s'lpg@9J
ID NAME PID LEVELITPUB个人空间%uY"z5u0CR
5 121 2 1
0d5V4T~lx!H0 2 11 1 2ITPUB个人空间;e }jL2C'o \{~~
1 10 0 3ITPUB个人空间?,dGQ3q"ySZ
j}O+Tc0 说明：
(VI N.m:~0 1、先从start with pid=1 句开始查询 得到 2 11 1 1 =====> level置1；
HJn4F+@-t-s0 2、根据pid = id，查询 id=1 句，得到 1 10 0 2 =====> level置2；
E3u6fY/s P2g A0 3、根据pid = id，查询 id=0 句，未查询到后结束该树枝；
X/m+m$yn1U0
[Sy f+o3{0 注：prior pid = id 句说明 pid是id的父节点，通过pid查询id
K4wcO/} Q"{0
2]:@*pu L?%jC0
2h i uXkZ @0ITPUB个人空间1T6VkkV&zH
四、sys_connect_by_path函数讲解：
jWt \8Q0ITPUB个人空间/B U bGEv$f
sys_connect_by_path函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，在一个格中显示
}'NY3j}V+f-]U0
t.P;N8W7_:T@v0 select level ,sys_connect_by_path( id , '/' ) from TBL_TEST
4V~ _ A.K7X$B0
(lum2P ~#`jI8Q0 start with pid= 1ITPUB个人空间Q%F@Zew
ITPUB个人空间0@ K$d4Zo"^DH4[-F
connect by prior pid = id ;ITPUB个人空间'fzWW,~'alk)_H
ITPUB个人空间)d$u @em/| ?(L
ITPUB个人空间-nXL!d.bT^
ITPUB个人空间K8`.b'W%R
select level ,sys_connect_by_path(pid, '/' ) from TBL_TEST
|R:V0oM u0
4S | d an*dS0 start with pid= 1ITPUB个人空间-[Z;C6z4C&~X
8p Oz o VJC0 connect by prior pid = id ;ITPUB个人空间,u$T@U$a] O
/cNJ;qkH0cC0ig4`0 ITPUB个人空间}SX W2qKY^
ITPUB个人空间%zT,^}6~ }
可以比较这两段代码的运行结果与code example1的结果之间的差异，即可理解此函数用法。
#Ep LB!m#p6?8^0 select level from dual connect by level <= 10ITPUB个人空间 vh NM6Y3_t0w
%S K#jE.{(j0 select level from dual connect by 1 = 1ITPUB个人空间)y,t q w IJ
上例均可查询得到1 .. N 的序列(但最多100行)
x+H;sX)jN0
edw:G9x[$D"u.uS}y0 我们来分析一下其工作原理，level<=10用来控制循环的次数，即要重复多少次扫描表dual中的内容。第一次扫描得出的结果集的level都是1,第二次扫描的结果集的level都是2,依此类推。可能用文字描述的不太容易懂，下面我们通过试验来说明：ITPUB个人空间2ln2{-n R
ITPUB个人空间GG}2B {
with x asITPUB个人空间(Y!^:gO7LTO}
D HU E P"~'Q0 ( select 'aa' chr from dual
F0~hwy#|] vQ7x+m0
}@ [(Jg0 union all
&n;bu\] vB0ITPUB个人空间(vc'`d?.qJ6G
select 'bb' chr from dual)ITPUB个人空间SE,_@$DQCV)Z
ITPUB个人空间 Lsj1X V,IvE#eV/R
select level ,chr,lpad( ' ' ,( level - 1 )* 5 , '-' )||chr other from x connect by level <= 3
g!D&g2z4}5T0ITPUB个人空间F*L7`[ x5Q
LEVEL CHR OTHERITPUB个人空间5f J-@ RW0H
1 aa aaITPUB个人空间2Z ["m ?x"f-p+fR
2 aa ---- aa
ggj M*FTg;B0 3 aa --------- aaITPUB个人空间 G\'?t)Vut&C2EAo
3 bb --------- bbITPUB个人空间L FJ2I jb@3{3^1?&@
2 bb ---- bbITPUB个人空间7r y KCe Sc
3 aa --------- aa
(]:u%IxI J%L him KA0 3 bb --------- bbITPUB个人空间J4tZ}7U4w C?-e
1 bb bb
z)g Q;IJ1M0 2 aa ---- aa
f X%B`nLz@"O!l0 3 aa --------- aaITPUB个人空间iD&dIJ
3 bb --------- bbITPUB个人空间c2g/NE7u H
2 bb ---- bbITPUB个人空间5R l.`6O(W
3 aa --------- aa
U w6d;B1mp0 3 bb --------- bb
t M3]KY J3D g0 可见是全部level的树形结构，当扫描对象是dual时，即一个level只生成一条记录.
KE UA pc%[/J.J0 ITPUB个人空间 ^Ko)FM!C#| H
二、如何解决from dual只显示100行的问题：ITPUB个人空间 xmH,}}4td
selectlevel from dual connectbylevel <=300
R z0Tg"T/V?Z0 只显示100行，但据说只是9i的显示问题，解决方法如下：
xu*p-Y$I Z0 select * from (level from dual connectbylevel <=300)ITPUB个人空间0sfz;_Q(A
即可显示300行！用以解决无法多行显示问题
(s!w:?L9KY/Kvd0
~.` d)ou+v6~:j5us0 应用举例如下：
{+u'qXm kaX0ITPUB个人空间 g {1H j6bR2Q
select to_date( 2008 || '0101' , 'yyyymmdd' ) + rownum - 1 rq,ITPUB个人空间&I]&H!c h2u
Yi5\g+`:x4^0 to_char(to_date( 2008 || '0101' , 'yyyymmdd' ) + rownum - 1 , 'day' ) dayITPUB个人空间3P8Sh;?Ou L&d
'\7w F,jut1R0 from ( select rownum from dual
E9}2H6t9LJ2WY9k9mi0 connect by rownum <= to_date( 2008 || '1231' , 'yyyymmdd' ) - to_date( 2008 || '0101' , 'yyyymmdd' )+ 1 );ITPUB个人空间$R+Cy a-Qy)r
作用：列出所有日期及星期几，可用于查询工作日
*pPg6?-| e~0 ITPUB个人空间 zH3? } S(J \'w*b
ZqFl)A8ZF6d\0 ITPUB个人空间~-y,r#{:NF"fi
三、start with ... connect by 用法讲解：ITPUB个人空间0F'{m rU@Dw
构建如下table：ITPUB个人空间q5]VU'J he1e9sE-y
ID NAME PIDITPUB个人空间M+e#|Z b G(EP&^Q
1 10 0
L7FJ7dzy0 2 11 1
!|uM0J}H0 3 20 0
]}P YI9}0 4 12 1ITPUB个人空间X't2H j XD+x$N+]
5 121 2ITPUB个人空间 xU/MF9q,p
code example1：ITPUB个人空间]4X I+Pl{v Z
"C)c Y`/GH0 select TBL_TEST.*, level from TBL_TEST
u[ o9xUy`x0ITPUB个人空间&ujV$AJW h B
start with pid= 1 -- 可写到 connect by 后面
LXIu#]3D9F2q0
G%\FBr/e0 connect by prior pid = idITPUB个人空间)iBP(O"{.P
yW9] rH6K.N5h V-X0 ID NAME PID LEVELITPUB个人空间jfCht1?8Y
2 11 1 1ITPUB个人空间v5lQHKA S+O%Z
1 10 0 2ITPUB个人空间m2r*w$w1q6{(\/e#q
4 12 1 1ITPUB个人空间(M[5vn-eYO
1 10 0 2ITPUB个人空间,N2B1{9o8G
2I5i_Tr W0 code example2：ITPUB个人空间 n-o!d?!Y.z Rk1g#}
*\,L}1?;PoZ N0 select TBL_TEST.*, level from TBL_TEST
"V n Eq$y%ss0
G!CBg8nUH o!k G0 start with id = 5 -- 可写到 connect by 后面ITPUB个人空间 Z:zJ xLU ]/nZ
](L5u YpW2gms[0 connect by prior pid = idITPUB个人空间$G2M:X*}2k Y5k'?
ITPUB个人空间5k _"~ ` g s'lpg@9J
ID NAME PID LEVELITPUB个人空间%uY"z5u0CR
5 121 2 1
0d5V4T~lx!H0 2 11 1 2ITPUB个人空间;e }jL2C'o \{~~
1 10 0 3ITPUB个人空间?,dGQ3q"ySZ
j}O+Tc0 说明：
(VI N.m:~0 1、先从start with pid=1 句开始查询 得到 2 11 1 1 =====> level置1；
HJn4F+@-t-s0 2、根据pid = id，查询 id=1 句，得到 1 10 0 2 =====> level置2；
E3u6fY/s P2g A0 3、根据pid = id，查询 id=0 句，未查询到后结束该树枝；
X/m+m$yn1U0
[Sy f+o3{0 注：prior pid = id 句说明 pid是id的父节点，通过pid查询id
K4wcO/} Q"{0
2]:@*pu L?%jC0
2h i uXkZ @0ITPUB个人空间1T6VkkV&zH
四、sys_connect_by_path函数讲解：
jWt \8Q0ITPUB个人空间/B U bGEv$f
sys_connect_by_path函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，在一个格中显示
}'NY3j}V+f-]U0
t.P;N8W7_:T@v0 select level ,sys_connect_by_path( id , '/' ) from TBL_TEST
4V~ _ A.K7X$B0
(lum2P ~#`jI8Q0 start with pid= 1ITPUB个人空间Q%F@Zew
ITPUB个人空间0@ K$d4Zo"^DH4[-F
connect by prior pid = id ;ITPUB个人空间'fzWW,~'alk)_H
ITPUB个人空间)d$u @em/| ?(L
ITPUB个人空间-nXL!d.bT^
ITPUB个人空间K8`.b'W%R
select level ,sys_connect_by_path(pid, '/' ) from TBL_TEST
|R:V0oM u0
4S | d an*dS0 start with pid= 1ITPUB个人空间-[Z;C6z4C&~X
8p Oz o VJC0 connect by prior pid = id ;ITPUB个人空间,u$T@U$a] O
/cNJ;qkH0cC0ig4`0 ITPUB个人空间}SX W2qKY^
ITPUB个人空间%zT,^}6~ }
可以比较这两段代码的运行结果与code example1的结果之间的差异，即可理解此函数用法。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25472150/viewspace-690109/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Oracle DBA 常用的SQL日志
下一篇：
oracle job 案例
请登录后发表评论
登录
全部评论
