PIPE函式的例子

很早之前，大概2、3年以前，就看到過PIPE函式，當時由於沒有什麼具體的需求需要使用這種函式，所以就沒有深究。

同樣是由於沒有具體的需求，也就沒有寫過關於這種函式的例子。因為我認為為了PIPE函式而寫PIPE函式的意義不大，而當時又沒有找到除了PIPE函式而使用其他方法解決不了的案例，所以一直沒有描述過這種函式。

這兩天在研究樹形查詢，自己透過PL/SQL實現CONNECT BY NOCYCLE的時候，意外的碰到了一種情況，感覺使用PIPE函式是最佳解決方法，於是有了這篇文章。

由於這篇文章的例子來源於下面兩篇文章，所以單獨看這篇文章可能無法理解文章中例子的含義，建議先閱讀下面兩個連結，然後再看這篇文章。

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

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

 

 

10g新增了一些樹形查詢的偽列和操作，使得樹形查詢的功能更加強大。而9i中由於沒有對應的功能，可能會導致很多問題很難實現。於是寫了幾篇文章描述10g中的一些功能如何在9i中實現。

其中最難實現的功能莫過於CONNECT BY NOCYCLE了。由於必須自己來實現樹形查詢的功能，因此選擇透過PL/SQL編寫函式，透過遞迴呼叫的方式實現CONNECT BY NOCYCLE的功能。又因為PL/SQL獲取的是一個ID的列表，因此還需要將其轉為為表函式關聯原表來獲取詳細的記錄。

將固定字元分隔的字串轉化為表的形式可以參考這篇文章：http://yangtingkun.itpub.net/post/468/455390

一個簡單的例子如下：

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行。

下面看看是如何實現CONNECT BY NOCYCLE的：

SQL> CREATE OR REPLACE TYPE T_IN IS TABLE OF NUMBER;
  2  /

型別已建立。

SQL> CREATE OR REPLACE FUNCTION F_TO_T_IN (P_IN VARCHAR2) RETURN T_IN AS
  2   V_RETURN T_IN DEFAULT T_IN();
  3   V_IN VARCHAR2(32767);
  4   V_COUNT NUMBER DEFAULT 0;
  5  BEGIN
  6   V_IN := LTRIM(P_IN || '/', '/');
  7   WHILE(INSTR(V_IN, '/') > 0) LOOP
  8    V_RETURN.EXTEND;
  9    V_COUNT := V_COUNT + 1;
 10    V_RETURN(V_COUNT) := SUBSTR(V_IN, 1, INSTR(V_IN, '/') - 1);
 11    V_IN := SUBSTR(V_IN, INSTR(V_IN, '/') + 1);
 12   END LOOP;
 13   RETURN V_RETURN;
 14  END;
 15  /

函式已建立。

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) := '/' || P_VALUE;
  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 FROM T_TREE WHERE FATHER_ID = P_FATHER) LOOP
 10     IF INSTR(P_STR_ID || '/', '/' || I.ID || '/') = 0 THEN
 11      P_STR_ID  := P_STR_ID || '/' || I.ID;
 12      P_STR_PK  := P_STR_PK || '/' || I.PK;
 13      P_GET_CHILD_STR(I.ID, P_STR_ID, P_STR_PK);
 14     END IF;
 15    END LOOP;
 16   END;
 17  BEGIN
 18   FOR I IN (SELECT PK, ID FROM T_TREE WHERE PK = P_VALUE) LOOP
 19    V_STR_ID := '/' || I.ID;
 20    P_GET_CHILD_STR(I.ID, V_STR_ID, V_STR_PK);
 21   END LOOP;
 22   RETURN V_STR_PK;
 23  END;
 24  /

函式已建立。

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

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

已選擇7行。

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

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

已選擇7行。

利用自定義的函式，可以在9i中模仿出CONNECT_BY_NOCYCLE的效果，那麼現在問題來了，前面都是透過主鍵指定的記錄，而對於樹形查詢，START WITH可以指定任意的條件，比如：

SQL> SELECT *
  2  FROM T_TREE
  3  START WITH ID = 4
  4  CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

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

已選擇6行。

而對於透過PL/SQL函式實現的NOCYCLE而言，由於輸入的條件是PK等於某個值，因此這裡需要變化一下SQL的形式：

SQL> SELECT A.*
  2  FROM T_TREE A,
  3  TABLE(SELECT F_TO_T_IN(F_FIND_CHILD(PK)) FROM T_TREE WHERE ID = 4) B
  4  WHERE A.PK = B.COLUMN_VALUE;
TABLE(SELECT F_TO_T_IN(F_FIND_CHILD(PK)) FROM T_TREE WHERE ID = 4) B
      *
第 3 行出現錯誤:
ORA-01427: 單行子查詢返回多個行

SQL> SELECT A.*
  2  FROM T_TREE A,
  3  TABLE(SELECT F_TO_T_IN FROM (TABLE(SELECT F_FIND_CHILD(PK) FROM T_TREE WHERE ID = 4))) B
  4  WHERE A.PK = B.COLUMN_VALUE;
TABLE(SELECT F_TO_T_IN FROM (TABLE(SELECT F_FIND_CHILD(PK) FROM T_TREE WHERE ID = 4))) B
                             *
第 3 行出現錯誤:
ORA-22905: 無法從非巢狀表項訪問行

這裡普通的函式無法同時處理多條記錄，因此這裡就是使用PIPE函式的最佳時機。由於PIPE函式的輸入引數為CURSOR，所以先定義一個包：

SQL> CREATE OR REPLACE PACKAGE PK_TREE AS
  2   TYPE T_RECORD IS RECORD (PK NUMBER);
  3   TYPE T_CURSOR IS REF CURSOR RETURN T_RECORD;
  4  END;
  5  /

程式包已建立。

SQL> CREATE OR REPLACE FUNCTION F_PIPED_TREE (P_CUR PK_TREE.T_CURSOR)
  2  RETURN T_IN PIPELINED AS
  3   V_PK NUMBER;
  4   V_IN T_IN;
  5  BEGIN
  6   LOOP
  7    FETCH P_CUR INTO V_PK;
  8    EXIT WHEN P_CUR%NOTFOUND;
  9     V_IN := F_TO_T_IN(F_FIND_CHILD(V_PK));
 10     FOR I IN 1..V_IN.COUNT LOOP
 11      PIPE ROW (V_IN(I));
 12     END LOOP;
 13   END LOOP;
 14   CLOSE P_CUR;
 15   RETURN;
 16  END;
 17  /

函式已建立。

上面就建立了一個PIPE函式，這個函式將一個查詢作為輸入，依次處理查詢中的每條記錄，並將結果放到一個巢狀表中，最後返回給使用者。

有了這個函式，上面的SQL就可以等價的寫成：

SQL> SELECT A.*
  2  FROM T_TREE A,
  3  TABLE(F_PIPED_TREE(CURSOR(SELECT PK FROM T_TREE WHERE ID = 4))) B
  4  WHERE A.PK = B.COLUMN_VALUE;

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

已選擇6行。

感覺PIPE函式還是很有用的，只是由於不瞭解而使得這個功能很少被用到。