db2 多值数据字典解析

问题描述：

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

适用范围

DB2 9.7/10.5

示例数据

业务字典表：

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

解决办法：

首先创建3个解析函数，分别为：f_elemIdx，f_split，f_exists_dic_item；
然后给出函数的调用方式。

/*函数：f_elemIdx*/
CREATE FUNCTION f_elemIdx ( pi_str CLOB(64K) )
   RETURNS TABLE ( ordinal INTEGER, index INTEGER )
   LANGUAGE SQL
   --DETERMINISTIC
   NO EXTERNAL ACTION
   --CONTAINS SQL
   F1: BEGIN ATOMIC
   RETURN
      WITH t(ordinal, index) AS
         ( VALUES ( 0, 0 )
           UNION ALL
           SELECT ordinal+1, COALESCE(NULLIF(
                     -- find the next delimiter ','
                     LOCATE(',', pi_str, index+1), 0),
                     LENGTH(pi_str)+1)
           FROM   t
                  -- to prevent a warning condition for infinite
                  -- recursions, we add the explicit upper
                  -- boundary for the "ordinal" values
           WHERE ordinal < 100 AND
                  -- terminate if there are no further delimiters
                  -- remaining
                  LOCATE(',', pi_str, index+1) <> 0 )
      SELECT ordinal, index
      FROM   t
      UNION ALL
      -- add indicator for the end of the string
      SELECT MAX(ordinal)+1, LENGTH(pi_str)+1
      FROM   t
      ORDER BY 1;
    END;

/*函数：f_split*/
CREATE FUNCTION f_split( string VARCHAR(255) )
   RETURNS TABLE ( elements VARCHAR(255) )
   LANGUAGE SQL
   DETERMINISTIC
   NO EXTERNAL ACTION
   --CONTAINS SQL
   F1: BEGIN ATOMIC
   RETURN
      WITH t(ordinal, index) AS
         ( SELECT ordinal, index
           FROM   TABLE ( f_elemIdx(string) ) AS x )
      SELECT SUBSTR(string, t1.index+1, t2.index - t1.index - 1)
      -- the join below makes sure that we have the lower and
      -- upper index where we can find each of the ',' delimiters
      -- that are separating the elements. (For this, we exploit
      -- the additional indexes pointing to the beginning and end
      -- of the string.)
      FROM   t AS t1 JOIN t AS t2 ON
                ( t2.ordinal = t1.ordinal+1 ) ;
  
END;

/*函数：f_exists_dic_item*/
CREATE FUNCTION f_exists_dic_item(col VARCHAR(255), item VARCHAR(255))
LANGUAGE SQL
RETURNS char(1)
DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
	Declare sReturn VARCHAR(255);  
    SET sReturn = (
    	SELECT elements FROM TABLE(f_split(col)) WHERE elements = item
    );
    IF sReturn IS NULL THEN 
    	RETURN '0';
    ELSE 
    	RETURN '1';
    END IF;
END;

/*
db2创建别名，兼容oracle dual
*/
CREATE ALIAS DUAL FOR SYSIBM.DUAL;

/*
db2多值字典解析
*/
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 
)
SELECT x1.id, x1.gupIds, x0.gupId, x0.gupName 
FROM x1,x0
WHERE f_exists_dic_item(x1.gupIds,x0.gupId)='1'
ORDER BY 1,3
;