Oracle Pipelined Function 管道函数实例

管道函数的原理，请参考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;