LISTAGG関数の新機能を試す（Oracle Database 12c Release 2）

# LISTAGG関数
Oracle Databaseには行列の入れ替えを行う関数としてLISTAGGが提供されています。この関数は取得した行データを単一の文字列に連結します。
LISTAGG関数では各行データを連結する際に、レコードのセパレータを指定することができます。連結する順番はWITHIN GROUP (ORDER BY)句で指定します。
 Oracle Database 12cでは、文字列長があふれた場合の挙動を変更できるようになりました。
LISTAGG関数の仕様は[マニュアル](https://docs.oracle.com/cd/E82638_01/SQLRF/LISTAGG.htm)に記載されています。

# データの準備
この検証で使用するデータはTPC-DSで定義されたcustomerテーブルです。SQL*Loaderを使って100,000件のレコードを格納します。

|列名|データ型|NOT NULL|
|:--|:--|:--|
| C_CUSTOMER_SK |                            NUMBER(38)| NOT NULL |
| C_CUSTOMER_ID |                            CHAR(16)|NOT NULL |
| C_CURRENT_CDEMO_SK|                                 NUMBER(38)|
| C_CURRENT_HDEMO_SK |                                NUMBER(38)|
| C_CURRENT_ADDR_SK   |                               NUMBER(38)|
| C_FIRST_SHIPTO_DATE_SK |                            NUMBER(38)|
| C_FIRST_SALES_DATE_SK  |                            NUMBER(38)|
| C_SALUTATION          |                             CHAR(10)|
| C_FIRST_NAME           |                            CHAR(20)|
| C_LAST_NAME           |                             CHAR(30)|
| C_PREFERRED_CUST_FLAG |                             CHAR(1)|
| C_BIRTH_DAY           |                             NUMBER(38)|
| C_BIRTH_MONTH         |                             NUMBER(38)|
| C_BIRTH_YEAR          |                             NUMBER(38)|
| C_BIRTH_COUNTRY       |                             VARCHAR2(20)|
| C_LOGIN               |                             CHAR(13)|
| C_EMAIL_ADDRESS       |                             CHAR(50)|
| C_LAST_REVIEW_DATE    |                             CHAR(10)|

# データベースの設定
Oracle Database 12cではVARCHAR2型の最大文字列長が32,768バイトまで拡張できるようになりました。ただし検証環境ではテスト環境ではデフォルトのままです。このためVARCHAR2型の最大長は4,000バイトになります。

```
SQL> SHOW PARAMETER max_string_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD
SQL>
```

# LISTAGG関数の実行
## 正常実行時
LISTAGG関数が正常に実行される場合、行レコードで取得された値が単一の文字列に変換されます。
下記は、セパレータとしてコロン（:）を指定しています。条件としてc_last_review_date列と、c_birth_day列を指定しています。

```
SQL> SELECT
  2  LISTAGG(TRIM(c_first_name), ':') WITHIN GROUP (ORDER BY c_customer_id)
  3  FROM customer WHERE c_last_review_date='2452648' AND c_birth_day=31;

LISTAGG(TRIM(C_FIRST_NAME),':')WITHINGROUP(ORDERBYC_CUSTOMER_ID)
--------------------------------------------------------------------------------
Christine:Gerald:Eliseo:Vivian:Michael:Walter:Gregory:Bernice
```

c_first_name列の値がコロンを区切りとして、単一の文字列に連結されていることがわかります。TRIM関数を指定しているのはc_first_name列がCHAR型であるためです。VARCHAR2型の場合は空白は出力されません。

## デフォルトの動作
WHERE句に指定した条件により、LISTAGG関数が生成する文字列の長さは変わってきます。文字列の長さが最大長を超えるとデフォルトでは
LISTAGG関数はORA-01489が発生して異常終了します。

```
SQL> SELECT 
  2 LISTAGG(c_last_name, ':') WITHIN GROUP (ORDER BY c_customer_id) 
  3 FROM customer;
FROM customer
     *                                                                            *
行3でエラーが発生しました。:
ORA-01489: 文字列を連結した結果、長さが最大長を超えました

```
この動作はLISTAGG関数にON OVERFLOW ERRORを指定しても同じです（Oracle Database 12c新構文）。

```
SQL> SELECT 
  2  LISTAGG(c_last_name, ':' ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY c_customer_id)
  3  FROM customer;
FROM customer
     *
行3でエラーが発生しました。:
ORA-01489: 文字列を連結した結果、長さが最大長を超えました

```

Oracle Database 12c Release 2では、エラーを抑止するためにはON OVERFLOW TRUNCATE句を指定することができます。

```
SQL> SELECT 
  2  LISTAGG(TRIM(c_last_name), ':' ON OVERFLOW TRUNCATE)
  3  WITHIN GROUP (ORDER BY c_customer_id)
  4* FROM customer;

LISTAGG(TRIM(C_LAST_NAME),':'ONOVERFLOWTRUNCATE)WITHINGROUP(ORDERBYC_CUSTOMER_ID
--------------------------------------------------------------------------------
Sanders:Williams:Brewer:Lohr:Terry:Dillon:Huber:Mcgrath:Martin:Jarrett:Perez:Par
ker:Cooper:Brooks:Roberts:Patterson:Goodwin:Thompson:Gresham:Roland:Nicholson:At
(省略しています)
Thornton:Barba:Fulcher:Sheppard:Barr:Ramirez:Donovan:Andrews:Long:Jones:Norton:P
utman:Anderson:Clark:Thompson:Fuller:Fink:Cohen:...(99416)
```

文字列の最後にセパレータ（...）が出力され、(99416)という数字が出力されています。これは99416個のデータが省略されたことを示します。
セパレータの文字列は変更できます。下記はセパレータを文字列'---'に指定しています。

```
SQL> SELECT 
  2  LISTAGG(TRIM(c_last_name), ':' ON OVERFLOW TRUNCATE '---')
  3  WITHIN GROUP (ORDER BY c_customer_id)
  4  FROM customer;

LISTAGG(TRIM(C_LAST_NAME),':'ONOVERFLOWTRUNCATE'---')WITHINGROUP(ORDERBYC_CUSTOM
--------------------------------------------------------------------------------
Sanders:Williams:Brewer:Lohr:Terry:Dillon:Huber:Mcgrath:Martin:Jarrett:Perez:Par
ker:Cooper:Brooks:Roberts:Patterson:Goodwin:Thompson:Gresham:Roland:Nicholson:At
(省略しています)
Thornton:Barba:Fulcher:Sheppard:Barr:Ramirez:Donovan:Andrews:Long:Jones:Norton:P
utman:Anderson:Clark:Thompson:Fuller:Fink:Cohen:---(99416)
```

省略された数を出力しない場合はWITHOUT COUNT句を指定します。セパレータを出力させなくすることはできません。

```
SQL> SELECT 
  2  LISTAGG(TRIM(c_last_name), ':' ON OVERFLOW TRUNCATE '---' WITHOUT COUNT)
  3  WITHIN GROUP (ORDER BY c_customer_id)
  4  FROM customer;

LISTAGG(TRIM(C_LAST_NAME),':'ONOVERFLOWTRUNCATE'---'WITHOUTCOUNT)WITHINGROUP(ORD
--------------------------------------------------------------------------------
Sanders:Williams:Brewer:Lohr:Terry:Dillon:Huber:Mcgrath:Martin:Jarrett:Perez:Par
ker:Cooper:Brooks:Roberts:Patterson:Goodwin:Thompson:Gresham:Roland:Nicholson:At
(省略しています)
Thornton:Barba:Fulcher:Sheppard:Barr:Ramirez:Donovan:Andrews:Long:Jones:Norton:P
utman:Anderson:Clark:Thompson:Fuller:Fink:Cohen:Alexander:Thompson:Mclendon:---
```

ON OVERFLOW TRUNCATE句を指定した場合の最大長は、システムが許可する最大長よりも小さくなります。連結する行の値が途中で切れることはありません。

```
SQL> SELECT
  2  LENGTH(LISTAGG(c_last_name, ':' ON OVERFLOW TRUNCATE '---')
  3  WITHIN GROUP (ORDER BY c_customer_id))
  4  FROM customer;

LENGTH(LISTAGG(C_LAST_NAME,':'ONOVERFLOWTRUNCATE'---')WITHINGROUP(ORDERBYC_CUSTO
--------------------------------------------------------------------------------
                                                                            3978

SQL>
```