Oracle中过程/函数返回结果集

叁歲伎倆 2022-08-24 01:57 308阅读 0赞

**Oracle中过程/函数返回结果集**

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

![None.gif][] CREATE   OR   REPLACE   FUNCTION  A\_Test(  
![None.gif][]                orType  **varchar2**   
![None.gif][]        ) RETURN  SYS\_REFCURSOR  
![None.gif][]         is   
![None.gif][]               type\_cur SYS\_REFCURSOR;  
![None.gif][]         BEGIN   
![None.gif][]             OPEN  type\_cur  FOR   
![None.gif][]                     select  col1,col2,col3  from  testTable ;  
![None.gif][]                   RETURN   type\_cur;  
![None.gif][]         END ;

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

![None.gif][] CREATE   OR   REPLACE  TYPE "SPLIT\_ARR"   AS  OBJECT(nowStr  **varchar2** ( **18** ))

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

![None.gif][]    CREATE   OR   REPLACE  TYPE "SPLIT\_TAB"  AS   TABLE   of  split\_arr;

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

![None.gif][] CREATE   OR   REPLACE   FUNCTION  GetSubStr(  
![None.gif][]                    str   in   **varchar2** ,  \-- 待分割的字符串   
![None.gif][]                    splitchar  in   **varchar2**   \-- 分割标志   
![None.gif][]             )  
![None.gif][]             return  split\_tab  
![None.gif][]             IS   
![None.gif][]              restStr  **varchar2** ( **2000** )  default  GetSubStr. str ; \-- 剩余的字符串   
![None.gif][]               thisStr  **varchar2** ( **18** ); \-- 取得的当前字符串   
![None.gif][]               indexStr  **int** ; \-- 临时存放分隔符在字符串中的位置   
![None.gif][]                
![None.gif][]              v split\_tab : =  split\_tab();  \-- 返回结果   
![None.gif][]   
![None.gif][]             begin   
![None.gif][]                 dbms\_output.put\_line(restStr);  
![None.gif][]                  while  length(restStr)  !=   **0**   
![None.gif][]                   LOOP  
![None.gif][]                      << top >>   
![None.gif][]                     indexStr : =  instr(restStr,splitchar);  \-- 从子串中取分隔符的第一个位置   
![None.gif][]   
![None.gif][]                      if  indexStr  =   **0**   and  length(restStr)  !=   **0**    then \-- 在剩余的串中找不到分隔符   
![None.gif][]                          begin   
![None.gif][]                          v.extend;  
![None.gif][]                          v(v. count ) : =  split\_arr(Reststr);  
![None.gif][]                           return  v;  
![None.gif][]                         end ;  
![None.gif][]                      end   if ;  
![None.gif][]                      
![None.gif][]                      if  indexStr  =   **1**   then \-- \-第一个字符便为分隔符,此时去掉分隔符   
![None.gif][]                          begin   
![None.gif][]                             restStr : =  substr(restStr, **2** );  
![None.gif][]                              goto     top ;  
![None.gif][]                         end ;  
![None.gif][]                      end   if ;  
![None.gif][]                      
![None.gif][]                      if  length(restStr)  =   **0**   or  restStr  is   null   then   
![None.gif][]                         return  v;  
![None.gif][]                      end   if ;  
![None.gif][]                     
![None.gif][]                     v.extend;  
![None.gif][]                     thisStr : =  substr(restStr, **1** ,indexStr  \-   **1** );  \-- 取得当前的字符串   
![None.gif][]                      restStr : =  substr(restStr,indexStr  \+   **1** ); \-- \-取剩余的字符串   
![None.gif][]   
![None.gif][]                     v(v. count ) : =  split\_arr(thisStr);  
![None.gif][]                    END  LOOP;  
![None.gif][]                  return  v;  
![None.gif][]             end ;

在PL/SQL developer中可以直接调用

![None.gif][] cursor  strcur  is   
![None.gif][]                          select  nowStr  from   Table (GetSubStr( ' 111,222,333,,, ' , ' , ' ));

(3)以管道形式输出:

![None.gif][] create  type row\_type  as  object(a  **varchar2** ( **10** ), v  **varchar2** ( **10** )); \-- 定义行对象   
![None.gif][]          create  type table\_type  as   table   of  row\_type;  \-- 定义表对象   
![None.gif][]          create   or   replace   function  test\_fun(  
![None.gif][]            a  in   **varchar2** ,b  in   **varchar2**   
![None.gif][]        )  
![None.gif][]         return  table\_type pipelined  
![None.gif][]         is   
![None.gif][]            v row\_type; \-- 定义v为行对象类型   
![None.gif][]          begin   
![None.gif][]           for  thisrow  in  ( select  a, b  from  mytable  where  col1 = a  and  col2  =  b) loop  
![None.gif][]            v : =  row\_type(thisrow.a, thisrow.b);  
![None.gif][]             pipe  row (v);  
![None.gif][]           end  loop;  
![None.gif][]           return ;  
![None.gif][]         end ;  
![None.gif][]         select   \*   from   table (test\_fun( ' 123 ' , ' 456 ' ));

查看原文：[http://blog.csdn.net/feiliu010/archive/2007/03/23/1538822.aspx][http_blog.csdn.net_feiliu010_archive_2007_03_23_1538822.aspx]

[None.gif]: /images/20220823/da747b3fec0b4b63843fb766fff20100.png
[http_blog.csdn.net_feiliu010_archive_2007_03_23_1538822.aspx]: http://blog.csdn.net/feiliu010/archive/2007/03/23/1538822.aspx