Oracle中函数/过程返回多个值（结果集）

浏览数：14 / 时间：2015年06月12日

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‘));