为了更好的查询一个树状结构的表,在Oracle的PL/SQL中提供乐一个诱人的特性——CONNECT BY子句。它大大的方便了我们查找树状表:遍历一棵树、寻找某个分支……,但还是存在一些不足。在Oracle 10G,就对这个特性做了增强。下面就举例说明一下:
CONNECT_BY_ROOT
一张表,有多颗子树(根节点为0),现在我想知道每个节点属于哪个子树。举例:铃音目录结构下有多个大分类:中外名曲、流行经典、浪漫舞曲……,每个大类下面又有多个子类,子类下面还可以细分。那现在想要知道每个子类分属哪个大类,或者要统计每个大类下面有多少个子类。
看下面的例子,DIRINDEX分别为1、2、3的就是大分类,其他编号的都是子类或孙子类:
selectdirindex,fatherindex,RPAD(' ',2*(LEVEL-1))|| dirnamefromt_tonedirlib
startwithfatherindex=0
connectbyfatherindex=priordirindex
DIRINDEXFATHERINDEX DIRNAME
--------------------- ------------------------------------
10中文经典
521kkkkkkk
7052222
5852sixx
5952seven
6952uiouoooo
5552four
71流行风云
81影视金曲
11118aaa
11128bbb
11138ccc
91古典音乐
811小熊之家
10481龙珠
10581snoppy
10181叮当1
10281龙猫
10381叮当2
20热门流行
312有奖活动
322相约香格里拉
502新浪彩铃
30老歌回放
3333老电影
3353怀旧金曲
26 rows selected
如何统计1、2、3三个大类下有哪些子类,有多少个子类?在9i及以前要做这样的统计十分麻烦。现在10G提供了一个新特性:CONNECT_BY_ROOT, 他的作用就是使结果不是当前的节点ID,而满足查询条件下的根节点的ID。以上面为例,我们需要得到以上结果只需要执行以下语句就可以搞定了:
selectCONNECT_BY_ROOT dirindex,fatherindex,RPAD(' ',2*(LEVEL-1))|| dirnamefromt_tonedirlib
startwithfatherindex=0
connectbyfatherindex=priordirindex
CONNECT_BY_ROOTDIRINDEXFATHERINDEX RPAD('',2*(LEVEL-1))||DIRNAME
----------------------- ------------- -----------------------------
10中文经典
11kkkkkkk
152222
152sixx
152seven
152uiouoooo
152four
11流行风云
11影视金曲
18aaa
18bbb
18ccc
11古典音乐
11小熊之家
181龙珠
181snoppy
181叮当1
181龙猫
181叮当2
20热门流行
22有奖活动
22相约香格里拉
22新浪彩铃
30老歌回放
33老电影
33怀旧金曲
26 rows selected
查出来的结果中,CONNECT_BY_ROOTDIRINDEX就是各个子类(孙子类)所属的大类编号,如果需要统计,就只要执行以下语句马上可以统计出来了:
selectrootindex,count('X')from
(selectCONNECT_BY_ROOT dirindexasrootindex
fromt_tonedirlib
startwithfatherindex=0
connectbyfatherindex=priordirindex)a
groupbya.rootindex
ROOTINDEX COUNT('X')
--------- ----------
119
24
33
3 rows selected
CONNECT_BY_ISLEAF
经常有DBA因为要查找树状表中的叶子节点而苦恼。大部分DBA为了解决这个问题就给表增加了一个字段来描述这 个节点是否为叶子节点。但这样做有很大的弊端:需要通代码逻辑来保证这个字段的正确性。
Oracle 10G中提供了一个新特性——CONNECT_BY_ISLEAF——来解决这个问题了。简单点说,这个属性结果表明当前节点在满足条件的查询结果中是否为叶子节点, 0不是,1是:
selectCONNECT_BY_ISLEAF,dirindex,fatherindex,RPAD(' ',2*(LEVEL-1))|| dirname
fromt_tonedirlib
startwithfatherindex=0
connectbyfatherindex=priordirindex
CONNECT_BY_ISLEAF DIRINDEX FATHERINDEX RPAD(' ',2*(LEVEL-1))||dirname
----------------- ---------------- ---------------------------------
010中文经典
0521kkkkkkk
17052222
15852sixx
15952seven
16952uiouoooo
15552four
171流行风云
081影视金曲
111118aaa
111128bbb
111138ccc
191古典音乐
0811小熊之家
110481龙珠
110581snoppy
110181叮当1
110281龙猫
1103 81叮当2
020热门流行
1312有奖活动
1322相约香格里拉
1502新浪彩铃
030老歌回放
13333老电影
13353怀旧金曲
26 rows selected
一看结果,清晰明了!
CONNECT_BY_ISCYCLE
我们的树状属性一般都是在一条记录中记录一个当前节点的ID和这个节点的父ID来实现。但是,一旦数据中出现了循环记录,如两个节点互为对方父节点,系统就会报ORA-01436错误:
insertintot_tonedirlib(dirindex,fatherindex,dirname,status)values(666,667,'123',5);
1 row inserted
insertintot_tonedirlib(dirindex,fatherindex,dirname,status)values(667,666,'456',5);
1 row inserted
selectdirindex,fatherindex,RPAD(' ',2*(LEVEL-1))|| dirnamefromt_tonedirlib
startwithfatherindex=666
connectbyfatherindex=priordirindex
ORA-01436:用户数据中的CONNECT BY循环
10G中,可以通过加上NOCYCLE关键字避免报错。并且通过CONNECT_BY_ISCYCLE属性就知道哪些节点产生了循环:
selectCONNECT_BY_ISCYCLE,dirindex,fatherindex,RPAD(' ',2*(LEVEL-1))|| dirname
fromt_tonedirlib
startwithfatherindex=666
connectbyNOCYCLEfatherindex=priordirindex
CONNECT_BY_ISCYCLE DIRINDEX FATHERINDEX RPAD(' ',2*(LEVEL-1))||dirname
----------------- ---------------- ---------------------------------
0667666 456
1666667123
2 rows selected
以上就是在10G中增强的CONNECT BY了。当然对于这些增强特性的作用肯定不止如上介绍的,还需要更多高人去挖掘了。