11g中利用listagg函数实现自动拼接INSERT语句

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 11g中利用listagg函数实现自动拼接INSERT语句

本来今天想继续写另一篇外传,但总是熬这么晚不是个事儿,况且今儿北京又输了,恨铁不成钢,堵得慌。。。

白天工作忙,晚上看娃睡了之后才有一些时间可以随便写一些,总结一下,记录一下,算是让自己内心的各种问题抒发释放一下。碰巧打开电脑,有位测试的同事下午留言问了一个问题,一想干脆今儿休息一下,写篇短小精悍的,更接地气一些的文章,至少还是工作中可以用到的,这位同事的留言是这样,

我怎么从一个表中提取 所有字段   一个表字段太多 我要写insert的语句 一个个粘字段 好费劲。。。

首先,11.2版本中限制每张表最多可以有1000列,
11g中利用listagg函数实现自动拼接INSERT语句
记不清楚了,好像是侯松的书中曾说,若超过256个字段的记录,可能就会出现行链接,所以表中字段多少还是要根据业务需求和非功能需求,综合考虑。

扯远了,我们继续说这个需求,现在有一张表,字段很多,要拼接插入的SQL语句,方法其实很多,比如:复制“desc 表名”的结果,然后放入UE编辑器中编辑,再用替换功能拼接出字段间的逗号可能是非常直接的一种方法。

但我们是程序员,这么做在非常紧急的情况下,容不得有过多时间考虑的时候可以,可未免还是有些LOW。

换个思路,这个问题是否可以这么考虑,
1.我现在要得到一张表所有字段的列表,字段间需要使用逗号分隔。
2.表字段会存储于数据字段表中,例如user_tab_cols,是否可以考虑借助他来拼接。
3.一张表所有字段在user_tab_cols中是按照行(column_name列)来存储的,我们现在其实需要的是将column_name列转换为行且用逗号分隔开。

现在的问题就是如何得到列转行的结果。检索了一些网上的资料,有些使用case when,有些使用decode函数,但这些前提是需要知道有多少列需要转换为行,现在我们的问题中是不知道这些,其实Oracle还是有行列转换的函数可以直接做这个工作,例如wm_concat函数可以做。

1.创建测试表
11g中利用listagg函数实现自动拼接INSERT语句

2.使用函数wm_concat,
11g中利用listagg函数实现自动拼接INSERT语句
提示了ORA-00904错误,为什么?

Maclean Liu解释的很清楚(http://www.askmaclean.com/archives/wmsys-wm_concat.html),

对于该函数,Oracle官方的态度是其从来没有将该函数列入任何官方文档中,这个函数仅仅是让Oracle Dev研发在针对内部对象例如SYS的存储过程、字典表等使用的,并没有鼓励普通的应用开发者去使用该WMSYS.WM_CONCAT函数,但是由于部分应用开发者发现了这个函数,而且觉得较为好用,所以在应用程序编写过程中大量使用该函数,其结果是由于Oracle对该函数在后续版本中的修改(包括fix、增强)乃至于完全去掉这个函数都是有可能的。

我这使用的是11.2版本的库,因此这函数其实已经被删除了,所以才会报ORA-00904错误。

3.Oracle在11.2中其实还是推出了listagg函数,作为可以实现行列转换的新特性。语法如下,
11g中利用listagg函数实现自动拼接INSERT语句

这函数主要可以做三类工作,
11g中利用listagg函数实现自动拼接INSERT语句

说的还是比较晦涩,现在暂时仅关注和这个需求有关的部分,
11g中利用listagg函数实现自动拼接INSERT语句

这么使用listagg函数,就可以将user_tab_cols的column_name字段行转换为列,并用逗号分开。

如果再“懒”一些,
11g中利用listagg函数实现自动拼接INSERT语句
甚至可以定制一些脚本可以自动化生成常用的SQL语句。

总结
1.借助user_tab_cols视图和11g新特性listagg函数,可以实现行列转换的需求。
2.10g版本可以使用wm_concat函数实现行列转换,可毕竟这函数不是官方推荐的函数。
3.测试同事要求使用带列名的INSERT语句,这点其实还是非常好的,我不清楚开发是否这么做,因为若仅用INSERT INTO TABLE VALUES(…)来写,未来表字段有变更,很可能忘记改,就会导致SQL执行错误,算是一种隐患。

如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!

11g中利用listagg函数实现自动拼接INSERT语句
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 11g中利用listagg函数实现自动拼接INSERT语句


 上一篇
一个执行计划异常变更的案例 – 外传之聚簇因子(Clustering Factor) 一个执行计划异常变更的案例 – 外传之聚簇因子(Clustering Factor)
之前的几篇文章:《一个执行计划异常变更的案例 - 前传》《一个执行计划异常变更的案例 - 外传之绑定变量窥探》《一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法》《一个执行计划异常变更的案例 - 外传之rolling inva
下一篇 
一个执行计划异常变更的案例 – 外传之查询执行计划的几种方法 一个执行计划异常变更的案例 – 外传之查询执行计划的几种方法
之前的几篇文章: 《一个执行计划异常变更的案例 - 前传》 《一个执行计划异常变更的案例 - 外传之绑定变量窥探》 《一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法》 《一个执行计划异常变更的案例 - 外传之rolli