Oracle中函数/过程返回结果集的几种方式:

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

这里面我有疑问，返回游标的话那怎么调用里面的值呢，大家帮我想想，不会用哦