存储过程多表关联返回sys_refcursor，如何接收？

[lxd99423]

-- 存储过程申明
CREATE OR REPLACE PROCEDURE sp_o2oOrderDt(
    c_cursor OUT SYS_REFCURSOR,
    p_whid in varchar2 default '',
    p_orgid in char default '',
    p_orderid in varchar2 default ''
)
IS
BEGIN
  OPEN c_cursor FOR
  select a.goodsid,a.goodsname,b.entname
  from goodsdoc a join entdoc b on a.entid = b.entid;
END;

-- 调用
declare 
  ret sys_refcursor;
  f_billsn int;
begin
  f_billsn := 0;
  sp_o2oOrderDt(ret, p_whid => '', p_orgid => '', p_orderid => '');
  for rec in ret loop
    dbms_output.put_line(rec.goodsname);
  end loop;
end;

-- 调用时报错


因为实际关联了6张表，返回了几十个字段，不想挨个写字段，难道非得定义一个record类型来接收吗？或者能不能通过%rowtype类型来解决，高手指点一下

[H_Gragon]

不懂帮顶！

[卖水果的net]

-- SYS_REFCURSOR 的结果，只能通过  fetch .. into   这种形式遍历 
-- 用你的语句，简单的给你改了一个，你再研究一下

SQL> CREATE OR REPLACE PROCEDURE sp_o2oOrderDt(
  2      c_cursor OUT SYS_REFCURSOR,
  3      v_type varchar
  4  )
  5  IS
  6  BEGIN
  7    OPEN c_cursor FOR
  8    select object_id, object_name from user_objects where object_type = v_type ;
  9  END;
 10  /
Procedure created
SQL> -- 调用
SQL> set serverout on;
SQL> declare
  2    ret sys_refcursor;
  3    id int;
  4    name varchar(30);
  5  begin
  6  
  7    sp_o2oOrderDt(ret, v_type => 'TABLE');
  8    loop
  9        fetch ret into id, name ;
 10        exit when ret%NOTFOUND;
 11        dbms_output.put_line (id || ':  '|| name);
 12    end loop;
 13  end;
 14  /

73532:  YYYY
73529:  XX
73851:  TTT
74185:  C
PL/SQL procedure successfully completed
SQL> drop procedure sp_o2oOrderDt ;
Procedure dropped

SQL> 

[sych888]

create or replace procedure test_ref_cursor(v_cursor out sys_refcursor) as begin open v_cursor for select * from emp; end; --方法一，通过SQL PLUS 前台调用存储过程，代码块如下 SQL> declare 2 type v_cursor is ref cursor RETURN emp%RowType; 3 v_cur v_cursor; 4 v_temp v_cur%rowtype; 5 begin 6 test_ref_cursor(v_cur); 7 loop 8 exit when v_cur%notfound; 9 fetch v_cur into v_temp; 10 dbms_output.put_line(v_temp.ename); 11 end loop; 12 close v_cur; 13 end; 14 / --方法二，代码如下： declare type v_cursor is ref cursor; v_cur v_cursor; v_temp emp%rowtype; begin open v_cur for select * from emp; loop exit when v_cur%notfound; fetch v_cur into v_temp; dbms_output.put_line('v_temp='||v_temp.ename); end loop; close v_cur; end;