如何寫一個回傳TABLE的Function

步驟一.首先建置一個TYPE OBJECT

  CREATE OR REPLACE TYPE ZENHR.DATE_TEMP AS OBJECT ( 
    USER_NO VARCHAR2(6), 
    SDATE VARCHAR2(8) 
  ); 

步驟二.宣告一個TYPE

  CREATE TYPE ZEN_DATE_TEMP AS TABLE OF DATE_TEMP;

步驟三.撰寫一個Function

  CREATE OR REPLACE FUNCTION get_dates (UserNO in VARCHAR2 , SDATE in VARCHAR2 ) 
  RETURN ZEN_DATE_TEMP pipelined AS 
  -- Weng 2007/6/28 
  -- 回傳Table 
  v_tab ZEN_DATE_TEMP := ZEN_DATE_TEMP(); 
  BEGIN 
    FOR cur IN (SELECT USER_NO, USER_SDATE FROM Y17_USER WHERE 1=1 ORDER BY USER_NO) 
    LOOP 
      PIPE ROW(DATE_TEMP(cur.USER_NO, cur.USER_SDATE)); 
    END LOOP; 
    RETURN; 
  END; 
  / 

步驟四.取得Function內容

  select * from TABLE(get_dates('','')) a; 

注意事項一.刪除注意事項
a.刪除時需注意順序(反向刪除)
b.刪除順序如下:

  DROP FUNCTION get_dates;  -- 1 
  DROP TYPE ZEN_DATE_TEMP;  -- 2 
  DROP TYPE ZENHR.DATE_TEMP;  -- 3

.參考資料

  http://www.oracle-base.com/articles/9i/PipelinedTableFunctions9i.php
  http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/08_subs.htm#19700