为了更好的查询一个树状结构的表在Oracle的PL/SQL中提供乐一个诱人的特性——CONNECT BY子句它大大的方便了我们查找树状表遍历一棵树寻找某个分支……但还是存在一些不足在Oracle G就对这个特性做了增强下面就举例说明一下
CONNECT_BY_ROOT
一张表有多颗子树（根节点为）现在我想知道每个节点属于哪个子树举例铃音目录结构下有多个大分类中外名曲流行经典浪漫舞曲……每个大类下面又有多个子类子类下面还可以细分那现在想要知道每个子类分属哪个大类或者要统计每个大类下面有多少个子类
看下面的例子DIRINDEX分别为的就是大分类其他编号的都是子类或孙子类
select dirindex fatherindex RPAD( *(LEVEL)) || dirname from t_tonedirlib
start with fatherindex =
connect by fatherindex = prior dirindex
DIRINDEX FATHERINDEX DIRNAME
中文经典
kkkkkkk
sixx
seven
uiouoooo
four
流行风云
影视金曲
aaa
bbb
ccc
古典音乐
小熊之家
龙珠
snoppy
叮当
龙猫
叮当
热门流行
有奖活动
相约香格里拉
新浪彩铃
老歌回放
老电影
怀旧金曲
rows selected
如何统计三个大类下有哪些子类有多少个子类？在i及以前要做这样的统计十分麻烦现在G提供了一个新特性CONNECT_BY_ROOT他的作用就是使结果不是当前的节点ID而满足查询条件下的根节点的ID以上面为例我们需要得到以上结果只需要执行以下语句就可以搞定了
select CONNECT_BY_ROOT dirindex fatherindex RPAD( *(LEVEL)) || dirname from t_tonedirlib
start with fatherindex =
connect by fatherindex = prior dirindex
CONNECT_BY_ROOTDIRINDEX FATHERINDEX RPAD(*(LEVEL))||DIRNAME
中文经典
kkkkkkk
sixx
seven
uiouoooo
four
流行风云
影视金曲
aaa
bbb
ccc
古典音乐
小熊之家
龙珠
snoppy
叮当
龙猫
叮当
热门流行
有奖活动
相约香格里拉
新浪彩铃
老歌回放
老电影
怀旧金曲
rows selected
查出来的结果中CONNECT_BY_ROOTDIRINDEX就是各个子类（孙子类）所属的大类编号如果需要统计就只要执行以下语句马上可以统计出来了 select rootindex count(X) from
(select CONNECT_BY_ROOT dirindex as rootindex
from t_tonedirlib
start with fatherindex =
connect by fatherindex = prior dirindex) a
group by arootindex
ROOTINDEX COUNT(X)
rows selected
CONNECT_BY_ISLEAF
经常有DBA因为要查找树状表中的叶子节点而苦恼大部分DBA为了解决这个问题就给表增加了一个字段来描述这个节点是否为叶子节点但这样做有很大的弊端需要通代码逻辑来保证这个字段的正确性
Oracle G中提供了一个新特性——CONNECT_BY_ISLEAF——来解决这个问题了简单点说这个属性结果表明当前节点在满足条件的查询结果中是否为叶子节点 不是是 select CONNECT_BY_ISLEAF dirindex fatherindex RPAD( *(LEVEL)) || dirname
from t_tonedirlib
start with fatherindex =
connect by fatherindex = prior dirindex
CONNECT_BY_ISLEAF DIRINDEX FATHERINDEX RPAD( *(LEVEL))||dirname
中文经典
kkkkkkk
sixx
seven
uiouoooo
four
流行风云
影视金曲
aaa
bbb
ccc
古典音乐
小熊之家
龙珠
snoppy
叮当
龙猫
叮当
热门流行
有奖活动
相约香格里拉
新浪彩铃
老歌回放
老电影
怀旧金曲
rows selected
一看结果清晰明了！
CONNECT_BY_ISCYCLE
我们的树状属性一般都是在一条记录中记录一个当前节点的ID和这个节点的父ID来实现但是一旦数据中出现了循环记录如两个节点互为对方父节点系统就会报ORA错误 insert into t_tonedirlib(dirindex fatherindex dirname status) values ( );
row inserted
insert into t_tonedirlib(dirindex fatherindex dirname status) values ( );
row inserted
select dirindex fatherindex RPAD( *(LEVEL)) || dirname from t_tonedirlib
start with fatherindex =
connect by fatherindex = prior dirindex
ORA: 用户数据中的 CONNECT BY 循环
G中可以通过加上NOCYCLE关键字避免报错并且通过CONNECT_BY_ISCYCLE属性就知道哪些节点产生了循环 select CONNECT_BY_ISCYCLE dirindex fatherindex RPAD( *(LEVEL)) || dirname
from t_tonedirlib
start with fatherindex =
connect by NOCYCLE fatherindex = prior dirindex
CONNECT_BY_ISCYCLE DIRINDEX FATHERINDEX RPAD( *(LEVEL))||dirname
rows selected
以上就是在G中增强的CONNECT BY了当然对于这些增强特性的作用肯定不止如上介绍的还需要更多高人去挖掘了
