Oracle管道函数(转)

http://www.blogjava.net/hill/articles/oraclepipelined.html(转)

 

Typical Pipelined Example

This are the typical steps to perform when using PL/SQL Table Functions:

The producer function must use the PIPELINED keyword in its declaration.
 The producer function must use an OUT parameter that is a record, corresponding to a row in the result set.
 Once each output record is completed, it is sent to the consumer function through the use of the PIPE ROW keyword.
 The producer function must end with a RETURN statement that does not specify any return value.
 The consumer function or SQL statement then must use the TABLE keyword to treat the resulting rows from the PIPELINE function like a regular table.

The first step is to define the format of the rows that are going to be returned. In this case here, we're going to return a INT, DATE followed by a VARCHAR2(25).

CREATE OR REPLACE TYPE myObjectFormat AS OBJECT( A INT, B DATE, C VARCHAR2(25))

 

 

 

Next a collection type for the type previously defined must be created.

CREATE OR REPLACE TYPEmyTableType
  AS TABLE OF myObjectFormat

 

 

 

 

Finally, the producer function is packaged in a package. It is a pipelined function as indicated by the keyword pipelined.

CREATE OR REPLACE PACKAGE myDemoPack
AS
      FUNCTION prodFunc RETURNmyTableTypePIPELINED;
END;

 

 

CREATE OR REPLACE PACKAGE BODY myDemoPack AS
FUNCTION prodFunc RETURNmyTableTypePIPELINED IS
BEGIN
  FOR i in 1 .. 5
    LOOP
      PIPE ROW (myObjectFormat(i,SYSDATE+i,'Row '||i));
    END LOOP;
    RETURN;
  END;
END;

 

Test It:

ALTER SESSION SET NLS_DATE_FORMAT='dd.mm.yyyy';
SELECT * FROM TABLE(myDemoPack.prodFunc());

         A B          C
---------- ---------- ---------
         1 31.05.2004 Row 1
         2 01.06.2004 Row 2
         3 02.06.2004 Row 3
         4 03.06.2004 Row 4
         5 04.06.2004 Row 5

Conclusion

Pipelined functions are useful if there is a need for a data source other than a table in a select statement.