内容


在 SQL PL 中使用阵列交换数据

在应用程序和存储过程之间交换数据的新功能

Comments
免费下载:IBM® DB2® Express-C 9.7.2 免费版 或者 DB2® 9.7 for Linux®, UNIX®, and Windows® 试用版
下载更多的 IBM 软件试用版,并加入 IBM 软件下载与技术交流群组,参与在线交流。

介绍

关系模型的大部分功能都来自于这样一个事实:关系表是代表同质数据集合的单一数据结构。表是表示现存数据和查询结果的便利方式。

然而,有时使用表来表示集合会导致大量且可能低效的代码。例如,考虑通过客户已购买的项目的列表调用存储过程的应用程序。在没有诸如阵列的集合类型的情况下,应用程序需要在调用存储过程之前在表中存储项目的列表,使存储过程通过从表中选择来读取列表,然后再删除该列表。

要避免使用表的相关开销,应用程序可以一些字符串格式对项目列表进行编码,并使存储过程对该字符串进行解码以便提取项目。与只传递表示为阵列的列表相比,两种解决方法都很繁琐且没有效率。

DB2 9.5 引入了对 SQL PL 中的阵列数据类型的支持,以便帮助解决类似上面所述的那些应用程序开发问题。鉴于阵列在所有主要编程语言中阵列都是主要部分,所以阵列的概念无需做更多的解释。相反,看一个对 SQL PL 中的阵列进行说明的简单示例。清单 1 显示了一个阵列数据类型和使用该类型的过程。

清单 1. 阵列数据类型和使用该类型的过程
1.  CREATE TYPE phonenumbers AS VARCHAR(12) ARRAY[1000]
2.  -- Procedure find_customers searches for numbers in
3.  -- numbers_in that begin with the given area_code,
4.  -- and reports them in numbers_out.
5.  -- Phone numbers are strings of the form 416-413-9394
6.  CREATE PROCEDURE find_customers(
7.         IN numbers_in phonenumbers,
8.         IN area_code CHAR(3),
9.         OUT numbers_out phonenumbers)
10.  BEGIN
11.    DECLARE i, j, max INTEGER;
12.    SET i = 1;
13.    SET j = 1;
14.    SET numbers_out = NULL;
15.    SET max = CARDINALITY(numbers_in);
16.    WHILE i <= max DO
17.      IF substr(numbers_in[i], 1, 3) = area_code THEN
18.        SET numbers_out[j] = numbers_in[i];
19.        SET j = j + 1;
20.      END IF;
21.      SET i = i + 1;
22.    END WHILE;
23.  END

清单 1 中的第一行显示了创建名为 phonenumbers 类型的创建类型 语句。此类型具有最大基数 1000 — 这意味着此类型的值可以具有 0 到 1000 个元素,且其元素类型 为 VARCHAR(12)。过程 find_customers 采用类型为 phonenumbers 的两个参数(一个为 IN,另一个为 OUT),并通过以给定区域代码开始的输入数字来填充 OUT 参数。正如在此示例中所看到的,使用熟悉的方括号语法来执行阵列子索引,且第一个元素的子索引为 1。函数 CARDINALITY 用于第 15 行,返回输入阵列中的元素数量。WHILE 循环扫描输入阵列;在元素以所需的区域代码开始时,将其添加到输出阵列。

DB2 在运行时使用阵列类型的最大基数来确保下标在在限制范围内。在此示例中,用于类型 phoneList 变量上的子索引小于 1 或大于 1000 时,DB2 会提醒您运行时错误。如果在定义阵列类型时没有明确其最大基数值,那么可省略。此步骤被显示在以下清单中:

CREATE TYPE unboundedPhoneList AS VARCHAR(12) ARRAY[];

省略类型定义中的最大基数时,此类型值上的子索引范围从 1 到整数类型的最大正值。然而,阵列的已分配内存是基于其基数的,而不是基于其类型的最大基数。因此,省略最大基数不会对已分配的内存量产生任何影响。我们将在本文的后面讨论内存管理的更多细节。

将元素添加到阵列中

清单 1 示例中,新的元素总是附加在末尾,因此每次添加一个元素,numbers_out 的基数就增加 1,如第 18 行所示。然而,一般情况下,可以以任意顺序设置阵列的元素。

在阵列变量为 NULL 或被指定的元素位于超过当前基数的位置上时,最古老的元素和新元素之间的所有元素都被隐式分配 NULL 值。例如,假设类型 phonenumbers 的变量 phoneList 现在是 NULL。执行以下语句后

SET phoneList[10] = '416-413-9394';

phoneList 的基数为 10,在位置 1 到 9 中的元素为 NULL。位置 11 以及更高位置中的元素都不存在(请注意这不同于 NULL)。因此,诸如下面的语句

SET phone = phoneList[11];

会创建运行时错误,因为它引用不存在的元素。

如果您现在执行语句

SET phoneList[12] = '416-413-7727';

phoneList 的基数将会为 12,而语句

SET phone = phoneList[11];

将设置变量电话为 NULL

使用阵列构造函数

不必一次初始化一个阵列元素,如清单 1 所示。在已知阵列中所有元素的值时,通过阵列构造函数可在单一操作中创建阵列。有两种类型的阵列构造函数:

  • 通过枚举的构造函数
  • 通过查询的构造函数

通过枚举的构造函数允许您将所有元素的值指定为逗号分隔的标量值,如以下两个清单中所示。以下清单中的值都是文字,这是初始化阵列时最常见的情况。一般情况下,枚举中的每一个值都可以成为任意标量 SQL 表达式。

SET phoneList = ARRAY['416-413-9394', '416-413-7727', '416-413-6254'];

清单 2 显示具有 4 个元素的阵列构造函数,其值分别由 NULL 常数、变量、文字和标量子查询提供。

清单 2. 具有四个元素的阵列构造函数
SET phoneList = ARRAY[NULL, phone, '416-413-7727',
                      (SELECT phone
                       FROM authors
                       WHERE author_id = 100)];

没有元素的阵列构造函数表示空阵列(也就是说,基数等于 0 的阵列)。不应将空阵列与 NULL 阵列或与只包含 NULL 值的阵列混为一谈。清单 3 中的每一个语句都将变量 phoneList 设置为不同的值。请注意每条语句旁边的注释。

清单 3. NULL 和空阵列值
SET phoneList = NULL; /* NULL value. */
SET phoneList = ARRAY[]; /* empty array */
SET phoneList = ARRAY[NULL]; /* array with one NULL element */

在单一操作中构建阵列的另一种方式是使用通过查询的构造函数,其创建具有由单列子选择返回的值的阵列,如以下清单所示:

SET phoneList = ARRAY[SELECT phone FROM authors WHERE city = 'Toronto'];

如果子选择返回空表,则构造函数的结果就是空阵列。而且,正如所期望的那样,阵列构造函数中指定的元素数量超过阵列的最大基数时,DB2 会提示您一个错误。

将内存分配到阵列

动态分配并重新分配 SQL PL 中的阵列。在阵列变量值为 NULL 时,不为它分配内存。将此与 C 中的阵列相比,其中整个阵列的空间都是预先分配的。

分配一个初始值给阵列变量时,DB2 会分配足够的内存以便存放此值。随后,如果分配一个不适合已分配内存的新值给变量,DB2 会隐式执行重新分配。

变量的已分配内存量取决于几个因素。如果阵列的最大可能大小(给定其类型)不是太大,则第一次是将值分配给变量时 DB2 会为最大大小分配空间。这可以保证稍后无论多少元素添加到阵列,也无论那些元素的大小,都没有必要重新分配。

多大才算太大由 DB2 基于空闲内存的可用性来决定。如果阵列的最大可能大小被视为太大,则 DB2 只分配足够的空间来存放初始值,并可能分配额外空间来允许一些增长。

调整阵列

在到目前为止的示例中,您已经看到了将阵列放置到一起的不同方式。SQL PL 还提供了 TRIM_ARRAY 函数,以便从阵列中删除元素。以下列表显示了用来从 phoneList 中删除最后两个元素的 TRIM_ARRAY

SET phoneList = TRIM_ARRAY(phoneList, 2);

针对 TRIM_ARRAY 的第二个参数的值必须是 1 和第一个参数的基数之间的值。

将表转化为阵列

在本文的开始,我讲了 SQL PL 中阵列支持背后的主要动机是为关系表提供更简单的替代项,以表示相对小的集合。然而,通常情况下要通过从表中提取值来获得阵列的初始值。同样,很多时候,阵列的内容需要转变为表中的行,要么被存储或与其他表联合。要使阵列和表之间的转换成为可能,DB2 要提供新的 ARRAY_AGG 函数和 UNNEST 运算符。

ARRAY_AGG 函数是将值汇集到阵列中的列函数。

假设您具有下面的表:

表 1. 客户表
CUST_IDNAMEPHONELOCATION
17Joe Cat416-305-3745Toronto
113Tom Dog905-305-3747Ajax
716Sam Bear416-305-3746Toronto
5Jill Bird905-723-1662Markham
221Kim Frog416-478-9683Toronto

假设您具有如表 1 所示的客户部,以下语句将变量电话设置为一个阵列,包含 Toronto 的所有客户电话号码:

SET phones = (SELECT ARRAY_AGG(phone) FROM customers WHERE location = 'Toronto');

以上语句以任意顺序生成电话。如果顺序是相关的,可以在 ARRAY_AGG 中指定 ORDER BY 子句,如以下清单所示:

SET phones = (SELECT ARRAY_AGG(phone ORDER BY name) 
   FROM customers WHERE location = 'Toronto');

请注意,SELECT 语句中的 ORDER BY 子句不会对阵列元素的顺序产生任何影响,因为它将应用于该语句返回的行。在此示例中,只有一个这样的行。

使用通过查询的阵列构造函数可以等效地表示 ARRAY_AGG 的简单使用。然而,ARRAY_AGG 函数还可用于返回多行和/或多列的语句。

例如, 清单 4 中的语句定义一个游标,创建每个城市中客户电话和 ID 的阵列。语句的结果如图 2 中所示。

清单 4. 用于创建每一个城市中客户电话和 ID 的阵列的语句
DECLARE myCursor CURSOR FOR
  SELECT location,
         ARRAY_AGG(phone ORDER BY name) AS PHONES,
         ARRAY_AGG(cust_id) AS IDS
  FROM customers
  GROUP BY location

表 2 显示了清单 4 中语句的结果。

表 2. 语句结果
LOCATIONPHONEID
Toronto
1: 416-305-37451: 17
2: 416-305-37462: 716
3: 416-305-37473: 221
Ajax1: 905-305-37471: 113
Markham1: 905-723-16621: 5

将阵列转化成表

一旦计算了数据集合,就经常需要在 SQL 语句中使用此集合,例如将数据插入表或将其与存储在表中的数据联接。SQL PL 中的 UNNEST 运算符使此类操作成为可能。

在其最简单的形式中,UNNEST 采用阵列并生成单列表。清单 5 中的语句为具有 phoneList 中的电话号码的客户选择客户 ID。

清单 5. 选择客户 ID
SELECT cust_id
FROM customers, UNNEST(phoneList) as T(phone)
WHERE customers.phone = T.phone

在其最一般的形式中,UNNEST 可采用几个阵列作为参数,且其可生成其他列以便反映子索引实施的顺序。对于 清单 6 中的查询,假设 productIdsproductQs 是按顺序包含行项目的 ID 和数量的两个阵列。行项目编号反映在阵列子索引中。清单 6 中的 UNNEST 创建包含三列的表,其将插入到 order_items 表中。WITH ORDINALITY 子句使 UNNEST 生成另一个整数类型的列,包含与每一个元素关联的位置。

