db2编写动态sql db2动态sql查询

引言

SQL 语言作为标准的查询语言，几乎被所有的数据库管理系统 (DBMS) 所支持，并成为国际标准。标准的 SQL 语言一般包括三类，即 DDL (Data Definition Language, 数据描述语言 ) 、DML (Data Manipulation Language, 数据操纵语言 ) 和 DCL(Data Control Language，数据控制语言 )。通过这些标准的 SQL 语句，使得各种数据库能以一种较为统一的方式被访问。

DB2（本文以下专指 DB2 UDB for Linux, Unix 和 Windows 版本）允许用户通过多种编程接口发送 SQL 语句到数据库引擎，然后由引擎统一编译并且运行。SQL 语句从编译和运行的角度可以分为两种，静态 SQL和 动态 SQL，这两种 SQL 在使用方式、运行机制和性能表现等方面各有特点 :

• 静态 SQL：静态 SQL 语句一般用于嵌入式 SQL 应用中，在程序运行前，SQL 语句必须是确定的，例如 SQL 语句中涉及的列名和表名必须是存在的。静态 SQL 语句的编译是在应用程序运行前进行的，编译的结果会存储在数据库内部。而后程序运行时，数据库将直接执行编译好的 SQL 语句，降低运行时的开销。
• 动态 SQL：动态 SQL 语句是在应用程序运行时被编译和执行的，例如，使用 DB2 的交互式工具 CLP 访问数据库时，用户输入的 SQL 语句是不确定的，因此 SQL 语句只能被动态地编译。动态 SQL 的应用较多，常见的 CLI 和 JDBC 应用程序都使用动态 SQL。

表 1列举了静态 SQL 和动态 SQL 的比较结果。

表 1. 静态 SQL 和动态 SQL 的比较

静态 SQL	动态 SQL
SQL 语句直接嵌入到宿主编程语言，程序需要预编译处理这些嵌入的 SQL 语句	SQL 语句一般作为宿主语言的变量出现。嵌入式动态 SQL 应用需要预编译，非嵌入式 SQL 应用则无需预编译
SQL 语句在程序被编译时已知，涉及的数据库对象已存在	SQL 语句在程序被编译时未知，涉及的数据库对象可以是运行时才创建的
SQL 语句在程序运行前被编译	SQL 语句在程序运行时被编译
SQL 语句的编译结果在 DB2 的目录 (catalog) 中持久化保存	SQL 语句的编译结果缓存在数据库的内存里
运行时仅读取目录 (catalog)	运行时编译 SQL 语句需对目录 (catalog) 加锁
SQL 语句的优化是根据编译时的数据库统计信息进行的，不能完全反映运行时的情况	SQL 语句的优化是根运行时的数据库统计信息进行的
对 SQL 语句所访问的数据对象的权限检查是在绑定时进行的	对 SQL 语句所访问的数据对象的权限检查是在运行时进行的
权限控制的粒度是包（package，一组 SQL 语句的编译结果），用户仅需要访问包的权限	权限控制的粒度是 SQL 语句，用户需要具有访问 SQL 语句中每个数据对象的权限
如果 SQL 语句中的对象被修改，如 DDL 执行，整个包都需要重新绑定	当 SQL 语句中的对象被修改时，仅执行过的语句在下次运行时需要重新编译

根据编程方法的不同，DB2 的应用程序开还可以分为嵌入式 SQL 编程和非嵌入式编程 :

• 嵌入式 SQL 编程将 SQL 语句嵌入到宿主语言 (host) 的程序中，例如 C/C++ 程序。因为宿主语言不识别 SQL 语句，先要对程序进行预编译，把 SQL 语句转变为对 DB2 服务的调用，并重写源代码，最后再使用宿主语言的编译器对应用程序进行编译。嵌入式 SQL 都需要被绑定到特定的数据库中，可分为嵌入式静态 SQL 和嵌入式动态 SQL。
• 非嵌入式应用程序不需要预编译，且方法较多，如 CLI、JDBC、ODBC、ADO.NET 等等，这些方法中都使用动态 SQL。表 2列举了常见的 DB2 编程接口。

表 2. DB2 的编程接口一览

编程接口	静态 / 动态	是否为嵌入式
嵌入式 SQL	静态和动态	是
DB2 CLI	动态	否
SQLJ	静态	是
JDBC	动态	否
ADO.NET，OLE DB	动态	否
Perl DBI	动态	否
PDO(PHP 数据对象 )	动态	否

