-- 存储过程申明
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类型来解决,高手指点一下
解决方案 »
- 关于oracle比较库结构的问题。
- 请问在oracle登陆中出现的问题
- 触发器:更新当前表的时候报错ora-04091
- 如何备份含有LONG RAW域的数据库表格???
- SQL执行效率怎么这么低
- oracle9i 插入中文乱码
- 关于电信CRM开发的问题
- oracle(8.1.6)的联机帮助.是怎样搞的?
- 问:应用序列的表中的关键字问题?
- in out pkg_sspq_po_submat.cur_sspq_po_submat
- 12C 12.1.0.2 @Linux 6.5: [INS-20802] Creating Container Database for Oracle Grid
- 中文版CentOS下,安装ORACLE12C,图形界面乱码问题
-- 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 droppedSQL>
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;