//由数据字典codes集,获取数据字典names集--oracle方式 CREATE OR REPLACE FUNCTION GET_DICNAMES_BY_DICCODES( srcValues IN VARCHAR2,splitvalue IN VARCHAR2,dictype in varchar2) RETURN VARCHAR2 IS v_pos NUMBER; v_result VARCHAR2(2000) := '0'; v_code VARCHAR2(10); v_codeName VARCHAR2(40); v_valuestmp varchar2(2000); BEGIN v_valuestmp := srcValues; loop v_pos := instr(v_valuestmp,splitvalue,1,1); if v_pos =0 then v_code := v_valuestmp; else v_code := substr(v_valuestmp,v_pos-1); end if; begin select dic_name into v_codeName from dictionaryitem t where t.dic_type = dictype and dic_code = v_code; EXCEPTION WHEN OTHERS THEN v_codeName := v_code; end; if v_result = '0' then v_result := v_codeName; else v_result := v_result||','||v_codeName; end if; v_valuestmp := substr(v_valuestmp,v_pos+1); if v_pos = 0 then exit; end if ; end loop; RETURN v_result; EXCEPTION WHEN OTHERS THEN RETURN ''; END GET_DICNAMES_BY_DICCODES;
测试示例:
--select GET_DICNAMES_BY_DICCODES('01,02,03,04',','BUSI_TYPES') from dual;输出结果: 01_消费,02_退款,03_转账,04_提现 原文链接:https://www.f2er.com/oracle/208043.html