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

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

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

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

二、如何编写管道函数：

例1：

CREATE OR REPLACE PACKAGE pkg1 AS

TYPE numset_t IS TABLE NUMBER;

FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;

END pkg1;

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 * FROM TABLE(pkg1.f1(5));

COLUMN_VALUE

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

1

2

3

4

5

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

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

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;

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 * 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

四 用法扩展：

1、表函数间传递数据：

SELECT * FROM TABLE(f(CURSOR(SELECT * FROM TABLE(g()))));

2、使用游标变量接收管道函数返回的结果：

OPEN c FOR SELECT * FROM TABLE(f(...));

3、使用多个游标变量入参：

例3：

-- Define the ref cursor types

CREATE PACKAGE refcur_pkg IS

TYPE refcur_t1 IS REF CURSOR RETURN employees%ROWTYPE;

TYPE refcur_t2 IS REF CURSOR RETURN departments%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 g_trans(p1 refcur_t1, p2 refcur_t2)

RETURN outrecset PIPELINED;

END refcur_pkg;

/

CREATE PACKAGE BODY refcur_pkg IS

FUNCTION g_trans(p1 refcur_t1, p2 refcur_t2)

RETURN outrecset PIPELINED IS

out_rec outrec_typ;

in_rec1 p1%ROWTYPE;

in_rec2 p2%ROWTYPE;

BEGIN

LOOP

FETCH p2 INTO in_rec2;

EXIT WHEN p2%NOTFOUND;

END LOOP;

CLOSE p2;

LOOP

FETCH p1 INTO in_rec1;

EXIT WHEN p1%NOTFOUND;

-- first row

out_rec.var_num := in_rec1.employee_id;

out_rec.var_char1 := in_rec1.first_name;

out_rec.var_char2 := in_rec1.last_name;

PIPE ROW(out_rec);

-- second row

out_rec.var_num := in_rec2.department_id;

out_rec.var_char1 := in_rec2.department_name;

out_rec.var_char2 := TO_CHAR(in_rec2.location_id);

PIPE ROW(out_rec);

END LOOP;

CLOSE p1;

RETURN;

END;

END refcur_pkg;

/

-- SELECT query using the g_trans table function

SELECT * FROM TABLE(refcur_pkg.g_trans(

CURSOR(SELECT * FROM employees WHERE department_id = 60),

CURSOR(SELECT * FROM departments WHERE department_id = 60)));

4、管道函数作为聚合函数使用：

例4：

CREATE TABLE gradereport (student VARCHAR2(30), subject VARCHAR2(30),

weight NUMBER, grade NUMBER);

INSERT INTO gradereport VALUES('Mark', 'Physics', 4, 4);

INSERT INTO gradereport VALUES('Mark','Chemistry', 4, 3);

INSERT INTO gradereport VALUES('Mark','Maths', 3, 3);

INSERT INTO gradereport VALUES('Mark','Economics', 3, 4);

CREATE PACKAGE pkg_gpa IS

TYPE gpa IS TABLE OF NUMBER;

FUNCTION weighted_average(input_values SYS_REFCURSOR)

RETURN gpa PIPELINED;

END pkg_gpa;

/

CREATE PACKAGE BODY pkg_gpa IS

FUNCTION weighted_average(input_values SYS_REFCURSOR)

RETURN gpa PIPELINED IS

grade NUMBER;

total NUMBER := 0;

total_weight NUMBER := 0;

weight NUMBER := 0;

BEGIN

-- The function accepts a ref cursor and loops through all the input rows

LOOP

FETCH input_values INTO weight, grade;

EXIT WHEN input_values%NOTFOUND;

-- Accumulate the weighted average

total_weight := total_weight + weight;

total := total + grade*weight;

END LOOP;

PIPE ROW (total / total_weight);

RETURN; -- the function returns a single result

END;

END pkg_gpa;

/

-- the query result comes back as a nested table with a single row

-- COLUMN_VALUE is a keyword that returns the contents of a nested table

SELECT w.column_value "weighted result" FROM TABLE(

pkg_gpa.weighted_average(CURSOR(SELECT weight, grade FROM gradereport))) w;

5、在管道函数中进行DML操作,我们使用自治事务使管道函数作为独立事务处理：

CREATE FUNCTION f(p SYS_REFCURSOR)

RETURN CollType PIPELINED IS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

NULL;

END;

/

6、对管道函数进行DML操作：

实际上我们无法直接对管道函数进行DML操作，例如以下语句都会失败：

UPDATE F(CURSOR(SELECT * FROM tab)) SET col = value;

INSERT INTO f(...) VALUES ('any', 'thing');

官方给出的方案是创建一个基于管道函数的VIEW，然后在这个VIEW上创建相应的instead of 触发器。下面给出操作实例：

CREATE OR REPLACE VIEW V_F_TRANS AS

SELECT x.var_num, x.var_char1, x.var_char2

FROM TABLE(refcur_pkg.f_trans(CURSOR (SELECT *

FROM emp))) x;

CREATE OR REPLACE TRIGGER tri_f_trans

INSTEAD OF INSERT ON v_f_trans

FOR EACH ROW

BEGIN

dbms_output.put_line('Trigger of a pipelined funtion based view was on fire!');

END;

SCOTT@orcl> insert into v_f_trans values(102, 'abc','def');

Trigger of a pipelined funtion based view was on fire!

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

By    Dylan.