DB2 V9.5版本中数据库应用程序移植的改进

• 支持在过程和调用过程的应用程序中使用 ARRAY 数据类型。

• 支持全局变量。全局变量是已命名的内存变量，您可以通过 SQL 语句访问和修改此变量。版本 9.5 支持已创建的会话全局变量，这些变量与特定会话相关联并包含该会话独有的值。

• 支持其他供应商的 SQL 方言。版本 9.5 包含对 DECODE、NVL、LEAST 和 GREATEST 函数的支持。

数组数据类型

在以前的DB2版本，碰到数据集合处理的程序移植，我们只能通过临时表或游标来处理，这是非常麻烦的。

版本 9.5 支持 ARRAY 集合数据类型。您可以在存储过程和应用程序中对它们进行处理，此功能使从其他数据库供应商移植已支持数组的应用程序和存储过程更容易。

您可以使用数组以在应用程序与存储过程之间更有效率地传递数据以及存储和处理 SQL 过程中的瞬态数据集合，而不必使用关系表。SQL 过程中可用的数组运算符允许更有效率地存储和检索数据。

版本 9.5 中对数组数据类型的支持允许您执行以下操作：

• 根据数组创建用户定义的类型；例如，CREATE TYPE INT10 AS INTEGER ARRAY[10] 定义最多 10 个整数值的数组的类型；

• 在存储过程和应用程序中声明数组类型的变量和参数；

• 创建和处理数组值，数组处理的基本部分包括数组构造方法、设置子下标、元素计数和整理；

• 在 JDBC 和 CLI 应用程序与 SQL 和 Java 存储过程之间来回传递数组；

• 将数组转换为表（每个表行一个数组元素）以及将聚集列转换为数组，以便更容易在数组与 SQL 之间通过接口进行连接；

• 使用输入和输出数组参数从命令行处理器调用过程。

ARRAY 数据类型定义：

CREATE TYPE type-name AS data-type ARRAY [integer-constant]

其中：data-type 不支持LONG VARCHAR, LONG VARGRAPHIC, LOB, XML类型， integer-constant 最大值2147483647

与ARRAY相关的函数：

ARRAY_AGG 将行数据集聚集成数组

UNNEST 将数组数据转成行数据

CARDINALITY 返回数组中的元素号，类型 BIGINT

MAX_CARDINALITY 返回数组中所含的最大元素号，类型 BIGINT

注意，这些函数只用于SQL procedure

SELECT T.PHONE, T.ID, T.INDEX FROM UNNEST(PHONES, IDS)

WITH ORDINALITY AS T(PHONE, ID, INDEX)

ORDER BY T.INDEX

Example 1

这个例子包含2个存储过程, sum 和 main. 存储过程 main 建立一个含有6个元素（integer）的数组。传递这个数组给存储过程sum, 计算出所有元素值的和并返回给调用存储过程 main. 存储过程 sum 说明了array subindexing 和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 @

Example 2

这个例子，我们使用2个数组类型(intArray 和 stringArray)，使用一个包含列(id 和 name)的 persons 表。 存储过程 processPersons 添加3个人到 persons表, 并返回person名字的数组，按id排序，名字中包含字母 'o'。 3个persons 的 id 和 name 作为数组元素(ids 和 names)添加到数组中。这些数组使用UNNEST 函数，将数组数据以包含2列数据的表的表达形式表达，并插入到persons 表中。最后使用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 @

Example 3

这个例子说明在java代码中如何调用含有数组参数的存储过程，本例子中存储过程bonus_calculate含有2个输入参数，一个是数组projs，一个是整形percentage

………

String sql = 'CALL bonus_calculate(?, ?)';

CallableStatement callStmt = con.prepareCall(sql);

// Create an SQL Array

projects[0] = 'AD3111';

projects[1] = 'IF1000';

projects[2] = 'MA2111';

java.sql.Array projectArray=con.createArrayOf('VARCHAR',projects);

// set IN parameters

callStmt.setArray(1, projectArray);

callStmt.setInt(2,percentage);

// call the procedure

callStmt.execute();

…………

CREATE PROCEDURE bonus_calculate (IN projs projects, IN percentage integer)

BEGIN

DECLARE emp_array employees;

DECLARE bonus_array bonus;

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;

INSERT INTO bonus_temp

SELECT T.empno, T.bonus

FROM unnest(emp_array, bonus_array)

