为了让 PL/SQL 函数返回数据的多个行,必须通过返回一个 REF CURSOR 或一个数据集合来完成.REF CURSOR 的这种情况局限于可以从查询中选择的数据,而整个集合在可以返回前,必须进行具体化.Oracle 9i 通过引入的管道化表函数纠正了后一种情况.表函数是返回整个行的集(通常作为一个集合)的函数,可以直接从 SQL 语句中进行查询,就好像它是一个真正的数据库表一样.管道化表函数与之相似,但是它像在构建时一样返回数据,而不是一次全部返回.管道化表函数更加有效,因为数据可以尽可能快地返回.
管道化表函数必须返回一个集合.在函数中,PIPE ROW 语句被用来返回该集合的单个元素,该函数必须以一个空的 RETURN 语句结束,以表明它已经完成.一旦我们创建了上述函数,我们就可以使用 TABLE 操作符从 SQL 查询中调用它.
管道化表函数经常被用来把数据从一种类型转化成另一种类型.
实现根据分隔符分隔字符串函数的例子:
CREATE OR REPLACE TYPE t_Table IS TABLE OF VARCHAR2(4000);
CREATE OR REPLACE FUNCTION FN_SplitStringByDelimiter(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN t_Table PIPELINED AS
v_delimiter_index INTEGER;
v_start_index INTEGER := 1;
v_table VARCHAR2(4000);
v_delimiter_length INTEGER := LENGTH(p_delimiter);
/*
功能:把某一个字符串按照指定的分隔符分隔,并以Table形式返回
*/
BEGIN
LOOP
-- 查找当前分隔符的位置
v_delimiter_index := INSTR(p_string || p_delimiter, p_delimiter, v_start_index);
-- 如果在字符串找不到字串则返回0,退出循环
EXIT WHEN v_delimiter_index = 0;
-- 得到分隔符前的值
v_table := SUBSTR(p_string, v_start_index, v_delimiter_index - v_start_index);
IF v_table IS NOT NULL THEN
-- 返回该集合的单个元素
PIPE ROW(v_table);
END IF;
-- 检索位置设置为当前分隔符的下一个
v_start_index := v_delimiter_index + v_delimiter_length;
END LOOP;
-- 必须以一个空的 RETURN 语句结束
RETURN;
END FN_SplitStringByDelimiter;调用方法:SELECT * FROM TABLE(FN_SplitStringByDelimiter ('a.b.c.d.e', '.'));
SELECT * FROM TABLE(FN_SplitStringByDelimiter ('123-;456-;789-;000', '-;'));
返回结果: