Introduction to new features of Oracle 11g R2 analysis Function (ii) analysis function Listagg

In 11GR2, Oracle analysis functions are further enhanced.

This article introduces the new analysis function Listagg.

11GR2 also adds an analytic function Listagg, the function of which implements string concatenation.

In 11GR2, Oracle has finally implemented this analytic function:

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 (), type VARCHAR2 (20));

Table has been created.

sql> INSERT INTO T-select RowNum, object_name, object_type from Dba_objects;

71968 lines have been created.

Sql> commit;

Submit completed.

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

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45457.htm

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

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 rows have been selected.

This is the 3 uses of this function, which is a clustered function without group by, which contains the aggregate functions of group BY, and analytic functions.

The most powerful feature of this new analysis function is the ability to sort with a custom aggregate function.

Of course, this function returns the VARCHAR2 type, which is still not resolving the problem of a long return string:

Sql> Select Listagg (name) within group (Order by ID)

2 from T;

From T

*

Line 2nd Error:

ORA-01489: The result of string concatenation is too long

To resolve this problem, you need to customize the aggregate function: