Db2 SQL PL的数组类型

环境

• 操作系统：Ubuntu 20.04
• Db2：11.5.0.0

简单数组和关联数组

首先明确一点，SQL PL的数组，下标的起始值是1而不是0！

SQL PL的数组类型分为简单数组（simple array）和关联数组（associative array）。

• 简单数组：下标必须是整数，下标是连续的；
• 关联数组：下标可以不是整数（比如可以是 VARCHAR ），下标可以不连续；

简单数组就是通常意义上的数组（只不过下标从1开始），而关联数组看上去有点像Map，下标是key，元素是value。

文本重点介绍简单数组。

对于简单数组，在创建数组类型时，可以指定大小，比如：

create or replace type simplearray as int array[100]

来看一个简单数组和关联数组对比的例子。创建文件 test1.sql 如下：

create or replace type simplearray as int array[]@
create or replace type assocarray as int array[int]@

set serveroutput on@

begin
	declare arr1 simplearray;
	declare arr2 assocarray;

	call dbms_output.put_line('array_first(arr1): ' || coalesce(cast(array_first(arr1) as varchar(100)), 'NULL'));

	call dbms_output.put_line('array_last(arr1): ' || coalesce(cast(array_last(arr1) as varchar(100)), 'NULL'));

	call dbms_output.put_line('array_first(arr2): ' || coalesce(cast(array_first(arr2) as varchar(100)), 'NULL'));

	call dbms_output.put_line('array_last(arr2): ' || coalesce(cast(array_last(arr2) as varchar(100)), 'NULL'));

	set arr1[10] = 123;
	set arr2[10] = 123;

	call dbms_output.new_line();

	call dbms_output.put_line('arr1[10]: ' || arr1[10]);

	call dbms_output.put_line('cardinality(arr1): ' || cardinality(arr1));

	call dbms_output.put_line('max_cardinality(arr1): ' || max_cardinality(arr1));

	call dbms_output.put_line('arr2[10]: ' || arr2[10]);

	call dbms_output.put_line('cardinality(arr2): ' || cardinality(arr2));

	call dbms_output.put_line('max_cardinality(arr2): ' || coalesce(cast(max_cardinality(arr2) as varchar(100)), 'NULL'));

	call dbms_output.new_line();

	call dbms_output.put_line('array_first(arr1): ' || coalesce(cast(array_first(arr1) as varchar(100)), 'NULL'));

	call dbms_output.put_line('array_last(arr1): ' || coalesce(cast(array_last(arr1) as varchar(100)), 'NULL'));

	call dbms_output.put_line('array_first(arr2): ' || coalesce(cast(array_first(arr2) as varchar(100)), 'NULL'));

	call dbms_output.put_line('array_last(arr2): ' || coalesce(cast(array_last(arr2) as varchar(100)), 'NULL'));

end@

set serveroutput off@

drop type simplearray@
drop type assocarray@

注意：创建关联数组类型时，需要指定下标类型，本例中指定为 int 。换句话说，如果没指定下标类型，就是简单数组；如果指定了下标类型，就是关联数组。

运行 test1.sql ，如下：

➜  temp0528 db2 -td@ -f test1.sql
DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SET SERVEROUTPUT command completed successfully.

DB20000I  The SQL command completed successfully.

array_first(arr1): NULL
array_last(arr1): NULL
array_first(arr2): NULL
array_last(arr2): NULL

arr1[10]: 123
cardinality(arr1): 10
max_cardinality(arr1): 2147483647
arr2[10]: 123
cardinality(arr2): 1
max_cardinality(arr2): NULL

array_first(arr1): 1
array_last(arr1): 10
array_first(arr2): 10
array_last(arr2): 10

DB20000I  The SET SERVEROUTPUT command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

数组的常用函数

cardinality()

对于简单数组，给 arr1[10] 赋值后，其cardinaity（基数）是10，也就是说 arr1[1] 到 arr1[9] 都被隐式初始化为 NULL 了。而对于关联数组，给 arr2[10] 赋值后，其cardinaity（基数）是1，也就是说只有 arr2[10] 被赋值了。

