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

当不需要利用表来存储而仅仅是一个结果集的话就可以使用这个函数了。

问题的提出：itpub xxxzheng

表table 1

DATE
2007-01-01
2007-01-02
.........
2007-01-30

表table2
empId date
jc001 2007-01-01
jc001 2007-01-02
jc001 2007-01-03
jc001 2007-01-08
jc001 2007-01-20
jc002 2007-01-01
jc002 2007-01-02
jc002 2007-01-02
jc003 2007-01-15
jc002 2007-01-30
....................
我想的结果是连个表进行连接查询
得到
DATE ID
2007-01-01 JC001
......... NULL
2007-01-30 JC001
2007-01-01 JC002
.................
2007-01-30 JC002
2007-01-01 JC003
..................................
2007-01-30 JC003

就是说如果T2 中没这个日期则ID NULL
就是要一各整月数据，但T2中没有整月的数据，T1 是各 日期表丛2007-01-01 -　2007-01-30

准备测试数据
create table test_12 (id varchar2(10),d date);
insert into test_12 values ('jc001',date'2007-01-01');
insert into test_12 values ('jc001',date'2007-01-02');
insert into test_12 values ('jc001',date'2007-01-03');
insert into test_12 values ('jc001',date'2007-01-08');
insert into test_12 values ('jc001',date'2007-01-20');
insert into test_12 values ('jc002',date'2007-01-01');
insert into test_12 values ('jc002',date'2007-01-02');
insert into test_12 values ('jc002',date'2007-01-03');
insert into test_12 values ('jc003',date'2007-01-15');
insert into test_12 values ('jc003',date'2007-01-30');
commit;

表1没有使用，我利用了自动生成一个月的日期的语句，请参看《动态生成日期的经典sql》。

SQL> select to_char(to_date('2007-01-01','yyyy-mm-dd') + rownum - 1,'yyyy-mm-dd') ym from dual
2 connect by rownum <= to_char(last_day(to_date('2007-01-01','yyyy-mm-dd')),'dd')
3 /

YM
----------
2007-01-01
2007-01-02
2007-01-03
2007-01-04
2007-01-05
2007-01-06
2007-01-07
2007-01-08
2007-01-09
2007-01-10
2007-01-11
2007-01-12
2007-01-13
2007-01-14
2007-01-15
2007-01-16
2007-01-17
2007-01-18
2007-01-19
2007-01-20
2007-01-21
2007-01-22
2007-01-23
2007-01-24
2007-01-25
2007-01-26
2007-01-27
2007-01-28
2007-01-29
2007-01-30
2007-01-31

31 rows selected

下面要是实现上面的要求，需要利用这个动态生成的结果集与test_12做左连接就可以了。

SQL> select a.ym,b.id from (
2 select to_char(to_date('2007-01-01','yyyy-mm-dd') + rownum - 1,'yyyy-mm-dd') ym from dual
3 connect by rownum <= to_char(last_day(to_date('2007-01-01','yyyy-mm-dd')),'dd')) a,
4 (Select Id,to_char(d,'yyyy-mm-dd') ym From test_12 Where Id ='jc001') b
5 Where a.ym = b.ym(+)
6 /

YM ID
---------- ----------
2007-01-01 jc001
2007-01-02 jc001
2007-01-03 jc001
2007-01-04
2007-01-05
2007-01-06
2007-01-07
2007-01-08 jc001
2007-01-09
2007-01-10
2007-01-11
2007-01-12
2007-01-13
2007-01-14
2007-01-15
2007-01-16
2007-01-17
2007-01-18
2007-01-19
2007-01-20 jc001
2007-01-21
2007-01-22
2007-01-23
2007-01-24
2007-01-25
2007-01-26
2007-01-27
2007-01-28
2007-01-29
2007-01-30
2007-01-31

31 rows selected

这样只能一次查出对应一个empid的数据，那么如何取出所的呢？下面考虑使用函数实现．

CREATE OR REPLACE TYPE ym_object
AS OBJECT
(
d_date Varchar2(20),
empid VARCHAR2(20)
);

create or replace type ym_array as table of ym_object;

create or replace function f_gen(v_date in Varchar2)
Return ym_array Pipelined
Is
type t_test is table of Varchar2(20);
var_test1 t_test := t_test();
var_test2 t_test := t_test();

Cursor cur_result(v_date Varchar2,v_empid Varchar2) Is
select a.ym,b.id from (
select to_char(to_date(v_date,'yyyy-mm-dd') + rownum - 1,'yyyy-mm-dd') ym from dual
connect by rownum <= to_char(last_day(to_date(v_date,'yyyy-mm-dd')),'dd')) a,
(Select Id,to_char(d,'yyyy-mm-dd') ym From test_12 Where id = v_empid) b
Where a.ym = b.ym(+);
Begin
For rec In (Select Distinct id From test_12)
Loop
Open cur_result(v_date,rec.id);
fetch cur_result BULK COLLECT INTO var_test1,var_test2;
Close cur_result;
For i In 1..var_test1.Count() Loop
pipe row(ym_object(var_test1(i),var_test2(i)));
End Loop;
End Loop;
return;
end;

SQL> select * from test_12;

ID D
---------- -----------
jc001 2007-1-1
jc001 2007-1-2
jc001 2007-1-3
jc001 2007-1-8
jc001 2007-1-20
jc002 2007-1-1
jc002 2007-1-2
jc002 2007-1-3
jc003 2007-1-15
jc003 2007-1-30

10 rows selected

SQL> select * from table(f_gen('20070101'));

