【PL/SQL】ユーザー定義集計関数の作り方 | コーディングライフスタイル

Oracle Databaseでは、標準の SUM、AVG、LISTAGG だけでは表現しにくい集約処理を、ユーザー定義集計関数として実装できます。たとえば、独自ルールの文字列連結、CLOBへの長文集約、JSON配列化、業務スコア計算、複数値を持つ統計集約などです。

ユーザー定義集計関数は、ODCIAggregate インターフェースに沿ったオブジェクト型を作り、CREATE FUNCTION ... AGGREGATE USING でSQL関数として登録します。オブジェクト型の基本は OBJECT TYPEとメンバーメソッドの使い方、通常の関数設計は PL/SQLプロシージャ・ファンクション完全ガイドもあわせて確認してください。

この記事で扱うこと

ODCIAggregateInitialize / Iterate / Merge / Terminate の役割
LISTAGG ではなくUDAを使う判断基準
VARCHAR2版の文字列集約関数
CLOB対応の長文集約関数
NULL、重複、順序、4000バイト制限、ORA-06502への注意
並列実行、Analytic Function、依存・権限・再コンパイル

最初に結論：まずLISTAGGで足りるか確認する

文字列連結だけが目的なら、まず標準の LISTAGG を検討します。現在のOracleでは WITHIN GROUP による順序指定や、バージョンによっては ON OVERFLOW TRUNCATE、DISTINCT なども使えます。標準関数で済むなら、その方が保守しやすく高速です。

LISTAGGでよい単純な文字列連結、順序付き連結、区切り文字付き連結で足りる場合です。

UDAを検討する独自の状態管理、CLOB集約、複雑なスコア計算、JSON配列化、重複判定や正規化を含む集約が必要な場合です。

避けたい標準集計関数で十分なのに、学習コストの高いUDAへ寄せる設計です。運用・依存・再コンパイルの負担が増えます。

LISTAGG の標準的な使い方や4000バイト制限の考え方は Oracle LISTAGG関数の使い方完全ガイドを参照してください。

ODCIAggregateの4メソッド

UDAは、集計処理のライフサイクルを4つのメソッドで表現します。OracleはSQLの集計処理中にこれらのメソッドを自動的に呼び出します。

Initialize集計コンテキストを初期化します。グループごとの最初に呼ばれます。

Iterate入力行ごとに呼ばれ、集計コンテキストへ値を足していきます。

Merge並列実行やROLLUPなどで部分集計結果を統合します。省略できる場面もありますが、実務では実装しておく方が安全です。

Terminate最後に集計結果を返します。返却型と内部状態の型を混同しないようにします。

VARCHAR2版の文字列集約関数を作る

まずは分かりやすい例として、文字列をカンマ区切りで連結するUDAを作ります。この版は VARCHAR2(4000) に収まる小さな集約向けです。長文や大量値を扱う場合は後述のCLOB版を使います。

オブジェクト型を定義する

string-agg-type.sql

