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

LISTAGG関数

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

データの準備

この検証で使用するデータは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>