Oracle多值字典解析

Oracle多值字典解析

问题描述：

一般情况下，业务数据中存储的字典值为单值，比如"1"或"2"，直接关联字典表后，可以解析出字典值；
但有时因业务要求，需要存储多选值，比如：“1,2,11,12”，此时，无法直接关联字典表进行解析；
本文讲解如下通过函数,进行多值字典解析；

适用范围

oracle 11g

业务字典表：

GUPID	GUPNAME
1	分组1
2	分组2
3	分组3
4	分组4
5	分组5
11	分组11
12	分组12

业务数据表：

ID	GUPIDS
1	1,2,3
2	1,11,12
3	4,5

最终需要解析成如下结果：

ID	GUPID	GUPNAME
1	1	分组1
1	2	分组2
1	2	分组3
2	1	分组1
2	11	分组11
2	12	分组12
3	4	分组4
3	5	分组5

解决办法：

通过组合使用 CONNECT BY LEVEL，regexp_substr 语法可以解析出字典内容。

/* 业务字典表 */
create table tb_dic as 
  select 1 as gupId, '分组1' as gupName from dual union all 
  select 2 as gupId, '分组2' as gupName from dual union all 
  select 3 as gupId, '分组3' as gupName from dual union all 
  select 4 as gupId, '分组4' as gupName from dual union all 
  select 5 as gupId, '分组5' as gupName from dual union all 
  select 11 as gupId, '分组11' as gupName from dual union all 
  select 12 as gupId, '分组12' as gupName from dual 
;

/* 业务数据表 */
create table tb_data as 
  select 1 as id, '1,2,3' as gupIds from dual union all 
  select 2 as id, '1,11,12' as gupIds from dual union all 
  select 3 as id, '4,5' as gupIds from dual 
;

/* 分析业务数据中，存在的字典值数 */
select x1.id, x1.gupIds, x0.gupId, x0.gupName
from tb_dic x0,
  (select id, gupIds,length(gupIds) - length(replace(gupIds, ',')) + 1 as dics
  from tb_data
  ) x1 ,
  (/* 用于生成连续数值 */
  select LEVEL as rid from dual CONNECT BY LEVEL <= (select max(gupId) from tb_dic x0)
  ) x2 
where regexp_substr(x1.gupIds, '[^,]+',1,x2.rid)=x0.gupId
and x2.rid <=x1.dics
order by 1,3
;

或者，整合上面的内容为一条sql，输出结果一样，如下：

with  x0 as (/* 业务字典表 */
  select 1 as gupId, '分组1' as gupName from dual union all 
  select 2 as gupId, '分组2' as gupName from dual union all 
  select 3 as gupId, '分组3' as gupName from dual union all 
  select 4 as gupId, '分组4' as gupName from dual union all 
  select 5 as gupId, '分组5' as gupName from dual union all 
  select 11 as gupId, '分组11' as gupName from dual union all 
  select 12 as gupId, '分组12' as gupName from dual 
), x1 as (/* 业务数据表 */
  select 1 as id, '1,2,3' as gupIds from dual union all 
  select 2 as id, '1,11,12' as gupIds from dual union all 
  select 3 as id, '4,5' as gupIds from dual 
), x2 as (/* 分析业务数据中，存在的字典值数 */
select id, gupIds,length(gupIds) - length(replace(gupIds, ',')) + 1 as dics
from x1
)
select x2.id, x2.gupIds, x0.gupId, x0.gupName
  from x2,x0,
    (/* 用于生成连续数值 */
    select LEVEL as rid from dual CONNECT BY LEVEL <= (select max(gupId) from x0)
    ) t 
where regexp_substr(x2.gupIds, '[^,]+',1,t.rid)=x0.gupId
and t.rid <=x2.dics
order by 1,3