oracle函数返回一个变量包含多个属性

Oracle中函数/过程返回结果集的3种方式，现总结如下：
    以函数return为例,存储过程只需改为out参数即可,在oracle 10g测试通过.
    (1) 返回游标:
        return的类型为:SYS_REFCURSOR
        之后在IS里面定义变量:curr SYS_REFCURSOR;
        最后在函数体中写:
         open cur for
            select ......;
         return cur;
        例:
      

CREATEORREPLACEFUNCTION A_Test(
                orTypevarchar2
        )RETURN SYS_REFCURSOR
       is
               type_cur SYS_REFCURSOR;
       BEGIN
           OPEN type_curFOR
                   select col1,col2,col3from testTable ;
                 RETURN  type_cur;
       END;

    (2)返回table类型的结果集:
        首先定义一个行类型:
          

CREATEORREPLACE TYPE "SPLIT_ARR" AS OBJECT(nowStrvarchar2(18))

        其次以此行类型定义一个表类型:
        

 CREATEOR REPLACE TYPE "SPLIT_TAB"ASTABLEof split_arr;

        定义函数(此函数完成字符串拆分功能):
          

CREATEORREPLACEFUNCTION GetSubStr(
                  strinvarchar2,--待分割的字符串
                   splitcharinvarchar2--分割标志
            )
           return split_tab
           IS
              restStrvarchar2(2000)default GetSubStr.str;--剩余的字符串
              thisStrvarchar2(18);--取得的当前字符串
              indexStrint;--临时存放分隔符在字符串中的位置
            
              v split_tab := split_tab();--返回结果

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

                    if indexStr= 0and length(restStr)!=0 then--在剩余的串中找不到分隔符
                       begin
                          v.extend;
                          v(v.count) := split_arr(Reststr);
                         return v;
                       end;
                    endif;
                   
                    if indexStr= 1then---第一个字符便为分隔符,此时去掉分隔符
                       begin
                             restStr := substr(restStr,2);
                            goto  top;
                       end;
                    endif;
                   
                    if length(restStr)=0or restStris nullthen
                       return v;
                    endif;
                  
                     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 strcuris
                        select nowStrfromTable(GetSubStr('111,222,333,,,',','));

    (3)以管道形式输出:
      

create type row_typeas object(a varchar2(10), vvarchar2(10));--定义行对象
       create type table_typeastableof row_type;--定义表对象
       createor replacefunction test_fun(
            ainvarchar2,binvarchar2
        )
       return table_type pipelined
       is
            v row_type;--定义v为行对象类型
       begin
         for thisrowin (select a, bfrom mytablewhere col1=aand col2= b) loop
            v := row_type(thisrow.a, thisrow.b);
           pipe row (v);
         end loop;
         return;
       end;
       select* fromtable(test_fun('123','456'));