环境
- 操作系统: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.