sys_refcursor的用法实例

 
 

   
   

    
    

   
   
   
   

    
    

     
     --创建过程，参数为sys_refcursor，为out型
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     create 
     
     or 
     
     replace 
     
     procedure aabbsys_refcursor(o 
     
     out sys_refcursor) 
     
     is
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     begin
    
    
   
   

   
   

    
    

   
   
   
   

    
    
  
     
     open o 
     
     for 
     
     select * 
     
     from basplumain;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     end;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     ---
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     --测试过程，使用aabbsys_refcursor传出的值
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     create 
     
     or 
     
     replace 
     
     procedure aabb_test_sys_refcursor(
     
     return 
     
     out 
     
     int)
     
     is
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     type t_basplumain 
     
     is 
     
     table 
     
     of basplumain%rowtype;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     t_plu t_basplumain;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     v sys_refcursor;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     begin
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       aabbsys_refcursor(v);
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       fetch v bulk collect into t_plu;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       for i in 1..t_plu.count loop
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
         dbms_output.put_line(t_plu(i).pluname);
    
    
   
   

   
   

    
    

   
   
   
   

    
    
  
     
     end 
     
     loop;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     end;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     ---
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     --sqlplus测试脚本
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     --set serveroutput on
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     declare
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     v_return 
     
     int 
     
     default 
     
     0;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     begin
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       aabb_test_sys_refcursor(v_return);
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     end;
    
    
   
   
 
 

来源出自 https://blog.youkuaiyun.com/zml19910422/article/details/31747045