Oracle用存储过程返回游标

传出游标过程:

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;