转载

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语句

这函数主要可以做三类工作，

说的还是比较晦涩，现在暂时仅关注和这个需求有关的部分，

这么使用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执行错误，算是一种隐患。

正文到此结束