DB2 for IBM i 7.1 新功能: 对 SQL 数组的支持(转)

转自：http://www.ibm.com/developerworks/cn/ibmi/library/i-cn-db2array/

简介

我们都知道，在一般的过程性语言（Procedure languages）中，诸如 Java，是通过集合（collection）的形式来管理同类型的多个数值的。比如说，可以用一个集合来存放电话列表，或者名字列表等。而在 SQL 过程性语言（以下简称 SQL PL）中，传统的方法是依靠关系表这种结构来表示并存储同类型的数据集合。随着用户程序的复杂化，应用程序与数据库之间的数据交换也愈发的复杂多样。传统的方法也越来越低效与不便。考虑这样一个例子，一个项目经理负责 10 个项目，每个项目有特定的项目名。他可能需要查询某一个项目，也可能需要查询全部的这 10 个项目，也可能是任意若干个项目。是否设计出一个存储过程，能够接受的输入参数，是变化个数的项目数呢？答案是，在 SQL PL 中支持数组数据类型！

继 DB2 9.5 for Linux, UNIX and Windows（以下简称 DB2 9.5 for LUW）以及 DB2 for z/OS 之后，在 IBM i 7.1 版本中，对于数组类型在 SQL PL 中的支持，无疑为软件开发商和开发人员带来了好消息。DB2 for i 7.1 中对数组的支持与 DB2 9.5 for LUW 中对数组的支持是兼容的。同时，IBM Toolbox for Java JDBC 驱动程序也能够通过标准 JDBC API 传递数组参数。同时，Database Monitor 和 Visual Explain 工具也进行了增强，从而能够支持使用数组的应用开发。

在本文中，我们将首先简要的介绍数组类型的基本使用方法，然后通过一个实例演示如何使用数组完成用户程序，最后，我们将描述在 IBM i 平台上对于数组支持的一些特性和限制。

回页首

数组类型基本使用方法

数组的基本使用方法包括数组类型的建立，数组变量的构造，数组元素的赋值，引用，表述，数组的修剪等。下面分别举例说明。更多的细节和具体使用方法可以参考 SQL 用户手册。

建立数组数据类型

一个数组数据类型其实就是一个用户定义类型（UDT），被定义为内置数据类型的数组。如同其他UDT一样，数组类型也是通过CREATE TYPE语句来建立。而数组变量也总是与一个数组数据类型相关联的。数组数据类型定义有一个最大基数（maximum cardinality）。这个最大基数定义了这个数组的一个范围，即此数组最多的元素个数也不能超过这个数目。注意，一个数组类型的变量最大不能超过 4 GB。如果数组类型在定义时没有指明最大基数，则 DB2 会根据数组元素数据类型的大小自动分配一个数值。

清单 1 中列出了几个典型的建立数组数据类型的语句。

清单 1. 建立数组数据类型

        
CREATE TYPE IDList AS INTEGER ARRAY[1000];
CREATE TYPE Projects AS VARCHAR(100) ARRAY[50];
CREATE TYPE Resumes AS XML ARRAY[];

数组类型建立之后，相关的数据类型信息可以在系统编目表中查询到。系统表QSYS2.SYSTYPES中的每一行都包含了一个数据类型的信息，数组数据类型也是一样的。注意，列METATYPE的值如果是’A’，则表明这是一个数组类型，如果是’U’，则是一个用户定义类型，如果是’S’，则表示是一个系统自定义类型。另外，列source_type表示这个数组类型的每一个元素是哪种数据类型的，常用的如”INTERGER”, ”VARCHAR”, “GRAPHIC” 或者”FLOAT”等等。同时，我们可以通过列maximum_cardinality获得此数组类型定义的最大基数，而如果此数组的基本数据类型是字符类型的话，列CCSID表示了此字符类型所使用的字符集编码。

数组变量的声明，赋值与修剪

当数组类型建立之后，我们就可以在SQL存储过程中声明并使用数组变量了。

声明的格式与其他数据类型是一样的，如清单 2 所示：

清单 2. 声明数组变量

        
DECLARE intArrayVal1 IntArray;
DECLARE charArrayVal VarcharArray;

声明之后的数组变量可以通过构造函数或赋值语句进行赋值。选用哪种方式取决于程序中数组变量实际的使用情况。当这个数组中所有的元素都已知的时候，这个数组可以仅仅通过一个操作，利用数组构造函数，就建立起来。有两种类型的数组构造函数：

• 枚举构造函数
• 查询构造函数

枚举构造函数允许你指明通过逗号隔开的所有的元素值。如清单 3 所示，这些值都是字符串，这也是数组构造时最常用的一种方法。

