ORACLE カンマ区切り(CSV)で文字列集計する方法 - SQL ホームページ制作 | 墨田区

ORACLE カンマ区切り(CSV)で文字列集計する方法 – SQL

LINEで送る
Pocket

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

ユーザー定義集計関数インターフェースとは ODCIAggregate ルーチンと呼ばれる一連のルーチンをオブジェクト型のメソッドとして実装し、FUNCTION 文を使用して集計関数を作成します。PL/SQL、C、C++、Java で実装できます。
グループ化したレコードをカンマ区切り(CSV)などで出力したいときなどにとても便利です。




【PR】マジか?!「アレ」してるLINEスタンプっていったい・・・


サンプル

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

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 を定義する


利用するユーザー定義集計関数インターフェース

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

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


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対多のテーブルを連結する場合にも利用できます。
実務ではこのような情報を、参考情報として画面や帳票に表記したい場合が多々ありますので、この集計関数があると便利です。
今回は NULL だった場合の判定処理は外してありますので、利用する条件に合わせて調整してみてください。


おつかれさまでした。

LINEで送る
Pocket

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

コメントを残す

お名前 (必須)
メールアドレス
(アドレスは公開されません)

コメント(必須)

Trackback URL