主要说明RETURNPIPELINED，经验者请绕道。最近我得到了一个需求：要求为method传入String，内容如用户ID0,用户ID1,用户ID2...，然后根据这些ID返回一个结果集作
正好ORACLE方面博客还没有这方面内容，趁此机会便写一小段。
既然我们要返回一个结果集，那便是要得到一个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;
本文出自 “It's SWFUpload!!” 博客，，请务必保留此出处
Statement：The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
