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

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拯救世界必將導致各種問題。
 為什麼會有這種需求，可能是因為資料關係梳理地有些倉促，資料散落在不同的資料表。
 無論如何這是一個糟糕的情境。