Oracle ユーザー定義集計関数を使ってカンマ区切り(CSV)で文字列集計する方法 - SQL

以下のようなテーブルとレコードがあったとします。

CREATE TABLE EMPLOYEE
(
  EMPLOYEE   NVARCHAR2(50) NOT NULL,
  DEPARTMENT   NVARCHAR2(50) NOT NULL
);

これを今回作成する、STRCAT という文字列をカンマ区切りで集計する集計関数を利用すると、以下のようになります。

select DEPARTMENT, STRCAT(EMPLOYEE)
from EMPLOYEE
group by DEPARTMENT;

作成の手順は下記の通りです。

1. ユーザー定義集計関数インターフェースを実装する Type と Type Body をオブジェクト型として定義する
2. 作成した Type と紐付けた Function を定義する

戻り値は全て NUMBER 型 です。ODCIConst という定数を管理しているパッケージを利用して戻り値を返します。

• ODCIAggregateInitialize(sctx IN OUT )
• ODCIAggregateIterate(self IN OUT , val )
• ODCIAggregateMerge(self IN OUT , ctx2 IN)
• ODCIAggregateTerminate(self IN , ReturnValue OUT , flags IN NUMBER)

まずは TYPE を定義します。

CREATE OR REPLACE TYPE STRCAT_TYPE AS OBJECT
(
  cat_string VARCHAR2(32767),
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT STRCAT_TYPE) RETURN NUMBER,
  member function ODCIAggregateIterate(self IN OUT STRCAT_TYPE, val IN VARCHAR2) RETURN NUMBER,
  member function ODCIAggregateTerminate(self IN OUT STRCAT_TYPE, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,
  member function ODCIAggregateMerge(self IN OUT STRCAT_TYPE, ctx2 IN STRCAT_TYPE) RETURN NUMBER
);

次に 実装部分である TYPE BODY を定義します。

CREATE OR REPLACE TYPE BODY STRCAT_TYPE IS
  STATIC FUNCTION ODCIAggregateInitialize(
    sctx IN OUT STRCAT_TYPE
  ) RETURN NUMBER IS
  BEGIN
    sctx := STRCAT_TYPE(null);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(
    self IN OUT STRCAT_TYPE,
    val IN VARCHAR2
  ) RETURN NUMBER IS
  BEGIN
    self.cat_string := self.cat_string || ','|| val;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(
    self IN OUT STRCAT_TYPE,
    returnValue OUT VARCHAR2,
    flags IN NUMBER
  ) RETURN NUMBER IS
  BEGIN
    returnValue := ltrim(rtrim(self.cat_string,','),','); 
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(
    self IN OUT STRCAT_TYPE,
    ctx2 IN STRCAT_TYPE
  ) RETURN NUMBER IS
  BEGIN
    self.cat_string := self.cat_string || ',' || ctx2.cat_string; 
  RETURN ODCIConst.Success;
  END;
END;

最後に、集計関数である FUNCTION を定義します。パラレル実行可能としています。

CREATE OR REPLACE FUNCTION STRCAT(input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING STRCAT_TYPE;

1. STRCAT関数をOracle PackageにするとORA-00600が発生する。
2. NLS_CHARSET:AL32UTF8/NLS_NCHAR_CHARACTERSET:AL16UTF16でDB作成していた場合、STRCAT関数の戻り値が文字化けする。UTF8/UTF8ならOK。

ORACLEでユーザー定義集計関数を使ってカンマ区切り(CSV)で文字列集計する方法を紹介しました。

この集計関数は、1対多のテーブルを連結する場合にも利用できます。実務ではこのような情報を、参考情報として画面や帳票に表記したい場合が多々ありますので、この集計関数があると便利です。

今回は NULL だった場合の判定処理は外してありますので、利用する条件に合わせて調整してみてください。