sql：翻译存在逗号的字典

函数1，获取翻译字典表

        tj_bmk.t_code_all：字典表名

        code_name：代码中文名

        code_id：代码

         file：代码类别

CREATE OR REPLACE FUNCTION codedetail (inkind IN VARCHAR2, incode IN VARCHAR2)
   RETURN VARCHAR2
IS
   detailvalue   tj_bmk.t_code_all.code_name%TYPE;
BEGIN

   IF inkind IS NULL OR incode IS NULL
   THEN
      RETURN NULL;
   END IF;

  SELECT nvl(code_name,code_id)
   INTO detailvalue
     FROM tj_bmk.t_code_all where code_id=incode AND fiel=inkind;


   RETURN detailvalue;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN incode;
     -- RETURN '';
END;

函数2，翻译包括用逗号隔开的代码

 先创建上面的函数，然后下面这个函数直接创建即可

create or replace function f_transation_detail(inkind varchar2,in_zdryxl in varchar2) return varchar2 is
  res varchar2(2000);
  start_index number := 1;
  end_index number := 1;
begin

  end_index := instr(in_zdryxl,',',start_index);
  if end_index + 1 > 1 then
    res := codedetail(inkind,substr(in_zdryxl, start_index, end_index - 1));
    start_index := end_index;
    while end_index + 1 > 1 loop
        end_index := instr(in_zdryxl,',',start_index + 1);
        if end_index = 0 then
          res := res ||','||codedetail(inkind,substr(in_zdryxl,start_index + 1));
        else
          res := res ||','||codedetail(inkind,substr(in_zdryxl,start_index + 1,end_index - start_index - 1));
        end if;
        start_index := end_index;
    end loop;
  else
    res := codedetail(inkind,in_zdryxl);
  end if;
  return(res);
end;