oracle数据库树形结构用法总结

oracle数据库树形结构用法总结
之前看过一篇关于oracle数据库（主要针对10g数据库）里树形结构用法的总结文章，
总结的比较好，和大家分享一下，希望对大家有所帮助。 




表结构： 


QUOTE 
create table tree1 (parent varchar2(30),child varchar2(30)); 
insert into tree1 values(null,'亚洲'); 
insert into tree1 values(null,'澳大利亚'); 
insert into tree1 values(null,'欧洲'); 
insert into tree1 values(null,'北美洲'); 
insert into tree1 values('亚洲','中国'); 
insert into tree1 values('亚洲','小日本'); 
insert into tree1 values('澳大利亚','威尔士'); 
insert into tree1 values('威尔士','悉尼'); 
insert into tree1 values('加利福尼亚','红海'); 
insert into tree1 values('加拿大','安大略地区'); 
insert into tree1 values('中国','北京'); 
insert into tree1 values('北京','知春路') 
insert into tree1 values('英格兰','伦敦'); 
insert into tree1 values('欧洲','大不列颠'); 
insert into tree1 values('小日本','大阪'); 
insert into tree1 values('小日本','东京'); 
insert into tree1 values('北美洲','加拿大'); 
insert into tree1 values('北美洲','美国'); 
insert into tree1 values('安大略地区','渥太华'); 
insert into tree1 values('安大略地区','多伦多'); 
insert into tree1 values('美国','加利福尼亚'); 
insert into tree1 values('大不列颠','英格兰'); 




使用START WITH . . . CONNECT BY . . .从句可将父级地区与子级地区连接起来，
并将其层次等级显示出来。 


QUOTE 
select level,lpad(' ',level*3)||child child from tree1 
start with parent is null connect by prior child = parent; 


返回结果： 


QUOTE 
rownum level            child




------   ---------        ------------ 
1 1    亚洲 
2 2       中国 
3 3          北京 
4 4             avicit 
5 2       小日本 
6 3          大阪 
7 3          东京 
8 1    澳大利亚 
9 2       威尔士 
10 3          悉尼 
11 1    欧洲 
12 2       大不列颠 
13 3          英格兰 
14 4             伦敦 
15 1    北美洲 
16 2       加拿大 
17 3          安大略地区 
18 4             渥太华 
19 4             多伦多 
20 2       美国 
21 3          加利福尼亚 
22 4             红海 


---------------------------------------------------------------- 




Oracle 9i 开始，可以通过 SYS_CONNECT_BY_PATH 函数实现将从父
节点到当前行内容以“path”或者层次元素列表的形式显示出来。 如下例所示： 


QUOTE 
select level,sys_connect_by_path(child,'/') path from tree1 
start with parent is null connect by prior child = parent; 




返回结果： 




QUOTE 
rownum         level           path 
---          ------          -------- 
1 1 /亚洲 
2 2 /亚洲/中国 
3 3 /亚洲/中国/北京 
4 4 /亚洲/中国/北京/avicit 
5 2 /亚洲/小日本 
6 3 /亚洲/小日本/大阪 
7 3 /亚洲/小日本/东京 
8 1 /澳大利亚 
9 2 /澳大利亚/威尔士 
10 3 /澳大利亚/威尔士/悉尼 
11 1 /欧洲 
12 2 /欧洲/大不列颠 
13 3 /欧洲/大不列颠/英格兰 
14 4 /欧洲/大不列颠/英格兰/伦敦 
15 1 /北美洲 
16 2 /北美洲/加拿大 
17 3 /北美洲/加拿大/安大略地区 
18 4 /北美洲/加拿大/安大略地区/渥太华 
19 4 /北美洲/加拿大/安大略地区/多伦多 
20 2 /北美洲/美国 
21 3 /北美洲/美国/加利福尼亚 
22 4 /北美洲/美国/加利福尼亚/红海 


-------------------------------------------------------------- 


Oracle 10g 中，还有其他更多关于层次查询的新特性 。例如，
有的时候用户更关心的是每个层次分支中等级最低（最底层）的内容
。那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是最底层。
如果是最底层就会在伪列中显示“1”，如果不是最底层而是一个分支
（例如当前内容是其他行的父亲）就显示“0”。用例： 


QUOTE 
select connect_by_isleaf,sys_connect_by_path(child,'/') path from tree1 
start with parent is null connect by prior child = parent; 




返回结果： 


QUOTE 
1 0 /亚洲 
2 0 /亚洲/中国 
3 0 /亚洲/中国/北京 
4 1 /亚洲/中国/北京/avicit 
5 0 /亚洲/小日本 
6 1 /亚洲/小日本/大阪 
7 1 /亚洲/小日本/东京 
8 0 /澳大利亚 
9 0 /澳大利亚/威尔士 
10 1 /澳大利亚/威尔士/悉尼 
11 0 /欧洲 
12 0 /欧洲/大不列颠 
13 0 /欧洲/大不列颠/英格兰 
14 1 /欧洲/大不列颠/英格兰/伦敦 
15 0 /北美洲 
16 0 /北美洲/加拿大 
17 0 /北美洲/加拿大/安大略地区 
18 1 /北美洲/加拿大/安大略地区/渥太华 
19 1 /北美洲/加拿大/安大略地区/多伦多 
20 0 /北美洲/美国 
21 0 /北美洲/美国/加利福尼亚 
22 1 /北美洲/美国/加利福尼亚/红海 


