10g tree Query New characteristic connect_by_isleaf 9i realization Way

Oracle provides a new pseudo-column in 10g: Connect_by_isleaf, which allows you to determine whether the current record is a leaf node of a tree.

Here is a description of how to implement the corresponding function in 9i.

First, construct an example:

Sql> CREATE TABLE t_tree (ID number PRIMARY KEY, father_id number, NAME VARCHAR2 (30));

Table has been created.

Sql> INSERT into T_tree VALUES (1, 0, ' A ');

1 lines have been created.

Sql> INSERT into T_tree VALUES (2, 1, ' BC ');

1 lines have been created.

Sql> INSERT into T_tree VALUES (3, 1, ' DE ');

1 lines have been created.

Sql> INSERT into T_tree VALUES (4, 1, ' FG ');

1 lines have been created.

Sql> INSERT into T_tree VALUES (5, 2, ' HIJ ');

1 lines have been created.

Sql> INSERT into T_tree VALUES (6, 4, ' KLM ');

1 lines have been created.

Sql> INSERT into T_tree VALUES (7, 6, ' NOPQ ');

1 lines have been created.

Sql> COMMIT;

Submit completed.

Sql> SELECT * from T_tree;

ID father_id NAME
---------- ---------- ------------------------------
1 0 A
2 1 BC
3 1 DE
4 1 FG
5 2 HIJ
6 4 KLM
7 6 NOPQ

7 rows have been selected.

Here's a look at the Connect_by_isleaf features:

Sql> SELECT ID, father_id, NAME, Connect_by_isleaf LEAF
2 from T_tree
3 START with father_id = 0
4 CONNECT by PRIOR ID = father_id;

ID father_id NAME LEAF
---------- ---------- ------------------------------ ----------
1 0 A 0
2 1 BC 0
5 2 HIJ 1
3 1 DE 1
4 1 FG 0
6 4 KLM 0
7 6 NOPQ 1

7 rows have been selected.

Sql> SELECT ID, father_id, NAME, Connect_by_isleaf LEAF
2 from T_tree
3 START with ID = 7
4 CONNECT by PRIOR father_id = ID;

ID father_id NAME LEAF
---------- ---------- ------------------------------ ----------
7 6 NOPQ 0
6 4 KLM 0
4 1 FG 0
1 0 A 1

Connect_by_isleaf can determine whether the current record is a leaf node of a tree. This feature does not have a simple method to implement in 9i, only through the analysis function to judge:

Sql> SELECT
2 ID,
3 father_id,
4 NAME,
5 Case as lead (levels) > Levels THEN 0 ELSE 1 End LEAF
6 from
7 (
8 SELECT rownum RN, ID, father_id, NAME, level levels
9 from T_tree
START with father_id = 0
One CONNECT by PRIOR ID = father_id
12);

ID father_id NAME LEAF
---------- ---------- ------------------------------ ----------
1 0 A 0
2 1 BC 0
5 2 HIJ 1
3 1 DE 1
4 1 FG 0
6 4 KLM 0
7 6 NOPQ 1

7 rows have been selected.

Sql> SELECT
2 ID,
3 father_id,
4 NAME,
5 Case as lead (levels) > Levels THEN 0 ELSE 1 End LEAF
6 from
7 (
8 SELECT rownum RN, ID, father_id, NAME, level levels
9 from T_tree
START with ID = 7
One CONNECT by PRIOR father_id = ID
12);

ID father_id NAME LEAF
---------- ---------- ------------------------------ ----------
7 6 NOPQ 0
6 4 KLM 0
4 1 FG 0
1 0 A 1

The function of connect_by_isleaf pseudo columns can be implemented relatively simply in 9i by using analytic functions.