ITPub博客

首页 > Linux操作系统 > Linux操作系统 > PIPE函数的例子

PIPE函数的例子

原创 Linux操作系统 作者:yangtingkun 时间:2008-07-23 19:12:18 0 删除 编辑

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

 

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/4227/viewspace-407213/ ,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1964
  • 访问量
    11085795