DB2 Version 9.7 for Linux, UNIX, and Windows

Array support in SQL procedures

SQL procedures support parameters and variables of array types. Arrays are a convenient way of passing transient collections of data between an application and a stored procedure or between two stored procedures.

Within SQL stored procedures, arrays can be manipulated as arrays in conventional programming languages. Furthermore, arrays are integrated within the relational model in such a way that data represented as an array can be easily converted into a table and data in a table column can be aggregated into an array. The examples below illustrate several operations on arrays. Both examples are command line processor (CLP) scripts that use the percentage character (%) as a statement terminator.

Example 1

This example shows two procedures, sub and main. Procedure main creates an array of 6 integers using an array constructor. It then passes the array to procedure sum, which computes the sum of all the elements in the input array and returns the result to main. Procedure sum illustrates the use of array subindexing and of the CARDINALITY function, which returns the number of elements in an array.

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 %

Example 2

In this example, we use two array data types (intArray and stringArray), and a persons table with two columns (id and name). Procedure processPersons adds three additional persons to the table, and returns an array with the person names that contain letter 'o', ordered by id. The ids and name of the three persons to be added are represented as two arrays (ids and names). These arrays are used as arguments to the UNNEST function, which turns the arrays into a two-column table, whose elements are then inserted into the persons table. Finally, the last set statement in the procedure uses the ARRAY_AGG aggregate function to compute the value of the output parameter.

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 %