SQL のプロシージャーおよび関数での配列サポート

SQL プロシージャーおよび SQL スカラー関数は、配列型のパラメーターおよび変数をサポートします。配列は、アプリケーションとストアード・プロシージャーの間、2 つのストアード・プロシージャーの間、または関数呼び出し時において、一時的なデータ集合を渡すための便利な方法です。

SQL のプロシージャーおよび関数内では、配列は従来型のプログラミング言語での配列と同様に扱うことができます。さらに、配列がリレーショナル・モデルに統合されることによって、配列で表現されるデータを表に変換する操作と、表の列内のデータを配列に集約する操作を容易に行えます。以下の例では、配列上でのいくつかの操作を例示します。

例 1

この例では、2 つのプロシージャー sum および main を取り上げます。プロシージャー main は、配列コンストラクターを使用して、6 つの整数で構成される配列を作成します。次いでその配列をプロシージャー sum に渡し、そこで入力配列のすべての要素の合計を計算した後、結果が main に戻ります。プロシージャー sum は、配列の副索引作成の使用方法、および配列内の要素数を返す CARDINALITY 関数の使用方法を例示するものです。

CREATE TYPE intArray AS INTEGER ARRAY[100] 

CREATE PROCEDURE sum(IN inList intArray, OUT total INTEGER) 
BEGIN 
DECLARE i, n INTEGER; 

SET n = CARDINALITY(inList); 

SET i = 1; 
SET total = 0; 

WHILE (i <= n) DO 
  SET total = total + inList[i]; 
  SET i = i + 1; 
END WHILE; 

END

CREATE PROCEDURE main(OUT arrayTotal INTEGER) 
BEGIN 
DECLARE numList intArray; 

SET numList = ARRAY[1,2,3,4,5,6]; 

CALL sum(numList,arrayTotal); 

END

例 2

この例は例 1 と似ていますが、1 つのプロシージャー main を使用して、sum という名前の関数を呼び出しています。

CREATE TYPE intArray AS INTEGER ARRAY[100] 

CREATE FUNCTION sum(inList intArray) RETURNS INTEGER 
BEGIN 
DECLARE i, n, total INTEGER; 

SET n = CARDINALITY(inList); 

SET i = 1; 
SET total = 0; 

WHILE (i <= n) DO 
  SET total = total + inList[i]; 
  SET i = i + 1; 
END WHILE; 

RETURN total;

END

CREATE PROCEDURE main(OUT arrayTotal INTEGER) 
BEGIN 
DECLARE numList intArray; 

SET numList = ARRAY[1,2,3,4,5,6]; 

SET arrayTotal = sum(numList); 

END

例 3

この例では、 2 つの配列データ・タイプ (intArray と stringArray)、および 2 つの列 (id と name) がある persons 表を使用します。プロシージャー processPersons は、追加の 3 人を表に追加し、文字「a」を含んだ個人名を id で順序付けした配列を返します。追加される 3 人の ID および名前は、2 つの配列 (id および name) で表されます。これらの配列は UNNEST 関数の引数として使用され、そこで配列は 2 列の表に変換されます。その後、それらの要素は persons 表に挿入されます。最後に、プロシージャーの最後の set ステートメントで、ARRAY_AGG 集約関数を使用して出力パラメーターの値を計算します。

CREATE TYPE intArray AS INTEGER ARRAY[100]  

CREATE TYPE stringArray AS VARCHAR(10) ARRAY[100]  

CREATE TABLE persons (id INTEGER, name VARCHAR(10)) 

INSERT INTO persons VALUES(2, 'Tom'),  
                          (4, 'Gina'),  
                          (1, 'Kathy'),
                          (3, 'John') 

CREATE PROCEDURE processPersons(OUT witha stringArray) 
BEGIN 
DECLARE ids intArray; 
DECLARE names stringArray; 

SET ids = ARRAY[5,6,7]; 
SET names = ARRAY['Denise', 'Randy', 'Sue']; 

INSERT INTO persons(id, name) 
   (SELECT t.i, t.n FROM UNNEST(ids, names) AS t(i, n)); 

SET witha = (SELECT ARRAY_AGG(name ORDER BY id) 
                    FROM persons 
                    WHERE name LIKE '%a%'); 
END