在下面的几个章节中，我们将陆续从使用角度上描述静态和动态 SQL 在各种编程接口中的应用，并运用一些实例来介绍在具体的场景中如何选择。

静态 SQL 应用

嵌入式静态 SQL

无论是嵌入式静态 SQL 还是嵌入式动态 SQL，都需要先进行预编译，并绑定到特定的数据库。DB2 的嵌入式 SQL 应用程序支持以下几种语言：C，C++，COBOL，FORTRAN 和 REXX™。

对嵌入式静态 SQL 而言，只能使用编译时确定的 SQL 语句和访问编译时已经存在的数据库对象。清单 1是一个查询表的例子，使用 C 语言 :

清单 1. 嵌入式静态 SQL 的 C 语言片断

//test.sqc       EXEC SQL INCLUDE SQLCA;       EXEC SQL BEGIN DECLARE SECTION;          sqlint32 t_seq = 0;          char t_name[64]={0};       EXEC SQL END DECLARE SECTION;       ...       t_seq = 5;       EXEC SQL SELECT c_name INTO :t_name FROM test_tbl WHERE seq=:t_seq;       printf("c_name = %s \n", t_name);

使用下面的命令，对上述代码进行预编译和编译：

清单 2. 嵌入式静态 SQL 的编译命令

# 需要先连接数据库      db2 connect to TESTDB       # 使用 PREP 命令对 sqc 源文件进行预编译，这将生成 test.c 源文件      db2 PREP test.sqc       # 使用 C 编译器对 test.c 进行编译      xlC -q64 -I$DB2PATH/include -g -L$DB2PATH/lib -ldb2 -o test test.c

SQLJ

SQLJ 是应用于静态 SQL 的 Java 编程接口。使用 SQLJ 编写应用程序和使用其他的语言接口相似，一个典型的 SQLJ 应用程序主要包括以下几个方面：

• 载入包含 SQLJ 和 JDBC 的 Java 包。
• 定义收发数据的载体变量。
• 连接至数据库，运行相应的 SQL 语句并且正确处理错误情况，最后从数据库断开。

清单 3是 SQLJ 中执行 SELECT 语句的代码片断。

清单 3. 简单的 SQLJ 程序片断

// 载入相关包      import sqlj.runtime.*;                                                   import java.sql.*;       // 连接至数据库      Connection con0 = DriverManager.getConnection(url);       // 执行相关的 SQL 语句      #sql [ctx] iter = {SELECT NAME FROM EMP};       // 得到结果      while (iter.next()) {                                                      System.out.println(iter.LASTNAME());       }

在 SQLJ 应用程序中，可以使用 ExecutionContext 类去控制和监控 SQL 语句的执行，如 清单 4所示。

清单 4. 在 SQLJ 使用 ExecutionContext

// 分配存储执行上下文的变量      ExecutionContext exeCtx=new ExecutionContext();                           // 关联变量和要执行的语句      #sql [connCtx, exeCtx] {DELETE FROM EMP WHERE SALARY > 10000};         // 获取结果      System.out.println("Deleted " + exeCtx.getUpdateCount() + " rows");

动态 SQL 应用

嵌入式动态 SQL

与嵌入式静态 SQL 相同，嵌入式动态 SQL 也需要预编译。不同的是，嵌入式动态 SQL 将 SQL 语句存放在宿主语言的字符型变量中，这样的 SQL 语句在预编译时是不被处理的，而是被当作主机变量对待，直到程序运行时才被编译执行。

得益于动态 SQL 的优点，嵌入式动态 SQL 可以处理运行时才确定的 SQL 语句，例如由程序运行时拼接的 SQL 语句。为了处理返回结果未知的 SELECT 语句，嵌入式动态 SQL 使用 SQLDA(SQL descriptor area) 结构和 DESCRIBE 语句获取结果集的结构和属性。SQLDA 结构如 图 1所示。HEADER 描述整个结果集的信息，而每个 SQLVAR 结构描述结果集中一个字段的信息。

图 1. SQLDA 结构

 

清单 5展示了如何使用 SQLDA 结构和 DESCRIBE 语句处理 SELECT 语句。

清单 5. 使用 SQLDA 结构和 DESCRIBE 语句的伪代码

