在oracle中建置一個回傳select的function

tags: oracle

在mssql中，要弄一個回傳select的function或stored procedure好像是一件非常簡單的事，只要在文末下一個select就回傳了。

不過在oracle好像不是這麼一回事，需要不少步驟，這邊僅記錄個人能夠成功的步驟，但是已經有點忘了當初參考的出處，如有雷同部份再請告知，讓在下可以標註參考來源。

首先，建立一個參照的物件

-- 建立一個參照物件
CREATE OR REPLACE TYPE NOTES_OBJ_EOD_MLIST AS OBJECT ( 
    MATNR VARCHAR2(54),
    MTIDX VARCHAR2(54),
    IDNRK VARCHAR2(54),
    FERTH_LBR VARCHAR2(54),
    FERTH_LED VARCHAR2(54)

  ); 

接下來，建立一個暫存表，命名習慣上我會直接在參照物件後面加上一個TEMP，看個人喜好

CREATE TYPE NOTES_OBJ_EOD_MLIST_TEMP AS TABLE OF NOTES_OBJ_EOD_MLIST;  

然後建立你的function

-- 建立FUNCTION
create or replace function NOTES_FUNC_EOD_MLIST(YOUR_PARAMETERS) return NOTES_OBJ_EOD_MLIST_TEMP pipelined AS
BEGIN
    FOR cur IN (        
        SELECT MATNR, MTIDX, IDNRK, FERTH_LBR, FERTH_LED FROM YOUR_TABLE
    ) -- END cur
    LOOP  -- return date
        PIPE ROW(NOTES_OBJ_EOD_MLIST(cur.MATNR,
                                    cur.MTIDX,
                                    cur.IDNRK,
                                    cur.FERTH_LBR,
                                    cur.FERTH_LED
                                    )
        );
    END LOOP;

    RETURN;

END;

第1行：要特別注意到，最後的return是你的暫存表，並且加上pipelined
第3行：把你的select用一個for cur in包住，其中cur你可以自己用你自己的命名
第6行：用loop end loop包住預計回傳的資料
第7行：用pipe row(你建立的參照物件(欄位))來處理要回傳的資料

最後只要再return，就可以成功弄一個可以回傳select的function

使用方法也很簡單，見下範例：

select * from table(your_function(parameter, if have))