Oracle Pipelined Function

abstractcyj發表於2013-08-24
OracleFunction
建立一個測試表：
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;
  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部落格 ” ，連結：http://blog.itpub.net/8520577/viewspace-769215/，如需轉載，請註明出處，否則將追究法律責任。

相關文章