Array support in SQL procedures and functions

SQL procedures and SQL scalar functions 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, between two stored procedures, or on a function invocation.

Within SQL procedures and functions, arrays can be manipulated like arrays are 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.

Example 1

This example shows two procedures, sum 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 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

Example 2

This example is similar to Example 1 but uses one procedure, main, to invoke a function named 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

Example 3

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 the letter 'a', ordered by id. The ids and names 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'),  
                          (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