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

转载地址：http://blog.csdn.net/feiliu010/archive/2007/03/23/1538822.aspx 

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