建立一个测试表：

create table t(id,owner,object_id) as

select rownum,owner,object_id from all_objects;

创建必要的类型：

create type typ_test is object(

id number,

owner varchar2(50),

object_id number

);

create type typ_test_tab is table of typ_test;

创建存储过程:

CREATE OR REPLACE FUNCTION pipe_func RETURN typ_test_tab

PIPELINED IS

CURSOR cur_t IS

SELECT * FROM t;

TYPE tab_id IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

TYPE tab_owner IS TABLE OF t.owner%TYPE INDEX BY PLS_INTEGER;

TYPE tab_objectid IS TABLE OF t.object_id%TYPE INDEX BY PLS_INTEGER;

v_ids tab_id;

v_owners tab_owner;

v_objs tab_objectid;

BEGIN

OPEN cur_t;

LOOP

FETCH cur_t BULK COLLECT

INTO v_ids, v_owners, v_objs LIMIT 10000;

EXIT WHEN cur_t%NOTFOUND;

FOR i IN 1 .. v_ids.count LOOP

PIPE ROW(typ_test(v_ids(i), v_owners(i), v_objs(i)));

END LOOP;

CLOSE cur_t;

RETURN;

END pipe_func;

调用：

SELECT * FROM TABLE(CAST(pipe_func AS typ_test_tab)) WHERE id = 1

SELECT * FROM TABLE(pipe_func) WHERE id = 1

经测试，以上两种方式调用貌似都可行.

可以参考：

ITPub博客

Oracle Pipelined Function