Outgoing cursor process:
Create or replace procedure pub_mes2erpauto_cur_kt (I _flag in varchar2,
Res out varchar2,
O_cur out sys_refcursor) Is
V_class varchar2 (20 );
V_classstr varchar2 (100 );
V_mmark varchar2 (20 );
V_mmarkstr varchar2 (100 );
V_mtype varchar2 (20 );
V_mtypestr varchar2 (100 );
V_cursql varchar2 (1000 );
Begin
If length (I _flag) = 3 then
V_class: = substr (I _flag, 1, 1 );
V_mmark: = substr (I _flag, 2, 1 );
V_mtype: = substr (I _flag, 3, 2 );
RES: = 'OK ';
Else
Res: = 'error ';
End if;
If v_class <> '0' then
V_classstr: = 'and class_id =' | v_class;
End if;
If v_mmark <> '0' then
V_mmarkstr: = 'and model_flag = ''' | v_mmark | '''';
End if;
If v_mtype <> '0' then
V_mtypestr: = 'and model_type = ''' | v_mtype | '''';
End if;
V_cursql: = 'select pro_name
From emesp. tp_mes2erp_auto_kt
Where group_flag = ''ccauto ''' | v_classstr |
V_mmarkstr | v_mtypestr;
Open o_cur for v_cursql;
Exception
When others then
Res: = 'error ';
End pub_mes2erpauto_cur_kt;
Call process:
Create or replace procedure PUB_MO_mes2erpcc_KT (v_back_id in varchar2,
V_fLAG IN VARCHAR2,
Res out VARCHAR2) is
V_index_id VARCHAR2 (20 );
V_startdate date;
V_mesdate date;
V_erpdate date;
V_ SQL varchar2 (1100 );
-- V_CUR sys_refcursor;
TYPE c_type is ref cursor;/* define the parameter TYPE */
V_CUR c_type;/* define a benchmark */
TYPE rule_record is record (/* specify the TYPE of the key set */
Pro_name emesp. tp_mes2erp_auto_kt.pro_name % type );
TYPE type_ruleconf is table of rule_record index by BINARY_INTEGER;
Rowsa type_ruleconf;/* define a record set */
Begin
Pub_mes2erpauto_cur_kt (V_FLAG, RES, V_cur );
FETCH v_cur bulk collect/* batch Set */
INTO rowsa;
For I in 1 .. rowsa. COUNT loop
Begin
V_ SQL: = 'begin' | rowsa (I). pro_name | 'end ;';
Execute immediate v_ SQL
Using in v_back_id, out res;
End;
End loop;
EXCEPTION
WHEN OTHERS THEN
RES: = 'error ';
End PUB_MO_mes2erpcc_KT;