Oracle列转行函数wm_concat版本不兼容解决方案

向右看齐 2021-09-17 05:26 341阅读 0赞

### 业务场景 ###

本博客记录一下Oracle列转行函数在Oracle11的一些不兼容问题，vm\_concat在一些业务场景是必须的。不过这个函数使用要谨慎，底层实现应该也是group by等等实现的，性能并不是特别好。这个函数在Oracle12是没有的，在Oracle11是不太兼容的，Oracle10可以正常使用。最近遇到这个问题，网上博客很多都写到了自定义列转行函数的办法去解决。但是这种办法并不一定适用所有的业务场景。我并没有采用。不过有些场景还是可以使用的。

### 网上优秀例子 ###

下面是网络记录比较详细的例子  
[https://www.cnblogs.com/hanzongze/p/oracle-wm\_concat.html][https_www.cnblogs.com_hanzongze_p_oracle-wm_concat.html]

### 解决方案 ###

通过自定义函数解决也是可以的，不过我并不是这样做的。  
下面介绍一下我的解决方法。首先分析一下，Oracle11不兼容vm\_concat列转行函数，并不代表其它函数不兼容，或许可以找到其它代替的，通过找资料，发现了Oracle11提供的另外一个函数：listagg()函数  
语法：listagg(参数，‘分隔符’) within group(order by 参数id)  
列子：`to_char(listagg(ur.user_role,',' ) within GROUP (order by (ur.user_role))) userrole,`

原来SQL：

select u.user_sex,
           u.full_name,
           u.user_code,
           to_char(wm_concat(ur.user_role)) userrole,
           to_char(wm_concat(r.role_name)) rolename,
           to_char(r.role_type) roletype,
           u.status
      from t_user u
      left outer join t_user_role ur
        on ur.user_code = u.user_code
      left outer join t_role r
        on r.user_role = ur.user_role
        //省略group by，vm_concat都需要group by的
    group by ....

首先通过SQL获取一下oracle版本号

<select id="getOracleVersion" resultType="string">
    	SELECT * FROM v$version WHERE banner LIKE 'Oracle%'
    </select>

下面代码仅供参考，就是获取oracle版本号，然后传到mybatis的xml文件

String oracleVersion = approveConfigDao.getOracleVersion();
    //eg:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    //eg:11.2.0.4.0
    //11.1版本和11.2版本用法不一样。
    String[] versionArr = StringUtils.split(oracleVersion, ".");
    if(versionArr[0].equals("11")){
    		String[] subVersionArr = StringUtils.split(versionArr[1], ".");
    		oracleVersion = versionArr[0]+"."+subVersionArr[0];
    }else{
    		oracleVersion = versionArr[0];
    }
    LOG.info("oracle版本:"+oracleVersion);
    map.put("oracleVersion", oracleVersion);

orm框架是用mybatis的，所以sql稍微改一下，oracle10调vm\_concat，oralce11的调listagg函数

select u.user_sex,
           u.full_name,
           u.user_code,
    <choose>
    						<when test="oracleVersion=='10' or oracleVersion=='11.1'">
    							to_char(wm_concat(ur.user_role)) userrole ,
    							to_char(wm_concat(r.role_name)) rolename ,
    						</when>
    						<otherwise>
    							to_char(listagg(ur.user_role,',' ) within GROUP (order by (ur.user_role))) userrole,
    							to_char(listagg(r.role_name,',' ) within GROUP (order by (r.role_name))) rolename,
    						</otherwise>
    </choose>
    to_char(r.role_type) roletype,
           u.status
      from t_user u
      left outer join t_user_role ur
        on ur.user_code = u.user_code
      left outer join t_role r
        on r.user_role = ur.user_role
        //省略group by ,vm_concat都需要group by的
        group by ...

[https_www.cnblogs.com_hanzongze_p_oracle-wm_concat.html]: https://www.cnblogs.com/hanzongze/p/oracle-wm_concat.html