oracle select 存储过程 游标,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;