ORACLE管道化表函数

最新推荐文章于 2022-02-10 17:53:11 发布

cszj38709

最新推荐文章于 2022-02-10 17:53:11 发布

阅读量235

点赞数

文章标签：数据库

在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造成的严重影响。所以我们暂且忽略吧。或者有高手帮忙补充补充~！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10356975/viewspace-749610/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10356975/viewspace-749610/