Oracle-provides dynamic result sets for subqueries

Oracle-provides dynamic result sets for subqueries

We have encountered such a requirement: to input a String for the method, such as "User ID0, user ID1, user ID2... ", then return a result set based on these IDs as a data table for query by others.
Can SELECT * from tbl where id in ('user ID0, user ID1, user id2') solve the problem?
However, the result set cannot be obtained through a simple SELECT statement.

Let me clarify the problems to be solved:

After passing such a String parameter to FUNCTION, how can we make it RETURN a result set dynamically for other SELECT statements.

Since we want to return a result set, we need to get a table of xxx type. XXX can be VARCHAR2 or INTEGER or % rowtype of a TABLE, but my situation is a little more complicated, I want to create an OBJECT TYPE myself.

The RETURN type of the FUNCTION to be written is created as follows:
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;

The following describes how to create a FUNCTION:
Create or replace function REGROUP_USER_BY_USERIDSTR (useridstr in VARCHAR2)
RETURN TYP_USER_TBL
PIPELINED IS

-- Parameter Declaration starts
TYPE USER_CURSOR is ref cursor;
USER_INFO_LIST USER_CURSOR; -- the CURSOR used to obtain the search result

TYPE USER_ROW is record (
USER_ID CHAR (40), USER_NUM VARCHAR2 (200), CREATE_DATE DATE );
USER_INFO USER_ROW; -- used to extract RECORD of records in CURSOR

USER_ROW4RESULT TYP_USER_RECORD; -- the data row object of the dataset to be returned
QUERYSTR VARCHAR2 (2000); -- SELECT statement after Splicing
-- Parameter declaration ends

BEGIN
-- Various judgments are made based on the input ID to concatenate SELECT statements and assign values to QUERYSTR.
OPEN USER_INFO_LIST for querystr; -- OPEN CURSOR
-- Cyclically obtain the result from CURSOR and convert the result to the TYP_USER_RECORD object. Then, put the object in 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 return type is of the table type, you can use the TABLE () function for query during the call.
SELECT * from table (REGROUP_USER_BY_USERIDSTR)

In addition, the persistence framework used in my current project is MyBatis, and this statement is executed correctly.
Although the parameter can be directly passed into SELECT * from xx in () for query, it may also need to be truncated to COLLECION. below is the FUNCTION of this 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;

This method may only make development easier. Trying to save the world with an SQL statement will inevitably lead to various problems.
Why is there such a requirement? It may be because the data relationship is too hasty and the data is scattered across different data tables.
In any case, this is a bad scenario.