Oracle11gr2新增遞迴WITH語句（二）

在11.2中，WITH語句得到進一步的增強，可以支援遞迴的呼叫。

這一篇簡單介紹WITH語句的遞迴呼叫的SEARCH語句。

Oracle11gr2新增遞迴WITH語句（一）：http://yangtingkun.itpub.net/post/468/496038

 

 

Oracle提供的WITH遞迴併不僅僅是樹形查詢的一個重複，事實上利用遞迴WITH語句可以提供更加靈活的樹形查詢功能。

下面簡單介紹一下遞迴WITH新增的SEARCH語句。

還是上一篇文章中的例子。

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> CREATE TABLE T_TREE (
  2  ID NUMBER PRIMARY KEY,
  3  FATHER_ID NUMBER,
  4  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> INSERT INTO T_TREE VALUES (8, 5, 'RSTU');

已建立 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
         8          5 RSTU

已選擇8行。

看一個使用SEARCH語句的例子：

SQL> WITH A (ID, FATHER_ID, NAME) AS
  2  (SELECT ID, FATHER_ID, NAME
  3  FROM T_TREE
  4  WHERE ID = 1
  5  UNION ALL
  6  SELECT B.ID, B.FATHER_ID, B.NAME
  7  FROM A, T_TREE B
  8  WHERE A.ID = B.FATHER_ID)
  9  SEARCH DEPTH FIRST BY NAME SET NAME_ORDER
 10  SELECT * FROM A
 11  ORDER BY NAME_ORDER;

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

已選擇8行。

SQL> WITH A (ID, FATHER_ID, NAME) AS
  2  (SELECT ID, FATHER_ID, NAME
  3  FROM T_TREE
  4  WHERE ID = 1
  5  UNION ALL
  6  SELECT B.ID, B.FATHER_ID, B.NAME
  7  FROM A, T_TREE B
  8  WHERE A.ID = B.FATHER_ID)
  9  SEARCH BREADTH FIRST BY NAME SET NAME_ORDER
 10  SELECT * FROM A
 11  ORDER BY NAME_ORDER;

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

已選擇8行。

透過使用SEARCH語句，構造了一個新列，這個新列可以根據樹形查詢的深度返回記錄，也可以根據樹形查詢的廣度返回結果。