Oracle管道函数(Pipelined Table Function)介绍
时间：2022-03-13 22:40
一 概述：
1、管道函数即是可以返回行集合（可以使嵌套表nested table 或数组 varray）的函数，我们可以像查询物理表一样查询它或者将其
赋值给集合变量。
2、管道函数为并行执行，在普通的函数中使用dbms_output输出的信息，需要在服务器执行完整个函数后一次性的返回给客户端。如果需要在客户端
实时的输出函数执行过程中的一些信息，在oracle9i以后可以使用管道函数(pipeline function)。
3、关键字PIPELINED表明这是一个oracle管道函数，oracle管道函数的返回值类型必须为集合，在函数中，PIPE ROW语句被用来返回该集合的单个元
素，函数以一个空的RETURN 语句结束，以表明它已经完成。
4、由于管道函数的并发多管道流式设计以及实时返回查询结果而去除了中间环节因此可以带来可观的性能提升。
二、如何编写管道函数：
例1：
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 触发器。下面给出操作实例：
---------------------------------------
By Dylan.
Oracle管道函数(Pipelined Table Function)介绍,布布扣,bubuko.com
