oracle 9i判断是叶子或根节点，是比较麻烦的一件事情，SQL演示脚本如下:
view plaincopy to clipboardprint?

??? DROP TABLE idb_hierarchical;?
??? create TABLE idb_hierarchical?
??? (?
??? id number,?
??? parent_id number,?
??? str varchar2(10)?
??? );?
?????
??? insert into idb_hierarchical values(1,null,'A');?
??? insert into idb_hierarchical values(2,1,'B');?
??? insert into idb_hierarchical values(3,2,'C');?
??? insert into idb_hierarchical values(4,3,'D');?
??? insert into idb_hierarchical values(5,2,'E');?
??? insert into idb_hierarchical values(6,2,'F');?
??? insert into idb_hierarchical values(7,3,'G');?
??? insert into idb_hierarchical values(8,4,'H');?
??? insert into idb_hierarchical values(9,4,'I');?
??? insert into idb_hierarchical values(10,null,'J');?
??? insert into idb_hierarchical values(11,10,'K');?
??? insert into idb_hierarchical values(12,11,'L');?
??? insert into idb_hierarchical values(13,10,'M');?

[sql] view plaincopy

??? DROP TABLE idb_hierarchical;?
??? create TABLE idb_hierarchical?
??? (?
??? id number,?
??? parent_id number,?
??? str varchar2(10)?
??? );?
?????
??? insert into idb_hierarchical values(1,null,'A');?
??? insert into idb_hierarchical values(2,1,'B');?
??? insert into idb_hierarchical values(3,2,'C');?
??? insert into idb_hierarchical values(4,3,'D');?
??? insert into idb_hierarchical values(5,2,'E');?
??? insert into idb_hierarchical values(6,2,'F');?
??? insert into idb_hierarchical values(7,3,'G');?
??? insert into idb_hierarchical values(8,4,'H');?
??? insert into idb_hierarchical values(9,4,'I');?
??? insert into idb_hierarchical values(10,null,'J');?
??? insert into idb_hierarchical values(11,10,'K');?
??? insert into idb_hierarchical values(12,11,'L');?
??? insert into idb_hierarchical values(13,10,'M');?

示例数据清单如下:
view plaincopy to clipboardprint?

??? SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL?
????? FROM idb_hierarchical?
???? START WITH PARENT_ID IS NULL?
??? CONNECT BY PARENT_ID = PRIOR ID;?

[sql] view plaincopy

??? SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL?
????? FROM idb_hierarchical?
???? START WITH PARENT_ID IS NULL?
??? CONNECT BY PARENT_ID = PRIOR ID;?

表1:数据清单 STR_LEVEL ??? ID ??? PARENT_ID ??? LVL
+..A ??? 1 ??? ? ??? 1
+….B ??? 2 ??? 1 ??? 2
+……C ??? 3 ??? 2 ??? 3
+……..D ??? 4 ??? 3 ??? 4
+……….H ??? 8 ??? 4 ??? 5
+……….I ??? 9 ??? 4 ??? 5
+……..G ??? 7 ??? 3 ??? 4
+……E ??? 5 ??? 2 ??? 3
+……F ??? 6 ??? 2 ??? 3
+..J ??? 10 ??? ? ??? 1
+….K ??? 11 ??? 10 ??? 2
+……L ??? 12 ??? 11 ??? 3
+….M ??? 13 ??? 10 ??? 2

在表1中，ID为8、9、 7、5、6、12、13都没有子节点，因此称为叶节点。
1.oracle9i 查询叶节点

只显示叶子节点SQL
view plaincopy to clipboardprint?

??? SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL?
????? FROM idb_hierarchical I?
????? --在oracle 9i中显示叶节点，需要判断是否有子节点即可?
????? WHERE NOT EXISTS(SELECT 1?
????? FROM idb_hierarchical B?
????? WHERE I.ID=B.PARENT_ID)?
???? START WITH PARENT_ID IS NULL?
??? CONNECT BY PARENT_ID = PRIOR ID;?

[sql] view plaincopy

??? SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL?
????? FROM idb_hierarchical I?
????? --在oracle 9i中显示叶节点，需要判断是否有子节点即可?
????? WHERE NOT EXISTS(SELECT 1?
????? FROM idb_hierarchical B?
????? WHERE I.ID=B.PARENT_ID)?
???? START WITH PARENT_ID IS NULL?
??? CONNECT BY PARENT_ID = PRIOR ID;?

表2 STR_LEVEL ??? ID ??? PARENT_ID ??? LVL
+……….H ??? 8 ??? 4 ??? 5
+……….I ??? 9 ??? 4 ??? 5
+……..G ??? 7 ??? 3 ??? 4
+……E ??? 5 ??? 2 ??? 3
+……F ??? 6 ??? 2 ??? 3
+……L ??? 12 ??? 11 ?