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相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址