为了让 PL/SQL 函数返回数据的多个行,必须通过返回一个 REF CURSOR 或一个数据集合来完成。REF CURSOR 的这种情况局限于可以从查询中选择的数据,而整个集合在可以返回前,必须进行具体化。Oracle 9i 通过引入的管道化表函数纠正了后一种情况。表函数是返回整个行的集(通常作为一个集合)的函数,可以直接从 SQL 语句中进行查询,就好像它是一个真正的数据库表一样。管道化表函数与之相似,但是它像在构建时一样返回数据,而不是一次全部返回。管道化表函数更加有效,因为数据可以尽可能快地返回。
管道化表函数必须返回一个集合。在函数中,PIPE ROW 语句被用来返回该集合的单个元素,该函数必须以一个空的 RETURN 语句结束,以表明它已经完成。一旦我们创建了上述函数,我们就可以使用 TABLE 操作符从 SQL 查询中调用它。
管道化表函数经常被用来把数据从一种类型转化成另一种类型。
例子1:
--需要预先定义str_split类型
CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (4000)
--管道化表函数
CREATE OR REPLACE FUNCTION fn_split(p_str IN VARCHAR2,
p_delimiter IN VARCHAR2)
RETURN str_split
PIPELINED IS
/*
测试:SELECT * FROM TABLE (fn_split('aa,abb,123abc,12345678', ','));
结果:
aa
abb
123abc
12345678
*/
pvar_j INT := 0;
pvar_i INT := 1;
pvar_i_lenstr INT := 0; --字符串长度
pvar_i_lensmark INT := 0; --分隔符的长度
str VARCHAR2(4000);
BEGIN
pvar_i_lenstr := LENGTH(p_str);
pvar_i_lensmark := LENGTH(p_delimiter);
WHILE pvar_j < pvar_i_lenstr LOOP
pvar_j := INSTR(p_str, p_delimiter, pvar_i);
IF pvar_j = 0 THEN
pvar_j := pvar_i_lenstr;
str := SUBSTR(p_str, pvar_i);
PIPE ROW(str);
IF pvar_i >= pvar_i_lenstr THEN
EXIT;
END IF;
ELSE
str := SUBSTR(p_str, pvar_i, pvar_j - pvar_i);
pvar_i := pvar_j + pvar_i_lensmark;
PIPE ROW(str);
END IF;
END LOOP;
RETURN;
END fn_split;
例子2:
Declare
tm_i Integer;
Begin
Select Count(*) into tm_i From User_Objects t Where Lower(t.OBJECT_NAME) = Lower('mytype') And Lower(t.OBJECT_TYPE) = Lower('Type');
If tm_i = 0 Then
Execute Immediate
'CREATE OR REPLACE TYPE mytype AS OBJECT
(
field1 NUMBER,
field2 VARCHAR2(50)
)';
else
Execute Immediate ' drop type mytype force ';
Execute Immediate
'CREATE OR REPLACE TYPE mytype AS OBJECT
(
field1 NUMBER,
field2 VARCHAR2(50)
)';
End If;
End;
/
CREATE OR REPLACE TYPE mytypelist AS TABLE OF mytype;
CREATE OR REPLACE FUNCTION pipelineme
RETURN mytypelist
PIPELINED IS
v_mytype mytype;
BEGIN
FOR v_count IN 1 .. 10 LOOP
v_mytype := mytype(v_count, 'Row ' || v_count);
PIPE ROW(v_mytype);
END LOOP;
RETURN;
END pipelineme;
测试:SELECT * FROM TABLE (pipelineme);
结果:
出处:http://flysky0814.itpub.net/post/35477/419443