oracle游标返回日期,Oracle 游标返回结果集

Oracle 游标返回结果集

有俩种方法:

一种是声明系统游标,一种是声明自定义游标,然后后面操作一样,参数类型为

in out 或out

(1)声明个人系统游标.(推荐)

create or replace p_temp_procedure

(

cur_arg out sys_refcursor;     --方法1

)

begin

open cur_arg for select * from tablename;

end

调用

declare

cur_calling sys_refcursor;

begin

p_temp_procedure(cur_calling);  --这样这个游标就有值了

for rec_next in cur_calling loop

....

end loop;

end;

create or replace procedure getList(id in varchar2,mycur out sys_refcursor)

is

v_sql varchar2(4000);

begin

dbms_output.put_line('call it success');

v_sql:='select * from userbean s where s.id=:1';

open mycur for v_sql using id;

commit;

end;

create or replace procedure my_procedure

(mypageNo number,mypageSize number,myInfo_Cursor out sys_refcursor)

as

pageNO number;

pageSize number;

begin

open myInfo_Cursor for

select * from product

where pid between 1 + (pageNo - 1) * pageSize and 4 + (pageNo - 1) * pageSize;

end;

declare

my_cursor sys_refcursor;

begin

my_procedure(1,2,my_cursor);

for rec_next in my_cursor loop

dbms_output.put_line('pid:'||rec_next.pid||'  ptypeid:'||rec_next.ptypeid||'  proname:'||rec_next.proname||'  price:'||rec_next.price||'  pronum:'||rec_next.pronum);

end loop;

end;