清单 3. 枚举构造函数

SET StuList = ARRAY['Cynthia', 'Andy', 'Tom', ‘Mary’];

而查询构造函数则提供了一种通过查询语句建立数组的方式。见下例:

清单 4. 查询构造函数

SET StuList = ARRAY[SELECT name FROM allmembers WHERE occupation = 'student'];

除了数组构造函数，还有一种单独操作数组元素进行赋值的方法。通过清单 5 所示的方法，可以很容易的用赋值语句将一个元素加入数组中：

清单 5. 单独操作数组元素赋值

SET StuArray[1] = ‘Cynthia’;
SET TeamMember[i] = (select count(headcount) from namelist where teamcode = i);
SET testArray[10] = null;

注意，当一个由某给定下标所表示的数组元素被赋予某一个值时，所有这个数组中先前没有被赋值的数组元素都将自动被置成空值（NULL）。

数组与表的互换

DB2 提供了两个非常强大的函数，能够支持数组变量与关系表之间的互换。通过 ARRAY_AGG 聚合函数，可以将查询结果转换成数组。如清单 6 所示：

清单 6. ARRAY_AGG 函数示例

SET bonusList = SELECT ARRAY_AGG(salary * 0.18) FROM EMPLOYEE;
		

ARRAY_AGG 函数也可以使用在一个 SELECT INTO 语句的 SELECT 子句中，如清单 7 所示：

清单 7. 使用在 SELECT 子句中的 ARRAY_AGG 函数

SELECT ARRAY_AGG(salary ORDER BY salary) INTO salaryList FROM EMPLOYEE;

而与这个函数相对应的 UNNEST 函数，则可以将数组转换成表。我们知道，当处理一个数据集合时，常常需要在 SQL 语句中处理这个集合中的数据，比如将数据插入到一个关系表中，或者将其与某关系表进行连接操作（JOIN）。所以这个时候，UNNEST 操作符就大显身手了，它可以使这些步骤在一个操作中就完成。UNNEST 函数接受一个或多个数组变量作为参数，返回一个表，这个表的每一列都代表相应的一个数组中的元素。这个生成表的行数等于这些作为输入参数的数组中最大的那个基数。清单 8 中的示例展示了一个典型的 UNNEST 使用方法。

清单 8. UNNEST 函数示例

