Oracle中函数/过程返回结果集的几种方式:
??? 以函数return为例,存储过程只需改为out参数即可,在oracle 10g测试通过.
??? (1) 返回游标:
??????? return的类型为:SYS_REFCURSOR
??????? 之后在IS里面定义变量:curr SYS_REFCURSOR;
??????? 最后在函数体中写:
???????? open cur for
??????????? select ......;
???????? return cur;
??????? 例:
??????
CREATE OR REPLACE FUNCTION A_Test(
??????????????? orType varchar2
??????? )RETURN SYS_REFCURSOR
??????? is
?????????????? type_cur SYS_REFCURSOR;
??????? BEGIN
??????????? OPEN type_cur FOR
??????????????????? select col1,col2,col3 from testTable ;
????????????????? RETURN? type_cur;
??????? END;

??? (2)返回table类型的结果集:
??????? 首先定义一个行类型:
??????????
CREATE OR REPLACE TYPE "SPLIT_ARR"? AS OBJECT(nowStr varchar2(18))

??????? 其次以此行类型定义一个表类型:
????????
? CREATE OR REPLACE TYPE "SPLIT_TAB" AS TABLE of split_arr;

??????? 定义函数(此函数完成字符串拆分功能):
??????????
CREATE OR REPLACE FUNCTION GetSubStr(
?????????????????? str in varchar2, --待分割的字符串
?????????????????? splitchar in varchar2 --分割标志
??????????? )
??????????? return split_tab
??????????? IS
????????????? restStr varchar2(2000) default GetSubStr.str;--剩余的字符串
????????????? thisStr varchar2(18);--取得的当前字符串
????????????? indexStr int;--临时存放分隔符在字符串中的位置
????????????
????????????? v split_tab := split_tab(); --返回结果

??????????? begin
???????????????? dbms_output.put_line(restStr);
???????????????? while length(restStr) != 0
?????????????????? LOOP
???????????????????? <<top>>
???????????????????? indexStr := instr(restStr,splitchar); --从子串中取分隔符的第一个位置

???????????????????? if indexStr = 0 and length(restStr) != 0? then--在剩余的串中找不到分隔符
??????????????????????? begin
????????????????????????? v.extend;
????????????????????????? v(v.count) := split_arr(Reststr);
????????????????????????? return v;
??????????????????????? end;
???????????????????? end if;
???????????????????
???????????????????? if indexStr = 1 then---第一个字符便为分隔符,此时去掉分隔符
??????????????????????? begin
???????????????????????????? restStr := substr(restStr,2);
???????????????????????????? goto?? top;
??????????????????????? end;
???????????????????? end if;
???????????????????
???????????????????? if length(restStr) = 0 or restStr is null then
??????????????????????? return v;
???????????????????? end if;
??????????????????
???????????????????? v.extend;
???????????????????? thisStr := substr(restStr,1,indexStr - 1); --取得当前的字符串
???????????????????? restStr := substr(restStr,indexStr + 1);---取剩余的字符串

???????????????????? v(v.count) := split_arr(thisStr);
?????????????????? END LOOP;
???????????????? return v;
??????????? end;

??????? 在PL/SQL developer中可以直接调用
?????????
cursor strcur is
???????????????????????? select nowStr from Table(GetSubStr('111,222,333,,,',','));

??? (3)以管道形式输出:
??????
create type row_type as object(a varchar2(10), v varchar2(10));--定义行对象
??????? create type table_type as table of row_type; --定义表对象
??????? create or replace function test_fun(
??????????? a in varchar2,b in varchar2
??????? )
??????? return table_type pipelined
??????? is
??????????? v row_type;--定义v为行对象类型
??????? begin
????????? for thisrow in (select a, b from mytable where col1=a and col2 = b) loop
??????????? v := row_type(thisrow.a, thisrow.b);
??????????? pipe row (v);
????????? end loop;
????????? return;
??????? end;
??????? select * from table(test_fun('123','456'));

转载 http://www.db2china.net/home/space.php?uid=65425&do=blog&id=29997