oracle中使用wm_concat函数的方法及弊端(解决办法)

oracle中使用wm_concat函数的方法及弊端(解决办法)

2017年11月11日 11:32:40 专心写bug 阅读数：22542

该函数在10版本推出，可以把列值以英文逗号分隔起来并显示成一行，例子：

1.  SQL> create table test(id number,name varchar2(20));  

2.  SQL> insert into test values(1,'a');  

3.  SQL> insert into test values(1,'b');  

4.  SQL> insert into test values(1,'c');  

5.  SQL> insert into test values(2,'d');  

6.  SQL> insert into test values(2,'e');  

7.  SQL> commit;  

8.  效果1 : 行转列  

9.  SQL> select wm_concat(name) from test;  

10. WM_CONCAT(NAME)  

11. -------------------------------------------------------------------------  

12. a,b,c,d,e  

13. 效果2: 把结果里的逗号替换成"|"  

14. SQL> select replace(wm_concat(name),',','|') from test;  

15. REPLACE(WM_CONCAT(NAME),',','|')  

16. -----------------------------------------------------------------------  

17. a|b|c|d|e

我们经常用TO_CHAR包含转换好的记录，这样可以正常展示成字符串(因为在PL_SQL中只用wm_concat的话可能查出的是BLOB字段)

以上参考地址：http://www.jb51.net/article/37604.htm

 

重点来了：wm_concat有长度限制或者版本不支持，为了解决这弊端，我们进行重新写一个函数代替

函数名暂且定位TO_STRING

CREATE OR REPLACE FUNCTION "TO_STRING" (table_in    in varchar2_table,

                           delimiter_in in varchar2 default ',')

   return varchar2 is

   v_idx pls_integer;

   v_str varchar2(32767);

   v_dlm varchar2(10);

 begin

   v_idx := table_in.first;

   while v_idx is not null loop

     v_str := v_str || v_dlm || table_in(v_idx);

     v_dlm := delimiter_in;

     v_idx := table_in.next(v_idx);

   end loop;

   return v_str;

 end to_string;

上面函数中有个varchar2_table，这个是我们建立在oracle的type

CREATE OR REPLACE TYPE "VARCHAR2_TABLE"  as table of varchar2(4000)

 

代替的用法如下：

select to_char(wm_concat(id))代替为：

select TO_STRING(cast(collect(to_char(id)) as varchar2_table))