10g树形查询新特性CONNECT_BY_ROOT的9i实现方式

在10g中Oracle提供了新的操作：CONNECT_BY_ROOT，通过这个操作，可以获取树形查询根记录的字段。

这里描述一下在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
@FjrQ,b x/U0---------- ---------- ------------------------------ITPUB个人空间 A*G7Ii CLv rC"Q_
         1         0 A
#M8w
U5c1J Hv)N:b4F0         2          1 BC
5O/}nHhU4E*F0         3          1 DEITPUB个人空间"G}4o;c2d6IB(nlZ
         4          1 FG
@h;Q0G0mp(|]0         5          2 HIJITPUB个人空间1e0Gc L!mf\E)q
         6          4 KLMITPUB个人空间4j5p1yu^7} f
         7          6 NOPQ

已选择7行。

下面看看CONNECT_BY_ROOT的功能：

SQL> SELECT ID, FATHER_ID, NAME, CONNECT_BY_ROOT(NAME) ROOT_NAMEITPUB个人空间*V(^R5NNkf
  2  FROM T_TREE
$ru|h7}?WX-l6X8D0  3  START WITH FATHER_ID = 0ITPUB个人空间 s-M8H(j5dV1cW
  4  CONNECT BY PRIOR ID = FATHER_ID;

        ID  FATHER_ID NAME       ROOT_NAME
!]?T W,^D0---------- ---------- ---------- ----------
i }wZY;r0         1         0 A          A
YlOhQ!Ig:@0         2          1 BC         A
"X d*}/^X6Q0         5          2 HIJ        A
2eQQi4z0         3          1 DE         AITPUB个人空间*n{T ?6Lit
         4          1 FG         AITPUB个人空间+~ wk~-Z\
         6          4 KLM        A
1[j,lE}w7v'J:Gu Li&p0         7          6 NOPQ       A

已选择7行。

SQL> SELECT ID, FATHER_ID, NAME,
L]1s@ G5a0  2  CONNECT_BY_ROOT(NAME) ROOT_NAME, CONNECT_BY_ROOT(ID) ROOT_ID
.n$pQHb#jqr
E)f0  3  FROM T_TREE
3z
`w`R6i~ nY0  4  START WITH ID = 7ITPUB个人空间b~ Vy&r+~s
  5  CONNECT BY PRIOR FATHER_ID = ID;

        ID  FATHER_ID NAME       ROOT_NAME     ROOT_ID
_ l2i.vW*W'V/LR'k0---------- ---------- ---------- ---------- ----------ITPUB个人空间Ph#|[
r!x\
         7          6 NOPQ       NOPQ                7
:t,AQ!v#K0         6          4 KLM        NOPQ                7ITPUB个人空间/rE$F`i
         4          1 FG         NOPQ                7ITPUB个人空间#I7c&CzaV
         1         0 A          NOPQ                7

CONNECT_BY_ROOT操作的功能就是获取根节点记录的字段信息。这个功能在9i当中可以利用SYS_CONNECT_BY_PATH来实现。

 SQL> SELECT ID,ITPUB个人空间ktXO"^$V(z"H
  2   FATHER_ID,ITPUB个人空间P.F`dQE V
  3   NAME,
_q,\}+`0  4   SUBSTR
5OyYrr5n ttD{0  5    (
U5yF8]A+O7s0  6     SYS_CONNECT_BY_PATH(NAME, '/') || '/',ITPUB个人空间K\W|6vE T|.?
  7     2,ITPUB个人空间Y9`*q#g5_qOF G4r+]Z
  8     INSTR(SYS_CONNECT_BY_PATH(NAME, '/') || '/', '/', 1, 2) - 2
/R~{`b;a8X+|:cG0  9    ) ROOT_NAME
&a1CujcEFe{/r0 10  FROM T_TREEITPUB个人空间wt2uV b^t[
 11  START WITH FATHER_ID = 0
*CC$}c a%r C]0 12  CONNECT BY PRIOR ID = FATHER_ID;

        ID  FATHER_ID NAME       ROOT_NAMEITPUB个人空间0`N
ih#My]
---------- ---------- ---------- ----------ITPUB个人空间mgl x:rrD'H
         1         0 A          AITPUB个人空间6Q @*TaOMC6@ cn
         2          1 BC         A
"\D
^Vga0         5          2 HIJ        AITPUB个人空间P,h0g:J+FP6]
         3          1 DE         AITPUB个人空间0G'UnbvW
         4          1 FG         AITPUB个人空间!DcSg*v{RobXq
         6          4 KLM        AITPUB个人空间q8x&KG3C z^-}.^;r
         7          6 NOPQ       A

已选择7行。

SQL> SELECT ID,ITPUB个人空间Qf5Me yZe@5K
  2   FATHER_ID,ITPUB个人空间1A:X/R }eo7{p n
  3   NAME,ITPUB个人空间i:I2C+|` igI
  4   SUBSTRITPUB个人空间c.^,|0X&WAY w
  5    (
#y~]dXe)z[0  6     SYS_CONNECT_BY_PATH(NAME, '/') || '/',ITPUB个人空间V7`M.]O.v d\
  7     2,
-@~+h"Fv @/H4IYO0  8     INSTR(SYS_CONNECT_BY_PATH(NAME, '/') || '/', '/', 1, 2) - 2ITPUB个人空间&a\@@5k1l1E1[U[
  9    ) ROOT_NAME,
:Y#k9q$D5WIk.I0 10   TO_NUMBER(SUBSTRITPUB个人空间dJ-g4N*h jN)R
 11    (ITPUB个人空间Y-} z7O9S2cIz0}c
 12     SYS_CONNECT_BY_PATH(ID, '/') || '/',
!G/ia-p(sP0 13     2,ITPUB个人空间y[5hS#jBlToC
 14     INSTR(SYS_CONNECT_BY_PATH(ID, '/') || '/', '/', 1, 2) - 2ITPUB个人空间:bZ:[Bt
 15    )) ROOT_IDITPUB个人空间6aCd W#pWA
 16  FROM T_TREEITPUB个人空间&~6J!H V$`d:d!f
 17  START WITH ID = 7ITPUB个人空间s]7TR
?1C
 18  CONNECT BY PRIOR FATHER_ID = ID;

        ID  FATHER_ID NAME       ROOT_NAME     ROOT_ID
k{0[,Y7_[n#K0---------- ---------- ---------- ---------- ----------
+P:z0PZ'I5a7z&Lv0         7          6 NOPQ       NOPQ                7
${'J aX8XHu0         6          4 KLM        NOPQ                7ITPUB个人空间q r)tXN'X
         4          1 FG         NOPQ                7
a&j{$A['`z:n n0         1         0 A          NOPQ                7

虽然9i中的实现比10g要麻烦一些，但是利用SYS_CONNECT_BY_PATH还是可以实现这个功能的。