清单 6. WITH ORDINALITY 子句
INSERT INTO order_items
       (SELECT orderId, T.index, T.pid, T.pq
        FROM UNNEST(productIds, productQs)
             WITH ORDINALITY as T(pid, pq, index)

要总结阵列和表之间转换的覆盖范围,请重新访问第一个示例的过程 phonenumbers。清单 1 中的版本使用循环来处理输入阵列并填充输出阵列。然而,事实证明该过程中的所有逻辑都可表达为使用 UNNESTARRAY_AGG 的单一 SQL 语句,如清单 7 所示:

清单 7. 重新访问过程 find_customers
1. CREATE TYPE phonenumbers AS VARCHAR(12) ARRAY[1000]
2. -- Procedure find_customers searches for numbers in
3. -- numbers_in that begin with the given area_code,
4. -- and reports them in numbers_out.
5. -- Phone numbers are strings of the form 416-413-9394
6. 
7. CREATE PROCEDURE find_customers(
8. IN numbers_in phonenumbers,
9. IN area_code CHAR(3),
10. OUT numbers_out phonenumbers)
11. BEGIN
12. SET numbers_out =
13.     (SELECT ARRAY_AGG(T.num)
14.      FROM UNNEST(numbers_in) AS T(num)
15.      WHERE substr(T.num, 1, 3) = area_code);
16. END

最后的示例证明虽然阵列允许我们将值集合高效并简洁地传递到存储过程,但一旦在过程中,将阵列转换成表仍非常有用,以便利用 SQL 强大的 set-at-a-time 语义来处理阵列元素。

在客户端上使用阵列

到目前为止的示例演示了 SQL PL 中的阵列。在客户端上,Java 数据库连接(Java Database Connectivity,JDBC)、命令行接口(Command Line Interface,CLI)和命令行处理器 (Command Line Processor) 中都支持阵列。可从这些接口中的任意一个调用具有阵列参数的过程。清单 8 显示了从 CLP 向前面定义的过程 find_customers 发出的 CALL 语句以及 CLP 生成的输出。

清单 8. 从 CLP 调用具有阵列参数的过程
db2 CALL find_customers(ARRAY['416-305-3745', '905-414-4565', '416-305-3746'], '416', ?)

  Value of output parameters
  --------------------------
  Parameter Name  : NUMBERS_OUT
  Parameter Value : [416-305-3745,
                     416-305-3746]

  Return Status = 0

清单 9 显示了 Java 片段,该片段说明如何从 JDBC 应用程序调用过程 find_customers

清单 9. 从 JDBC 应用程序调用过程 find_customers
import java.sql.*;

public class array_sample
{
  public static void main(String argv[])
  {
    com.ibm.db2.jcc.DB2Connection con = null;
    try
    {
      // Connect to the db
      Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
      con = (com.ibm.db2.jcc.DB2Connection)
                DriverManager.getConnection("jdbc:db2:test");

      // Create an array of strings
      String[] phones=new String[10];
      phones[0]="416-305-3745";
      phones[1]="905-414-4565";
      phones[2]="416-305-3746";

      // Create an SQL ARRAY value of type "ARRAY of VARCHAR"
      java.sql.Array phonesArrayIn =con.createArrayOf("VARCHAR", phones);

      // Prepare the call statement
      CallableStatement callStmt =
            con.prepareCall("CALL find_customers(?, ?, ?)");

      // Set IN parameters
      callStmt.setArray(1, phonesArrayIn);
      callStmt.setString(2, "416");

      // Register OUT parameter
      callStmt.registerOutParameter(3, java.sql.Types.ARRAY);

      // Call the procedure
      callStmt.execute();

      // Get value of OUT parameter
      java.sql.Array phonesArrayOut = callStmt.getArray(3);
      String[] phonesOut = (String [])phonesArrayOut.getArray();

      // Print result
      System.out.println("Result:");
      for(int i = 0; i < phonesOut.length; i++)
      {
        System.out.println(phonesOut[i]);
      }

      con.close();
    }
    catch (Exception e)
    {
      e.printStackTrace();
    }
  }
}

清单 10 显示了 DB2 CLI 片段,该片段说明如何从 DB2 CLI 应用程序调用过程 find_customers。

清单 10. 从 CLI 应用程序调用过程 find_customers
#include <stdio.h>
#include <stdlib.h>
#include <string>
#include <sqlcli1.h>
#include <sql.h>
#include <sqlenv.h>

int rc = 0;

int main(int argc, char *argv[])
{
  SQLHANDLE henv; /* environment handle */
  SQLHANDLE hdbc; /* connection handle */
  SQLHSTMT stmt; /* statement handle*/

  char dbAlias[SQL_MAX_DSN_LENGTH + 1];
  char user[10];
  char pswd[10];

  if (argc == 1)
  {
    strcpy(dbAlias ,"TEST");
    strcpy(user, "");
    strcpy(pswd, "");
  }
  else 
  {
    printf("Incorrect usage \n");
    return -1; 
  }
  printf("Connecting to %s\n",dbAlias);

  SQLRETURN cliRC = SQL_SUCCESS;

  /* allocate an environment handle */
  cliRC = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
  if (cliRC != SQL_SUCCESS)
  {
    printf("\n--ERROR while allocating the environment handle.\n");
    return 1;
  }

  /* allocate a database connection handle */
  cliRC = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
  if (cliRC != SQL_SUCCESS)
  {
    printf("\n--ERROR while allocating the database handle.\n");
    return 1;
  }
  printf("\n  Connecting to %s...", dbAlias);

  /* connect to the database */
  cliRC = SQLConnect(hdbc,
                     (SQLCHAR *)dbAlias,
                     SQL_NTS,
                     (SQLCHAR *)user,
                     SQL_NTS,
                     (SQLCHAR *)pswd,
                     SQL_NTS);
  printf("  Connected to %s.\n", dbAlias);

  /* allocate a statement handle */
  cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &stmt);
  if (cliRC != SQL_SUCCESS)
  {
    printf("\n--ERROR while allocating the statement handle.\n");
    return 1;
  }

  int i=0;
  // Declare local C variables to store the input and output to the stored procedures 
  char numbers_in[1000][13]={0};
  char numbers_out[1000][13];
  char area_code[3];

  //declare IPD/APD attributes
  SQLHANDLE hIPD;
  SQLHANDLE hAPD;
  // Define the size of the arrays
  SQLLEN size = 1000;

  sprintf(area_code,"416");
  printf("%s\n",area_code);
  sprintf(numbers_in[0],"416-305-3745");
  sprintf(numbers_in[1],"905-414-4565");
  sprintf(numbers_in[2],"416-305-3746");

  //Bind input/output parameters
  if (SQLBindParameter(stmt, ++i, SQL_PARAM_INPUT, SQL_C_CHAR,
      SQL_VARCHAR, 12, 0, &numbers_in, 13, NULL)!=SQL_SUCCESS || 
      SQLBindParameter(stmt, ++i, SQL_PARAM_INPUT, SQL_C_CHAR,               
      SQL_CHAR, 3, 0, area_code, 3, NULL)!=SQL_SUCCESS || 
      SQLBindParameter(stmt, ++i, SQL_PARAM_OUTPUT, SQL_C_CHAR, 
      SQL_VARCHAR, 12, 0, &numbers_out, 13, NULL)!=SQL_SUCCESS)
  {

    printf ("Binding unsuccessful \n");
    return 1;
  }
  
  //GetStmtAttr for IPD/APDs        
if(SQLGetStmtAttr(stmt,SQL_ATTR_IMP_PARAM_DESC,&hIPD,SQL_IS_INTEGER,NULL)!=SQL_SUCCESS ||
  SQLGetStmtAttr(stmt,SQL_ATTR_APP_PARAM_DESC,&hAPD,SQL_IS_INTEGER,NULL)!=SQL_SUCCESS)
  {
     printf ("GetStmtAttr unsuccessful\n");
     return 1;
  }
   // For input array parameters, define the SQL_DESC_CARDINALITY attribute of IPD 
  if( SQLSetDescField(hIPD,1, SQL_DESC_CARDINALITY,(SQLPOINTER)1000,SQL_IS_INTEGER) 
  !=SQL_SUCCESS || 
   // For output array parameters, define the SQL_DESC_CARDINALITY field for APD 
      SQLSetDescField(hAPD,3, SQL_DESC_CARDINALITY,(SQLPOINTER)1000, SQL_IS_INTEGER) 
      !=SQL_SUCCESS ||
   // For all array parameters, define the SQL_DESC_CARDINALITY_PTR field for APD 
      SQLSetDescField(hAPD,1,SQL_DESC_CARDINALITY_PTR,&size,SQL_IS_INTEGER) 
      !=SQL_SUCCESS || 
     SQLSetDescField(hAPD,3, SQL_DESC_CARDINALITY_PTR, &size, SQL_IS_INTEGER) 
      != SQL_SUCCESS)
  {  
     printf ("SQLSetDescField unsuccessful\n");
     return 1;
  }
  if (SQLPrepare(stmt, (SQLCHAR*)"CALL find_customers(?, ?, ?)", SQL_NTS)!= SQL_SUCCESS)
  {
     printf ("SQLPrepare unsuccessful\n");
     return 1;
  }
  rc = SQLExecute(stmt);
  printf ("SQLExec: rc %d %d \n",rc,SQL_SUCCESS );

  cliRC = SQLFreeHandle(SQL_HANDLE_STMT,stmt);

  /* free the statement handle */
  cliRC = SQLFreeStmt(stmt, SQL_CLOSE);
  return 0;
}

