在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还是可以实现这个功能的。