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

在10g中Oracle提供了新的操作：CONNECT_BY_ROOT，透過這個操作，可以獲取樹形查詢根記錄的欄位。

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

[@more@]

首先還是構造一個例子：

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_ROOT的功能：

SQL> SELECT ID, FATHER_ID, NAME, CONNECT_BY_ROOT(NAME) ROOT_NAME
2 FROM T_TREE
3 START WITH FATHER_ID = 0
4 CONNECT BY PRIOR ID = FATHER_ID;

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

已選擇7行。

SQL> SELECT ID, FATHER_ID, NAME,
2 CONNECT_BY_ROOT(NAME) ROOT_NAME, CONNECT_BY_ROOT(ID) ROOT_ID
3 FROM T_TREE
4 START WITH ID = 7
5 CONNECT BY PRIOR FATHER_ID = ID;

ID FATHER_ID NAME ROOT_NAME ROOT_ID
---------- ---------- ---------- ---------- ----------
7 6 NOPQ NOPQ 7
6 4 KLM NOPQ 7
4 1 FG NOPQ 7
1 0 A NOPQ 7

CONNECT_BY_ROOT操作的功能就是獲取根節點記錄的欄位資訊。這個功能在9i當中可以利用SYS_CONNECT_BY_PATH來實現。

SQL> SELECT ID,
2 FATHER_ID,
3 NAME,
4 SUBSTR
5 (
6 SYS_CONNECT_BY_PATH(NAME, '/') || '/',
7 2,
8 INSTR(SYS_CONNECT_BY_PATH(NAME, '/') || '/', '/', 1, 2) - 2
9 ) ROOT_NAME
10 FROM T_TREE
11 START WITH FATHER_ID = 0
12 CONNECT BY PRIOR ID = FATHER_ID;

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

已選擇7行。

SQL> SELECT ID,
2 FATHER_ID,
3 NAME,
4 SUBSTR
5 (
6 SYS_CONNECT_BY_PATH(NAME, '/') || '/',
7 2,
8 INSTR(SYS_CONNECT_BY_PATH(NAME, '/') || '/', '/', 1, 2) - 2
9 ) ROOT_NAME,
10 TO_NUMBER(SUBSTR
11 (
12 SYS_CONNECT_BY_PATH(ID, '/') || '/',
13 2,
14 INSTR(SYS_CONNECT_BY_PATH(ID, '/') || '/', '/', 1, 2) - 2
15 )) ROOT_ID
16 FROM T_TREE
17 START WITH ID = 7
18 CONNECT BY PRIOR FATHER_ID = ID;

ID FATHER_ID NAME ROOT_NAME ROOT_ID
---------- ---------- ---------- ---------- ----------
7 6 NOPQ NOPQ 7
6 4 KLM NOPQ 7
4 1 FG NOPQ 7
1 0 A NOPQ 7

雖然9i中的實現比10g要麻煩一些，但是利用SYS_CONNECT_BY_PATH還是可以實現這個功能的。