将 DB2 的阵列与 Oracle 的 VARRAY 进行比较

将应用程序从 Oracle 迁移到 DB2 的开发人员将发现在 DB2 中的新阵列支持特别有用,因为阵列通常用于 Oracle PL/SQL。本附录概述了 DB2 中 ARRAY 数据类型与 Oracle 的 VARRAY 之间的主要差异,以及 VARRAY 上最常见的操作如何映射到 ARRAY。

在 DB2 中,类型阵列的变量类似于任何其他 SQL 数据类型的变量。而且,与其他数据类型一样,DB2 提供一些采用阵列作为参数和/或返回阵列作为结果的函数,如 CARDINALITYTRIM_ARRAY

在 Oracle 中,VARRAY 是对象类型。通过使用方法而不是函数来操纵 VARRAY。清单 11 通过调整 myArray 中最后两个元素的语句和将 myArray 的基数保存到 myCard 语句,说明了 DB2 和 Oracle 之间的这种差异。

清单 11. 调整 DB2 和 Oracle 中的阵列元素
DB2:
myArray = TRIM_ARRAY(myArray, 2);
myCard = CARDINALITY(myArray);

Oracle:
myArray.TRIM(2);
myCard = myArray. COUNT;

正如您在第 1 部分中所看到的,在通过非 NULL 值初始化阵列时或阵列增长超过它们当前大小时,DB2 会隐式为阵列分配内存。另一方面,在将元素添加到阵列以前,Oracle 需要明确的分配操作。清单 12 说明了这种差异。

