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

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

ユーザー定義集計関数インターフェースとは ODCIAggregate ルーチンと呼ばれる一連のルーチンをオブジェクト型のメソッドとして実装し、FUNCTION 文を使用して集計関数を作成します。PL/SQL、C、C++、Java で実装できます。

グループ化したレコードをカンマ区切り(CSV)などで出力したいときなどにとても便利です。

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


カンマ区切りで文字列集計するサンプル

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


CREATE TABLE EMPLOYEE
(
  EMPLOYEE   NVARCHAR2(50) NOT NULL,
  DEPARTMENT   NVARCHAR2(50) NOT NULL
);
EMPLOYEEDEPARTMENT
墨田 太郎営業部
墨田 次郎営業部
墨田 三郎管理部
墨田 四郎営業部
墨田 五郎総務部

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


select DEPARTMENT, STRCAT(EMPLOYEE)
from EMPLOYEE
group by DEPARTMENT;
DEPARTMENTSTRCAT(EMPLOYEE)
営業部墨田 太郎,墨田 四郎,墨田 次郎
管理部墨田 三郎
総務部墨田 五郎

作成の手順

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

  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)

STRCAT集計関数

まずは 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 だった場合の判定処理は外してありますので、利用する条件に合わせて調整してみてください。

おつかれさまでした。

この記事がお役に立ちましたら シェア をお願いいたします。