10g樹形查詢新特性CONNECT_BY_ISCYCLE的9i實現方式（三）

在10g中Oracle提供了新的偽列：CONNECT_BY_ISCYCLE，透過這個偽列，可以判斷是否在樹形查詢的過程中構成了迴圈，這個偽列只是在CONNECT BY NOCYCLE方式下有效。

初步實現CONNECT_BY_ISCYCLE的功能。

10g樹形查詢新特性CONNECT_BY_ISCYCLE的9i實現方式（一）：http://yangtingkun.itpub.net/post/468/466977

10g樹形查詢新特性CONNECT_BY_ISCYCLE的9i實現方式（二）：http://yangtingkun.itpub.net/post/468/467154

 

 

上一篇文章中描述瞭如何在9i中實現CONNECT BY NOCYCLE，在這個基礎上，終於可以實現這個系列文章的最終目標：CONNECT_BY_ISCYCLE。

首先還是透過例子看看CONNECT_BY_ISCYCLE的功能：

SQL> CREATE TABLE T_TREE
  2  (
  3   PK NUMBER PRIMARY KEY,
  4   ID NUMBER,
  5   FATHER_ID NUMBER,
  6   NAME VARCHAR2(30)
  7  );

表已建立。

SQL> INSERT INTO T_TREE VALUES (1, 1, 0, 'A');

已建立 1 行。

SQL> INSERT INTO T_TREE VALUES (2, 2, 1, 'BC');

已建立 1 行。

SQL> INSERT INTO T_TREE VALUES (3, 3, 1, 'DE');

已建立 1 行。

SQL> INSERT INTO T_TREE VALUES (4, 4, 1, 'FG');

已建立 1 行。

SQL> INSERT INTO T_TREE VALUES (5, 5, 2, 'HIJ');

已建立 1 行。

SQL> INSERT INTO T_TREE VALUES (6, 6, 4, 'KLM');

已建立 1 行。

SQL> INSERT INTO T_TREE VALUES (7, 7, 6, 'NOPQ');

已建立 1 行。

SQL> INSERT INTO T_TREE VALUES (8, 0, 0, 'ROOT');

已建立 1 行。

SQL> INSERT INTO T_TREE VALUES (9, 4, 7, 'FG');

已建立 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT * FROM T_TREE;

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

已選擇9行。

SQL> SELECT A.*, CONNECT_BY_ISCYCLE CYCLED
  2  FROM T_TREE A
  3  START WITH PK = 8
  4  CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

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

已選擇8行。

SQL> SELECT A.*, CONNECT_BY_ISCYCLE CYCLED
  2  FROM T_TREE A
  3  START WITH PK = 4
  4  CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

        PK         ID  FATHER_ID NAME                               CYCLED
---------- ---------- ---------- ------------------------------ ----------
         4          4          1 FG                                      0
         6          6          4 KLM                                     0
         7          7          6 NOPQ                                    1

為了實現這個功能，還是隻能透過PL/SQL函式想辦法，因為只有在這個函式中，才能獲得哪條記錄出現迴圈。

實現的方法有很多種，比如透過單獨的函式來實現，或者將前面的T_IN改為一個RECORD，使用單獨的列來存放是否迴圈標識，不過這些方法都比較麻煩。最簡單的方法莫過於在當前的函式返回值上做文章。

這裡可以做的文章很多，比如當前的例子，由於PK都是整數，所以在出現迴圈的PK處新增了小數部分。

SQL> CREATE OR REPLACE FUNCTION F_FIND_CHILD(P_VALUE VARCHAR2) RETURN VARCHAR2 AS
  2   V_STR_ID VARCHAR2(32767);
  3   V_STR_PK VARCHAR2(32767);
  4  
  5   PROCEDURE P_GET_CHILD_STR (P_FATHER IN VARCHAR2,
  6    P_STR_ID IN OUT VARCHAR2,
  7    P_STR_PK IN OUT VARCHAR2) AS
  8   BEGIN
  9    FOR I IN (SELECT PK, ID, FATHER_ID FROM T_TREE WHERE FATHER_ID = P_FATHER
 10     ORDER BY DECODE(ID, FATHER_ID, 0, 1)) LOOP
 11     IF INSTR(P_STR_ID || '/', '/' || I.ID || '/') > 0 THEN
 12      P_STR_PK := P_STR_PK || '.1';
 13     ELSE
 14      P_STR_ID  := P_STR_ID || '/' || I.ID;
 15      P_STR_PK  := P_STR_PK || '/' || CASE WHEN I.ID = I.FATHER_ID THEN '.1' END || I.PK;
 16      P_GET_CHILD_STR(I.ID, P_STR_ID, P_STR_PK);
 17     END IF;
 18    END LOOP;
 19   END;
 20  BEGIN
 21   FOR I IN (SELECT PK, ID, FATHER_ID FROM T_TREE WHERE PK = P_VALUE) LOOP
 22    V_STR_ID := '/' || I.ID;
 23    V_STR_PK := '/' || I.PK;
 24    P_GET_CHILD_STR(I.ID, V_STR_ID, V_STR_PK); 
 25   END LOOP;
 26   RETURN V_STR_PK;
 27  END;
 28  /

函式已建立。

SQL> SELECT F_FIND_CHILD(8) FROM DUAL;

F_FIND_CHILD(8)
--------------------------------------------------------------------------------
/8.1/1/2/5/4/6/7.1/3

下面只需要修改一下查詢的SQL語句：

SQL> SELECT A.*, DECODE(B.COLUMN_VALUE, A.PK, 0, 1) CYCLED
  2  FROM T_TREE A,
  3  TABLE(F_TO_T_IN(F_FIND_CHILD(8))) B
  4  WHERE A.PK = TRUNC(B.COLUMN_VALUE);

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

已選擇8行。

SQL> SELECT A.*, DECODE(B.COLUMN_VALUE, A.PK, 0, 1) CYCLED
  2  FROM T_TREE A,
  3  TABLE(F_TO_T_IN(F_FIND_CHILD(4))) B
  4  WHERE A.PK = TRUNC(B.COLUMN_VALUE);

        PK         ID  FATHER_ID NAME                               CYCLED
---------- ---------- ---------- ------------------------------ ----------
         4          4          1 FG                                      0
         6          6          4 KLM                                     0
         7          7          6 NOPQ                                    1

類似的技巧很多，比如將迴圈PK的返回值設為負數，或者在PK後面新增一個固定的標識等等。