从Oracle 8开始,我们就可以从一个collection类型的数据集合中查询出数据,这个集合称之为“虚拟表“。它的方法是“SELECT FROM TABLE(CAST(plsql_function AS collection_type))”,据说该方法在处理大数据量时会有内存方面的限制。到了Oracle 9i之后,一个称为pipelined表函数的技术被推出来。他和普通的表函数很类似,但还有有一些显著的差别。
第一,pipelined函数处理的数据,是以管道的方式,或者说是流的方式从预先准备的小数组中展现给用户,而普通表函数将数据全部准备好再展现给用户。
第二,pipelined函数可以并发,这意味着PLSQL可以同一时间在多个进程上执行。
第三,pipelined函数可以很容易将存储过程转换成用bulk操作的行,有利于实现的复杂转换逻辑的SQL。
(miki西游 @mikixiyou 原文链接: http://mikixiyou.iteye.com/blog/1628397 )
了解pipelined函数的最佳方法是看一个简单的例子。对于任何一个pipelined函数,都必须有两点要求。
1、一个定义在模式中或者包中collection类型;
2、一个单独的PL/SQL函数或一个包中的函数,函数的返回类型后面必须加pipelined关键字;
在下面的例子中,我们将创建一个简单的pipelined函数,输出若干行记录。首先需要一个collection类型,用于格式化输出。
- CREATE OR REPLACE TYPE number_ntt AS TABLE OF NUMBER;
CREATE OR REPLACE TYPE number_ntt AS TABLE OF NUMBER;
Oracle会使用这个类型缓存少量的记录作为pipelined函数调用时的输出。我们创建一个简单的pipelined函数。
- CREATE OR REPLACE FUNCTION row_generator(rows_in IN PLS_INTEGER)
- RETURN number_ntt PIPELINED
- IS
- BEGIN
- FOR i IN 1 .. rows_in LOOP
- PIPE ROW(i);
- END LOOP;
- RETURN;
- END;
CREATE OR REPLACE FUNCTION row_generator(rows_in IN PLS_INTEGER)
RETURN number_ntt PIPELINED
IS
BEGIN
FOR i IN 1 .. rows_in LOOP
PIPE ROW(i);
END LOOP;
RETURN;
END;
在这个SQL中:
在函数定义部分的关键字pipelined是pipelined函数定义的关键,返回的类型必须是事先定义的collection类型,如这里是number_tt。
在函数主体部分的”PIPE ROW”是将一个单行记录写入到collection流中。记录中所有字段的类型必须和collection类型中所有字段匹配。
在函数主体部分的“return“的值是一个空值,而不是有任何符合collection类的值。
这些就是pipelined函数定义时需要严格遵守的规则。
现在已经创建好一个pipelined函数,我们可以测试一下。
- SQL> select * from TABLE( row_generator(10) );
- COLUMN_VALUE
- ------------
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 10 rows selected
SQL> select * from TABLE( row_generator(10) );
COLUMN_VALUE
------------
1
2
3
4
5
6
7
8
9
10
10 rows selected
将pipelined函数row_generator放到一个“TABLE”操作符中,虚拟成一个数据源,类似表或视图。这里虚拟表只有一个字段,名称“COLUMN_VALUE“是其默认值。更复杂的输出则需要将collection定义得更复杂些,使用到object或者record。
我们通过一个例子比较一下pipelined函数或普通的表函数在返回collection时有何差异。
第一步,创建普通的表函数,返回colletion类型。
- CREATE OR REPLACE FUNCTION table_function RETURN number_ntt AS
- nt number_ntt := number_ntt();
- BEGIN
- FOR i IN 1 .. 500000 LOOP
- if (mod(i, 10000) = 0) then
- nt.EXTEND;
- nt(nt.LAST) := i;
- end if;
- END LOOP;
- RETURN nt; --<-- return whole collection
- END table_function;
CREATE OR REPLACE FUNCTION table_function RETURN number_ntt AS
nt number_ntt := number_ntt();
BEGIN
FOR i IN 1 .. 500000 LOOP
if (mod(i, 10000) = 0) then
nt.EXTEND;
nt(nt.LAST) := i;
end if;
END LOOP;
RETURN nt; --<-- return whole collection
END table_function;
第二步,创建pipelined函数,返回的也是collection类型
- CREATE OR REPLACE FUNCTION pipelined_function RETURN number_ntt
- PIPELINED AS
- BEGIN
- FOR i IN 1 .. 500000 LOOP
- if (mod(i, 10000) = 0) then
- PIPE ROW(i); --<-- send row to consumer
- end if;
- END LOOP;
- RETURN;
- END pipelined_function;
CREATE OR REPLACE FUNCTION pipelined_function RETURN number_ntt
PIPELINED AS
BEGIN
FOR i IN 1 .. 500000 LOOP
if (mod(i, 10000) = 0) then
PIPE ROW(i); --<-- send row to consumer
end if;
END LOOP;
RETURN;
END pipelined_function;
函数的功能都是将能和1000整除的数字输出出来。
再创建一个输出时间到毫秒的函数,用于测试两个函数的输出特点。
- CREATE FUNCTION get_time RETURN TIMESTAMP IS
- BEGIN
- RETURN LOCALTIMESTAMP;
- END get_time;
- /
CREATE FUNCTION get_time RETURN TIMESTAMP IS
BEGIN
RETURN LOCALTIMESTAMP;
END get_time;
/
第三步,测试两个函数
测试普通函数如下:
- ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'HH24:MI:SS.FF3';
- SELECT get_time() AS ts FROM DUAL;
- SELECT column_value, get_time() AS ts FROM TABLE(table_function);
- SQL>
- TS
- --------------------------------------------------------------------------------
- 15:27:26.031
- COLUMN_VALUE TS
- ------------ --------------------------------------------------------------------------------
- 100000 15:27:26.218
- 200000 15:27:26.218
- 300000 15:27:26.218
- 400000 15:27:26.218
- 500000 15:27:26.218
- SQL>
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'HH24:MI:SS.FF3';
SELECT get_time() AS ts FROM DUAL;
SELECT column_value, get_time() AS ts FROM TABLE(table_function);
SQL>
TS
--------------------------------------------------------------------------------
15:27:26.031
COLUMN_VALUE TS
------------ --------------------------------------------------------------------------------
100000 15:27:26.218
200000 15:27:26.218
300000 15:27:26.218
400000 15:27:26.218
500000 15:27:26.218
SQL>
结果显示,所有记录都是同一时间输出。
测试pipelined函数如下:
- SELECT get_time() AS ts FROM DUAL;
- SELECT column_value, get_time() AS ts FROM TABLE(pipelined_function);
- TS
- --------------------------------------------------------------------------------
- 15:27:26.265
- COLUMN_VALUE TS
- ------------ --------------------------------------------------------------------------------
- 100000 15:27:26.312
- 200000 15:27:26.343
- 300000 15:27:26.390
- 400000 15:27:26.421
- 500000 15:27:26.453
SELECT get_time() AS ts FROM DUAL;
SELECT column_value, get_time() AS ts FROM TABLE(pipelined_function);
TS
--------------------------------------------------------------------------------
15:27:26.265
COLUMN_VALUE TS
------------ --------------------------------------------------------------------------------
100000 15:27:26.312
200000 15:27:26.343
300000 15:27:26.390
400000 15:27:26.421
500000 15:27:26.453
结果显示,所有记录都是逐次输出。
这点对于用户的UI太重要了。试想,如果执行一个查询,过了10秒钟才显示出所有的结果好,还是还是每秒都显示一些记录,知道10秒钟显示完毕好?
如果这个输出的结果集再放到到百万记录,两个函数对PGA内存的消耗又完全不一样,这点更重要。