传出游标过程:
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;
调用过程:
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; /*定義遊標類型*/
V_CUR c_type; /*定義一個遊標*/
TYPE rule_record IS RECORD( /*定義記錄集類型*/
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; /*定義記錄集*/
begin
pub_mes2erpauto_cur_kt(V_FLAG, RES, V_cur); FETCH v_cur BULK COLLECT /*批量綁定數據集*/
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;