管道函数的原理,请参考oracle document:
http://docs.oracle.com/cd/E11882_01/appdev.112/e10765/pipe_paral_tbl.htm#ADDCI4686
先面是简单的实例,由于Pipeline 只能用array/nesttable作为返回值,所以需要创建oracle object;
这里我们使用oracle package作为接口。
--------------------------------------------------------
-- 1. 创建包头 DDL for Package MY_TYPES
--------------------------------------------------------
CREATE OR REPLACE PACKAGE MY_TYPES IS
--1.define Object and Array of oracle type
type lookup_row is record (idx number, text varchar2(20));
TYPE LOOKUPS_TAB IS TABLE OF LOOKUP_ROW;
--2. define function interface
FUNCTION LOOKUPS_FN RETURN MY_TYPES.LOOKUPS_TAB pipelined;
end My_Types;
/
--------------------------------------------------------
-- 2. 创建包体 DDL for Package Body MY_TYPES
--------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY MY_TYPES IS
--1.function
function Lookups_Fn return My_Types.lookups_tab
pipelined
is
v_row My_Types.lookup_row;
begin
for j in 1..10
loop
case j
when 1 then v_row.idx := 1; v_row.text := 'one';
else v_row.idx := j; v_row.text := 'other';
end case;
pipe row ( v_row );
end loop;
RETURN;
END LOOKUPS_FN;
end My_Types;
/
--------------------------------------------------------
-- 3. 通过table函数直接调用 invoke above function
--------------------------------------------------------
select * from table(MY_TYPES.Lookups_Fn()) t where t.idx<8;