10g樹形查詢新特性CONNECT_BY_ISLEAF的9i實現方式

在10g中Oracle提供了新的偽列：CONNECT_BY_ISLEAF，透過這個偽列，可以判斷當前的記錄是否是樹的葉節點。

這裡描述一下在9i中如何實現相應的功能。

 

 

首先構造一個例子：

SQL> CREATE TABLE T_TREE (ID NUMBER PRIMARY KEY, FATHER_ID NUMBER, NAME VARCHAR2(30));

表已建立。

SQL> INSERT INTO T_TREE VALUES (1, 0, 'A');

已建立 1 行。

SQL> INSERT INTO T_TREE VALUES (2, 1, 'BC');

已建立 1 行。

SQL> INSERT INTO T_TREE VALUES (3, 1, 'DE');

已建立 1 行。

SQL> INSERT INTO T_TREE VALUES (4, 1, 'FG');

已建立 1 行。

SQL> INSERT INTO T_TREE VALUES (5, 2, 'HIJ');

已建立 1 行。

SQL> INSERT INTO T_TREE VALUES (6, 4, 'KLM');

已建立 1 行。

SQL> INSERT INTO T_TREE VALUES (7, 6, 'NOPQ');

已建立 1 行。

SQL> COMMIT;

提交完成。

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行。

下面看看CONNECT_BY_ISLEAF的功能：

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行。

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可以判斷當前記錄是否是樹的葉節點。而這個功能在9i中沒有簡單的方法來實現，只能透過分析函式來進行判斷：

SQL> SELECT
  2   ID,
  3   FATHER_ID,
  4   NAME,
  5   CASE WHEN LEAD(LEVELS) OVER(ORDER BY RN) > LEVELS THEN 0 ELSE 1 END LEAF
  6  FROM
  7  (
  8   SELECT ROWNUM RN, ID, FATHER_ID, NAME, LEVEL LEVELS
  9   FROM T_TREE
 10   START WITH FATHER_ID = 0
 11   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行。

SQL> SELECT
  2   ID,
  3   FATHER_ID,
  4   NAME,
  5   CASE WHEN LEAD(LEVELS) OVER(ORDER BY RN) > LEVELS THEN 0 ELSE 1 END LEAF
  6  FROM
  7  (
  8   SELECT ROWNUM RN, ID, FATHER_ID, NAME, LEVEL LEVELS
  9   FROM T_TREE
 10   START WITH ID = 7
 11   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

利用分析函式可以相對簡單的在9i實現CONNECT_BY_ISLEAF偽列的功能。