在11gr2中，Oracle分析函数的功能进一步增强。

这篇介绍新增的分析函数LISTAGG。

Oracle11gr2分析函数新特性（一）：http://yangtingkun.itpub.net/post/468/491115

11gr2还新增了一个分析函数LISTAGG，这个函数的功能实现字符串的连接。在9i中可以用自定义聚集函数来实现：http://yangtingkun.itpub.net/post/468/3380

在10g中可以利用新增功能来实现：http://yangtingkun.itpub.net/post/468/484171

在11gr2中，Oracle终于实现了这个分析函数：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create table t (id number, name varchar2(30), type varchar2(20));

表已创建。

SQL> insert into t select rownum, object_name, object_type from dba_objects;

已创建71968行。

SQL> commit;

提交完成。

SQL> select listagg(name, ',') within group (order by id)
2 from t
3 where rownum < 10;

LISTAGG(NAME,',')WITHINGROUP(ORDERBYID)
-------------------------------------------------------------------------------------------
SYS_C00644,SYS_LOB0000000528C00002$$,KOTTB$,SYS_C00645,SYS_LOB0000000532C00002$$,KOTAD$,SYS_C00646,SYS_LOB0000000536C00002$$,KOTMD$

SQL> select type, listagg(name, ',') within group (order by id) name
2 from t
3 where type in ('DIRECTORY', 'JAVA SOURCE', 'SCHEDULE')
4 group by type;

TYPE NAME
-------------------- ---------------------------------------------------------------------
DIRECTORY ORACLE_OCM_CONFIG_DIR,DATA_PUMP_DIR,XMLDIR
JAVA SOURCE dbFWTrace,schedFileWatcherJava
SCHEDULE DAILY_PURGE_SCHEDULE,FILE_WATCHER_SCHEDULE,BSLN_MAINTAIN_STATS_SCHED

SQL> select name,
2 listagg(name, ',') within group (order by id) over(partition by type) s_name
3 from t
4 where type in ('DIRECTORY', 'JAVA SOURCE', 'SCHEDULE');

NAME S_NAME
------------------------- -----------------------------------------------------------
ORACLE_OCM_CONFIG_DIR ORACLE_OCM_CONFIG_DIR,DATA_PUMP_DIR,XMLDIR
DATA_PUMP_DIR ORACLE_OCM_CONFIG_DIR,DATA_PUMP_DIR,XMLDIR
XMLDIR ORACLE_OCM_CONFIG_DIR,DATA_PUMP_DIR,XMLDIR
dbFWTrace dbFWTrace,schedFileWatcherJava
schedFileWatcherJava dbFWTrace,schedFileWatcherJava
DAILY_PURGE_SCHEDULE DAILY_PURGE_SCHEDULE,FILE_WATCHER_SCHEDULE,BSLN_MAINTAIN_STATS_SCHED
FILE_WATCHER_SCHEDULE DAILY_PURGE_SCHEDULE,FILE_WATCHER_SCHEDULE,BSLN_MAINTAIN_STATS_SCHED
BSLN_MAINTAIN_STATS_SCHED DAILY_PURGE_SCHEDULE,FILE_WATCHER_SCHEDULE,BSLN_MAINTAIN_STATS_SCHED

已选择8行。

上面是这个函数的3种用法，分别是不带GROUP BY的聚集函数，包含GROUP BY的聚集函数，和分析函数。

和自定义聚集函数相比，这个新增分析函数的最大功能是实现了排序的功能。

当然这个函数返回的是VARCHAR2类型，也就是仍然没有解决返回字符串超长的问题：

SQL> select listagg(name) within group (order by id)
2 from t;
from t
*第 2 行出现错误:
ORA-01489: 字符串连接的结果过长

要解决这个问题，还是需要自定义聚集函数：

字符串连接超长的解决（一）：http://yangtingkun.itpub.net/post/468/482093

字符串连接超长的解决（二）：http://yangtingkun.itpub.net/post/468/482122