Oracle中過程/函式返回結果集

abraham_dba_2013發表於2015-01-12
原文網址 : http://blog.itpub.net/29439655/viewspace-1399318/
Oracle函式
引用：http://blog.csdn.net/feiliu010/article/details/1538822
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'));

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29439655/viewspace-1399318/，如需轉載，請註明出處，否則將追究法律責任。

相關文章