【虎の巻】第7回「Db2 for i 7.1のSQL配列（前編）」

SQLおよびJavaプロシージャと多数のパラメータをやり取りしたいとき、配列が使えたらと思ったことはありませんか？また、SQLを使って配列のデータをあたかもデータベースの表のように処理したり、逆にデータベースの表を配列データの様に処理したりできたら、処理が楽になるのにと考えたことはありませんか？ Db2 for i 7.1でサポートされるようになった配列データ型はこれらを可能にします。この配列データ型とそれに関連するSQL関数について、前編、後編の2回に渡りその便利さをお伝えします。まず、今回は配列データ型の定義法と配列変数の基本的な操作について述べます。（編集部）

この記事では、配列データ型の定義プロセス、SQL PLとJava両者における配列の作成、そして新しいSQL関数を使った配列変数の操作について説明します。

07/01/2010 トム・バルマー

ビジネス・アプリケーションが複雑を増すにつれ、データベース・スキーマおよびデータアクセス・コードも複雑になっています。アプリケーションがデータベースと長い一連のデータをやり取りする必要があるのも珍しいことではありません。自社のアプリケーションの基本的データである顧客ID、部品番号、日付範囲または他の一連のデータ型を想像してみてください。

歴史的に、SQLプロシージャと一連の値をやり取りするには幾つかの選択肢があります。

• 長い入力および出力リストを使用します。時間の経過とともに、プロシージャのパラメータリストは理解するのが耐え難い程長く難しくなる可能性があります。
• すべての値を１つのアドホックな「文字列」パラメータに結合します。これには、非文字型データを文字データに変換してまた元に戻すために、文字列を構築し分解する追加コードが必要です。データが極端に単純でない限り、このアプローチには予期しないエラーが不意に出現するという脆弱性があります。
• （多分DECLARE GLOBAL TEMPORARY TABLE文を使用して）プロシージャが使用する、あるいは結果セットのデータを返すための一時表を作成します。この方法はうまく動作しますが、パフォーマンスの観点で最も劣ったアプローチです。

Db2 for i 7.1の配列サポートは、プロシージャやデータアクセス・コードに追加の選択肢を提供します。SQLプロシージャならびにJavaで書かれた外部プロシージャ用のパラメータおよび変数として配列が定義できます。配列は1つのプロシージャから別のプロシージャに入力(IN)および出力(OUT)パラメータとして渡すことができます。Db2 for i 7.1はSQLプロシージャ特有の新しい関数をサポートします。これにより、配列から表へ、そして表から配列に容易に変換することができます。配列をリレーショナルモデルに統合することで、Db2は配列データ型のパフォーマンスと使いやすさの両方を提供します。

最後に、リリース7.1は既に配列をサポートしているデータベースシステムを使用しているベンダーや開発者にとって朗報です。Db2 for i 7.1の配列サポートは、Db2 LUW 9.5で提供されるサポートと互換性があります。IBM Toolbox for JavaのJDBCドライバーは、標準のJDBC APIによって配列をサポートするために、IBM i 7.1用に更新されています。データベース・モニターおよびVisual Explainのツールもまた、配列を使ったアプリケーション開発をサポートするために機能拡張されています。

配列データ型を作る

配列データ型は、組み込みデータ型の配列として定義されたユーザー定義型（UDT: User-Defined Type）です。配列データ型は、他のUDTと同様にCREATE TYPE文で定義されます。配列変数は常に配列UDTと関連付けられています。

配列データ型には最大多重度が定義されています。配列データ型の最大多重度は、配列の最大要素数を定義します。

CREATE TYPE testArray AS INTEGER ARRAY[10];
CREATE TYPE countries AS VARCHAR(100) ARRAY[];
CREATE TYPE messages AS XML ARRAY[50];

配列全体の大きさは4GBに制限されています。CREATE TYPE文で最大多重度が省略されている場合、データベースは要素のデータ型サイズに基づいて自動的にこれを割り当てます。下記のコード例1はこの挙動を示しており、あなたはこれを自分のシステム上で実行できます。

CardinalityChecker

