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_TREEITPUB个人空间W,\*N mo `$w4T{X
  2  (
-n0bz0K#[D4K W*cA8f0  3   PK NUMBER PRIMARY KEY,
.b*Yd5i:p0  4   ID NUMBER,
(]:s4eL1Td_ }0  5   FATHER_ID NUMBER,ITPUB个人空间du7~ d$t0X"hNUC
  6   NAME VARCHAR2(30)ITPUB个人空间\$k3Wcd!y4|&C:x
  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
c%TE/Gf0---------- ---------- ---------- ------------------------------ITPUB个人空间7zC#K*lv]o#Ba
         1          1         0 AITPUB个人空间I ir tyu7U%z
         2          2          1 BC
W
t(yR(c3Vav0         3          3          1 DEITPUB个人空间nQP1A1@z|+U!A
         4          4          1 FGITPUB个人空间Cj+w6?%`Bxd$B1|
         5          5          2 HIJITPUB个人空间~:{2I-mH*[ K
         6          6          4 KLMITPUB个人空间,JE
H:x)X7r9p[
         7          7          6 NOPQITPUB个人空间Pe0r/U%yj[
         8          0          0 ROOT
q8n,P6L%C9D%PI:P6O0         9          4          7 FG

已选择9行。

下面看看是如何实现CONNECT BY NOCYCLE的：

SQL> CREATE OR REPLACE TYPE T_IN IS TABLE OF NUMBER;
r A\"VJ3m0  2  /

类型已创建。

SQL> CREATE OR REPLACE FUNCTION F_TO_T_IN (P_IN VARCHAR2) RETURN T_IN ASITPUB个人空间'u-HaBYzT
  2   V_RETURN T_IN DEFAULT T_IN();ITPUB个人空间B6j o.W/a hv
  3   V_IN VARCHAR2(32767);ITPUB个人空间7}${{4@
OP,j`
  4   V_COUNT NUMBER DEFAULT 0;ITPUB个人空间Gf#g%` _9p-N.s^ b-B
  5  BEGIN
Lofpk5fO0  6   V_IN := LTRIM(P_IN || '/', '/');
~k)o(rb~7Fmv0  7   WHILE(INSTR(V_IN, '/') > 0) LOOP
7f8[0IavQ|t0  8    V_RETURN.EXTEND;ITPUB个人空间)r$y4s$TS pe%O
  9    V_COUNT := V_COUNT + 1;
)A5G)P`V.Jh0 10    V_RETURN(V_COUNT) := SUBSTR(V_IN, 1, INSTR(V_IN, '/') - 1);
DYDeO$F#P0 11    V_IN := SUBSTR(V_IN, INSTR(V_IN, '/') + 1);
[YB-O1W ~8I;?i dg0 12   END LOOP;ITPUB个人空间c?X(cyE*q
 13   RETURN V_RETURN;
+X1[#y0s3\c}0 14  END;
:p,TLX,?#`Q0 15  /

函数已创建。

SQL> CREATE OR REPLACE FUNCTION F_FIND_CHILD(P_VALUE VARCHAR2) RETURN VARCHAR2 AS
6v0^^Tp0  2   V_STR_ID VARCHAR2(32767);
cb?6^nF0  3   V_STR_PK VARCHAR2(32767) := '/' || P_VALUE;ITPUB个人空间Fs0bVg5f
  4 
"d/WgZ` W0  5   PROCEDURE P_GET_CHILD_STR (P_FATHER IN VARCHAR2,ITPUB个人空间6rasY*MA
  6    P_STR_ID IN OUT VARCHAR2,ITPUB个人空间5rc3qT{6l.x7K tF!T:@
  7    P_STR_PK IN OUT VARCHAR2) AS
LCT2b:WG?-O0  8   BEGIN
9i`MkVC5n0  9    FOR I IN (SELECT PK, ID FROM T_TREE WHERE FATHER_ID = P_FATHER) LOOP
X%u-MEoz0 10     IF INSTR(P_STR_ID || '/', '/' || I.ID || '/') = 0 THENITPUB个人空间h(T8`5Gq P4R+J
 11      P_STR_ID  := P_STR_ID || '/' || I.ID;
UW.JCk/a Qm
k0 12      P_STR_PK  := P_STR_PK || '/' || I.PK;ITPUB个人空间8~2nCxN;q&B7cg6Xz
 13      P_GET_CHILD_STR(I.ID, P_STR_ID, P_STR_PK);
)B|
^K*t(L;d:L8K `0 14     END IF;
(`M7vQ'f%p0 15    END LOOP;
-^PXV3L0|(L|0 16   END;
^|*G.D4g m0 17  BEGIN
3Mt%U3P{&X#F6Dv!i0 18   FOR I IN (SELECT PK, ID FROM T_TREE WHERE PK = P_VALUE) LOOP
/t*h]&mL0 19    V_STR_ID := '/' || I.ID;ITPUB个人空间m/e!uW4V\*ETS
 20    P_GET_CHILD_STR(I.ID, V_STR_ID, V_STR_PK);
@&t^;q~0 21   END LOOP;
TY^2M%s/`C&I9h0 22   RETURN V_STR_PK;ITPUB个人空间\8l5D-y,E\~p#QWJ
 23  END;ITPUB个人空间ZsWi+e4`\R6@8`t
 24  /

函数已创建。

SQL> SELECT *ITPUB个人空间)p1gI&k$U d
  2  FROM T_TREEITPUB个人空间$Q2Hb3b8U3V
  3  START WITH PK = 1
R;Kdd/cKrR0  4  CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

        PK         ID  FATHER_ID NAMEITPUB个人空间 ~xFW8aqg`
---------- ---------- ---------- ---------------------ITPUB个人空间&R8O0[#Tm u'D
         1          1         0 A
h[s.R_dmj$If0         2          2          1 BC
6IgRX'` F,SSg@0         5          5          2 HIJITPUB个人空间JA,q|+?iTr8f
         3          3          1 DE
1fN{-Q-Td1}0         4          4          1 FGITPUB个人空间,r2B,f#[I
Lj
         6          6          4 KLMITPUB个人空间5P/G3]YczG'}9RTH#yG
         7          7          6 NOPQ

已选择7行。

SQL> SELECT A.*
G/wzJ*`0  2  FROM T_TREE A,
n-c e D'O8C0  3  TABLE(F_TO_T_IN(F_FIND_CHILD(1))) BITPUB个人空间 C5v#JYV/y9G.T:V
  4  WHERE A.PK = B.COLUMN_VALUE;

        PK         ID  FATHER_ID NAMEITPUB个人空间9@5@_C8W
{6_xq
---------- ---------- ---------- ---------------------
$wjuM*C aC/o0         1          1         0 A
}%WS;f#mIojk(c0         2          2          1 BC
w3_ `b`6F0         5          5          2 HIJ
2~~'`jc0         3          3          1 DEITPUB个人空间K/nzG7Jl
         4          4          1 FGITPUB个人空间m,L)?1E2|:_f#G
         6          6          4 KLM
*Cl8JxK8maE0         7          7          6 NOPQ

已选择7行。

利用自定义的函数，可以在9i中模仿出CONNECT_BY_NOCYCLE的效果，那么现在问题来了，前面都是通过主键指定的记录，而对于树形查询，START WITH可以指定任意的条件，比如：

SQL> SELECT *ITPUB个人空间*Y{/q`5Y;ld
  2  FROM T_TREE
3]}B7YN0  3  START WITH ID = 4ITPUB个人空间$_c2g%U)Jj-Yv6J {~jb
  4  CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

        PK         ID  FATHER_ID NAMEITPUB个人空间 cq;L*P"|
---------- ---------- ---------- -------------------ITPUB个人空间+V$y-T9E2hI
         4          4          1 FGITPUB个人空间+D?G6}QlT
u
         6          6          4 KLM
#A9B+J_A Rk
Q&a0         7          7          6 NOPQ
*Une1Tv0         9          4          7 FG
0IV;}_/K r0         6          6          4 KLMITPUB个人空间?/tR6R*g)?
         7          7          6 NOPQ

已选择6行。

而对于通过PL/SQL函数实现的NOCYCLE而言，由于输入的条件是PK等于某个值，因此这里需要变化一下SQL的形式：

SQL> SELECT A.*ITPUB个人空间]|&Z'W)t7g+l!}
  2  FROM T_TREE A,ITPUB个人空间2AF;H2gT mh9^E
  3  TABLE(SELECT F_TO_T_IN(F_FIND_CHILD(PK)) FROM T_TREE WHERE ID = 4) BITPUB个人空间MU@c N3N:RC#X7p T
  4  WHERE A.PK = B.COLUMN_VALUE;ITPUB个人空间J'j;X.K]so
TABLE(SELECT F_TO_T_IN(F_FIND_CHILD(PK)) FROM T_TREE WHERE ID = 4) B
h9nyS:y$|vfs0      *ITPUB个人空间{(X[2B`9U
第3行出现错误:ITPUB个人空间RB Tor
ORA-01427:单行子查询返回多个行

SQL> SELECT A.*ITPUB个人空间1ZhV4Lfz8}@rre0z
  2  FROM T_TREE A,
C!a.}|V5V*F{Mj0  3  TABLE(SELECT F_TO_T_IN FROM (TABLE(SELECT F_FIND_CHILD(PK) FROM T_TREE WHERE ID = 4))) B
$mNK:`&X.{"I0  4  WHERE A.PK = B.COLUMN_VALUE;
@5?-|)zi*Q7Z2ai0TABLE(SELECT F_TO_T_IN FROM (TABLE(SELECT F_FIND_CHILD(PK) FROM T_TREE WHERE ID = 4))) B
C8|.Y&G G-JW0                             *ITPUB个人空间5jUT|J1M"ZXTI5LA
第3行出现错误:ITPUB个人空间2gf3{(P)OA J q
ORA-22905:无法从非嵌套表项访问行

这里普通的函数无法同时处理多条记录，因此这里就是使用PIPE函数的最佳时机。由于PIPE函数的输入参数为CURSOR，所以先定义一个包：

SQL> CREATE OR REPLACE PACKAGE PK_TREE ASITPUB个人空间uK4{cBC\
  2   TYPE T_RECORD IS RECORD (PK NUMBER);
O#L#BEg}-W2j
v0  3   TYPE T_CURSOR IS REF CURSOR RETURN T_RECORD;ITPUB个人空间kIsd*x9X)n.Y
  4  END;ITPUB个人空间#]+{j"Kz}1q
  5  /

程序包已创建。

SQL> CREATE OR REPLACE FUNCTION F_PIPED_TREE (P_CUR PK_TREE.T_CURSOR)ITPUB个人空间+QG\b_
  2  RETURN T_IN PIPELINED ASITPUB个人空间/[l7o
{!H%Ov
  3   V_PK NUMBER;
Y*i9SPx2?)lV:K0  4   V_IN T_IN;ITPUB个人空间 ]-h0{Bo%Z
  5  BEGIN
_[8gp9Xr3X3s0  6   LOOPITPUB个人空间&PAy-^/Q0{zs
  7    FETCH P_CUR INTO V_PK;
@F_H`3q0  8    EXIT WHEN P_CUR%NOTFOUND;
u'o"k Twk0@ jz+mDnO0  9     V_IN := F_TO_T_IN(F_FIND_CHILD(V_PK));
3Mvsl.?T P+?%q0 10     FOR I IN 1..V_IN.COUNT LOOPITPUB个人空间e0C'Q4m7u#P*bb
 11      PIPE ROW (V_IN(I));ITPUB个人空间iLiHI
 12     END LOOP;
c;M3q-Tu3?\ h!`0 13   END LOOP;ITPUB个人空间a/L3W
D!x4V}#o%@&m
 14   CLOSE P_CUR;ITPUB个人空间@Ae5y9zv*p
 15   RETURN;ITPUB个人空间"K%zh[s ~l
 16  END;
z~!]p.wPdm0 17  /

函数已创建。

上面就创建了一个PIPE函数，这个函数将一个查询作为输入，依次处理查询中的每条记录，并将结果放到一个嵌套表中，最后返回给用户。

有了这个函数，上面的SQL就可以等价的写成：

SQL> SELECT A.*ITPUB个人空间*[Yjf%z D{n
  2  FROM T_TREE A,ITPUB个人空间_U3u&e7lhd[
  3  TABLE(F_PIPED_TREE(CURSOR(SELECT PKFROM T_TREE WHERE ID = 4))) B
]%Us"_1o*ebr9Yc0  4  WHERE A.PK = B.COLUMN_VALUE;

        PK         ID  FATHER_ID NAMEITPUB个人空间-VH,F}/A6~9D
---------- ---------- ---------- ------------------------------
'qa!yIazwK A+zL3c @0         4          4          1 FG
DBm%E
ZJbD\0e0         6          6          4 KLMITPUB个人空间-y a:^5@@8gXS I o
         7          7          6 NOPQITPUB个人空间ZS0SD1H
         9          4          7 FGITPUB个人空间S-s2xx&y
         6          6          4 KLMITPUB个人空间jn-Gkx+~
         7          7          6 NOPQ

已选择6行。

感觉PIPE函数还是很有用的，只是由于不了解而使得这个功能很少被用到。