清单 12. 在 DB2 和 Oracle 中附加元素
DB2:
myArray[CARDINALITY(myArray) + 1] = 1000;

Oracle
myArray.EXTEND;
myArray(myArray,COUNT + 1) = 1000;

清单 12 还说明了 DB2 中(方括号)和 Oracle 中(括号)中子索引的不同语法。

涉及阵列的 Oracle 应用程序经常利用批量绑定操,其针对阵列中的每个元素反复执行 SQL DML 语句。在 Oracle 中,这些操作是 FORALLBULK COLLECT。DB2 不提供这些操作的直接等效操作,但是 FORALLBULK COLLECT 的最常见使用可有效地分别映射到涉及 UNNESTARRAY_AGG 的语句。

FORALL 语句为阵列中的每一个元素重复另外一个语句。清单 13 显示了 FORALL 语句的示例,其针对阵列部门中的每一个元素重复 INSERT

清单 13. FORALL 语句
FORALL i IN depts.FIRST..depts.LAST
INSERT INTO emp(deptno) values(depts(i));

清单 14 显示了使用 UNNEST 的 DB2 等效项。

清单 14. 将 FORALL 映射到 UNNEST
INSERT INTO emp (deptno)
      (SELECT T.deptno FROM UNNEST(depts))

现在让我们看看如何将 Oracle 的 BULK COLLECT 映射到 ARRAY_AGGBULK COLLECT 自身不是语句,而是可用于 SELECT INTOFETCH 语句的可选子句。

包括 BULK COLLECT 子句的 Oracle SELECT INTO 语句类似于 DB2 中具有 ARRAY_AGGSELECT。常规的 SELECT INTO 语句只返回一行。在包括 BULK COLLECT 子句后,该语句可返回多行;INTO 子句中的变量必须是阵列,且每一行存储在一个阵列元素中。

清单 15 说明了 BULK COLLECT 的使用。

