Oracle多行转换成字符串方法总结

作者：TangYP

 

问题提出：在做一些比较复杂的DB数据导出时，有时会要求“将不固定的多行数据组合成一个字符串返回”。

例子：ISV Portal中就遇到了类似的情况，要求对于每一个APP，返回其所属的所有类目名称，类目名称之间用[,]隔开。

本文就用此例子来介绍。在具体陈述实现方案之前，我们先介绍下我们即将操作的表结构：

1. SQL> desc app_category_link;
   Name                 Type         Nullable Default Comments     
   -------------------- ------------ -------- ------- -------------
   APP_CATEGORY_LINK_ID VARCHAR2(20)                  主关键       
   APP_ID               VARCHAR2(20)                  应用ID       
   APP_CATEGORY_ID      VARCHAR2(20)                  应用类别ID   

其中字段APP_ID和APP_CATEGORY_ID是一对多关系；

对于该类型的问题，总结一下大致有如下几种常见方案：

方案1：sys_connect_by_path + start with ... connect by ... prior + 分析函数

从上面的这个公式中我们可以看出，该方案主要是通过分析函数和父子级联查询来完成，一般是一条SQL搞定，比较省事。首先来看几个具体的实现SQL。
具体实现1：

1. SELECT app_id,
2.        ltrim(max(sys_connect_by_path(app_category_id, ',')), ',') categ_ids
3.   FROM (SELECT app_id,
4.                app_category_id,
5.                app_category_id || '|' || rn rchild,
6.                app_category_id || '|' || (rn - 1) rfather
7.           FROM (SELECT app_id,
8.                        app_category_id,
9.                        row_number() over(PARTITION BY app_id ORDER BY app_category_id) rn
10.                   FROM app_category_link))
11.  START WITH rfather LIKE '%|0'
12. CONNECT BY PRIOR rchild = rfather
13.  GROUP BY app_id;

具体实现2：

1. select app_id,
2.        ltrim(max(sys_connect_by_path(app_category_id, ',')), ',') categ_ids
3.   from (select t.app_id,
4.                t.app_category_id,
5.                min(t.app_category_id) over(partition by app_id) categ_min,
6.                (row_number() over(order by app_id, app_category_id)) +
7.                (dense_rank() over(order by app_id)) numid
8.           from app_category_link t)
9.  start with app_category_id = categ_min
10. connect by numid - 1 = prior numid
11.  group by app_id;

具体实现3：

1. select app_id,
2.        ltrim(max(sys_connect_by_path(app_category_id, ',')), ',') categ_ids
3.   from (select t.app_id,
4.                t.app_category_id,
5.                (row_number()
6.                 over(partition by app_id order by app_category_id)) numid
7.           from app_category_link t)
8.  start with numid = 1
9. connect by numid - 1 = prior numid
10.        and app_id = prior app_id
11.  group by app_id;

具体实现4：

1. select app_id,
2.        ltrim(sys_connect_by_path(app_category_id, ','), ',') categ_ids
3.   from (select t.app_id,
4.                t.app_category_id,
5.                (row_number()
6.                 over(partition by app_id order by app_category_id)) numid
7.           from app_category_link t)
8.  WHERE connect_by_isleaf = 1
9.  start with numid = 1
10. connect by numid - 1 = prior numid
11.        and app_id = prior app_id;

请注意看4种实现方式的区别，下面分别介绍下这4种实现方式的具体思路；
第1种实现采用了1个分析函数、2次子查询、一个like、以及父子级联查询字段值连接；可以猜测下性能肯定不咋的，2次子查询本来已经很耗时了，对查询出来的结果集还要用like匹配，速度就更慢了，此法可以查询到我们需要的具体数据，但是效率很低，不可取；他的实现思路是利用待查询字段值与各APP下面各类目ID的序列值进行组合，并作为父子关系级联的依据；
第2种实现采用了3个分析函数、1次全表扫描、以及父子级联字段值连接；和第1种实现比较而言的话效率会高不少；他的实现思路是利用各APP对应的最小类目ID作为父子级联的开始点，而父子级联的依据是row_number()+dense_rank()，这样做主要是为了避免无限循环；
第3、4两种实现思路基本上是一样的，都是1个分析函数、1次全表扫描、以及父子级联字段值连接；从代码长度来说，比前2种实现方式简洁了不少，思路也清晰了很多，直接利用各APP对应类目ID的序列值作为父子级联的开始点和连接依据；但仔细看看两者的SQL，会发现第3这种方式用到了group by子句，而第4种实现却没有用到，而是在where子句中添加了connect_by_isleaf = 1 的查询条件；从性能上来看，应该是第4种实现方式更高，但他只能在10g及其以后的版本中才能使用，connect_by_isleaf 字段是10g中新提供的一个伪列，他可以用来判断该条记录是否是树形记录的叶节点，不过还在用9i版本的可能就有些可惜了；
综合以上分析，对4种实现方案，个人推荐使用第3、4两种实现方式，具体哪种可以看所用oracle的版本而定，简而言之，这种实现方式优雅、简洁、高效；

