Db2 SQL PL的锚点类型和行数据类型

环境

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

锚点数据类型

锚点类型是基于另一个数据库对象而定义的类型，比如列、全局变量、SQL变量等。

锚点变量与普通变量的区别在于，前者将会引用到某个具体的东西上。从这个角度看，我感觉锚点类型有点类似于“外键”的概念。

创建表 tab1 和 tab2 如下：

CREATE TABLE tab1(col1 INT, col2 CHAR)

INSERT INTO tab1 VALUES (1,2)

INSERT INTO tab1 VALUES (3,4)

CREATE TABLE tab2 (col1a INT, col2a CHAR)

创建文件 test1.sql 如下：

CREATE OR REPLACE PROCEDURE p1()
BEGIN
  DECLARE var1 ANCHOR tab1.col1;

  SELECT col1 INTO var1 FROM tab1 WHERE col2 = 2;

  INSERT INTO tab2 VALUES (var1, 'a');
END@

CALL p1()@

运行脚本 test1.sql ，如下：

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

  Return Status = 0

查看表 tab2 ，可见多了一行记录：

➜  temp0527 db2 "select * from tab2"

COL1A       COL2A
----------- -----
          1 a    

  1 record(s) selected.

本例中，锚点变量 var1 定义为引用 tab1 表的 col1 列，接下来赋值为特定行的 col1 列的值，最后就可以像一般变量一样使用 var1 。

注意，给 var1 赋值时，可以没有满足条件的值，但不能有多个满足条件的值。

• 如果 SELECT col1 FROM tab1 WHERE col2 = 2 结果为空，则 var1 的值为 NULL ，没有问题（这一点也与外键一致，外键的值可以为空，即不引用任何具体值）；
• 如果 SELECT col1 FROM tab1 WHERE col2 = 2 结果有多个值，则会报错；

创建 test2.sql 文件如下：

CREATE OR REPLACE PROCEDURE p2(in var1 anchor tab1.col1)
BEGIN
  INSERT INTO tab2 VALUES (var1, 'b');
END@

CALL p2(100)@

运行 test2.sql 脚本，如下：

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

  Return Status = 0

结果有点出乎意料， 100 并不是 tab1 表的 col1 字段的值，但是也没报错， tab2 里插入了一条 (100, 'b') 的记录。既然这样，那声明为 anchor tab1.col1 又有什么用呢？

行数据类型

行变量，顾名思义，就是代表了一行记录。

要使用行变量，就要先创建行数据类型，比如：

create or replace type myrow as row(c1 int, c2 varchar(100))

然后就可以声明行变量了，比如：

declare r1 myrow

创建行变量时，其中的每个字段会初始化为 NULL 值。

可将下列类型的值指定给行变量：

• literal

set r1 = (1, 'aaa')

• 表达式

values (1, 'aaa') into r1

• 函数返回值

set r1 = f1()

• 其它变量

set r1 = r2

注意： r1 和 r2 必须是相同或兼容类型。如果 r1 和 r2 是完全不同的数据类型 myrow1 和 myrow2 ，即使 myrow1 和 myrow2 定义一样，也不能通过 set r1 = r2 来赋值，因为二者类型不兼容。

• 查询结果

set r1 = (select c1, c2 from t1)

注意：必须是0行或单行结果。

• NULL

注意：各个字段设置为 NULL ，但行变量本身并不为 NULL 。

行变量赋值方式：

• set

参见前面例子。

• values into

参见前面例子。

• select into

select 1, 'aaa' into r1 from sysibm.sysdummy1

• fetch into

declare c1 cursor for select 1, 'aaa' from sysibm.sysdummy1;

open c1;

fetch c1 into r1;

close c1;

对行变量的字段（即 <行变量>.<列> ）赋值的类型和方式和对行变量赋值的类型和方式类似。

不能直接比较两个行变量，只能其比较其字段值。

可以在insert语句中使用行变量，比如：

insert into t1 values r1

等价于：

insert into t1 values (r1.c1, r1.c2)

可以在一个insert语句中插入多个行变量：

insert into t1 values r1, r2

下面看一个完整例子。

创建文件 test3.sql 如下：

create or replace type myrow as row(c1 int, c2 varchar(100))@

set serveroutput on@

begin
	declare r1, r2 myrow;

	declare c1 cursor for select 3, 'ccc' from sysibm.sysdummy1;

	set r1 = (1, 'aaa');

	call dbms_output.put_line('1)' || r1.c1 || ', ' || r1.c2);

	set r2 = r1;

	call dbms_output.put_line('2)' || r2.c1 || ', ' || r2.c2);

	set r1 = null;

	call dbms_output.put_line('3)' || coalesce(cast(r1.c1 as varchar(100)), '') || ', ' || coalesce(r1.c2, ''));

	select 2, 'bbb' into r1 from sysibm.sysdummy1;

	call dbms_output.put_line('4)' || r1.c1 || ', ' || r1.c2);

	open c1;

	fetch c1 into r1;

	close c1;

	call dbms_output.put_line('5)' || r1.c1 || ', ' || r1.c2);
end@

set serveroutput off@

drop type myrow@

运行脚本 test3.sql ，如下：

➜ temp0527 db2 -td@ -f test3.sql 
DB20000I  The SQL command completed successfully.

DB20000I  The SET SERVEROUTPUT command completed successfully.

DB20000I  The SQL command completed successfully.

1)1, aaa
2)1, aaa
3), 
4)2, bbb
5)3, ccc

DB20000I  The SET SERVEROUTPUT command completed successfully.

DB20000I  The SQL command completed successfully.

注：在 3) 处，由于 NULL 和其他字符串拼接以后还是 NULL ，所以这里使用 coalesce() 函数将 NULL 转为 '' ，但是 int 类型的 NULL 和 '' 不兼容，所以先强制转换为 varchar 类型，然后再使用 coalesce() 函数。

参考

• 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 （中文版）