//test1.sqc       // 声明两个 SQLDA 指针，minsqlda 将是一个最小的 SQLDA 结构，用于 PREPARE 语句，      // 此时结果集的字段数量未知，所以只需一个最小的 SQLDA，即包含 HEADER 和一个 SQLVAR       struct sqlda * minsqlda = new sqlda;       struct sqlda * fulsqlda = NULL;             strcpy(hostVarStmt, "SELECT name FROM TEST_TBL");             // PREPARE 将填写 minsqlda 的 header，sqldabc 为 SQLDA 总长度，sqln 为 SQLVAR 数量，即字段数量      EXEC SQL PREPARE STMT INTO :*minsqlda FROM :hostVarStmt;             // 根据从 minsqlda 中获取的长度，分配完整的 SQLDA 结构 fulsqlda，其中将包括合适数量的 SQLVAR 结构      fulsqlda = (struct sqlda *)malloc(SQLDASIZE(minsqlda->sqln));             // 使用 DESCRIBE 语句，获取结果集中每个字段的描述信息，包括各字段的类型 (sqltype) 和长度 (sqllen)       EXEC SQL DESCRIBE STMT INTO :fulsqlda;             Loop       {         // 根据每个字段的长度，分配内存，将地址存储在对应 SQLVAR 的 sqldata 中      }             // 声明游标      EXEC SQL DECLARE c1 CURSOR FOR STMT;       EXEC SQL OPEN c1;             // 读取记录，记录中每个字段的内容将写入 fulsqlda 中对应 SQLVAR 结构的 sqldata 指向的内存      EXEC SQL FETCH c1 USING DESCRIPTOR :*fulsqlda;             // 循环读取所有记录      while (sqlca.sqlcode != 100)         {           EXEC SQL FETCH c1 USING DESCRIPTOR :*fulsqlda;         }             EXEC SQL CLOSE c1;

DB2 CLI

DB2 CLI（Call Level Interface）基于微软的 ODBC（Open Database Connectivity）标准，同时也增加了 DB2 特有的功能。它允许开发人员使用 C/C++ 语言访问 DB2 并通过函数调用将动态 SQL 语句传递给 DB2。DB2 CLI 一方面在 ODBC 的环境中作为 ODBC 驱动被 ODBC 管理器加载，另一方面，应用程序也可以直接使用 DB2 CLI API，此时具有更好的性能。清单 6展示了 CLI 如何执行一个 DELETE 语句。

清单 6. CLI 应用程序片断

/* SQL statements to be executed */ SQLCHAR * stmt1 = (SQLCHAR *)"delete from test1 where col1 = 5"; /* directly execute statement 1 */ cliRC = SQLExecDirect(hstmt, stmt1, SQL_NTS);

对一个返回结果未知的 SELECT 查询语句，需要使用相关的 CLI API 函数动态地获取对结果集的描述，并取回数据。图 2展示了这个处理过程。

图 2. DB2 CLI 应用程序处理 SELECT 语句流程

 

与嵌入式动态 SQL 应用相比，CLI 程序的灵活性更强。表 3列举了 CLI 应用程序和嵌入式动态 SQL 应用程序的比较。

表 3. CLI 应用程序和嵌入式动态 SQL 应用程序的比较

CLI	嵌入式动态 SQL
DB2 CLI 应用程序使用 API 函数发送 SQL 语句，应用程序的编译、连接和运行独立于特定数据库，即无需预编译，也不需要绑定到某个数据库实例。	嵌入式动态 SQL 应用程序需要预编译，并且需要绑定到特定的数据库实例。
CLI 提供的滚动游标（scroll cursor）支持前、后向移动一行或者多行记录，而移动的起点可以是第一行、最后一行或者之前存储的位置。	只支持顺序前向读取游标，并使用 FETCH 语句取得记录。
可以获取存储过程调用返回的结果集。并支持多种 DB2 服务器。	可以获取存储过程的输出型或输入 - 输出型参数的值，但不能获取存储过程返回的结果集。
只支持 C/C++ 语言。	支持 C/C++, FORTRAN、COBOL 和 Java（SQLJ）。
使用 CLI API 函数 SQLDescribeCol()、SQLAttribute() 描述未知 SQL 语句结果集的信息，包括结果集字段长度、类型、精度等信息。	使用 SQLDA 结构和 DESCRIBE 语句获取未知 SQL 语句结果集的字段列表，包括类型、长度等信息。

JDBC

