2020年9月17日 星期四

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

在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))