Oracle 管道化表函数
在PL/SQL中，如果要返回数据的多个行，必须通过返回一个REF CURSOR的游标，或者一个数据集合（如临时表或物理表）来完成，而REF CURSOR的局限于可以从查询中选择的数据，而数据集合的局限性在于必须先CREATE TABLE（无论是创建临时表还是物理表）来进行具体化，具体化后，会因为频繁删除表导致大量的碎片。
Oracle 9i开始，引入了管道化表函数，解决了这种情况。
管道化表函数是返回整个行的集合的函数，可以直接在SQL中进行查询，他就好像是真正的数据库表一样。他存在于内存中，比物理表速度要快几十倍。
管道化表函数必须返回一个集合，在函数中PIPE ROW 语句被用来返回该集合的单个元素，该函数必须以一个空的RETURN语句结束，以表明他已经完成。一旦创建了该函数，就可以使用TABLE()操作符从SQL查询中来调用它。
使用管道化表函数需要定义以下内容：
1. 定义一个OBJECT类型的TYPE
CREATE TYPE TY_OBJ AS OBJECT ();
2. 定义一个TYPE继承了这个TY_OBJ;
CREATE TYPE TA_OBJ AS TABLE OF TY_OBJ;
3. 创建一个管道化函数
CREATE OR REPLACE FUNCTION FUN_NAME RETURN TA_OBJ PIPELINED
IS
BEGIN
……
PIPE ROW ();
EXCEPTION
END;
下面是一个管道化表函数的例子。
CREATE TYPE TY_NUMLIST AS OBJECT(
N1 NUMBER,
N2 VARCHAR2(50)
);
CREATE TYPE TA_NUMLIST AS TABLE OF TY_NUMLIST;
CREATE OR REPLACE FUNCTION FN_NUM RETURN TA_NUMLIST PIPELINED
IS
FV_NUMLIST TY_NUMLIST;
BEGIN
FOR x IN 1.. 20 LOOP
fv_numlist := ty_numlist(x, 'Row'||x);
pipe row (fv_numlist);
END LOOP;
return;
END;
--接下来可以查询结果了；
SELECT * FROM table(fn_num);
这个管道化函数是可以带参数的,比如：创建时，使用了id这个参数
CREATE FUNCTION MY_FUN(id number) RETURN TT PIPELINED;
--那么查询时，可以这样调用
SELECT * FROM table(mu_fun(11));
下面是一些比较复杂的用法:把一个查询的结果通过管道化函数来返回。
1. 创建TYPE
CREATE OR REPLACE TYPE ty_prov AS object
(
prov_id number(8),
prov_code varchar2(9),
eng_code varchar2(9),
prov_name varchar2(12)
)
2. 创建TYPE
CREATE OR REPLACE TYPE ta_prov AS TABLE OF ty_prov
3.创建管道化函数
CREATE OR REPLACE FUNCTION getProvMsg(pp_code varchar2) return ta_prov pipelined
IS
fv_prov ty_prov;
BEGIN
for x in (SELECT prov_id, prov_code, eng_nm, prov_name FROM bam_t01_province_def WHERE prov_code = pp_code) loop
fv_prov := ty_prov(x.prov_id, x.prov_code, x.eng_nm, x.prov_name);
pipe row (fv_prov);
end loop;
return;
EXCEPTION
when others THEN
raise_application_error(-20001,'getProvMsg : '||sqlerrm );
END;
4. 使用下面语句来查询
SQL> select * from table(getProvmsg('100')) ;
PROV_ID PROV_CODE ENG_CODE PROV_NAME
---------- --------- --------- ------------
10100 100 BJ 北京
以上参考自：http://blog.csdn.net/jojo52013145/article/details/6758279
关于效率的小知识：
对于物理表，他的数据存储在物理磁盘上，当数据第一次被READ时，会被load到db cache中，根据LRU算法来决定这些数据会否置换出内存。如果数据一直在内存中，他会被所有session共享，由于数据是机构化的，所以在内存中扫描的效率是最高的。
对于临时表，它和它的索引都是创建在临时表空间上的，当在一个SESSION中第一次插入数据时，才开始在用户的默认临时表空间下分配临时数据段，不同的SESSION拥有不同的段，以保证不同会话不会相互影响。临时表的数据也是结构化的，第一次读取数据后，数据也会被CACHE到db cache中。
TABLE()函数是9i的新特性，实际上是将一个存储在内存中的对象结构化后，使这个对象能以表的形式来查询。对象是以流的方式存储的，对流的结构化转换会导致效率下降。所以它的效率应该低于存在于内存中的物理表和临时表。（但应该高于第一次装载入内存的物理表和临时表）
关于对内存的影响：
网上几乎没有资料提及这个TABLE()特性对SGA和PGA造成的严重影响。所以我们暂且忽略吧。或者有高手帮忙补充补充~！
from:封烨