JDBC 是 Java 编程语言访问关系型数据库的工业标准，类似于 ODBC，为访问基于 SQL 的数据库提供了一组调用级的 API。在 JDBC 规范中，存在 4 种类型的 JDBC 驱动，DB2 LUW V9.7 中支持 Type 2 和 Type 4 两种类型。更多关于 JDBC 的信息可参考 DB2 UDB for Linux, UNIX 和 Windows 中的 Java 开发概述（参见 参考资源部分）。

JDBC 以 Java 包的形式提供了一组接口和类，用来连接数据库和执行 SQL 语句，这里的 SQL 语句也是动态 SQL，无须预编译和数据库绑定。清单 7所示为 JDBC 程序片断。

清单 7. JDBC 应用程序片断

try{       stmt = con.prepareCall("select * from table1");       stmt.execute();       rs = stmt.getResultSet();       while (rs!=null && rs.next()) {          System.out.println("output: = " + rs.getInt(1));       }       }catch(SQLException sqle) {       System.out.println("Error, SQLCODE = " + sqle.getSQLState());       con.rollback();       }

除了编程语言和运行基础架构的不同，JDBC 应用和 CLI 应用具有很多相似的特性，例如它们都支持后向游标，分布式事务等。

ADO.NET 和 OLE DB

ADO.NET 是 Microsoft .NET Framework 基础类库的一部分，提供了访问关系型数据库系统和其他数据源的能力。ADO.NET 主要包括两个部分：DataSet 和 DataProvider。使用 ADO.NET，有三种 DataProvider 可以用来访问 DB2：

• OLE DB .NET Data Provider 和 ODBC .NET Data Provider 是两种桥接式 provider，它们将 ADO.NET 请求转换为对 IBM OLE DB Provider 或 IBM ODBC Driver 的请求。
• DB2 .NET Data Provider 是访问 DB2 时推荐使用的 .NET provider，由于没有额外的 OLE DB 或 ODBC 层，它具有更好的性能。

OLE DB 是一种较早出现的数据访问模型，它基于 Microsoft® 的 COM 技术，提供了访问不同信息源的统一方法。OLE DB 定义了 OLE DB 消费者和提供者，OLE DB 消费者可以通过 IBM OLE DB Provider for DB2 访问 DB2 中的数据。使用 OLE DB 访问 DB2 的应用程序可能包括 Microsoft® Visual Studio C++ 应用，Microsoft Visual Basic 应用，ATL 应用等。

其他应用程序开发接口

Perl 程序员可以使用 Perl 数据库接口 DBI 来访问 DB2。IBM 提供的 DBI 驱动为 IBM®DB2®Database Driver for Perl DBI (the DBD::DB2 driver)。Perl DBI 同样使用动态 SQL，而且 Perl DBI 的接口与 CLI 和 JDBC 很相似，易于使用。

PHP 在 Web 应用开发领域一直占据很大份额，IBM 提供了下面两种 PHP 扩展以访问 DB2 数据库：

• ibm_db2，是一种过程化的应用编程接口，与 PHP 应用一起编译，提供一系列的 PHP 函数来访问数据库。
• pdo_ibm，是 IBM 提供的 PHP Data Objects (PDO) 驱动。

此外，IBM 对 Python，Ruby on Rails 等编程语言都提供了相应的 DB2 访问支持。这些开发接口同样都使用动态 SQL。

应用场景以及如何选择

本节将结合几个应用场景来介绍如何选择 DB2 编程接口以及静态 / 动态 SQL。

场景 1

银行中账目统计系统比较稳定，一般运行在一个特定的数据库中，其目的是整合业务数据库上数据，并通过分析数据得到一些相关的统计信息。这样的系统具有以下一些特点 :

• 稳定，系统逻辑不会有什么变化，访问固定的数据库以及表。
• 定时运行，账目统计信息通常需要在一段时间内进行整合。
• 数据量比较大，对性能要求较高。

针对这些特点，建议使用主要由静态 SQL 语句组成的应用程序，这样该系统在建立的时候会被编译，然后相关的编译信息会被存储到数据库里，在以后的运行过程中避免重复编译，同时，这种应用由于结构简单使得其很容易维护。当然，由于系统的数据变化较快，而且数据量很大，所以建议定时重新编译程序，使得数据库对每条 SQL 语句可以选择最优的运行方式。

场景 2

