oracle将表字段逗号分隔的值进行拆分,并替换值

需求背景：需要源数据变动，需要对历史表已存的字段值根据源数据进行更新。如果是单字段存值，直接根据映射表关联修改即可。但字段里面若存的值是以逗号分割，比如旧值：‘old1,old2,old3’，要根据映射关系调整为'new1,new2,new3'。

要求：出初始化更新脚本；

一、数据准备

我的历史表为 test_table, 需要对MUTI_VALUE字段进行数据更新

这是映射表 YINSE_TABLE,分别存着新值和旧值

结果为：(old22没有匹配)

二、SQL逻辑编写

第一步：拆分

先将test_table表进行拆分，以下是拆分Sql,将muti_value字段按逗号分隔全部查询出来，以SINGEELEMENT表示被拆分后的旧值。

SELECT t.RULE_ID ,t.RULE_NAME ,TRIM(REGEXP_SUBSTR(t.MUTI_VALUE , '[^,]+', 1, LEVEL)) AS singeElement,t.MUTI_VALUE  
FROM test_table t
CONNECT BY
LEVEL <= REGEXP_COUNT(t.MUTI_VALUE, '[^,]+')
AND PRIOR t.RULE_ID = t.RULE_ID 
AND PRIOR SYS_GUID() IS NOT NULL ;

第二步：映射

将以拆分的查询结果关联映射表；先将以上查询结果做成临时表，即使用with as创建临时表split_value，再将临时表跟映射表YINGSE_TABLE做关联，用singelement关联old_value，得到新值。然后得到rule_id 和 new_value的映射关系

SELECT DISTINCT t.RULE_ID ,t.NEW_VALUE FROM (
WITH split_value AS (
SELECT t.RULE_ID ,t.RULE_NAME ,TRIM(REGEXP_SUBSTR(t.MUTI_VALUE , '[^,]+', 1, LEVEL)) AS singeElement,t.MUTI_VALUE  
FROM test_table t
CONNECT BY
LEVEL <= REGEXP_COUNT(t.MUTI_VALUE, '[^,]+')
AND PRIOR t.RULE_ID = t.RULE_ID 
AND PRIOR SYS_GUID() IS NOT NULL
)
SELECT p.OLD_VALUE ,p.NEW_VALUE ,s.*  FROM split_value s
INNER JOIN YINSE_TALBE p ON s.singeElement = p.OLD_VALUE
) t WHERE t.NEW_VALUE IS NOT NULL ;

第三步：组装合并，更新

将rule_id跟new_value的值再次组装成以逗号分隔的数据，通过rule_id，一次行将历史表的值替换成新值;先组装数据：

SELECT t.RULE_ID , LISTAGG(t.new_value, ',') WITHIN GROUP (ORDER BY t.NEW_VALUE) AS newMutiValue FROM ( SELECT DISTINCT t.RULE_ID ,t.NEW_VALUE FROM (WITH split_value AS (SELECT t.RULE_ID ,t.RULE_NAME ,TRIM(REGEXP_SUBSTR(t.MUTI_VALUE , '[^,]+', 1, LEVEL)) AS singeElement,t.MUTI_VALUE  FROM test_table tCONNECT BYLEVEL <= REGEXP_COUNT(t.MUTI_VALUE, '[^,]+')AND PRIOR t.RULE_ID = t.RULE_ID AND PRIOR SYS_GUID() IS NOT NULL)SELECT p.OLD_VALUE ,p.NEW_VALUE ,s.*  FROM split_value sINNER JOIN YINSE_TALBE p ON s.singeElement = p.OLD_VALUE) t WHERE t.NEW_VALUE IS NOT NULL 
) t GROUP BY t.RULE_ID ;

然后根据这个结果更新到test_table表中，使用rule_id进行update；以下是更新语句：

MERGE INTO test_table t
USING (SELECT t.RULE_ID , LISTAGG(t.new_value, ',') WITHIN GROUP (ORDER BY t.NEW_VALUE) AS newMutiValue FROM ( SELECT DISTINCT t.RULE_ID ,t.NEW_VALUE FROM (WITH split_value AS (SELECT t.RULE_ID ,t.RULE_NAME ,TRIM(REGEXP_SUBSTR(t.MUTI_VALUE , '[^,]+', 1, LEVEL)) AS singeElement,t.MUTI_VALUE  FROM test_table tCONNECT BYLEVEL <= REGEXP_COUNT(t.MUTI_VALUE, '[^,]+')AND PRIOR t.RULE_ID = t.RULE_ID AND PRIOR SYS_GUID() IS NOT NULL)SELECT p.OLD_VALUE ,p.NEW_VALUE ,s.*  FROM split_value sINNER JOIN YINSE_TALBE p ON s.singeElement = p.OLD_VALUE) t WHERE t.NEW_VALUE IS NOT NULL ) t GROUP BY t.RULE_ID 
) s ON (t.RULE_ID = s.RULE_ID)
WHEN MATCHED THEN UPDATE SET t.MUTI_VALUE = s.newMutiValue;

最后查询test_table表