[ORACLE] uses FUNCTION to dynamically obtain the result set

This article mainly describes RETURNPIPELINED. If you have experience, Please bypass it. Recently, I got a requirement: to input a String for the method, such as user ID0, user ID1, user ID2...., and then return a result set based on these IDs.

This article mainly describes RETURNPIPELINED. If you have experience, Please bypass it. Recently, I got a requirement: to input a String for the method, such as user ID0, user ID1, user ID2...., and then return a result set based on these IDs.

Just because the ORACLE blog does not have such content, take this opportunity to write a short article.

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 -- the parameter Declaration starts: TYPE USER_CURSOR is ref cursor; USER_INFO_LIST USER_CURSOR; -- CURSORTYPE USER_ROW is record (USER_ID CHAR (40), USER_NUM VARCHAR2 (200), CREATE_DATE DATE); USER_INFO USER_ROW; -- used to extract RECORDUSER_ROW4RESULT TYP_USER_RECORD of records in CURSOR; -- The data row object QUERYSTRVARCHAR2 (2000) of the dataset to be returned ); -- SELECT statement after splicing -- parameter declaration ends BEGIN -- various SELECT statements are judged based on the input ID and QUERYSTR is assigned an OPEN USER_INFO_LIST for querystr value; -- open CURSOR -- cyclically obtain the result from CURSOR and change the result to a pipeline object. Then, put the object in PIPE, 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 runs correctly.

Although the parameter can be directly passed into SELECT * from xx in () for query, it may also need to be truncated to COLLECION. The FUNCTION of this FUNCTION is attached below:

Create or replace type TBL_VARCHAR2 as table of VARCHAR2 (400); create or replace function STR2TBL (PARAM_STR IN VARCHAR2) RETURN Returns Using ASTMP_RECORD long default PARAM_STR | ','; ROW_INDEXNUMBER; TMP_TBL limit: = TBL_VARCHAR2 (); rows: = 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 article is from "It's SWFUpload !!" Blog, please keep this source