如何接受动态游标结果集

Question

返回动态游标结果集的过程
create or replace procedure testRef(
      mycursor out sys_refcursor
       )
as
 
begin
       open mycursor for select *from emp;
end;
===================================================================
接收动态游标结果集的过程
create or replace procedure receiveCursor(
       shiiken_nendo varchar2,
       shiken_kuben varchar2
       )
as
       c1 sys_refcursor;       
begin
       exec testref(:c1);
       dbms_output.put_line('hello ,oracle');
end;
====================================================================
都是我自己写的测试用的 想法就是在第一个存储过程返回结果集，第二个存储过程能接收并赋值给一个游标变量
打什么帮帮忙，查了一晚上没解决

Answer 1

create or replace procedure receiveCursor(
  shiiken_nendo varchar2,
  shiken_kuben varchar2
  )
as
  c1 sys_refcursor;   
begin
  testref(c1);--此处改为这个
  dbms_output.put_line('hello ,oracle');
end;
/

Answer 2

--如果在其中需要使用游标呢，改为类似下面就可以了
create or replace procedure receiveCursor(
  shiiken_nendo varchar2,
  shiken_kuben varchar2
  )
as
  c1 sys_refcursor;   
  v_rec emp%rowtype;
begin
  testref(c1);
  loop
    fetch c1 into v_rec;
exit when c1%notfound;
    dbms_output.put_line(v_rec.empno);
  end loop;
end;
/
--测试
SQL> declare
  2   v_shiiken_nendo varchar2(100) :='test1';
  3   v_shiken_kuben varchar2(100) :='test2';
  4  begin
  5   receiveCursor(v_shiiken_nendo,v_shiken_kuben);
  6  end;
  7  /
7369                                                                            
7499                                                                            
7521                                                                            
7566                                                                            
7654                                                                            
7698                                                                            
7782                                                                            
7788                                                                            
7839                                                                            
7844                                                                            
7876                                                                            
7900                                                                            
7902                                                                            
7934                                                                            PL/SQL 过程已成功完成。