Stored procedure multi-table associated return sys_refcursor, how to receive?-CodePudding

 
- stored procedure declaration 
The CREATE OR REPLACE PROCEDURE sp_o2oOrderDt (
C_cursor OUT SYS_REFCURSOR, 
P_whid varchar2 in default ', 
P_orgid char in default ', 
P_orderid varchar2 in default '
) 
IS 
The BEGIN 
The OPEN c_cursor FOR 
The select of al-qeada oodsid al-qeada oodsname, b.e ntname 
The from goodsdoc a join entdoc b on a.e ntid=b.e ntid; 
The END;

 
- the call 
Declare 
Ret sys_refcursor; 
F_billsn int. 
The begin 
F_billsn:=0; 
Sp_o2oOrderDt (ret, p_whid=& gt; "', p_orgid=& gt; "', p_orderid=& gt; "); 
For rec in ret loop 
Dbms_output. Put_line (rec. Goodsname); 
End loop; 
end;

- the call times wrong

Because the actual connection table 6, returned to the dozens of field, don't want to write each field, don't have to define a record type to receive? Or can be addressed by % rowtype type, ace gives directions

CodePudding user response:

Don't understand to the top!

CodePudding user response:

 
- SYS_REFCURSOR as a result, only through the fetch.. Into this form traversal 
- use your statement, simply give you change a, you'll study the 

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

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

SQL>

CodePudding user response:

The create or replace procedure test_ref_cursor (v_cursor out sys_refcursor)
As
The begin
The open v_cursor for select * from emp;
end;

- a method, through SQL PLUS front desk call a stored procedure, the code block below

SQL> Declare
2 type v_cursor is ref cursor RETURN emp % RowType;
3 v_cur v_cursor;
4 v_temp v_cur % rowtype;
5 the begin
6 test_ref_cursor (v_cur);
7 loop
8 the exit when v_cur % notfound;
9 the fetch v_cur into v_temp;
10. Dbms_output put_line (v_temp ename);
11 end loop;
12 close v_cur;
13 the end;
14/

- method 2, the code is as follows:

Declare
Type v_cursor is ref cursor;
V_cur v_cursor;
V_temp emp % rowtype;
The begin
The open v_cur for select * from emp;
Loop
Exit the when v_cur % notfound;
The fetch v_cur into v_temp;
V_temp dbms_output. Put_line ('='| | v_temp, ename);
End loop;
The close v_cur;
end;