SQL プロシージャーでの配列サポート

SQL プロシージャーは、配列タイプのパラメーターと変数をサポートします。配列は、アプリケーションとストアード・プロシージャーの間で、または 2 つのストアード・プロシージャー間で一時的なデータの集合を受け渡すのに便利な方法です。

SQL ストアード・プロシージャーでは、配列は、標準的なプログラミング言語の配列として操作可能です。また、配列として表されるデータを表に簡単に変換したり、表列内のデータを配列に集約したりするなど、配列はリレーショナル・モデルに統合されます。次の例では、配列の操作方法をいくつか示しています。どちらの例もコマンド行プロセッサー (CLP) スクリプトで、ステートメント終止符としてパーセント文字 (%) を使用しています。

例 1

この例には、sub と main の 2 つのプロシージャーが示されています。プロシージャー main は、配列コンストラクターを使用して 6 つの整数からなる 1 つの配列を作成します。このプロシージャーは、その後この配列をプロシージャー sum に渡します。プロシージャー sum は、入力配列内のすべての要素の合計を計算し、その結果をプロシージャー main に戻します。プロシージャー sum は、配列の副指標の使用法、および CARDINALITY 関数の使用法の例を示しています。この関数は、配列内の要素数を戻します。

create type intArray as integer array[100] %  

create procedure sum(in numList intArray, out total integer) 
begin 
declare i, n integer;  

set n = CARDINALITY(numList);
  
set i = 1; 
set total = 0; 
 
while (i <= n) do 
set total = total + numList[i]; 
set i = i + 1; 
end while;
  
end %  

create procedure main(out total integer) 
begin 
declare numList intArray; 

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

call sum(numList, total);  

end %

例 2

この例では、2 つの配列データ・タイプ (intArray および stringArray)、さらには 2 つの列 (id および name) を持つ persons 表を使用します。プロシージャー processPersons は、3 人の人物をこの表にさらに追加し、文字「o」が含まれる人物名を ID 順に並べた配列を返します。追加される 3 人の人物の ID と名前は、2 つの配列 (ids および names) として表されます。これらの配列は 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') % 
insert into persons values(4, 'Jill') % 
insert into persons values(1, 'Joe') % 
insert into persons values(3, 'Mary') %  

create procedure processPersons(out witho stringArray) 
begin 
declare ids intArray; 
declare names stringArray;  

set ids = ARRAY[5,6,7]; 
set names = ARRAY['Bob', 'Ann', 'Sue'];  

insert into persons(id, name) 
(select T.i, T.n from UNNEST(ids, names) as T(i, n));  

set witho = (select array_agg(name order by id) 
from persons 
where name like '%o%'); 
end %