有倆種方法:
一種是聲明系統游標,一種是聲明自定義游標,然后后面操作一樣,參數類型為
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;