转载

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执行错误,算是一种隐患。

正文到此结束
Loading...