10g樹形查詢新特性CONNECT_BY_ISCYCLE的9i實現方式(四)

yangtingkun發表於2008-07-29

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

CONNECT_BY_ISCYCLE功能的最終實現。

10g樹形查詢新特性CONNECT_BY_ISCYCLE9i實現方式(一):http://yangtingkun.itpub.net/post/468/466977

10g樹形查詢新特性CONNECT_BY_ISCYCLE9i實現方式(二):http://yangtingkun.itpub.net/post/468/467154

10g樹形查詢新特性CONNECT_BY_ISCYCLE9i實現方式(三):http://yangtingkun.itpub.net/post/468/467249

 

 

上一篇文章中基本實現了CONNECT_BY_ISCYCLE的功能。注意前面幾篇文章都是提到了“基本實現”,這是由於當前訪問的表採用硬編碼方式。這種方式能解決上面的問題,但是這種方式沒有通用性,需要針對不同的查詢,手工編寫不同的函式來解決問題。

因此需要將實現方式改為動態SQL方式,這樣不但保證了語句的通用性,而且可以實現不同方向的星型查詢。

先構造測試的例子:

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的形式來實現前一篇SQL的功能:

SQL> CREATE OR REPLACE FUNCTION F_FIND_CHILD
  2  (
  3   P_VALUE VARCHAR2,
  4   P_FATHER_COL VARCHAR2,
  5   P_CHILD_COL VARCHAR2,
  6   P_PK VARCHAR2,
  7   P_TABLE_NAME VARCHAR2
  8  ) RETURN VARCHAR2 AS
  9   V_STR_ID VARCHAR2(32767);
 10   V_STR_PK VARCHAR2(32767);
 11  
 12   PROCEDURE P_GET_CHILD_STR (P_FATHER IN VARCHAR2,
 13    P_STR_ID IN OUT VARCHAR2,
 14    P_STR_PK IN OUT VARCHAR2) AS
 15    TYPE T_TAB_STR IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
 16    V_TAB_PK T_TAB_STR;
 17    V_TAB_CHILD T_TAB_STR;
 18    V_TAB_FATHER T_TAB_STR;
 19   BEGIN
 20    EXECUTE IMMEDIATE 'SELECT ' || P_PK || ', ' || P_CHILD_COL || ', ' || P_FATHER_COL
 21     || ' FROM ' || P_TABLE_NAME || ' WHERE ' || P_FATHER_COL || ' = ''' || P_FATHER
 22     || ''' ORDER BY DECODE(' || P_FATHER_COL || ', ' || P_CHILD_COL || ', 0, 1)'
 23     BULK COLLECT INTO V_TAB_PK, V_TAB_CHILD, V_TAB_FATHER;
 24    FOR I IN 1..V_TAB_PK.COUNT LOOP
 25     IF INSTR(P_STR_ID || '/', '/' || V_TAB_CHILD(I) || '/') > 0 THEN
 26      P_STR_PK := P_STR_PK || '.1';
 27     ELSE
 28      P_STR_ID  := P_STR_ID || '/' || V_TAB_CHILD(I);
 29      P_STR_PK  := P_STR_PK || '/'
 30       || CASE WHEN V_TAB_CHILD(I) = V_TAB_FATHER(I) THEN '.1' END || V_TAB_PK(I);
 31      P_GET_CHILD_STR(V_TAB_CHILD(I), P_STR_ID, P_STR_PK);
 32     END IF;
 33    END LOOP;
 34   END;
 35  BEGIN
 36   EXECUTE IMMEDIATE 'SELECT ''/'' || ' || P_PK || ', ''/'' || ' || P_CHILD_COL 
 37    || ' FROM ' || P_TABLE_NAME || ' WHERE ' || P_PK || ' = :VALUE'
 38    INTO V_STR_PK, V_STR_ID USING P_VALUE;
 39    P_GET_CHILD_STR(LTRIM(V_STR_ID, '/'), V_STR_ID, V_STR_PK); 
 40   RETURN V_STR_PK;
 41  END;
 42  /

函式已建立。

下面檢查一下這個函式是否等價於Oracle提供的CONNECT BY NOCYCLE模式下的CONNECT_BY_ISCYCLE

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

        PK         ID  FATHER_ID NAME                               CYCLED
---------- ---------- ---------- ------------------------------ ----------
         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

已選擇7行。

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

        PK         ID  FATHER_ID NAME                               CYCLED
---------- ---------- ---------- ------------------------------ ----------
         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

已選擇7行。

採用了這種方式,對於葉節點到根節點的查詢同樣是支援的:

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

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

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

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

至此,終於實現了9i方式下CONNECT BY NOCYCLE查詢方式下的CONNECT_BY_ISCYCLE偽列的功能。

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/4227/viewspace-413532/,如需轉載,請註明出處,否則將追究法律責任。

相關文章