linux pipe函数功能,PIPE函数的例子

很早之前，大概2、3年以前，就看到过PIPE函数，当时由于没有什么具体的需求需要使用这种函数，所以就没有深究。

同样是由于没有具体的需求，也就没有写过关于这种函数的例子。因为我认为为了PIPE函数而写PIPE函数的意义不大，而当时又没有找到除了PIPE函数而使用其他方法解决不了的案例，所以一直没有描述过这种函数。

这两天在研究树形查询，自己通过PL/SQL实现CONNECT BY NOCYCLE的时候，意外的碰到了一种情况，感觉使用PIPE函数是最佳解决方法，于是有了这篇文章。

由于这篇文章的例子来源于下面两篇文章，所以单独看这篇文章可能无法理解文章中例子的含义，建议先阅读下面两个链接，然后再看这篇文章。

10g新增了一些树形查询的伪列和操作，使得树形查询的功能更加强大。而9i中由于没有对应的功能，可能会导致很多问题很难实现。于是写了几篇文章描述10g中的一些功能如何在9i中实现。

其中最难实现的功能莫过于CONNECT BY NOCYCLE了。由于必须自己来实现树形查询的功能，因此选择通过PL/SQL编写函数，通过递归调用的方式实现CONNECT BY NOCYCLE的功能。又因为PL/SQL获取的是一个ID的列表，因此还需要将其转为为表函数关联原表来获取详细的记录。

一个简单的例子如下：

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函数还是很有用的，只是由于不了解而使得这个功能很少被用到。