Oracle11gr2分析函式新特性（二）

在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