有俩种方法:
一种是声明系统游标,一种是声明自定义游标,然后后面操作一样,参数类型为
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;