一、语法
l3NM+kU.X;J'VI0大致写法:select * from some_table [where 条件1] connect by [条件2]startwith[条件3];51Testing软件测试网uE){/Ta${&YDb
其中 connect by 与startwith语句摆放的先后顺序不影响查询的结果,[where 条件1]可以不需要。
`9U%qj*mK0[where 条件1]、[条件2]、[条件3]各自作用的范围都不相同:
^\*V)M(f.n!j0Oyp051Testing软件测试网2o7yO
y ]P6@j
[where 条件1]是在根据“connect by [条件2]startwith[条件3]”选择出来的记录中进行过滤,是针对单条记录的过滤, 不会考虑树的结构;
+y8g|(q
M%X D:}/v@!J051Testing软件测试网o
V*i7{"s,Q6C4TG
[条件2]指定构造树的条件,以及对树分支的过滤条件,在这里执行的过滤会把符合条件的记录及其下的所有子节点都过滤掉;51Testing软件测试网'XYzUcW
51Testing软件测试网K^VZ |)F I%X S
[条件3]限定作为搜索起始点的条件,如果是自上而下的搜索则是限定作为根节点的条件,如果是自下而上的搜索则是限定作为叶子节点的条件;
-QAy1U1nw w'[F051Testing软件测试网PI:hh'Fa2^C8U
示例:
"|W1wZ cs!}7d0假如有如下结构的表:some_table(id,p_id,name),其中p_id保存父记录的id。
\vle9JA*Sr0select * from some_table t where t.id!=123 connect by prior t.p_id=t.id and t.p_id!=321startwitht.p_id=33 or t.p_id=66;
KIq|'sb0
[twT(u:]h0对prior的说明:51Testing软件测试网#]0\ \x7u4b
WH
prior存在于[条件2]中,可以不要,不要的时候只能查找到符合“startwith[条件3]”的记录,不会在寻找这些记录的子节点。要的时候有两种写法:connect by prior t.p_id=t.id 或 connect by t.p_id=prior t.id,前一种写法表示采用自上而下的搜索方式(先找父节点然后找子节点),后一种写法表示采用自下而上的搜索方式(先找叶子节点然后找父节点)。51Testing软件测试网qho JdFb?
,xH2X L6[MYI&kD0二、执行原理51Testing软件测试网*f7N7a
|/?~Xl
connect by...startwith...的执行原理可以用以下一段程序的执行以及对存储过程RECURSE()的调用来说明:51Testing软件测试网 qgvwS!jn&?
0f{1qUhn7hA0/* 遍历表中的每条记录,对比是否满足startwith后的条件,如果不满足则继续下一条,51Testing软件测试网B]#n1g;l6V1G z
X._bL
如果满足则以该记录为根节点,然后调用RECURSE()递归寻找该节点下的子节点,51Testing软件测试网"}CQ7agU@So dT
如此循环直到遍历完整个表的所有记录 。*/
"eF Fe
^-e0for rec in (select * from some_table) loop
Y'Jp!QPk0if FULLFILLS_START_WITH_CONDITION(rec) then51Testing软件测试网!|1z!{*o#FomS'E @
RECURSE(rec, rec.child);51Testing软件测试网oS\ q8Q]G
end if;
J!~8~W]E,_0end loop;
Q6T-R,E3T051Testing软件测试网*wN3c7U!dO]
/* 寻找子节点的存储过程*/
0Y8W#lQCy4F0procedure RECURSE (rec in MATCHES_SELECT_STMT, new_parent IN field_type) is
k4Q}4A?s#S0begin
#gG.b:u0z0APPEND_RESULT_LIST(rec); /*把记录加入结果集合中*/51Testing软件测试网(O:Q"inW['_9t
/*再次遍历表中的所有记录,对比是否满足connect by后的条件,如果不满足则继续下一条,51Testing软件测试网}6TM
P}%kvJ
如果满足则再以该记录为根节点,然后调用RECURSE()继续递归寻找该节点下的子节点,
.Q^ |;t
F;b} A.j([h0如此循环直到找至叶子节点。*/51Testing软件测试网-@pVT ND#p5wB/O,R
for rec_recurse in (select * from some_table) loop
l|N'q0Q2QW9y;i?0 if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.child, new_parent) then51Testing软件测试网zL5l5vy2X
RECURSE(rec_recurse,rec_recurse.child);
6?m8z{YP^"Y%as0 end if;51Testing软件测试网]G7^F^Pg$h9z ]
end loop;51Testing软件测试网GX9mQl"t
end procedure RECURSE;
?Jx2Vw0`YiEA051Testing软件测试网z9H OYC4{%H S
三、使用探讨51Testing软件测试网8T
H:k9}pe4T8PE
从上面的执行原理可以看到“connect by...startwith...”构造树的方式是:(1)如果是自上而下方式,则把表中的每一条记录都作为根节点来生成树,所以表中有多少条记录就会构造出多少棵树。(2)如果是自下而上的搜索方式,则把表中的每一条记录都作为叶子节点来生成分支,所以表中有多少条记录就会生成多少条分支。51Testing软件测试网Kc!i;q u|g1CHc
因此如果表中的记录不是严格遵照每条记录都只能有一个父记录的原则,那么就可能有部分记录会存在于多棵树中,那么在查找记录的时候就可能会出现找到多条重复记录的异常情况。[align=left][/align]
Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。
创建示例表:
CREATE TABLE TBL_TEST51Testing软件测试网g,AK(IW
(51Testing软件测试网0tR7cG Qt*Is1o
ID NUMBER,51Testing软件测试网1{Z^9P6G6{6j
NAME VARCHAR2(100 BYTE),51Testing软件测试网V2Qg N6B^Q6\
PID NUMBER DEFAULT 0
(i-K6_#p0g~T4E a.H0);
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
Z+i7a-}:F"rv0INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');51Testing软件测试网ue\-Q{+U#P[
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');51Testing软件测试网%Tb+vb-hv|t
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');51Testing软件测试网2g)Q c`'{nF:[?
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
从Root往树末梢递归
select * from TBL_TEST
drA
p6L8H&x
_n0 start with id=151Testing软件测试网w0L6_,G*y;j` k
connect by prior id = pid
从末梢往树ROOT递归
select * from TBL_TEST
O;j c_#qQS M-E0 start with id=5
5sS
s'{:E0 connect by prior pid = id
=====
对于oracle进行简单树查询(递归查询)
51Testing软件测试网i;QI/k6y%{
A*p0B:eWAN+H0DEPTID | PAREDEPTID | NAME |
NUMBER | NUMBER | CHAR (40 Byte) |
部门id | 父部门id(所属部门id) | 部门名称 |
SJ3Qrj0
2J^~']h/d
lJ051Testing软件测试网z+BZ9pILAX通过子节点向根节点追朔.51Testing软件测试网2j0NB%@s#m[j
- select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid
- select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid
select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid
51Testing软件测试网D)S\Q0j*\'N6f
x,~/KGz{%P0d m3PfU"kX:R bD0通过根节点遍历子节点.
fe;w
R;K+nV`.}0- select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid
- select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid
select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid
/};n9P3Wa,s0 51Testing软件测试网PJ`6?HbuB
&[.v#U+z.y.T
Y0可通过level 关键字查询所在层次.51Testing软件测试网'Y{ ~#lR-D
- select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid
- select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid
select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid
LS[$cgJUK$o9t0
6EfQ-I*A7Xn0w+f{p9Ej1x`;nu0再次复习一下:start with ...connect by 的用法,start with后面所跟的就是就是递归的种子。
DV"J6rH.`1r!t0'l7XN)^Bh@(J?0递归的种子也就是递归开始的地方connect by 后面的"prior" 如果缺省:则只能查询到符合条件的起始行,并不进行递归查询;51Testing软件测试网f Yz8g%X:~
51Testing软件测试网c~tsd+{connect by prior 后面所放的字段是有关系的,它指明了查询的方向。51Testing软件测试网%K-F-BK5Ds
[:Aa%G8Kpt0练习: 通过子节点获得顶节点51Testing软件测试网
S0P'z!ZZ2[i7].HA$H
- select FIRST_VALUE(deptid) OVER (ORDER BY LEVEL DESC ROWS UNBOUNDED PRECEDING) AS firstdeptid from persons.dept start with deptid=76 connect by prior paredeptid=deptid
51Testing软件测试网'KdYl
s====这种方法只是当表里就有一颗树,多棵树怎么办?51Testing软件测试网-B"m jms
@lF;^
51Testing软件测试网m]
j4sD{W
q&Agq9PDmPpSL0
/@muq A:D`]0