方案2：自定义Function/SP

在Oracle中提供了非常强大的自定义Function/SP功能，对于该需求，如果说大家觉得方案1中的SQL太长太复杂，那完全可以考虑这种方案；但是和java开发一样，相同的功能不同的人员来实现都会有不同的效果，代码的质量也都会不一样；下面我们就从不同方面来介绍几种function的具体实现方式；
实现1：最简单明了、但可扩展性极差；对于我们需要的该表app_category_link写一个特定的function，传入app_id，然后在function内部查询出该app_id对应的所有类目ID信息，然后对结果集循环，最后将连接成的字符串返回；若有很多类似的表都有这样的查询，就得写多个对应的function，所以扩展性极差，但却最简单、有效；
实现2：实现较复杂、但可扩展性较好；这种实现方式从实现1演变而来，主要是为了解决多表问题；需要将待查询表名、待查询字段key-value、需连接的字段名 作为参数传入function，function内部会组装这些参数并形成select语句，查询并对返回结果集循环连接；
实现3：实现简单、可扩展性好；不会吧！鱼和熊掌能够兼得？呵呵，当然可以，实现2中的思路值得借鉴，但是扩展性仍然不够，因为function内部需要组装查询sql，当然最好的方式是分工明确，function中只针对结果集进行循环连接，而不需要关心结果集是如何得来的，这和实际java开发有异曲同工之妙！
针对以上3种实现方式，很明显实现3占据几乎所有的优势，推荐采用；以下贴一段参考代码；
function实现：

1. create or replace type strings_table is table of varchar2(20);
2. /
3. create or replace function mymerge (pv in strings_table) return varchar2
4. is
5.   ls varchar2(4000);
6. begin
7.   for i in 1..pv.count loop
8.     ls := ls || ',' || pv(i);
9.   end loop;
10.   return ls;
11. end;
12. /

具体调用function的SQL实现：

1. select t0.app_id,
2.        mymerge(cast(multiset (select t.app_category_id
3.                        from app_category_link t
4.                       where t.app_id = t0.app_id) as strings_table)) categ_ids
5.   from (select distinct app_id from app_category_link) t0;

方案3：Oracle 10g + wmsys.wm_concat

可能我们提到的这个功能太普遍了，oracle在10g及其以上版本提供了一个内置函数，可以搞定类似需求，用了这个function之后代码变得异常简单，只能说太优雅喽；具体参见如下SQL：

1. select app_id, wmsys.wm_concat(app_category_id) categ_ids
2.   from app_category_link t
3.  group by t.app_id;

注意：该函数好像不支持对待查询字段排序，也就是说无法对app_category_id连接的顺序进行指定；若要完成该功能，只能利用子查询先用order by排序好，然后再调用该函数；
另外，由于该函数是加密过的，看不到源代码，所以不知道function内部具体是如何实现的，是不是采用我们上面方案2中所提到的某一种呢？
其实在10g里面，还有一个MODEL SQL，也可以实现类似的功能，不过我还没有研究过，暂时略过，有兴趣的同学可以先研究研究；

上面提到了很多的针对不固定行转换成字符串的实现方案，从各方案的比较来看，当然推荐用户升级到10g，这样就有很多的高级功能可以使用了；不过在现实需求中，还有一些是固定行转换成字符串，对于这种需求想象下，实现应该会更简单，呵呵，但实际效果却并没有我们想象中那么好，其实这些实现都是人肉级的查询，那都有哪些实现方式呢？
其一，MAX + DECODE / CASE：该组合经常被用于固定行转换成固定多列，我们只要稍微变化下就可以满足我们的固定行转换成字符串的需求了，用 || 或者concat函数将各列连接起来即可；
其二，ROW_NUMBER + LEAD：这种组合主要是利用分析函数来实现，因为分析函数可以很容易的提取到当前记录之前或者之后N条记录的值，所以当我们明确知道有多少行需要连接组合时，就可以利用LEAD这个分析函数来搞定；

对于本文中提到的一些Oracle所特有的function或者是SQL语法，请参考我前面的几篇文章：
用START WITH...CONNECT BY PRIOR子句实现递归查询：http://blog.csdn.net/sfdev/archive/2008/06/03/2508621.aspx
ORACLE进阶之三：分析函数：http://blog.csdn.net/sfdev/archive/2008/02/23/2115244.aspx