考虑电信运营商对用户话单的计费场景，图 3显示了一个电信运行系统结构图。业务系统产生的各种话单会集中存放在一个关系型数据库中，该数据库可能位于大规模的高可靠性服务器。而计费程序则往往作为一个单独的应用部署在其他规模较小的 UNIX 服务器上，并作为守护程序或者批处理程序运行。另外，很多业务规则例如计费标准可能存放在单独得小型数据库系统中。

图 3. 电信运营系统结构示意图

 

计费程序的工作主要包括：从话单数据库读新话单，从业务配置数据库读取计费标准，计算话单的费用和优惠，将计费完毕的话单写回话单数据库。

由于计费标准的选择经常和日期等变化的信息相关，程序中会存在一些不固定的 SQL 语句，因此静态 SQL 应用并不适用。另外，除了访问结构稳定的话单数据库，计费程序也需要访问一些变化概率较大的小型数据库，例如计费标准可能会因为运营商营销策略的调整而发生变化。如果使用嵌入式 SQL，就必须将应用程序和数据库进行绑定，这将失去灵活性。最后，为了快速对大量的话单进行计费，计费程序对性能也有较高要求，所以 CLI 应用程序比 JDBC 应用更适合计费程序。

场景 3

在基于 BS 结构的电子商务应用中，浏览器、应用服务器（包括 Web 服务器）和数据库构成了系统的三个要素。数据库为电子商务提供了事务管理、数据持久化、安全认证、联机备份等各方面的支持，是电子商务系统中的关键部分。图 4显示了一个简单的电子商务系统结构示意图。

图 4. 简单的电子商务系统结构示意图

 

在电子商务系统的实现中，数据库访问接口的选择很大程度上依赖于所选择的应用集成方案。例如传统的 J2EE 应用中使用 JDBC 实现数据库访问。又如开源框架 STRUTS，SPRING 和 HIBERNATE，由 HIBERNATE 完成对 JDBC 的封装，提供对数据库的访问能力。基于 PHP 开发的 Web 应用程序同样非常流行，如果选择 PHP 作为开发语言，那么使用 PDO（PHP Data Objects）和 IBM 提供的 pdo_ibm 驱动即可访问 DB2 数据库。对基于 .Net 框架开发的 Web 应用而言，ADO.NET 则是最合适的数据库访问接口。

静态 SQL 和动态 SQL 的选择原则

在以下情况下，适合选用静态 SQL：

• 程序需要处理 SQL 语句频率高，压力较大
• SQL 语句较为简单且已知不变
• SQL 语句访问的数据库对象变化很少
• 程序使用的 SQL 语句可以统一认证权限
• 同一条 SQL 语句重复执行的次数很少
• 很少运行 RUNSTATS 更新统计信息

在以下情况下，更适合选用动态 SQL：

• SQL 语句在应用程序执行时才生成
• SQL 语句访问的对象在程序运行前不存在
• 希望 SQL 语句根据运行时的数据库统计信息进行最好的优化
• 在程序运行中可能更改 SQL 语句的编译环境，如配置参数和特殊寄存器的值
• 程序运行时，同时有较多 DDL 语句执行
• 选用特定的应用程序框架，例如场景 3 中的电子商务案例

当然，所有的原则都是经验性的，真正的选择还取决于应用的实际情况。在时间允许的情况下，对各种选择进行比较测试也是可以考虑的方法。

参考资料

学习

• 通过 DB2 UDB for Linux, UNIX 和 Windows 中的 Java 开发概述，了解 DB2 中的 Java 开发。
• 通过 洞察 DB2 Universal Database: SQL 语句的生命周期，了解 DB2 对 SQL 语句的编译原理。
• 通过 概览 : DB2 和 Visual Studio .NET，了解使用 .NET 进行 DB2 开发。
• developerWorks Information Management 专区：在这里可以学到更多关于 Information Management 的知识。还可以找到技术文档、how-to 文章、培训、下载、产品信息等。
• 随时关注 developerWorks 技术活动 和 网络广播。 

获得产品和技术

• 使用可直接从 developerWorks 下载的 IBM 信息管理试用软件 构建您的下一个开发项目。

讨论

• 参与论坛讨论。
• 参与 developerWorks blogs 并加入 developerWorks 社区。

作者简介

杨晓华目前在 IBM 中国软件开发实验室任软件工程师，主要从事 DB2 联邦数据库 Runtime 部分的开发工作，熟悉跨平台的 C/C++ 编程。

胡中庭，IBM 软件开发中心软件工程师，北京交通大学硕士毕业，在Federation Server Development Group （联邦数据库项目组）工作两年半至今。