Oracle - 為子查詢提供動態結果集

標籤：

曾經遇到過這樣一個需求：要求為method傳入String，內容如"使用者ID0,使用者ID1,使用者ID2..."，然後根據這些ID返回一個結果集作為資料表供別人查詢。
SELECT * FROM TBL WHERE ID IN (‘使用者ID0,使用者ID1,使用者ID2‘) 不就可以解決問題嗎？
但實際情況是，結果集無法通過一個簡單的SELECT就可以得到。

讓我明確一下需要解決的問題：

我們給FUNCTION傳遞這樣的一個String參數後如何讓它動態RETURN一個結果集供其他SELECT語句使用。

既然我們要返回一個結果集，那便是要得到一個TABLE OF XXX類型，XXX可以是VARCHAR2或者INTEGER或者某個表的%ROWTYPE，但我的情況稍微複雜一點，我要自己建立一個OBJECT TYPE。

於是我們要寫的FUNCTION的RETURN類型是這樣建立的:

CREATE OR REPLACE TYPE TYP_USER_RECORD AS OBJECT (USER_ID CHAR(40),USER_NUM VARCHAR2(200),CREATE_DATE DATE);CREATE OR REPLACE TYPE TYP_USER_TBL AS TABLE OF TYP_USER_RECORD;

下面是FUNCTION的建立:

CREATE OR REPLACE FUNCTION REGROUP_USER_BY_USERIDSTR(USERIDSTR IN VARCHAR2)  RETURN TYP_USER_TBL  PIPELINED IS  --參數聲明開始  TYPE USER_CURSOR IS REF CURSOR;  USER_INFO_LIST USER_CURSOR;       --用來獲得檢索結果的CURSOR  TYPE USER_ROW IS RECORD(    USER_ID CHAR(40),USER_NUM VARCHAR2(200),CREATE_DATE DATE);  USER_INFO USER_ROW;               --用於提取CURSOR中的記錄的RECORD  USER_ROW4RESULT TYP_USER_RECORD;  --我們要返回的資料集的資料行對象  QUERYSTR          VARCHAR2(2000); --拼接後的SELECT語句  --參數聲明結束BEGIN  --此處根據傳入的ID進行了各種判斷拼接SELECT語句 並給QUERYSTR賦值  OPEN USER_INFO_LIST FOR QUERYSTR; --開啟CURSOR  --迴圈從CURSOR獲得結果 並將結果變成TYP_USER_RECORD對象 再將對象放到PIPE裡  LOOP    FETCH USER_INFO_LIST INTO USER_INFO;    EXIT WHEN USER_INFO_LIST%NOTFOUND;    USER_ROW4RESULT := TYP_USER_RECORD(USER_INFO.USER_ID,                              USER_INFO.USER_NUM,                              USER_INFO.CREATE_DATE);    PIPE ROW(USER_ROW4RESULT);  END LOOP;  CLOSE USER_INFO_LIST;  RETURN;END;

既然RETURN TYPE是TABLE類型的，調用時便可以使用TABLE()函數進行查詢。

SELECT * FROM TABLE(REGROUP_USER_BY_USERIDSTR)

另外，本人目前工程中使用的持久化架構是MyBatis，此語句執行無誤。
參數雖然可以直接傳入SELECT * FROM XX IN ()進行查詢，但也可能需要進行截取變成COLLECION，下面是該功能的FUNCTION:

CREATE OR REPLACE TYPE TBL_VARCHAR2 AS TABLE OF VARCHAR2(400);CREATE OR REPLACE FUNCTION STR2TBL( PARAM_STR IN VARCHAR2 ) RETURN TBL_VARCHAR2  AS      TMP_RECORD   LONG DEFAULT PARAM_STR || ‘,‘;      ROW_INDEX        NUMBER;      TMP_TBL    TBL_VARCHAR2 := TBL_VARCHAR2();  BEGIN      LOOP          ROW_INDEX := INSTR( TMP_RECORD, ‘,‘ );          EXIT WHEN (NVL(ROW_INDEX,0) = 0);          TMP_TBL.EXTEND;          TMP_TBL( TMP_TBL.COUNT ) := LTRIM(RTRIM(SUBSTR(TMP_RECORD,1,ROW_INDEX-1)));          TMP_RECORD := SUBSTR( TMP_RECORD, ROW_INDEX+1 );      END LOOP;      RETURN TMP_TBL;  END;

這種方式的意義可能只有讓開發方便了一些，試圖用一句SQL拯救世界必將導致各種問題。
為什麼會有這種需求，可能是因為資料關係梳理地有些倉促，資料散落在不同的資料表。
無論如何這是一個糟糕的情境。

Oracle - 為子查詢提供動態結果集