简介
本文使用了几个示例教您如何在 DB2® Universal Database™ 中编写高级 SQL 脚本,这些脚本可以用于(但并不仅限于)运行简单批处理进程、开发测试方案以及自动测试应用程序组件。本文假定您熟悉 DB2 SQL 过程语言的基本知识。
如果您已经阅读了我的上一篇有关“DB2 开发人员专区”的文章 — 在 DB2 UDB 中将 SQL 过程语言用于触发器,那么您就会熟悉将在这里看到的一些语法。
先决条件:要运行这些示例,需要 DB2 7.2 或者带 FixPak 3 的 DB2 v7.1。
示例 1:Hello world
遵循学习与计算机相关的新知识的常见惯例,我们将以一个简单脚本开始,创建一个名为 HELLO 的表,并在其中插入单词“hello world”。
1. 使用文本编辑器,输入如下行,然后将此文件保存为 helloworld.db2。
!echo Beginning Script1@ !echo creating table....@ CREATE TABLE HELLO (mycol VARCHAR(20))@ !echo inserting values....@ INSERT INTO HELLO VALUES ('hello world')@ !echo displaying result@ SELECT * FROM HELLO@ !echo cleaning up@ DROP TABLE HELLO@ !echo done.@
2. 使用与以下类似的命令,连接至一个数据库:
db2 connect to < dbname> user < userid> using < password>
3.当完成连接后,输入以下命令:
db2 -td@ -f helloworld.db2
这个命令执行时有点象带有一些特殊标志的标准 DB2 命令行处理器(Command Line Processor(CLP))命令:
- -t 标志单独指定了脚本使用标准分号(;)来作为命令的结束。
- 然而,当与 -d 标志和 @ 结合使用时,CLP 把 @ 解释为一个语句的结束。稍后有一个示例将强调这一点的重要性。还请注意:在 -d 和 @ 之间不可以有空格,因为那样会表示您要使用一个空格作为终止字符。
- 最后,-f 标志和文件名参数指定了用来作为 CLP 的输入的文件名。
4. 在 步骤 3输入命令后,脚本的屏幕输出看起来如下:
Beginning Script1 creating table.... DB20000I The SQL command completed successfully. inserting values.... DB20000I The SQL command completed successfully. displaying result MYCOL -------------------- hello world 1 record(s) selected. cleaning up... DB20000I The SQL command completed successfully. Done.
从上面的示例中,您会看到 DB2 中的基本脚本编制相当容易。这个示例强调了以下几点:
- 这种脚本编制不同于用‘db2’作为每个 DB2 语句前缀的 OS shell 脚本。有关这一点的示例,请参阅 从 OS shell 脚本传递参数。
- 通过用感叹号(!)作为 OS 命令的前缀,可以从这些 DB2 脚本执行 OS 命令。DB2 CLP 还支持 echo 语句,但我用“!echo”说明 OS 命令的使用。
- 脚本内的所有命令(包括 OS 命令)的结尾都要使用命令终止字符(@)。
技巧:您可能已注意到执行的 DB2 命令没有在屏幕上显示,而只显示了命令成功与否。如果要查看显示在屏幕上的命令,可以使用 -v 标志。例如:
db2 -v -td@ -f helloworld.db2
示例 2:一个带日志输出的更为复杂的脚本
现在让我们尝试一个更为复杂的脚本。例如,假定您希望使用脚本编制整夜执行一些无人照管的任务,但又不打算呆在办公室里来确保它成功执行。您可以做的就是将脚本输出写到一个日志文件,然后在第二天早上查看结果。
为了说明 DB2 支持的某些高级逻辑,我们通过添加以下限定条件使示例更为复杂:必须只在星期一和星期五执行脚本。为了强制执行这个规定,我们将在脚本中添加几个约束。
1.使用文本编辑器,输入下列代码,然后将文件保存为 complex.db2:
!echo beginning complex.db2@ !echo creating table....@ CREATE TABLE HELLO (mycol VARCHAR(20))@ begin atomic if (DAYOFWEEK (Current Timestamp)=2) then insert into HELLO values ('Hello Monday'); elseif (DAYOFWEEK (Current Timestamp)=6) then insert into HELLO values ('Hello Friday'); else SIGNAL SQLSTATE '80000' SET MESSAGE_TEXT='Script is for MON & FRI only!'; end if; end@ !echo Retrieving from HELLO table..@ SELECT * FROM HELLO@ !echo Cleaning up...@ DROP TABLE HELLO@ !echo This message will get written to screen, but not to the log file@ VALUES ' This message will get written to the screen AND log file'@
提示:DAYOFWEEK() 是一个函数,在给定一个时间戳记参数的情况下,它会返回一个表示一周中某一天的值(在 1(星期日)和 7(星期六)之间)。
在运行上面的脚本之前,让我们先对这里说明的一些 DB2 特性加以强调:
- 可以使用诸如 DAYOFWEEK() 那样的 DB2 内置函数。可以混合使用 DB2 提供的任何函数或者任何您自己的用户定义函数。
- 可以使用诸如 CURRENT TIMESTAMP、CURRENT SCHEMA、CURRENT DATE、CURRENT TIME 那样的 DB2 特殊寄存器。
- 可以使用 SIGNAL 抛出一个用户定义的 SQL 错误,错误将返回到启动这个脚本的控制台或应用程序。
- 可以在脚本中使用 DB2 的 VALUES 语句。然而,如果在原子复合语句中使用了 VALUES,那么将不显示函数的任何结果。
- 可以在单个脚本中混合使用复合和常规 SQL 语句。
- 可以在复合 SQL 语句中使用高级过程逻辑,而且命令可以跨越多行。
3. 要运行这个脚本,从命令行输入以下命令。象前面一样,首先需要与数据库连接。
db2 -td@ -f complex.db2 -z output.log
该命令中额外的 -z 标志和文件名参数 output.log 使 DB2 将所有 DB2 生成的输出写到指定文件。使用这个标志,可以让脚本运行,并稍后返回到您的工作站以分析脚本的结果。
表 1显示了当脚本在星期五运行时,屏幕输出和日志文件输出的横向比较:
屏幕输出 | 日志文件输出 |
---|---|
beginning complex.db2 DB20000I The SQL command completed successfully. Retrieving from HELLO table.. MYCOL -------------------- Hello Friday 1 record(s) selected. Cleaning up... DB20000I The SQL command completed successfully. This will get written to screen, but not to log file 1 ----------------------------------- This will get written to the screen AND log file 1 record(s) selected. |
DB20000I The SQL command completed successfully. MYCOL -------------------- Hello Friday 1 record(s) selected. DB20000I The SQL command completed successfully. 1 ----------------------------------- This will get written to the screen AND log file 1 record(s) selected. |
在上面的输出中,成功执行了脚本,但我们注意到屏幕输出不同于日志文件(output.log)的输出。用粗体突出显示了这些差别,并且它们都是用感叹号(!)作为前缀的 OS“echo”命令。因为那些命令都在 OS 级别上执行,而不是由 DB2 执行,所以没有通过 -z 标志记录它们的输出。
请注意最后一句由下面 DB2 的 VALUES 语句发出的注释:
This will get written to the screen AND log file
被同时写到屏幕和日志文件,因为这个命令是由 DB2,而不是 OS 执行的。
下面是当脚本在星期三运行时相对应的屏幕和日志文件的结果:
屏幕输出 | 日志文件输出 |
---|---|
beginning complex.db2 | DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0438N Application raised error with diagnostic text: "Script is for MON & FRI only!" SQLSTATE=80000 |
这次,粗体的突出显示指出了脚本的成功执行和未成功执行之间的区别。象预期的那样,查看脚本是如何失败的,并以我们定制的错误消息“Script is for MON & FRI only!”来显示“应用程序引起的错误 SQL0438N”。因为存在这个错误,所以 HELLO 表中没有插入任何行,并且随后的 DELETE 语句没有找到任何可删除的内容,从而产生一个对我们的需求无害的 SQL 警告。
研究复合 SQL
再次查看 步骤 1中的代码。BEGIN ATOMIC 和 END@ 表示一个 DB2 复合原子 SQL 语句。通过使用复合 SQL 语句,可以将几个 SQL 语句合并为一个语句,并使 DB2 把整个脚本主体当作一个非全有即全无(all-or-nothing)的语句(象一个事务)。
正如上面的示例阐述的那样,您可以在一个脚本中混合使用复合 原子和常规 SQL 语句来实现所期望的最终结果。使用复合原子 SQL 语句还让您使用 SQL 过程语言元素。
在脚本编制中不支持使用非原子复合 SQL 语句。
终止字符
请注意:在 BEGIN ATOMIC 和 END@ 之间的语句中,@ 符号不作为终止字符是如何使用的。在一个复合语句体中,必须使用分号来表示任何 SQL 语句的结束。基于这个原因,我们必须使用 -td@ 标志,从而使命令解析器能够区分脚本中复合 SQL 语句和其它语句的语句结尾。
复合 SQL 中的 SELECT 语句
如果在复合 SQL 语句中包含 SELECT 语句,那么 SELECT 语句的结果不会显示在屏幕上。这是因为对于 shell 来说,SELECT 语句“从未发生”(总体上,DB2 只返回复合原子语句的执行是成功还是失败)。例如,比较以下结果:
脚本代码 | 结果 |
---|---|
SELECT 'hello' FROM sysibm.sysdummy1@ |
DB20000I The SQL command completed successfully. 1 ------- hello 1 record(s) selected. |
BEGIN ATOMIC SELECT 'hello' FROM sysibm.sysdummy1; END@ |
DB20000I The SQL command completed successfully. |
提示:为了去除 SELECT 语句结果的列标题,请在发出 db2 命令时使用 -x 标志。如果脚本的输出要用来作为另一个进程的输入,那么去除标题是有用的。
抛出 SQL 异常
SIGNAL SQLSTATE...SET MESSAGE_TEXT 语句抛出一个用户定义的 SQL 异常。在上面的例子中,抛出 SQLSTATE 80000,其错误文本为:“Script is for MON & FRI only!”。 如果在同一个 BEGIN ATOMIC 节中存在其它修改数据的 SQL 语句,那么这个错误会使它们回滚。复合语句后面的语句将继续执行。
SIGNAL 语句的错误消息长度限制在 70 个字符。如果您指定了一个超出这一限制的消息,那么会在毫无警告的情况下截断它。
示例 3:确定脚本的参数
为了使脚本更灵活,您可能希望创建在执行期间可以从命令行上获取参数的脚本。遗憾的是,到目前为止,在我们所进行的这种脚本编制中,还无法将参数从 OS shell 上传递到脚本。然而,您可以通过如下方法解决这一限制:
- 临时创建一个获取参数的 SQL 用户定义函数(UDF)或 SQL 存储过程。
- 在脚本中调用这个函数或过程。
- 在脚本结束时删除这个 UDF 或存储过程。
选择使用 UDF 还是存储过程
选择存储过程而不是选择 UDF 有两个主要原因:
- 如果使用复杂查询和大型数据集,那么使用 SQL 存储过程可以达到最佳性能。
- 如果代码修改数据(INSERT、UPDATE 或 DELETE),那么必须使用 SQL 存储过程,因为 SQL UDF 目前还不支持数据修改。
从 OS shell 脚本传递参数
对于用“db2”作为数据库命令前缀的 OS shell 脚本,您可以按照以下方式传递参数(适用于 UNIX®):
db2 select * from employee where empno='$1' db2 select * from employee where empno='$2'
上面的脚本从 EMPLOYEE 表中选择雇员号(empno)等于从 OS 传递到脚本的第一个参数的记录,然后再选择雇员号与从 OS 传递到脚本的第二个参数相等的记录。(在 Windows® 平台上,使用 %1 和 %2 分别替代 $1 和 $2。)
然而,使用如上所示那样命令的 Shell 脚本,在脚本内部不能轻松支持如 IF/THEN/ELSE、局部变量、FOR LOOPS 等 SQL 过程元素。这主要是由于代码格式有较多的限制,从而使这种方法只适合于简单的脚本编制。
使用 UDF 的示例
1. 为了准备运行这个示例,我们需要创建一个表,并在其中插入一些值。连接至数据库,然后执行下列 SQL 语句:
CREATE TABLE tab1 (id INT NOT NULL PRIMARY KEY, text VARCHAR(10)) INSERT INTO tab1 VALUES (1, 'one') INSERT INTO tab1 VALUES (2, 'two')
2. 现在,将下列脚本输入到文本文件中,然后把它保存为 funcparam.ddl:
CREATE FUNCTION getText(key INT) LANGUAGE SQL RETURNS VARCHAR(20) BEGIN ATOMIC RETURN SELECT text FROM tab1 t WHERE t.id=key; END@
3. 创建名为 funcparam.cmd 的第二个文件,它是一个获取参数的 shell 脚本,含有以下内容(使用适用于您平台的版本):
UNIX | Windows |
---|---|
db2 connect to <dbname> db2 -td@ -f funcparam.ddl db2 values getText($1) db2 drop function getText (INT) |
db2 connect to <dbname> db2 -td@ -f funcparam.ddl db2 values getText(%1) db2 drop function getText(INT) |
4. 按如下执行脚本:
chmod +x funcparam.cmd (只有在 UNIX 中才需要用它来使文件可执行) funcparam.cmd 1
在上面的示例中,值 1 作为唯一参数传递给 funcparameter.cmd shell 脚本。然后,这个 shell 脚本创建函数,并用提供的参数调用这个函数。接着,在完成之前,它通过删除(drop)这个函数来清除其自身。请注意:在 UNIX 脚本中,需要使用 connect 语句,因为 UNIX 中的 shell 脚本被派生(fork)到了它们自己的进程中。在 Windows 中不需要 connect 语句。
这里是上面示例的输出:
db2 -td@ -f funcparam.ddl DB20000I The SQL command completed successfully. db2 values getText(1) 1 -------------------- one 1 record(s) selected. db2 drop function getText(INT) DB20000I The SQL command completed successfully.
使用 SQL 存储过程的示例
这里是与上面脚本等效的使用 SQL 存储过程的版本。(在机器上需要一个受支持的 C 编译器。更多信息,请参阅 DB2 Application Building Guide。)
1. 在文本文件中输入以下脚本,然后把这个文件保存为 procparam.ddl:
CREATE PROCEDURE getText (IN key INT) LANGUAGE SQL RESULT SETS 1 BEGIN DECLARE C1 CURSOR WITH RETURN FOR SELECT text FROM tab1 t WHERE t.id=key; -- leave cursor open so that result set is returned. OPEN C1; END@
2. 创建名为 procparam.cmd 的第二个文件,它是一个获取参数的 shell 脚本,含有以下内容(使用适用于您平台的版本):
UNIX | Windows |
---|---|
db2 connect to <dbname> db2 -td@ -f procparam.ddl db2 call getText($1) db2 drop procedure getText (INT) |
db2 connect to <dbname> db2 -td@ -f procparam.ddl db2 call getText(%1) db2 drop procedure getText (INT) |
然后,为了执行这个脚本,请完成:
chmod +x procparam.cm (仅 UNIX 需要) procparam.cmd 1
额外技巧
本节包含一些我觉得非常有用的技巧:
- 时间戳记和其它特殊寄存器
- UDF 及脚本中的游标
时间戳记和其它特殊寄存器
查看下面的脚本。您希望发生什么?
BEGIN ATOMIC Insert into HELLO values (char (current timestamp)); Insert into HELLO values (char (current timestamp)); END
乍一看,您可能希望在 Hello 表中插入两个稍有不同的时间戳记值。然而,如果执行它,那么您会发现同一个时间戳记值插入了两次。发生这种情况是因为实际上 ATOMIC 复合 SQL 语句是作为一个 SQL 语句执行的一组 SQL 语句。因此,要小心使用日期和时间寄存器。
当 BEGIN ATOMIC 是这个行为定义的一部分时,该行为也适用于 SQL UDF。这种行为不适用于 SQL 存储过程,除非在同一个 BEGIN ATOMIC 节中包含了 SQL 语句。
如果您需要生成唯一的时间戳记,将 GENERATE_UNIQUE() 函数与时间戳记一起使用。例如:
BEGIN ATOMIC insert into HELLO values (char(timestamp(generate_unique()) + current timezone)); insert into HELLO values (char(timestamp(generate_unique()) + current timezone)); END
UDF 及脚本中的游标
目前还不支持可更新的游标。如果您需要在脚本中使用游标,那么可以使用 FOR LOOP 构造(它的功能与只读游标类似),并在循环内使用 UPDATE 语句。例如:
BEGIN ATOMIC FOR cur1 AS SELECT c1, c2, c3 from mytable IF cur1.c1 = 1 THEN INSERT INTO sometable1 values (cur1.c2, cur1.c3); ELSE UPDATE sometable1 SET somecol=cur1.c2; END IF; END@
结束语
我们已经了解了如何利用 DB2 的增强脚本编制功能。脚本编制有助于自动执行任务、测试和使简单方案快速原型化。我们知道了如何编写包含 SQL 过程语言元素的脚本、如何将脚本的输出假脱机存储到一个日志文件以及如何通过使用 UDF 和 SQL 存储过程在脚本中处理参数。