oracle 管道方式输出,Oracle管道函数(Pipelined Table Function)介绍

3、关键字PIPELINED表明这是一个Oracle管道函数，oracle管道函数的返回值类型必须为集合，在函数中，PIPE ROW语句被用来返回该集合的单个元

素，函数以一个空的RETURN 语句结束，以表明它已经完成。

4、由于管道函数的并发多管道流式设计以及实时返回查询结果而去除了中间环节因此可以带来可观的性能提升。

二、如何编写管道函数：

例1：

CREATEORREPLACEPACKAGEpkg1AS

TYPEnumset_tISTABLENUMBER;

FUNCTIONf1(xNUMBER)RETURNnumset_tPIPELINED;

ENDpkg1;

CREATE OR REPLACE PACKAGE pkg1 AS

TYPE numset_t IS TABLE NUMBER;

FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;

END pkg1;

CREATEORREPLACEPACKAGEBODYpkg1AS

FUNCTIONf1(xNUMBER)RETURNnumset_tPIPELINEDIS

BEGIN

FORiIN1..xLOOP

PIPEROW(i);

ENDLOOP;

RETURN;

END;

ENDpkg1;

CREATE OR REPLACE PACKAGE BODY pkg1 AS

FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS

BEGIN

FOR i IN 1..x LOOP

PIPE ROW(i);

END LOOP;

RETURN;

END;

END pkg1;

SELECT*FROMTABLE(pkg1.f1(5));

SELECT * FROM TABLE(pkg1.f1(5));

COLUMN_VALUE

------------------------

1

2

3

4

5

三 管道函数用于数据转换：

例2：管道函数可以和常规函数一样接收任何参数，下面的管道函数中参数为ref cursor。

CREATEORREPLACEPACKAGErefcur_pkgIS

TYPErefcur_tISREFCURSORRETURNemp%ROWTYPE;

TYPEoutrec_typISRECORD(

var_numNUMBER(6),

var_char1VARCHAR2(30),

var_char2VARCHAR2(30));

TYPEoutrecsetISTABLEOFoutrec_typ;

FUNCTIONf_trans(prefcur_t)

RETURNoutrecsetPIPELINED;

ENDrefcur_pkg;

CREATE OR REPLACE PACKAGE refcur_pkg IS

TYPE refcur_t IS REF CURSOR RETURN emp%ROWTYPE;

TYPE outrec_typ IS RECORD (

var_num NUMBER(6),var_char1 VARCHAR2(30),var_char2 VARCHAR2(30));

TYPE outrecset IS TABLE OF outrec_typ;

FUNCTION f_trans(p refcur_t)

RETURN outrecset PIPELINED;

END refcur_pkg;

CREATEORREPLACEPACKAGEBODYrefcur_pkgIS

FUNCTIONf_trans(prefcur_t)

RETURNoutrecsetPIPELINEDIS

out_recoutrec_typ;

in_recp%ROWTYPE;

BEGIN

LOOP

FETCHpINTOin_rec;

EXITWHENp%NOTFOUND;

--firstrow

out_rec.var_num:=in_rec.empno;

out_rec.var_char1:=in_rec.ename;

out_rec.var_char2:=in_rec.mgr;

PIPEROW(out_rec);

--secondrow

out_rec.var_num:=in_rec.deptno;

out_rec.var_char1:=in_rec.deptno;

out_rec.var_char2:=in_rec.job;

PIPEROW(out_rec);

ENDLOOP;

CLOSEp;

RETURN;

END;

ENDrefcur_pkg;

CREATE OR REPLACE PACKAGE BODY refcur_pkg IS

FUNCTION f_trans(p refcur_t)

RETURN outrecset PIPELINED IS

out_rec outrec_typ;

in_rec p%ROWTYPE;

BEGIN

LOOP

FETCH p INTO in_rec;

EXIT WHEN p%NOTFOUND;

-- first row

out_rec.var_num := in_rec.empno;

out_rec.var_char1 := in_rec.ename;

out_rec.var_char2 := in_rec.mgr;

PIPE ROW(out_rec);

-- second row

out_rec.var_num := in_rec.deptno;

out_rec.var_char1 := in_rec.deptno;

out_rec.var_char2 := in_rec.job;

PIPE ROW(out_rec);

END LOOP;

CLOSE p;

RETURN;

END;

END refcur_pkg;

SELECT*FROMTABLE(

refcur_pkg.f_trans(CURSOR(SELECT*FROMempWHEREempno=7782)));

SELECT * FROM TABLE(

refcur_pkg.f_trans(CURSOR(SELECT * FROM emp WHERE empno=7782)));

VAR_NUM VAR_CHAR1 VAR_CHAR2

---------- ------------------------------ ------------------------------

7782 CLARK 7839

10 10 MANAGER

---------------------------------------

By Dylan.