WITH ORDINALITY AS T(empno,bonus, idx);

END@

全局变量

在以前的DB2版本，碰到含全局变量处理的移植程序移植，我们只能通过全局临时表来处理，使用起来非常麻烦。

全局变量改进了 SQL 语句之间的数据共享。版本 9.5 引入了全局变量的概念，它们是命名的内存变量，可以通过 SQL 语句访问和修改这些变量。全局变量使您能够在运行于同一会话（或连接）的不同 SQL 语句之间共享数据，而不需要应用程序逻辑来支持此数据传输。

应用程序不再需要发出这种语句来将值从一个语句的输出自变量（如主机变量）复制到另一个语句的输入自变量。此外，包含在数据库系统本身中的 SQL 语句（如定义触发器和视图的语句）现在可以访问此共享信息。

全局变量有助于您为数据库本身中的数据传输实现更复杂的交互式模型，以便您不必在应用程序或 SQL 过程中放置支持逻辑。与全局变量相关联的已定义特权确保所传送数据的安全性不会升高到强制通过应用程序逻辑。如果安全性存在问题，那么您可以通过 GRANT 和 REVOKE 语句来控制对全局变量的访问。

存储静态的、在会话期间很少更改的或者进行了管理控制的数据时，全局变量特别有用。用于将警报发送至 DBA 的寻呼机号以及指示是启用还是禁用某些触发器的指示器就是这种数据的示例。

版本 9.5 支持已创建的会话全局变量。会话全局变量与特定会话相关联并包含该会话独有的值。已创建的会话全局变量可用于任何正在您定义该变量的数据库上运行的活动 SQL 语句。系统目录包含已创建的会话全局变量的定义以及与这些全局变量相关联的特权。

例子：

创建全局变量：

CREATE VARIABLE myjob_current varchar (10) DEFAULT ('soft-engg');

获取全局变量信息：

SELECT substr (varschema, 1, 10) as varschema,

substr (varname, 1, 10) AS varname,

varid, substr(owner,1,10) AS owner,

ownertype, create_time,

substr(typeschema,1,10) AS typeschema,

substr(typename,1,10) AS typename, length

FROM syscat.variables

WHERE varname = 'MYJOB_CURRENT';

给用户praveen 和 sanjay 读和写权限，剥夺用户sanjay写权限：

GRANT READ, WRITE ON VARIABLE myjob_current TO USER praveen, USER sanjay;

REVOKE WRITE ON VARIABLE myjob_current FROM USER sanjay;

获取用户 praveen 和 sanjay 的全局变量权限信息：

SELECT substr (varschema, 1, 10) AS schema,

substr (varname, 1, 10) AS name,

substr(grantor,1,10) AS grantor, grantortype AS Rtype,

substr(grantee,1,10) AS grantee, granteetype AS Etype,

readauth, writeauth

FROM syscat.variableauth

WHERE varname ='MYJOB_CURRENT'; ]

在触发器中使用全局变量，本例是设计一个disable触发器示例：

CREATE VARIABLE disable_trigger char (1) DEFAULT ('N');

CREATE TRIGGER validate_t BEFORE INSERT ON EMPLOYEE

REFERENCING NEW AS n FOR EACH ROW

WHEN (disable_trigger = 'N' AND n.empno > '10000')

SIGNAL SQLSTATE '38000'

SET message_text = 'EMPLOYEE NUMBER TOO BIG and INVALID';

SET disable_trigger = 'Y'; 使触发器失效

简化用户权限检索：

CREATE VARIABLE schema1.gv_workdept CHAR

DEFAULT ((SELECT workdept FROM employee

WHERE firstnme = SESSION_USER));

CREATE VIEW schema1.emp_filtered AS

SELECT * FROM employee

WHERE workdept = schema1.gv_workdept;

在存储过程，嵌入C，java程序中都可使用全局变量。

新的函数

新的函数简化了应用程序移植，版本 9.5 包含一些与其他数据库供应商使用的标量函数同名的新标量函数。当您将现有应用程序移植到版本 9.5 中时，您可以继续使用其他供应商使用的函数名称，而无需更改代码。

提供了以下新的标量函数：

• NVL（现有的 COALESCE 和 VALUE 函数的同义词）

• LEAST 或 MIN（互为同义词）

• GREATEST 或 MAX（互为同义词）

• DECODE（类似于现有的 CASE 表达式）