oracle 多个 关键词,Pipelined 是Oracle的一个关键字

Pipelined是Oracle的一个关键字，这里举例说明一下(摘抄)其简单的用法。

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 TYPEmyTableTypeAS 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.

开心过好每一天。。。。。