-- 存盤程序申明
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型別來解決，高手指點一下
uj5u.com熱心網友回復：
不懂幫頂！
uj5u.com熱心網友回復：
-- 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>
uj5u.com熱心網友回復：
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/109239.html
標籤：開發
下一篇：求大神幫小白看一道題