INSERT INTO proj_list
       (SELECT projId, TB1.index, TB1.prjid, TB1.prjowner
        FROM UNNEST(projIds, prjowners)
             WITH ORDINALITY as TB1(prjid, prjowner, index)

回页首

数组使用范例

清单 9 提供了一个典型的用户使用范例来完整的展示数组数据类型在 SQL PL 中的使用方法。其中包括数组的建立，数组变量的声明，赋值，引用，以及 UNNEST，ARRAY_AGG 函数的使用场景。

范例场景说明：此用例基于示例数据库中的 employee 表。管理层需要根据项目绩效选出若干个年度最佳项目，并且给参与这些项目的员工办法绩效奖金。奖金基于此员工工资按一定比例来计算。

使用一个 varchar 的数组来存储所选出的项目名称。

使用一个存储过程来计算奖金。这个存储过程接受此数组和奖金计算百分比来作为输入参数。

清单 9. 数组使用范例

//Create a table "bonus_temp" to store employee ID and corresponding bonus.
CREATE TABLE bonus_temp (empno varchar(6), bonus double)
//Create ARRAY types to store the values for employee ID, bonus and projects.
CREATE TYPE projects AS VARCHAR(20) ARRAY[10]
//Create the ARRAY type "employee"
CREATE TYPE employees AS VARCHAR(6) ARRAY[20]
//Create the ARRAY type "bonus"
CREATE TYPE bonus AS DOUBLE ARRAY[20]

//Create a stored procedure to calculate the bonus
CREATE PROCEDURE bonus_calculate (IN projs projects, IN percentage integer)
BEGIN
DECLARE emp_array employees;
DECLARE bonus_array bonus;

-- Select the IDs and corresponding bonus in corresponding ARRAY type
-- "employees" and "bonus" using aggregate function 
-- ARRAY_AGG.
SELECT cast(array_agg(employee.empno) AS employees), 
       cast(array_agg(.10*salary) AS bonus) INTO emp_array,bonus_array  
  FROM vempprojact, unnest(projs) AS P(id), employee 
  WHERE P.id=vempprojact.projno AND employee.empno=vempprojact.empno;

-- Use UNNEST function to select the ARRAY elements from ARRAY 
-- variables and insert the same in "bonus_temp" table.
INSERT INTO bonus_temp 
   SELECT T.empno, T.bonus 
     FROM unnest(emp_array, bonus_array) 
     WITH ORDINALITY AS T(empno,bonus, idx); 
END

--Call the stored procedure to calculate the bonus.Input to this stored procedure is the 
--ARRAY of all projects which are applicable for bonus

Call bonus_calculate(ARRAY['AD3111', 'IF1000', 'MA2111'], 10)

--Select the data from the table "bonus_temp"
SELECT empno, bonus FROM bonus_temp

回页首

数组类型的使用限制

在 DB2 for IBM i 7.1 版本中，数组类型有一些使用限制。也就是说这些使用方式会导致 SQL 语句报错。其中一些是 DB2 for LUW 等版本已经支持而 DB2 for IBM i 7.1 还没有支持的。在这里罗列出来，请大家注意。

清单 10. 受限制的数组使用方法

1. SELECT * FROM T, UNNEST(ARRAY[t.c1,2])  
2. SELECT CAST(ARRAY[f1, f2, f3] as myType)[f4] FROM T 
3. set v1 = CAST(ARRAY[NULL] as int_array)  
4. set v_iarr1 = CAST(TRIM_ARRAY(v_iarr1,4) as int_array)  
5. Using ARRAY constructor inside CARDINALITY function 
6. Using ARRAY constructor as IN parameter in call stmt   
7. Using ARRAY constructor in CAST function on RHS of set stmt. 
8. TRIM_ARRAY(TRIM_ARRAY(v1, n1), n2)  
9. cardinality(TRIM_ARRAY(v,n1))

回页首

DB2 for IBM i 中对于数组类型的其他支持

在 DB2 for IBM i 中，除了在 SQL PL 语言中提供了对数组类型的支持以外，还有一些其他方面的更新也与数组类型相关。对于 i 平台上的开发人员，这些信息也是至关重要的。下面分别描述。

DB2 for i 7.1 QAQQINI 参数中提供了一个新的选项，即 ALLOW_ARRAY_VALUE_CHANGES。缺省值是“NO”，表示当查询优化器在查询中遇到对于数组变量的引用时，将对这个数组变量建立一个临时拷贝，并一直使用这个拷贝中的数值，从而避免了产生不可预知的结果的可能性。即查询优化器在查询运算的过程中对于数组变量的改变不会影响到查询结果的计算。而如果设置成“YES”，则代表查询优化器不会建立临时拷贝，而是直接使用数组变量拷贝中数组元素的数值。当然这种方式减少了程序的存储和运算消耗，但是却可能导致运算结果的不确定性，而这种情况是一个健壮的程序所需要避免的。当然，如果开发者能够保证程序中的数组变量在赋值之后不会被改变，则可以将 ALLOW_ARRAY_VALUE_CHANGES 选项设置成”YES”，这样在获得更好的执行效率的同时也不会影响程序结果的稳定性，是一种较好的实践方式。

另外，在 Database Monitor 文件中，也增加了一个新的记录类型，用来描述 SQL 查询中的数组变量。这个记录类型的 ID 是 3011。我们可以用清单 11 示例的语句来查询 Database Monitor 文件，从而获得其中数组变量的相关信息，包括数组名称，数组类型的最大基数，数组变量的基数，数组变量当前使用下标，数组变量元素值等。

清单 11. 查询 Database Monitor 文件

select
QQC101 AS array_name,
QQI1 AS max_cardinality, 
QQI2 AS cardinality, 
QQI3 AS index_position,  
VARCHAR(QQDBCLOB1, 100) as array_values
from monfilelib.mydbmon where QQRID =3011

回页首

总结

这篇文章简要的描述了 DB2 for i 7.1 版本中对数组数据类型的支持。通过在 SQL PL 中支持数组这种非常方便的数据表现方式，可以非常简单的处理在应用程序和存储过程之间传递的集合型的数据流。本文首先介绍了基本的数组操作（包括数组类型的建立，数组变量的声明和赋值，数组与关系表之间的互换等）, 然后通过一个具体程序范例展示了如何利用数组类型实现一个典型的用户场景。另外本文介绍了 DB2 for IBM i 7.1 版本中对于数组类型的一些使用限制，以及 IBM i 开发者所必须了解的系统对于数组的其他支持。关于数组类型更详细的内容，请参阅参考资源中的“IBM i 7.1 信息中心”。请记住对于数组的支持只是 IBM i 7.1 所引入的许多主要的 SQL 新功能之一。我们后面会有更多的文章来为您一一展现这些新功能。