Oracle数据库用SQL语句转换数据字典（一个字段多个值）

最近遇到一个让人非常头疼的问题，在开发过程中一个视图需要将字段的Code转换成数据字典中的Name,例：需要转换的字段格式：（code,code1,code2）转换成格式为：（name,name1,name2)字段以逗号分隔多个值，分享一下脱坑的过程。

1、首先想到得是这种数据用循环处理是最方便的，但是视图中不能用循环非常操蛋，然后想着写一个函数（FUNCTION）来循环处理这种数据,但是查了一下需要做的工作量很大，而且需求需要判断指定的code才翻译，没有指定不翻译为空，FUNCTION实现起来有点费劲而且不灵活弃用。

2、然后想着这种字段能不能转换成列然后就可以去和数据字典一一对应去匹配了，处理思路如下图

 

3、思路确定了就开始吧

     （1）以逗号分隔的一行转多行函数：

WITH SPLIT_DATA AS --数据字典转换格式：data(code,code,code)转换为：date(name,name,name)临时表
     (SELECT KEY,
             RN,
              目标表ID（替换成自己的）,
             SUBSTR(STR,
                    INSTR(STR, ',', 1, RN) + 1,
                    INSTR(STR, ',', 1, RN + 1) - INSTR(STR, ',', 1, RN) - 1) STR
        FROM (SELECT 目标字段（替换成自己的） KEY,
                     ',' || 目标字段（替换成自己的）|| ',' STR,
                     目标表ID（替换成自己的）
                FROM 目标表) A,
             (SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM < 10) B
       WHERE INSTR(STR, ',', 1, RN + 1) > 0
       ORDER BY  目标表ID（替换成自己的）,RN)

 

（2）匹配数据字典并进行行转列：

SELECT 
        WM_CONCAT(B.PARAM_NAME) AS PARAM_NAME, --行转列 （wm_concat Oracle12去除了此函数建议用listagg函数效果大同小异）
       PROJECT_ID
   FROM SPLIT_DATA A --临时表需要与上边SQL一起跑
   LEFT JOIN (SELECT *
                FROM COMM_PARAM_DICT T 
               WHERE T.CLS_CODE = 'SPECIAL_CLAUSE_TYPE') B --数据字典表
     ON A.STR = B.PARAM_CODE
  WHERE A.STR IN ('put', 'rateAdj', 'redeem')--只需要转换的字段code
  GROUP BY PROJECT_ID) T5
     ON T1.PROJECT_ID = T5.PROJECT_ID

 

（3）效果如下

注意：因为我需要转换其中的三个Code所以我限制了 全部转换的话把WHERE去掉就可以

（4）完整代码（需要吧标注字段更换成自己）

WITH SPLIT_DATA AS --数据字典转换格式：data(code,code,code)转换为：date(name,name,name)
 (SELECT KEY,
         RN,
         PROJECT_ID, --目标表ID（需要更换成自己的）
         SUBSTR(STR,
                INSTR(STR, ',', 1, RN) + 1,
                INSTR(STR, ',', 1, RN + 1) - INSTR(STR, ',', 1, RN) - 1) STR
    FROM (SELECT SPECIAL_CLAUSE_TYPE KEY, --目标字段（需要更换成自己要翻译的字段）
                 ',' || SPECIAL_CLAUSE_TYPE || ',' STR, --目标字段（需要更换成自己要翻译的字段）
                 PROJECT_ID --目标表ID（需要更换成自己的）
            FROM ABS_PROJ_PRPS_INFO) A, --目标表（需要更换成自己的）
         (SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM < 10) B
   WHERE INSTR(STR, ',', 1, RN + 1) > 0
   ORDER BY PROJECT_ID, RN) --目标表ID（需要更换成自己的）

SELECT A.PROJECT_ID,
       C.SPECIAL_CLAUSE_TYPE AS 替换前字段,
       WM_CONCAT(B.PARAM_NAME) AS 替换后字段
  FROM SPLIT_DATA A --上边的临时表
  LEFT JOIN ABS_PROJ_PRPS_INFO C --目标表（需要更换成自己的）
    ON A.PROJECT_ID = C.PROJECT_ID
  LEFT JOIN (SELECT *
               FROM COMM_PARAM_DICT T
              WHERE T.CLS_CODE = 'SPECIAL_CLAUSE_TYPE') B --数据字典表（需要更换成自己的）
    ON A.STR = B.PARAM_CODE
 WHERE A.STR IN ('put', 'rateAdj', 'redeem') --（限制只翻译这三个代码，不需要限制的话吧WHERE去掉即可）
 GROUP BY A.PROJECT_ID, C.SPECIAL_CLAUSE_TYPE

 

（5）大功告成

 

                                                                                                                   --曾梦想仗剑走天涯，看一看世界的繁华。