---------------------------------------------------------------- 


********重点推荐********* 


Oracle 10g 中的新特性——CONNECT_BY_ROOT。 它用在列名之前用于返回当前层的根节点。
如下面的例子，我可以显示出层次结构表中当前行数据所对应的最高等级节点的内
容 and 完整的层次路径。 
SQL： 


QUOTE 
select connect_by_root child,sys_connect_by_path(child,'/') path from tree1 
start with parent is null connect by prior child = parent 




返回结果： 


QUOTE 
rownum    CONNECT_BY_ROOTCHILD PATH 
1 亚洲 /亚洲 
2 亚洲 /亚洲/中国 
3 亚洲 /亚洲/中国/北京 
4 亚洲 /亚洲/中国/北京/avicit 
5 亚洲 /亚洲/小日本 
6 亚洲 /亚洲/小日本/大阪 
7 亚洲 /亚洲/小日本/东京 
8 澳大利亚 /澳大利亚 
9 澳大利亚 /澳大利亚/威尔士 
10 澳大利亚 /澳大利亚/威尔士/悉尼 
11 欧洲 /欧洲 
12 欧洲 /欧洲/大不列颠 
13 欧洲 /欧洲/大不列颠/英格兰 
14 欧洲 /欧洲/大不列颠/英格兰/伦敦 
15 北美洲 /北美洲 
16 北美洲 /北美洲/加拿大 
17 北美洲 /北美洲/加拿大/安大略地区 
18 北美洲 /北美洲/加拿大/安大略地区/渥太华 
19 北美洲 /北美洲/加拿大/安大略地区/多伦多 
20 北美洲 /北美洲/美国 
21 北美洲 /北美洲/美国/加利福尼亚 
22 北美洲 /北美洲/美国/加利福尼亚/红海 


-------------------------------------------------------------- 
在Oracle 10g 之前的版本中，如果在你的树中出现了环状循环
（如一个孩子节点引用一个父亲节点），Oracle 就会报出一个错误提示：
“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就
无法执行查询操作。而在 Oracle 10g 中，只要指定“NOCYCLE”就可以进行任意的查询操作。
与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE， 如果在当前行中引用
了某个父亲节点的内容并在树中出现了循环，那么该行的伪列中就会显示“1”，
否则就显示“0”。而这个功能就可以帮助我们校验一直让我们头疼不已的BOM结构死
循环（大多由于数据导入形成的），用例： 
表结构： 


QUOTE 
create table tree2(parent number,child number); 
insert into hier2 values(null,1); 
insert into hier2 values(1,2); 
insert into hier2 values(2,3); 
insert into hier2 values(3,1); 


SQL： 


QUOTE 
select connect_by_iscycle,sys_connect_by_path(child,'/') pathfrom tree2 
start with parent is null connect by nocycle prior child = parent; 




RETURN： 


QUOTE 
CONNECT_BY_ISCYCLE PATH 
------------------ ------- 
0 /1 
0 /1/2 
1 /1/2/3 


遍历某个地区下所有地区（oracle通用,但效率不高）： 
SQL: 


select * from tree1 connect by prior child=parent start with parent='亚洲' ; 


RETURN: 


QUOTE 
    PARENT CHILD 
1 亚洲 中国 
2 中国 北京 
3 北京 avicit 
4 亚洲 小日本 
5 小日本 大阪 
6 小日本 东京 




继续添加中： 
实际项目中，我们通常是某一个版本下的某产品BOM结构
（例如2005版本、2006版本等等），各版本间粒度可能存在一定差别，
这时候我们取某版本下某产品的BOM结构是一定要注意先取出该版本下的
数据再connect by ，否则conect by 完了之后再where版本=“某版本”的话，
查出来的数据就是阶乘关系！ 
表结构： 
在tree1的表里再添加一个字段vernum 
正确sql： 


QUOTE 
select * from(select * from tree1 where vernum='2005版本') 
connect by prior child=parent start with parent='亚洲' 


容易写成的错误sql： 
select * from tree1 connect by prior child=parent start with parent='亚洲' 
where vernum='2005版本' 






小结：oracle10g中，新增的几个特性执行效率是很高的，
而且解决了一些以前让人头疼的问题，建议大家使用。 
本地简单的做过测试，遍历12万条数据，用10g的新增特性查询，
遍历时间=60.447second（用isql/plus的分页显示，
几乎感觉不出任何停顿）;以前用的方法遍历同样数据：
110.801second，效率几乎提升了一倍！
等咱们测试部门的压力测试出来之后再给大家更有说服力一点的数据。