SQL 过程中的数组支持

SQL 过程支持数组类型的参数和变量。在应用程序与存储过程之间或两个存储过程之间传递瞬态数据集合时，使用数组会很方便。

在 SQL 存储过程中，可将数组作为传统编程语言中的数组操作。而且，数组将在关系模型中按以下方式集成：表示为数组的数据可轻松转换为表，并且表列中的数据可聚集成数组。下面的示例说明针对数组的一些操作。两个示例都是命令行处理器 (CLP) 脚本，它们使用百分比字符 (%) 作为语句终止符。

示例 1

此示例显示以下两个过程： sub 和 main。过程 main 使用数组构造函数创建包含 6 个整数的数组。然后将该数组传递至过程 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

在此示例中，我们使用两种数组数据类型（intArray 和 stringArray）以及带有两个列（id 和 name）的 persons 表。过程 processPersons 向表添加三个额外人员，并返回人员姓名中包含字母“o”的数组（按标识排序）。要添加的三个人员的标识和名称表示为两个数组（ids 和 names）。这些数组用作 UNNEST 函数的自变量，此函数将数组变为包含两个列的表，其元素将插入到 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 %