ref游标和普通游标（自定义游标）最大的区别是游标的结果集在什么地获取。
普通游标在声明的时候，获得结果集，例子 cursor v_cur is select * from t_test;
而ref游标在打开的时候，获得结果集，
例子：open infolist for select bi.* from bi_customer cf;
1.获得结果集
declare
type refcursor is ref cursor; --ref游标类型
infolist refcursor; --集合
customer bi_customer%rowtype; --行
customercode bi_customer.customercode%type;--字段
customername bi_customer.corporation%type;
begin
open infolist for
select bi.* from bi_customer cf; --全部
loop
fetch infolist
into customer;
exit when infolist%notfound;
dbms_output.put_line('客户编号为;：'||''||customer.customercode||', 地址为：'||customer.address );
end loop;
close infolist;
end;
2 过程限定结果集,带返回记录数
代码
create or replace package ord is--包
type ref_type is ref cursor;
procedure p_order(firstindex in number,--开始下标
lastindex in number,--结束下标
recordnum out number,--记录数
infolist out ref_type--结果集
);
end ord;
create or replace package body ord is
procedure p_order(firstindex in number,
lastindex in number,
recordnum out number,
infolist out ref_type) as
sql_tempstr varchar2(400);
sql_countstr varchar(500);
begin
sql_tempstr := 'select *
from (select rownum num, o.* from orders o) s
where 1>0';
if (firstindex is not null and lastindex is not null) then
sql_tempstr := sql_tempstr || 'and num between ' || firstindex ||
' and
' || lastindex;
end if;
sql_countstr := ' select count(*) from(' || (sql_tempstr) || ') ';
dbms_output.put_line(sql_tempstr);
dbms_output.put_line(sql_countstr);
open infolist for sql_tempstr;
Execute Immediate sql_countstr
into recordnum;
end p_order;
end ord;