D_DATE EMPID
-------------------- --------------------
2007-01-01 jc001
2007-01-02 jc001
2007-01-03 jc001
2007-01-04
2007-01-05
2007-01-06
2007-01-07
2007-01-08 jc001
2007-01-09
2007-01-10
2007-01-11
2007-01-12
2007-01-13
2007-01-14
2007-01-15
2007-01-16
2007-01-17
2007-01-18
2007-01-19
2007-01-20 jc001
2007-01-21
2007-01-22
2007-01-23
2007-01-24
2007-01-25
2007-01-26
2007-01-27
2007-01-28
2007-01-29
2007-01-30
2007-01-31
--------------
2007-01-01 jc002
2007-01-02 jc002
2007-01-03 jc002
2007-01-04
2007-01-05
2007-01-06
2007-01-07
2007-01-08
2007-01-09
2007-01-10
2007-01-11
2007-01-12
2007-01-13
2007-01-14
2007-01-15
2007-01-16
2007-01-17
2007-01-18
2007-01-19
2007-01-20
2007-01-21
2007-01-22
2007-01-23
2007-01-24
2007-01-25
2007-01-26
2007-01-27
2007-01-28
2007-01-29
2007-01-30
2007-01-31
-----------------
2007-01-01
2007-01-02
2007-01-03
2007-01-04
2007-01-05
2007-01-06
2007-01-07
2007-01-08
2007-01-09
2007-01-10
2007-01-11
2007-01-12
2007-01-13
2007-01-14
2007-01-15 jc003
2007-01-16
2007-01-17
2007-01-18
2007-01-19
2007-01-20
2007-01-21
2007-01-22
2007-01-23
2007-01-24
2007-01-25
2007-01-26
2007-01-27
2007-01-28
2007-01-29
2007-01-30 jc003
2007-01-31

93 rows selected

以上，通过利用 pipelined Function 实现了上面所说的功能，至于效率如何就需要大家根据实际情况看看了．

下面是引用了zhouwf0726 的一篇转帖的内容，让大家参考下 Pipelined Function 的用法！

Oracle Pipelined Table FunctionsOverview

Basically, when you would like a PLSQL (or java or c) routine to be the ?source?
of data -- instead of a table -- you would use a pipelined function.

PIPELINED functions will operate like a table.

A PL/SQL function may be used in a data warehouse database to transform large amounts of data.
This might also involve massaging the data in a series of transformations,
each performed by different functions. Prior to Oracle Database 9,
large transformations required either significant memory overhead,
or storing the data in intermediate tables between each stage of the transformation.
The loading process caused immense performance degradations in both cases.

Using PL/SQL table functions can significantly lower the over-head of doing such transformations.
PL/SQL table functions accept and return multiple rows, delivering them as they are ready rather
than all at once, and can be made to execute as parallel operations.

Simple Example - Generating Some Random Data

How could you create six unique random numbers between 1 and 49 with one SQL statement?

We would generate the set of numbers to pick from (see the innermost query that follows);
any table with 49 or more records would do it. First the quick-and-dirty solution without a pipelined function.

select r
from (select r
from (select rownum r
from all_objects
where rownum < 50)
order by dbms_random.value)
where rownum <= 6;

R
----------
10
2
19
34
12
21

That query works by generating the numbers 1 .. 49, using the inline view. We wrap that innermost
query as an inline view and sort it by a random value, using DBMS_RANDOM.VALUE.
We wrap that result set in yet another inline view and just take the first six rows.
If we run that query over and over, we'll get a different set of six rows each time.

This sort of question comes up frequently—maybe not about how to generate a set of six random numbers but rather,
"how can we get N rows?" For example, we'd like the inclusive set of all dates between 25-FEB-2004
and 10-MAR-2004. The question becomes how to do this without a "real" table, and the answer lies in
Oracle9i/10g with its PIPELINED function capability. We can write a PL/SQL function that will operate
like a table. We need to start with a SQL collection type; this describes what the PIPELINED function
will return. In this case, we are choosing a table of numbers; the virtual table we are creating will
simply return the numbers 1, 2, 3, ... N:

create type array
as table of number
/

Type created.

Next, we create the actual PIPELINED function. This function will accept an input to limit the number of rows
returned. If no input is provided, this function will just keep generating rows for a very long time
(so be careful and make sure to use ROWNUM or some other limit in the query itself!).
The PIPELINED keyword on line 4 allows this function to work as if it were a table:

create function
gen_numbers(n in number default null)
return array
PIPELINED
as
begin
for i in 1 .. nvl(n,999999999)
loop
pipe row(i);
end loop;
return;
end;
/

Function created.

Suppose we needed three rows for something. We can now do that in one of two ways:

select * from TABLE(gen_numbers(3));

COLUMN_VALUE
------------
1
2
3

or

select * from TABLE(gen_numbers)
where rownum <= 3;

COLUMN_VALUE
------------
1
2
3

Now we are ready to re-answer the original question, using the following functionality:

select *
from (
select *
from (select * from table(gen_numbers(49)))
order by dbms_random.random
)
where rownum <= 6
/

COLUMN_VALUE
------------
47
42
40
15
48
23

We can use this virtual table functionality for many things, such as generating that range of dates:

select to_date('25-feb-2004')+
column_value-1
from TABLE(gen_numbers(15))
/

TO_DATE('
---------
25-FEB-04
26-FEB-04
27-FEB-04
28-FEB-04
29-FEB-04
01-MAR-04
02-MAR-04
03-MAR-04
04-MAR-04
05-MAR-04
06-MAR-04
07-MAR-04
08-MAR-04
09-MAR-04
10-MAR-04

Note the name of the column we used: COLUMN_VALUE. That is the default name for the column coming back
from the PIPELINED function.

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 TYPE myTableType
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 RETURN myTableType PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY myDemoPack AS
FUNCTION prodFunc RETURN myTableType PIPELINED 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.