CREATE TYPE string10 AS VARCHAR(10) ARRAY[];
CREATE TYPE string1000 AS VARCHAR(1000) ARRAY[];
CREATE TYPE string30000 AS VARCHAR(30000) ARRAY[];
CREATE OR REPLACE PROCEDURE qgpl.CardinalityChecker (out card1 bigint, 
out card2 bigint, out card3 bigint)

BEGIN
 DECLARE array10 string10;
 DECLARE array1000 string1000;
 DECLARE array30000 string30000;
 set card1 = MAX_CARDINALITY(array10);
 set card2 = MAX_CARDINALITY(array1000);
 set card3 = MAX_CARDINALITY(array30000);
END;

call qgpl.CardinalityChecker(?,?,?);

> call qgpl.CardinalityChecker(?,?,?)

Return Code = 0

Output Parameter #1 = 357913941
Output Parameter #2 = 4286394
Output Parameter #3 = 143156

▲コード例1

Cまたは類似の言語の配列とは違い、最大多重度は必ずしも実行時に割り振られるメモリーサイズを定義しません。一般的に、Db2 for iは割り当てられた配列要素を格納するのに十分なメモリーを割り振ろうとし、必要に応じて割り振りサイズを拡張します。配列データ型の最大多重度はプロシージャ実行時に強制されます。

DECLARE myArray testArray;
SET myArray[999] = 1;

最大多重度を超えて配列要素を追加しようとすると、Db2はSQLSTATE 2202Eという下記のようなエラーが発生します。

SQLステート： 2202E
ベンダーコード: -20439
メッセージ：[SQ20439] 値999は有効な配列添え字ではありません。　理由. . . . . : 値999が配列の添え字として指定されましたが、値が負または0、配列の最大多重度より大きい、または現在の多重度よりも大きい配列要素を参照するために使用されています。値が-1の場合、添え字がNULLになっているかも知れません。　回復法. . . . . : 添え字を有効な値に変更しなさい。再度要求を試しなさい。

配列パラメータを使う

配列は入力(IN)、出力(OUT)そして入出力(INOUT)パラメータとしてサポートされます。ここに入力として配列を取り、出力として配列を返すプロシージャ例のコードが幾つかあります。そのプロシージャは入力としてDATE値の配列を入力とし、土曜と日曜だけに相当する値をもつ入力配列のサブセットを返します。

たとえば、入力日付が土曜、金曜、日曜だった場合、このプロシージャは土曜と日曜に該当する日付だけを返します。

例題の入力: ['2010-04-24', '2010-02-12', '2010-03-14']
例題の出力: ['2010-04-24', '2010-03-14']

まず、配列データ型を作成する必要があります。

CREATE TYPE dateArray AS DATE ARRAY[100];

配列データ型が作成されたら、これを参照するすべてのプロシージャはこの型をCURRENT PATH内にもつか、スキーマ名でこれを修飾する必要があります。

create procedure getWeekends(in myDates dateArray, out weekends dateArray)
begin
    -- 配列索引変数
    declare dateIndex, weekendIndex int default 1;

    -- myDateの配列長を格納する変数
    -- CARDINALITY関数を使って初期化する
    declare datesCount int;
    set datesCount = CARDINALITY(myDates);

    -- myData内の各日付に対し、日付が土曜か日曜なら
    -- weekendsという名前の出力配列にこれを追加する
    while dateIndex <= datesCount do
        if DAYOFWEEK(myDates[dateIndex]) in (1, 7) then
            set weekends[weekendIndex] = myDates[dateIndex];
            set weekendIndex = weekendIndex + 1;
        end if;

        set dateIndex = dateIndex + 1;
    end while;
end;

配列に要素を追加する

代入文を使って配列に要素を追加することができます。

SET myArray[1] = 100;
SET myArray[8] = null;
SET products[i] = (select sum(pcount) from inventory where code = codes[i]);

副指標の値の有効範囲は１から配列データ型の最大多重度までです。左側の副指標は任意の数値スカラー式の可能性があります。

与えられた副指標に該当する配列要素に値が代入されると、それより前のまだ値が代入されていない配列内の要素は自動的にNULLで初期化されます。

SET myArray = null;
SET myArray[2] = 102;
-- myIntegerはNULLに設定される
SET myInteger = myArray[1];

