prior oracle 10g,Oracle 10G 新特性——增强的CONNECT BY子句

为了更好的查询一个树状结构的表，在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))

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9650775/viewspace-920387/，如需转载，请注明出处，否则将追究法律责任。