ORACLE CONNECT_BY_ROOT

在10g中 Oracle提供了新的操作： CONNNECT_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 ---------- ---------- ------------------------------ 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还是可以实现这个功能的。