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

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


11gr2还新增了一个分析函数LISTAGG，这个函数的功能实现字符串的连接


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

SQL> select * from v$version;


BANNER


--------------------------------------------------------------------------------


Oracle Database11gEnterprise Edition Release11.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:字符串连接的结果过长


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


oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html