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

http://www.cnitblog.com/wufajiaru/archive/2009/04/28/56796.html

Oracle 存储过程返回结果集

关键字: 转载

Sql代码

1. Oracle 存储过程返回结果集   
2.   
3. 过程返回记录集：    
4. CREATE OR REPLACE PACKAGE pkg_test    
5. AS    
6.     TYPE myrctype IS REF CURSOR;    
7.      
8.     PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);    
9. END pkg_test;    
10. /    
11.      
12. CREATE OR REPLACE PACKAGE BODY pkg_test    
13. AS    
14.     PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)    
15.     IS    
16.        sqlstr   VARCHAR2 (500);    
17.     BEGIN    
18.        IF p_id = 0 THEN    
19.           OPEN p_rc FOR    
20.              SELECT ID, NAME, sex, address, postcode, birthday    
21.                FROM student;    
22.        ELSE    
23.           sqlstr :=    
24.              'select id,name,sex,address,postcode,birthday    
25.             from student where id=:w_id';    
26.           OPEN p_rc FOR sqlstr USING p_id;    
27.        END IF;    
28.     END get;    
29. END pkg_test;    
30. /    
31.      
32. 函数返回记录集:    
33. 建立带ref cursor定义的包和包体及函数：    
34. CREATE OR REPLACE    
35. package pkg_test as    
36. /* 定义ref cursor类型    
37.     不加return类型，为弱类型，允许动态sql查询，    
38.     否则为强类型，无法使用动态sql查询;    
39. */    
40.    type myrctype is ref cursor;     
41.       
42. --函数申明    
43.    function get(intID number) return myrctype;    
44. end pkg_test;    
45. /    
46.       
47. CREATE OR REPLACE    
48. package body pkg_test as    
49. --函数体    
50.     function get(intID number) return myrctype is    
51.       rc myrctype;  --定义ref cursor变量   
52.       sqlstr varchar2(500);    
53.     begin    
54.       if intID=0 then    
55.          --静态测试，直接用select语句直接返回结果   
56.          open rc for select id,name,sex,address,postcode,birthday from student;    
57.       else    
58.          --动态sql赋值，用:w_id来申明该变量从外部获得   
59.          sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id';    
60.          --动态测试，用sqlstr字符串返回结果，用using关键词传递参数   
61.          open rc for sqlstr using intid;    
62.       end if;    
63.       
64.       return rc;    
65.     end get;    
66.       
67. end pkg_test;    
68. /   

1. ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

http://blog.youkuaiyun.com/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 ' ));

 

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝fetch into 显示两次

open v_cursor for  
 select 1from dual;
 
loop
      fetch v_cursor into v_num;

DBMS_OUTPUT.PUT_LINE(v_num);
     exit when v_cursor%notfound;      
    end loop;
    close v_cursor;

 

将DBMS_OUTPUT.PUT_LINE(v_num); 放到exit when v_cursor%notfound;  之后就不会显示两次了