CREATE OR REPLACE TYPE string_agg_type AS OBJECT (
  total VARCHAR2(4000),

  STATIC FUNCTION ODCIAggregateInitialize(
    sctx IN OUT string_agg_type
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(
    self  IN OUT string_agg_type,
    value IN VARCHAR2
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(
    self IN OUT string_agg_type,
    ctx2 IN string_agg_type
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN  string_agg_type,
    returnValue OUT VARCHAR2,
    flags       IN  NUMBER
  ) RETURN NUMBER
);
/

TYPE BODYを実装する

string-agg-body.sql

CREATE OR REPLACE TYPE BODY string_agg_type AS

  STATIC FUNCTION ODCIAggregateInitialize(
    sctx IN OUT string_agg_type
  ) RETURN NUMBER IS
  BEGIN
    sctx := string_agg_type(NULL);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(
    self  IN OUT string_agg_type,
    value IN VARCHAR2
  ) RETURN NUMBER IS
  BEGIN
    IF value IS NOT NULL THEN
      IF self.total IS NULL THEN
        self.total := value;
      ELSE
        self.total := self.total || ', ' || value;
      END IF;
    END IF;

    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(
    self IN OUT string_agg_type,
    ctx2 IN string_agg_type
  ) RETURN NUMBER IS
  BEGIN
    IF ctx2.total IS NOT NULL THEN
      IF self.total IS NULL THEN
        self.total := ctx2.total;
      ELSE
        self.total := self.total || ', ' || ctx2.total;
      END IF;
    END IF;

    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN  string_agg_type,
    returnValue OUT VARCHAR2,
    flags       IN  NUMBER
  ) RETURN NUMBER IS
  BEGIN
    returnValue := self.total;
    RETURN ODCIConst.Success;
  END;

END;
/

SQL関数として登録する

string-agg-function.sql

CREATE OR REPLACE FUNCTION string_agg(
  input VARCHAR2
) RETURN VARCHAR2
  PARALLEL_ENABLE
  AGGREGATE USING string_agg_type;
/

PARALLEL_ENABLE を付ける場合、部分集計を統合する ODCIAggregateMerge の実装が重要です。集約順序が結果に影響する処理では、並列実行時の順序保証が難しい点にも注意します。

SQLで使う

登録したUDAは、標準集計関数と同じように GROUP BY と組み合わせて使えます。

GROUP BYで呼び出す例

string-agg-query.sql

CREATE TABLE sample_data (
  dept VARCHAR2(10),
  name VARCHAR2(50)
);

INSERT INTO sample_data VALUES ('A', '田中');
INSERT INTO sample_data VALUES ('A', '佐藤');
INSERT INTO sample_data VALUES ('B', '鈴木');
INSERT INTO sample_data VALUES ('B', '高橋');
COMMIT;

SELECT dept,
       string_agg(name) AS members
  FROM sample_data
 GROUP BY dept
 ORDER BY dept;

結果は部署ごとに名前を連結した文字列になります。ただし、このサンプルでは集約順序を関数内では保証していません。順序が重要な場合は、標準の LISTAGG ... WITHIN GROUP を優先するか、入力側で順序を安定させる設計を検討します。

CLOB対応の集約関数を作る

VARCHAR2(4000) を超える文字列集約が必要な場合は、内部状態と戻り値をCLOBにします。長文ログ、JSON配列、CSV断片、説明文の連結などではCLOB版が実務的です。

CLOB版の型定義

clob-agg-type.sql

CREATE OR REPLACE TYPE clob_agg_type AS OBJECT (
  total CLOB,

  STATIC FUNCTION ODCIAggregateInitialize(
    sctx IN OUT clob_agg_type
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(
    self  IN OUT clob_agg_type,
    value IN VARCHAR2
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(
    self IN OUT clob_agg_type,
    ctx2 IN clob_agg_type
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN  clob_agg_type,
    returnValue OUT CLOB,
    flags       IN  NUMBER
  ) RETURN NUMBER
);
/

CLOB版の実装

clob-agg-body.sql

CREATE OR REPLACE TYPE BODY clob_agg_type AS

  STATIC FUNCTION ODCIAggregateInitialize(
    sctx IN OUT clob_agg_type
  ) RETURN NUMBER IS
  BEGIN
    sctx := clob_agg_type(NULL);
    DBMS_LOB.CREATETEMPORARY(sctx.total, TRUE);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(
    self  IN OUT clob_agg_type,
    value IN VARCHAR2
  ) RETURN NUMBER IS
  BEGIN
    IF value IS NOT NULL THEN
      IF DBMS_LOB.GETLENGTH(self.total) > 0 THEN
        DBMS_LOB.APPEND(self.total, TO_CLOB(', '));
      END IF;

      DBMS_LOB.APPEND(self.total, TO_CLOB(value));
    END IF;

    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(
    self IN OUT clob_agg_type,
    ctx2 IN clob_agg_type
  ) RETURN NUMBER IS
  BEGIN
    IF ctx2.total IS NOT NULL AND DBMS_LOB.GETLENGTH(ctx2.total) > 0 THEN
      IF DBMS_LOB.GETLENGTH(self.total) > 0 THEN
        DBMS_LOB.APPEND(self.total, TO_CLOB(', '));
      END IF;

      DBMS_LOB.APPEND(self.total, ctx2.total);
    END IF;

    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN  clob_agg_type,
    returnValue OUT CLOB,
    flags       IN  NUMBER
  ) RETURN NUMBER IS
  BEGIN
    returnValue := self.total;
    RETURN ODCIConst.Success;
  END;

END;
/

CLOB集約関数を登録する

clob-agg-function.sql

CREATE OR REPLACE FUNCTION clob_agg(
  input VARCHAR2
) RETURN CLOB
  PARALLEL_ENABLE
  AGGREGATE USING clob_agg_type;
/

CLOBを扱う場合は、DBMS_LOB の基本、TEMP領域、LOB長、クライアント側の受け取りにも注意します。LOB全般は Oracle LOB完全ガイド、DBMS_LOB操作は DBMS_LOB完全ガイド、CLOB変換エラーは ORA-22835の原因と解決方法が関連します。

NULL・重複・順序の設計

UDAは標準集計関数と違い、NULLや重複、順序の扱いを自分で決める必要があります。仕様が曖昧なまま実装すると、実行環境や並列度によって結果が変わることがあります。

NULL無視するのか、空文字として扱うのか、明示的な文字列に変換するのかを決めます。

重複すべて残すのか、DISTINCT相当にするのかを決めます。DISTINCT化には内部状態が増えます。

順序UDA内部では入力順を安易に信頼しない方が安全です。順序が重要ならLISTAGGや入力側設計を検討します。

長さVARCHAR2版はORA-06502やORA-01489相当の長さ問題に注意します。長文はCLOB版へ逃がします。

文字列や数値のバッファ不足は ORA-06502の原因と解決方法も参考になります。

JSON配列集約に応用する

UDAはJSON配列のような独自フォーマット生成にも使えます。ただし、近年のOracleでは JSON_ARRAYAGG や JSON_OBJECT などの標準JSON関数も強力です。まず標準関数で足りるか確認し、独自ルールが必要な場合だけUDAを検討します。

JSON文字列を集約する考え方

json-agg-idea.sql

-- 標準関数で足りるならこちらを優先
SELECT JSON_ARRAYAGG(
         JSON_OBJECT(
           'id' VALUE employee_id,
           'name' VALUE employee_name
         )
       ) AS employees_json
  FROM employees
 WHERE department_id = :department_id;

-- 独自の整形、重複排除、ソート、エラー収集が必要な場合にUDAを検討する

Oracle JSONの基本は Oracle JSON完全ガイド、PL/SQLでJSONを取り込む処理は JSON_TABLEでJSONを取り込む方法が関連します。

並列実行とMergeの注意点

PARALLEL_ENABLE を付けると、並列実行時に各スレーブが部分集計を作り、最後に ODCIAggregateMerge で統合します。Mergeが正しく実装されていないUDAは、並列実行、ROLLUP、CUBE、GROUPING SETSで結果が崩れる可能性があります。

結合法則部分集計をどの順番で統合しても意味が崩れない設計が理想です。

順序依存文字列連結の順序に意味がある場合、並列Mergeで期待順序にならないことがあります。

巨大コンテキスト内部状態が大きすぎると、並列時のコンテキスト受け渡しが重くなります。

大量処理や並列処理の別解としては Oracleパイプライン表関数完全ガイドとパイプライン関数で大量データ処理を勝たせる完全ガイドも検討できます。

Analytic Functionとして使う

ユーザー定義集計関数は、条件によっては分析関数としても利用できます。ただし、ウィンドウごとに集計コンテキストが作られるため、内部状態が大きいUDAは重くなりやすいです。

OVER句で使う例

uda-analytic.sql

SELECT department_id,
       employee_id,
       clob_agg(employee_name) OVER (
         PARTITION BY department_id
       ) AS names_in_department
  FROM employees;

分析関数で済む集計は、まず標準のAnalytic Functionを検討します。分析関数の使いどころは分析関数を活用した集計ロジックの簡素化が関連します。

権限と依存関係

UDAを作成するには、CREATE TYPE と CREATE FUNCTION の権限が必要です。また、オブジェクト型、TYPE BODY、関数が依存関係を持つため、変更時の再コンパイル順序にも注意します。

権限と依存確認

uda-dependency-check.sql

-- 自分の型・関数を確認
SELECT object_name,
       object_type,
       status
  FROM user_objects
 WHERE object_name IN ('STRING_AGG_TYPE', 'STRING_AGG', 'CLOB_AGG_TYPE', 'CLOB_AGG')
 ORDER BY object_type, object_name;

-- コンパイルエラーを確認
SELECT name,
       type,
       line,
       position,
       text
  FROM user_errors
 WHERE name IN ('STRING_AGG_TYPE', 'CLOB_AGG_TYPE')
 ORDER BY name, sequence;

依存と再コンパイルは依存オブジェクトとINVALID再コンパイル、コンパイルエラーの読み方は PL/SQLコンパイル時エラーと警告の完全対処ガイド、権限管理は AUTHIDと権限管理の設計が関連します。

テスト観点

UDAはSQLエンジンから繰り返し呼ばれるため、通常の関数よりテスト観点が多くなります。少なくとも、NULL、1件、複数件、長文、グループ別、並列、空集合、再コンパイル後を確認します。

最低限のテストSQL

uda-test.sql

-- NULLが混ざる場合
SELECT string_agg(name)
  FROM (
    SELECT 'A' AS name FROM dual UNION ALL
    SELECT NULL FROM dual UNION ALL
    SELECT 'B' FROM dual
  );

-- グループ別
SELECT dept,
       string_agg(name)
  FROM sample_data
 GROUP BY dept;

-- CLOB版の長さ確認
SELECT DBMS_LOB.GETLENGTH(clob_agg(name)) AS len
  FROM sample_data;