他のSET <変数>文のタイプと違い、配列要素には１つずつ値を代入する必要があります。下記のコード例２はその構文を示しています。

ArraySetter

CREATE TYPE string10 AS VARCHAR(10) ARRAY[];

CREATE OR REPLACE PROCEDURE qgpl.ArraySetter (out p1 varchar(10), out p2 varchar(10))
BEGIN
 DECLARE array10 string10;

 -- 以下の命令
 -- set (array10[1], array10[2]) = ('abc', 'def');
 -- の代りに次の命令を実行する
 set array10[1] = 'abc';
 set array10[2] = 'def';

 -- 以下の命令
 -- set (p1, p2) = (array10[1], array10[2]);
-- の代りに次の命令を実行する
 set p1 = array10[1];
 set p2 = array10[2];
END ;

▲図：コード例２

配列への値の代入はすべて、SQLの記憶域割り当てルールを使用します。切り捨てや四捨五入のような配列要素へのあらゆるタイプの代入エラーは、アプリケーションに警告の代りにエラーが返されます。

多重度と最大多重度

CARDINALITY関数は１つの配列パラメータまたは変数を引数とし、値が代入された配列要素の数をBIGINT型の値として返します。言い換えれば、多重度は配列の現在の長さです。

SET myArray = NULL; -- CARDINALITY(myArray) はNULL
SET myArray[1] = 1; -- CARDINALITY(myArray) は1
SET myArray[10] = 99; -- CARDINALITY(myArray) は10
SET myArray[9] = -100; -- CARDINALITY(myArray) は10のまま

MAX_CARDINALITY関数は、CREATE TYPE文で定義された配列の最大多重度をBIGINT型の値として返します。

CREATE TYPE testArray AS INTEGER array[10];
CREATE PROCEDURE get_max_card BEGIN
    DECLARE myArray testArray;
    DECLARE max_card int;
    -- max_cardは10に設定される
    SET max_card = MAX_CARDINALITY(myArray);
end;

配列コンストラクターとTRIM_ARRAY

配列コンストラクター構文は、式のリストまたは単一列の照会の結果を使って配列を作成できるようにします。配列コンストラクターは代入文の右側だけでしか使えません。

SET myArray = ARRAY[1, 2, 3, null];
SET weather_data = ARRAY[min_temp, max_temp, avg_temp];
SET emptyArray = ARRAY[];
SET employeeList = ARRAY[select empid from employee where dept = 'A00' order by lastname];

配列の後部から要素を取り除くTRIM_ARRAY関数について述べることにも価値があります。TRIM_ARRAY(a, n)は配列aのコピーで、後ろn個の要素を削除したものを返します。2番目の引数は１と配列の多重度の間の値でなければなりません。この場合もやはり、TRIM_ARRAYは代入文の右側でしか使用できません。

-- 最後のメッセージを削除する
SET messages = TRIM_ARRAY(messages, 1);

-- 配列のコピーを作り、最後の10要素を削除する
SET sampleCopy = TRIM_ARRAY(samples, 10);

-- myArrayは空の配列になる
SET myArray = TRIM_ARRAY(myArray, CARDINALITY(myArray));

配列変数を操作する

副指標とNULL値の働き方について幾つか述べます。

• 配列は常に１から始まります。言い換えれば、配列の要素の範囲はmyArray[1]...myArray[cardinality(myArray)]です。
• 配列は空またはNULLであるかも知れません。空の配列変数の多重度は０です。空の配列変数はNULLという多重度をもちます。

SET myArray = null; -- CARDINALITY(myArray) はNULL
SET myArray = ARRAY[]; -- CARDINALITY(myArray) は0

• 初期化されていない配列変数またはパラメータはNULLと同等です。
• NULLという副指標をもつ配列を参照すると常にNULLが返されます。
• 仮に副指標の値が配列データ型の最大多重度より大きいとしても、任意の副指標式をもつNULL配列を参照すると常にNULLが返されます。
• Db2 for i 7.1では、副指標の値は整数型またはそれが０スケール（訳注：つまり、小数点以下の桁をもたない）ならば任意の互換性のある型にすることができます。