清单 15. BULK COLLECT 子句
DECLARE
  names NameList;
BEGIN
  SELECT ename BULK COLLECT INTO names 
  FROM emp WHERE sal > 1000;
END;

清单 16 显示了 DB2 的等效项。

将 BULK COLLECT 映射到 ARRAY_AGG
BEGIN
  DECLARE names NameList;
 
  SET names = (SELECT ARRAY_AGG(ename) FROM emp WHERE sal > 1000);
END;

同样,FETCH 语句包括 BULK COLLECT 子句时,用于 FETCH 的变量必须是阵列。ORACLE 隐式重复 FETCH,直到使用了来自游标的所有行并将每一行存储在阵列的一个元素中。

除了到目前为止讲述的差异外,Oracle 还支持存储 VARRAY 作为列类型,并支持创建记录的阵列和阵列的阵列。DB2 中上不支持这些功能。

性能研究

为衡量阵列的性能影响,使用 900GB 的数据库且执行 OLTP(联机事务处理)工作负荷。工作负荷包括大约 60% 的选择语句和 40% 的插入、更新以及删除语句。存储过程和 DB2 CLI 应用程序用于驱动工作负荷。实现了两组不同的存储过程。

对于第一组存储过程,在可能时使用 DB2 阵列。在第二组存储过程中,使用字符串替代了阵列,且大量使用光标以便从多行结果集合中提取行。对于从光标调用中检索到的每一行,将元素附加到字符串并具有分隔符。用户定义的函数用于在必要时将字符串分入单个元素。清单 17 显示了从 CLP 向过程 alternate_find_customers 发出的 CALL 语句的示例以及 CLP 生成的输出。针对存储过程的每个版本实现一个 DB2 CLI 应用程序。

清单 17. 从 CLP 使用 varchar 而不是阵列来调用过程
db2 CALL find_customers(ARRAY['416-305-3745;905-414-4565;416-305-3746', '416', ?)

  Value of output parameters
  --------------------------
  Parameter Name  : NUMBERS_OUT
  Parameter Value : 416-305-3745;416-305-3746

  Return Status = 0

与阵列的 varchar 表示相比,DB2 阵列数据类型具有显著的高性能。考虑到吞吐量和用户 CPU 利用率后,我们通过衡量得出性能大概提高了 70% (以下图表中的正常吞吐量)。大多数性能提高源自存储过程和 DB2 CLI 应用程序代码的简单化,即删除游标定义和访问以及删除使用 DB2 阵列时不需要的额外本地变量和用户定义的函数。这会导致用户 CPU 利用减少,转化为吞吐量上的提升。

图 1. 正常的吞吐量
对比 DB2 阵列性能和阵列 varchar 表示的条形图,阵列的 Varchar 表示具有正常吞吐量值 100,而 DB2 阵列具有值 170。
对比 DB2 阵列性能和阵列 varchar 表示的条形图,阵列的 Varchar 表示具有正常吞吐量值 100,而 DB2 阵列具有值 170。

结束语

本文介绍了 DB2 V9.5 中最近引入的对阵列数据类型的支持。像其他编程语言中的阵列一样,在 SQL PL 中对算法编码,DB2 阵列可用作辅助数据结构。但更重要的是,阵列是在应用程序和存储过程之间或在存储过程之间流动的数据集合的非常方便的表示形式。

除了阵列上的基本操作(如子索引、基数和调整),DB2 已经通过以下方式将阵列集成到关系模型中:结果集可以转化成阵列(例如,在其需要作为参数传递到另一个存储过程时)以及阵列可以转化成表。后一转换允许阵列中的数据源其他表中的数据联接,以及允许 SQL 的 set-at-a-time 语义用于表示为阵列的集合。

JDBC 和 DB2 CLI 阵列支持也是可用的,且有证据表明如果升级存储过程和应用程序以便使用新的 DB2 阵列数据类型,则会在性能上得到提升。

鸣谢

感谢 Serge Rielau 对本文初稿进行的审校。


相关主题


评论

添加或订阅评论,请先登录注册

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=657357
ArticleTitle=在 SQL PL 中使用阵列交换数据
publish-date=05092011