oracle 多值数据字典值校验与转换

oracle 多值数据字典值校验与转换

业务数据字典列大部分情况下为单值，如(1 男 或 2 女)；
但有时候会出现多值的情况，如饮食习惯：如（1，嗜热，2，嗜咸），并使用逗号分隔多值，如：（1,2）;
当需要进行字典解析或转换时，多值字典数据转换比较麻烦，首先需要通过某个函数判定字典值是否在字段中，
然后通过decode函数进行转换，

检查字典值是否存在数据列中

/*
检查字典值是否存在数据列中
参数值：
p_col 数据列名称
p_value 字典值
p_split 分隔符

返回值：
如果存在字典值，返回1，否则返回0
*/
create or replace function f_instr(p_col varchar2, p_value varchar2, p_split varchar2 := ',')
  return int as
  v_idx       integer;
  v_str       varchar2(500);
  v_strs_last varchar2(4000) := p_col;
  v_rtn       int := 0;
begin
  <<f_loop>>
  loop
    if v_strs_last is null or v_strs_last = '' then
      v_rtn := 0;
      exit f_loop;
    end if;

    v_idx := instr(v_strs_last, p_split);
    exit when v_idx = 0;

    v_str       := substr(v_strs_last, 1, v_idx - 1);
    v_strs_last := substr(v_strs_last, v_idx + 1);

    if v_str = p_value then
      v_rtn := 1;
      exit f_loop;
    end if;

  end loop;

  if v_strs_last = p_value then
    v_rtn := 1;
  end if;

  return v_rtn;
end f_instr;

-----------------------------------------------------------
-- 函数测试
drop table test1;
create table test1(
id varchar(10),
code  varchar(10)
);
insert into test1 values(1,'1,2,3');
insert into test1 values(2,'4');
insert into test1 values(3,'5');
insert into test1 values(4,null);
insert into test1 values(5,'');
insert into test1 values(6,'12,15');
commit;

select * from test1;

-- 编码转换
select id, code as src_code, 
decode(f_instr(code,'1'),1,'4 ') ||
decode(f_instr(code,'2'),1,'3 ') ||
decode(f_instr(code,'3'),1,'2 ') ||
decode(f_instr(code,'4'),1,'1 ') 
as dest_code
from test1