oracle 11g下的行转列pivot填坑--xmlType转clob或String

2019独角兽企业重金招聘Python工程师标准>>>

临近年关，我们给全公司用的API平台也到了要装逼的时刻，然而装逼利器还没搞完，那就是报表呈现，于是leader说你来搞一下吧。

echarts肯定是要的，报表呈现还是很完美的。然后就是数据库层面的了。我们装的是oracle 11g，这货提供了pivot来进行转列，然而它是有坑的，本次记录的就是这个。

pivot的定义很明确，https://www.oracle.com/technetwork/cn/articles/11g-pivot-101924-zhs.html 里面给出了一些例子。

官方例子：

select * from (
   select times_purchased, state_code
   from customers t
)
pivot                         -----使用pivot声明
(
   count(state_code)          -----聚合函数，用于聚合数据
   for state_code             -----for后面跟的是需要行转成列的字段，可以多个，用括号包起来，逗号分隔
    in ('NY','CT','NJ','FL','MO') ------转列后固定的列名
)
order by times_purchased

这个是静态的写法，缺陷是行转列的列名必须写死，传入string字符串拼接都不行，mybatis传入之后会报错。于是又来了一种动态写法：

select * from (
   select times_purchased as "Purchase Frequency", state_code
   from customers t
)
                               
pivot xml
(
   count(state_code)               
for state_code in (any)
)
order by 1

看着很完美对不对？用PL/SQL会告诉你，转换后的那一列是clob，而且里面放置的是xml

然而等你放到mybatis里，却发现无论如何都会报错，先让你加载一个xdb6的jar包，又让你加载xmlparserv2的jar，等你都加载完，又给你报错，说找不到binxml下的一个方法。你打开jar一开，果然没有，去oracle官网死活也找不到jar包，只能找到API。这时候就陷入了僵局，oracle这么坑的吗？官网上不是说是clob吗？

其实不是的，这货根本就是一个xmlType类型的数据，还没轮到mybatis懵逼，oracle自己的驱动就懵逼了。

这个时候你可能会想到oracle的xml解析函数，extractValue、extract之类，用了之后发现也不行。其实官网上没说的就是，类型转换。

把select * 干掉，换成自己的字段输出：

重点是getstringval()或者是getclobval()转换方法，将字段内容转换成可以解析的格式，然后mybatis就可以解析了，也可以去掉xdb6/xmlparserv2这类jar包了，直接输出的就是xml，然后用dom4j进行解析转换。甚至可以做成一个注解，实现自动解析。

这个坑费了我好几个小时，其实还是对oracle里的一些不常用的函数不熟导致的。网上大量的都是在plsql里写的sql，用mybatis这种在程序里编写的博客少，而且还有好多人告诉你，写死，写死，输出写死的sql，极其容易被误导。

转载于:https://my.oschina.net/forever9999/blog/2986344