Oracle 游标记录使用示例

create table userTabel( 
  userid number(10), 
  username varchar2(100), 
  constraint PK_USERID PRIMARY KEY(userid) 
); 
commit; 

insert into userTabel values(1,'Albert'); 
insert into userTabel values(2,'reboot') ;
insert into userTabel values(3,'Jeff');


create or replace package pkg_BB is
-- Author  : ADMINISTRATOR
  -- Created : 2008-07-17 8:35:52
  -- Purpose : 
  
  -- Public type declarations
  type mycur is ref cursor;  
  type myrecord is record( 
                          myid usertabel.userid%type,
                          myname usertabel.username%type);
  procedure pro_GetCur(cur_return out mycur);
  
end pkg_BB;

create or replace package body pkg_BB is
  -- Function and procedure implementations
  procedure pro_GetCur(cur_return out mycur)  
  is    
  begin
   open cur_return for select * from Usertabel;    
  end pro_GetCur;
end pkg_BB;

测试代码：

declare
  rec pkg_bb.myrecord;
  cur pkg_bb.mycur;
begin
 pkg_bb.pro_GetCur(cur);
 
 /*;
  close cur;
 dbms_output.put_line(rec.myid);
 dbms_output.put_line(rec.myname);*/
 loop 
 fetch cur into rec;
 exit when cur%notfound;
 dbms_output.put_line(rec.myid);
 dbms_output.put_line(rec.myname);
 end loop;
 close cur;
 end;

输出结果：
1
Albert
2
reboot
3
Jeff