参考网址:
http://stackoverflow.com/questions/11541383/ordering-by-list-of-strings-in-oracle-sql-without-listagg
字符串拼接技巧和方式:
http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php
http://www.williamrobertson.net/documents/one-row.html
在进行使用WM_CONCAT或者自定义的聚合函数,进行拼串的时候,可能遇到拼串形成的结果集大于4000,这时候,系统会提示,超过系统限制。所以,在这个时候,最好的处理办法就是将结果集处理成CLOB格式,
下面共有两种处理方式:
1、使用函数
1 create or replace type str2tblType as table of varchar2(4000)
1 CREATE OR REPLACE FUNCTION tab2clob(p_str2tbltype str2tbltype,
2 p_delim IN VARCHAR2 DEFAULT ',') RETURN CLOB IS
3 l_result CLOB;
4 BEGIN
5 FOR cc IN (SELECT column_value
6 FROM TABLE(p_str2tbltype)
7 ORDER BY column_value) LOOP
8 l_result := l_result || p_delim || cc.column_value;
9 END LOOP;
10 RETURN ltrim(l_result, p_delim);
11 END;
初始化数据:
1 BEGIN
2 FOR idx IN 1 .. 10000 LOOP
3 INSERT INTO ts1 (tm) VALUES (sys_guid());
4 END LOOP;
5 END;
测试的SQL语句:
1 SELECT tab2clob(CAST(COLLECT(tm) AS str2tbltype)) attributes
2 FROM ts1
3 WHERE rownum < 1000
如下的SQL语句错误:由于类型不同
1 SELECT sys_util.tab2clob(CAST(COLLECT(deptno) AS str2tbltype))
2 FROM (SELECT DISTINCT deptno FROM emp)
会抛出如下的异常信息:
因为在str2tbltype中声明的是varchar2,但是现在deptno是数字,所以数据类型会发生不一致,所以,可以使用to_char见其进行转换,来避免上述的错误:
1 SELECT sys_util.tab2clob(CAST(COLLECT(to_char(deptno)) AS str2tbltype))
2 FROM (SELECT DISTINCT deptno FROM emp)
二:使用Oracle的SQL提供的处理XML的语句:XMLAGG()
SQL语句如下:
1 SELECT rtrim(xmlagg(xmlparse(content ename || ',' wellformed) ORDER BY ename)
2 .getclobval(),
3 ',') attributes,
4 deptno
5 FROM emp
6 GROUP BY deptno;
或者使用如下的语句,可以实现同样的功能:
参考网站:
http://www.williamrobertson.net/documents/one-row.html
1 SELECT deptno,
2 trim(xmlagg(xmlelement(content, ename || ',' ) ORDER BY ename)
3 .extract('//text()').getclobval())
4 AS concatenated
5 FROM emp
6 GROUP BY deptno;
下面的语句,没有调用getClobVal(),聚合的结果集是字符串
1 SELECT deptno,
2 trim(xmlagg(xmlelement(content, ename || ',' ) ORDER BY ename)
3 .extract('//text()'))
4 AS concatenated
5 FROM emp
6 GROUP BY deptno;
关于Oracle中XML的知识,请参考:
http://www.oratechinfo.co.uk/sqlxml.html
或者
http://www.cnblogs.com/caroline/archive/2012/11/15/2770887.html
I believe that we are who we choose to be. Nobody‘s going to come and save you, you‘ve got to save yourself.
我相信我们成为怎样的人是我们自己的选择。没有人会来拯救你,你必须要自己拯救自己。