在10g中Oracle提供了新的伪列:CONNECT_BY_ISLEAF,通过这个伪列,可以判断当前的记录是否是树的叶节点。
这里描述一下在9i中如何实现相应的功能.
首先构造一个例子:
select * from t_tree;
ID | FATHER_ID | NAME | |
1 | 1 | 0 | A |
2 | 2 | 1 | BC |
3 | 3 | 1 | DE |
4 | 4 | 1 | FG |
5 | 5 | 2 | HIJ |
6 | 6 | 4 | KLM |
7 | 7 | 6 | NOPQ |
看看CONNECT_BY_ISLEAF的功能:
ID | FATHER_ID | NAME | LEAF | |
1 | 1 | 0 | A | 0 |
2 | 2 | 1 | BC | 0 |
3 | 5 | 2 | HIJ | 1 |
4 | 3 | 1 | DE | 1 |
5 | 4 | 1 | FG | 0 |
6 | 6 | 4 | KLM | 0 |
7 | 7 | 6 | NOPQ | 1 |
ID | FATHER_ID | NAME | LEAF | |
1 | 7 | 6 | NOPQ | 0 |
2 | 6 | 4 | KLM | 0 |
3 | 4 | 1 | FG | 0 |
4 | 1 | 0 | A | 1 |
CONNECT_BY_ISLEAF可以判断当前记录是否是树的叶节点。而这个功能在9i中没有简单的方法来实现,只能通过分析函数来进行判断:
ID | FATHER_ID | NAME | LEAF | |
1 | 1 | 0 | A | 0 |
2 | 2 | 1 | BC | 0 |
3 | 5 | 2 | HIJ | 1 |
4 | 3 | 1 | DE | 1 |
5 | 4 | 1 | FG | 0 |
6 | 6 | 4 | KLM | 0 |
7 | 7 | 6 | NOPQ | 1 |
ID | FATHER_ID | NAME | LEAF | |
1 | 7 | 6 | NOPQ | 0 |
2 | 6 | 4 | KLM | 0 |
3 | 4 | 1 | FG | 0 |
4 | 1 | 0 | A | 1 |
利用分析函数可以相对简单的在9i实现CONNECT_BY_ISLEAF伪列的功能。