Oracle-Providing a dynamic result set for subqueries

Once encountered such a requirement: Request for method passed in string, content such as "user ID0, user ID1, user ID2 ...", and then according to these IDs return a result set as a data table for others to query.
SELECT * FROM TBL WHERE ID IN (‘用户ID0,用户ID1,用户ID2‘)Can't you solve the problem?
But the reality is that the result set cannot be obtained by a simple select.

Let me clarify the issues that need to be addressed:

After we give the function a string parameter, how to let it return a result set dynamically for use by other SELECT statements.

Since we are going to return a result set, that is to get a table of xxx type, XXX can be VARCHAR2 or integer or a table %ROWTYPE , but my case is slightly more complicated, I want to create an object type myself.

So the return type of function that we're going to write is created like this:

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;

Here is the creation of function:

  CREATE OR REPLACE FUNCTION regroup_user_by_useridstr (useridstr in VARCHAR2) RETURN typ_user_tbl pipelined is  --The parameter declaration begins with TYPE User_cursor is REF CURSOR;       User_info_list User_cursor;  Cursor TYPE User_row is RECORD (user_id CHAR (+), User_num VARCHAR2 ($), create_date DATE) used to obtain the results of the search;               User_info User_row;  --The record User_row4result Typ_user_record used to extract the records in the cursor; --The data row object of the dataset we want to return Querystr VARCHAR2 (2000); ---The SELECT statement after stitching--the end of the parameter declaration begin--here is a variety of judgments based on the ID of the Mosaic SELECT statement and assign value to Querystr OPEN user_info_list for querystr;    --Open the cursor-loop to get the result from the cursor and turn the result into a Typ_user_record object and put the object into the 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;  

Since the return type is of type table, it can be queried using the table () function when called.

SELECT * FROM TABLE(REGROUP_USER_BY_USERIDSTR)

In addition, I currently use the persistence framework in the project is MyBatis, this statement is executed without error.
Although parameters can be passed directly SELECT * FROM XX IN () into the query, they may also need to be intercepted into collecion, the function of which is:

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;

The meaning of this approach may only make development easier, and trying to save the world with a SQL will inevitably lead to a variety of problems.
Why this demand, may be because data relations comb a little hasty, data scattered in different data tables.
Anyway, this is a bad scene.

Oracle-Providing a dynamic result set for subqueries