max_cardinality()

简单数组的 max_cardinality 是一个很大的数字，而关联数组的 max_cardinality 是 NULL 。

array_first() 和 array_last()

可用 array_first() 和 array_last() 函数获取数组的第一个和最后一个元素的下标。注意有可能是 NULL 值。

array_next() 和 array_prior()

同理，可用 array_next() 和 array_prior() 函数获取下一个或者上一个元素的下标。注意有可能是 NULL 值：

set nextindex = array_next(arr1, index)

trim_array()

可用 trim_array() 函数来截取数组的一部分，比如，若只想保留前2个元素：

set arr1 = trim_array(arr1, 2)

array_delete()

类似的，可用 array_delete() 函数来删除数组或者一部分，当然，要删除一部分，只适用于关联数组：

set arr1 = array_delete(arr1)

set arr2 = array_delete(arr2, 1, 2)

注意：第二行代码只适用于关联数组。

array_exists()

可用 array_exists() 函数查询数组元素是否存在，例如：

if (array_exists(arr1, 2)) then
	......
end if

unnest()

我理解 unnest 是 nest （嵌套）的反义词，也就是展开，具体是指把数组的每个元素变成result table的一行，简而言之就是把数组转化为表。

创建文件 test5.sql 如下：

create or replace type intArray as int array[100]@

set serveroutput on@

begin
	declare arr1 intArray;
	declare n int;

	set arr1 = array[5, 6, 7, 8];

	set n = (select count(*) from (select t1.c1 from unnest(arr1) as t1(c1)));

	call dbms_output.put_line(n);
end@

set serveroutput off@

运行脚本 test5.sql ，如下：

➜  temp0528 db2 -td@ -f test5.sql 
DB20000I  The SQL command completed successfully.

DB20000I  The SET SERVEROUTPUT command completed successfully.

DB20000I  The SQL command completed successfully.

4

DB20000I  The SET SERVEROUTPUT command completed successfully.

array_agg()

把一堆元素聚集成一个数组。一般可以从表的某列作为源，所以简而言之就是把表转化为数组。

创建文件 test4.sql 如下：

create or replace type intArray as int array[100]@

set serveroutput on@

begin
	declare arr1 intArray;
	declare i int;

	set arr1 = (select array_agg(c1) from t1);

	set i = 1;
	while (i <= cardinality(arr1)) do
		call dbms_output.put_line(arr1[i]);
		set i = i + 1;
	end while;
end@

set serveroutput off@

运行脚本 test4.sql （需要确保表 t1 以及列 c1 存在），如下：

➜  temp0528 db2 -td@ -f test4.sql 
DB20000I  The SQL command completed successfully.

DB20000I  The SET SERVEROUTPUT command completed successfully.

DB20000I  The SQL command completed successfully.

1
2

DB20000I  The SET SERVEROUTPUT command completed successfully.

该例中，把表 t1 的 c1 字段的值聚集到了数组 arr1 里。

示例

本例是数组元素求和。创建文件 test2.sql 如下：

create or replace type myarray as int array[]@

create or replace procedure mysum(in p1 myarray, out p2 int)
begin
	declare i, n int;

	set n = cardinality(p1);

	set i = 1;

	set p2 = 0;

	while (i <= n) do
		set p2 = p2 + p1[i];
		set i = i + 1;
	end while;
end@

set serveroutput on@

begin
	declare arr1 myarray;
	declare total int;

	set arr1 = array[5, 6, 7, 8];
	call mysum(arr1, total);

	call dbms_output.put_line('total = ' || total);
end@

set serveroutput off@

运行脚本 test2.sql ，如下：

➜  temp0528 db2 -td@ -f test2.sql
DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SET SERVEROUTPUT command completed successfully.

DB20000I  The SQL command completed successfully.

total = 26

DB20000I  The SET SERVEROUTPUT command completed successfully.

参考

• https://www.ibm.com/docs/en/db2/11.5?topic=data-developing-routines
• https://www.ibm.com/docs/zh/db2/11.5?topic=data-developing-routines （中文版）