oracle 数据库函数 返回数据集合

关于oracle的plsql Pipelined pipe row的用法(转) 为了让 PL/SQL 函数返回数据的多个行，必须通过返回一个 REF CURSOR 或一个数据集合来完成。REF CURSOR 的这种情况局限于可以从查询中选择的数据，而整个集合在可以返回前，必须进行具体化。Oracle 9i 通过引入的管道化表函数纠正了后一种情况。表函数是返回整个行的集（通常作为一个集合）的函数，可以直接从 SQL 语句中进行查询，就好像它是一个真正的数据库表一样。管道化表函数与之相似，但是它像在构建时一样返回数据，而不是一次全部返回。管道化表函数更加有效，因为数据可以尽可能快地返回。
管道化表函数必须返回一个集合。在函数中，PIPE ROW 语句被用来返回该集合的单个元素，该函数必须以一个空的 RETURN 语句结束，以表明它已经完成。一旦我们创建了上述函数，我们就可以使用 TABLE 操作符从 SQL 查询中调用它。
管道化表函数经常被用来把数据从一种类型转化成另一种类型。
下面是用 Pipelined Table 实现 split 函数的例子：
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);
CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN ty_str_split PIPELINED
IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2 (4000);
BEGIN
len := LENGTH (p_str);
len1 := LENGTH (p_delimiter);
WHILE j < len
LOOP
j := INSTR (p_str, p_delimiter, i);
IF j = 0
THEN
j := len;
str := SUBSTR (p_str, i);
PIPE ROW (str);
IF i >= len
THEN
EXIT;
END IF;
ELSE
str := SUBSTR (p_str, i, j - i);
i := j + len1;
PIPE ROW (str);
END IF;
END LOOP;
RETURN;
END fn_split;
/
测试：SELECT * FROM TABLE (fn_split ('1;;12;;123;;1234;;12345', ';;'));
结果：
1
12
123
1234
12345
又一个简单的例子：CREATE TYPE mytype AS OBJECT (
field1 NUMBER,
field2 VARCHAR2 (50)
);
CREATE TYPE mytypelist AS TABLE OF mytype;
CREATE OR REPLACE FUNCTION pipelineme
RETURN mytypelist PIPELINEDIS
v_mytype mytype;
BEGIN
FOR v_count IN 1 .. 20
LOOP
v_mytype := mytype (v_count, 'Row ' || v_count);
PIPE ROW (v_mytype); END LOOP;
RETURN;
END pipelineme;
SELECT * FROM TABLE (pipelineme);
FIELD1 FIELD2
------ ------------------------
1 Row 1
2 Row 2
3 Row 3
4 Row 4
5 Row 5
6 Row 6
7 Row 7
8 Row 8
9 Row 9
10 Row 10
11 Row 11
12 Row 12
13 Row 13
14 Row 14
15 Row 15
16 Row 16
17 Row 17
18 Row 18
19 Row 19
20 Row 20

评论

1 楼 thomas01042003 2012-02-23  

Oracle函数返回Table集合

   在实际的应用中，为了让PL/SQL 函数返回数据的多个行，必须通过返回一个 REF CURSOR 或一个数据集合来完成。REF CURSOR 的这种情况局限于可以从查询中选择的数据，而整个集合在可以返回前，必须进行具体化。 9i 通过引入的管道化表函数纠正了后一种情况。表函数是返回整个行的集（通常作为一个集合）的函数，可以直接从 SQL 语句中进行查询，就好像它是一个真正的数据库表一样。管道化表函数与之相似，但是它像在构建时一样返回数据，而不是一次全部返回。管道化表函数更加有效，因为数据可以尽可能快地返回。

    管道化表函数必须返回一个集合。在函数中，PIPE ROW 语句被用来返回该集合的单个元素，该函数必须以一个空的 RETURN 语句结束，以表明它已经完成。一旦我们创建了上述函数，我们就可以使用 TABLE 操作符从 SQL 查询中调用它。



1.使用自定义类型

/* Formatted on 2010/02/26 08:42 (Formatter Plus v4.8.8) */

CREATE OR REPLACE TYPE objemp AS OBJECT (

   maxsal   NUMBER,

   minsal   NUMBER

);





/* Formatted on 2010/02/26 08:43 (Formatter Plus v4.8.8) */

CREATE OR REPLACE TYPE tabemp AS TABLE OF objemp;



2.使用Pipeline管道函数和Pipe row()

CREATE OR REPLACE FUNCTION getmaxminsalary (department NUMBER)

   RETURN tabemp PIPELINED

AS

   maximum_salary   NUMBER;

   minimum_salary   NUMBER;

   v_errorcode      NUMBER;

   v_errortext      VARCHAR2 (200);

   v                objemp;

BEGIN

   FOR myrow IN (SELECT MAX (sal) m_sal, MIN (sal) min_sal

                   FROM emp

                  WHERE deptno = departmnet)

   LOOP

      v := objemp(myrow.m_sal, myrow.min_sal);

      PIPE ROW (v);

   END LOOP;



   RETURN;

EXCEPTION

   WHEN OTHERS

   THEN

      v_errorcode := SQLCODE;

      v_errortext := SUBSTR (SQLERRM, 1, 200);



      INSERT INTO log_table

                  (code, MESSAGE, info

                  )

           VALUES (v_errorcode, v_errortext, 'getMaxMinSalary'

                  );

END;

3.使用Table操作符

SELECT * FROM TABLE(getMaxMinSalary(10));