DB2存储过程-基础详解
关键字: 存储过程
您将：
- 学习 SQL PL 的基本要素。
- 理解如何声明变量、条件和处理程序。
- 学习控制流语句。
- 学习游标处理和如何返回结果集。
- 理解错误处理机制。
.-,-----------------.
V |
|--DECLARE----SQL-variable-name-+------------------------------->
.-DEFAULT NULL------.
>--+-data-type--+-------------------+-+-------------------------|
| '-DEFAULT--constant-' |
SQL-variable-name
图 1 显示了受支持的 DB2 数据类型：
DEFAULT
下面是变量声明的一些例子：
- DECLARE v_salary DEC(9,2) DEFAULT 0.0;
- DECLARE v_status char(3) DEFAULT ‘YES’;
- DECLARE v_descrition VARCHAR(80);
- DECLARE v1, v2 INT DEFAULT 0;
请注意，从 DB2 version 9.5 开始才支持在一个 DECLARE 语句中声明多个相同数据类型的变量。
数组数据类型
SQL 过程从 9.5 版开始支持数组类型的变量和参数。要定义一个数组类型的变量，需要先在数据库中创建该类型，然后在过程或应用程序中声明它。数组是临时的值，可以在存储过程和应用程序中操纵它，但是不能将它存储到表中。
DB2 支持以下创建数组数据类型的语法：
清单 2. 创建数组数据类型的语法
Sql代码
>>-CREATE TYPE—array-type-name--AS--| data-type |--ARRAY--[---------->
.-2147483647-------.
>--+------------------+--]-------------------------------------><
'-integer-constant-'数组类型的名称应该用模式加以限定，并且在当前服务器上应该是惟一的。LONG VARCHAR、LONG VARGRPAHIC、XML 和用户定义类型不能作为数组元素的数据类型。
下面是数组类型的例子：
Sql代码
CREATE TYPE numbers as INTEGER ARRAY[100];
CREATE TYPE names as VARCHAR(30) ARRAY[];
CREATE TYPE MYSCHEMA.totalcomp as DECIMAL(12,2) ARRAY[];请注意，整数 “constant” 指定数组的最大基数，它是可选的。数组元素可以通过 ARRAY-VARIABLE(subindex) 来引用，其中 subindex 必须介于 1 到数组的基数之间。
现在可以在 SQL 过程中使用这个数据类型：
清单 3. 在过程中使用数组数据类型
Sql代码
CREATE PROCEDURE PROC_VARRAY_test (out mynames names)
BEGIN
DECLARE v_pnumb numbers;
SET v_pnumb = ARRAY[1,2,3,5,7,11];
SET mynames(1) =’MARINA’;
…
END
DB2 支持一些操作数组的方法。例如，函数 CARDINALITY(myarray) 返回一个数组中元素的个数。
赋值
SQL PL 提供了 SET 语句来为变量和数组元素赋值。
下面是一个 SET 语句的简化的语法：
|
这个变量名可以是一个本地变量、全局变量或数组元素的名称。
下面是一些例子：
清单 4. SET 语句的例子
|
为变量赋值的其他方法有：
|
下面的例子演示了这些方法的使用：
清单 5. VALUE INTO 和 SELECT INTO 的例子
|
专用寄存器
专用寄存器（special register） 是 DBA 定义的一个存储块，供一个应用程序过程使用。寄存器中的值可以在 SQL 语句或 SQL PL 语句中访问和引用。在 IBM DB2 database for Linux, UNIX, and Windows Information Center 可以找到所有的专用寄存器（参见 参考资料
最常用的专用寄存器有：
- CURRENT DATE
- CURRENT TIME
- CURRENT TIMESTAMP
- CURRENT USER
- CURRENT PATH
所有这些寄存器都可以通过在名称中加下划线来引用。例如，CURRENT_DATE。
下面的过程返回当前日期和时间：
清单 6. 返回当前日期和时间的过程
|
执行后，该过程返回：
|
有些专用寄存器的值可以通过 SET 语句来更新。例如，为了更新正在访问的模式，需要像下面这样更改专用寄存器 CURRENT SCHEMA。
|
若要更改默认函数路径，则需要更新专用寄存器 CURRENT PATH。
游标
声明
SQL PL 提供 DECLARE cursor 语句来定义一个游标，并提供其他语句来支持返回其他结果集和游标处理。
下面是游标声明的语法：
清单 7. 游标声明的语法
|
Select-statement
WITHOUT HOLD/WITH HOLD
下面是一个显式声明游标的例子，它可以用于过程中后面的迭代处理：
清单 8. 游标声明的例子
|
虽然 SQL 语句不能包含参数占位符，但是它可以引用在游标之前声明的本地变量。例如：
清单 9. 使用本地变量的游标声明
|
游标和结果集
在 SQL 过程中，除了迭代结果集中的行以外，游标还可以做更多的事情。游标还可用于将结果集返回给调用程序或其他过程。
- WITHOUT RETURN/WITH return
- WITH RETURN TO CALLER
- WITH RETURN TO CLIENT
若要从一个过程中返回结果集，需要：
- 创建一个过程，创建时指定 DYNAMIC RESULT SETS 子句。
- 声明游标，声明时指定 WITH RETURN 子句。
- 打开该游标，并使之保持 open 状态。
如果关闭该游标，则结果集将不能返回给调用者应用程序。
清单 10 演示了一个游标的声明，该游标从一个过程中返回一个结果集：
清单 10. 返回一个结果集的游标的声明
|
游标处理
为了在一个过程中处理一个游标的结果，需要做以下事情：
- 在存储过程块的开头部分 DECLARE 游标。
- 打开该游标。
- 将游标的结果取出到之前已声明的本地变量中（隐式游标处理除外，在下面的 FOR 语句中将对此加以解释）。
- 关闭该游标。（注意：如果现在不关闭游标，当过程终止时将隐式地关闭游标）。
简介
DB2 SQL Procedural Language（SQL PL）是 SQL Persistent Stored Module 语言标准的一个子集。该标准结合了 SQL 访问数据的方便性和编程语言的流控制。通过 SQL PL 当前的语句集合和语言特性，可以用 SQL 开发综合的、高级的程序，例如函数、存储过程和触发器。这样便可以将业务逻辑封装到易于维护的数据库对象中，从而提高数据库应用程序的性能。
SQL PL 支持本地和全局变量，包括声明和赋值，还支持条件语句和迭代语句、控制语句的转移、错误管理语句以及返回结果集的方法。这些话题将在本教程中讨论。
变量声明
SQL 过程允许使用本地变量赋予和获取 SQL 值，以支持所有 SQL 逻辑。在 SQL 过程中，在代码中使用本地变量之前要先进行声明。
清单 1 中的图演示了变量声明的语法：
清单 1. 变量声明的语法
学习 DB2 9.5 SQL Procedural Language，包括变量、条件、处理程序声明、控制流和迭代语句以及错误处理机制。
条件语句
SQL PL 中支持两种类型的条件语句 — IF 语句和 CASE 语句。
IF 语句
通过 IF 语句可以根据一个条件的状态来实现逻辑的分支。IF 语句支持使用可选的 ELSEIF 子句和默认的 ELSE 子句。END IF
清单 11 展示了一个示例 IF 语句。
清单 11. IF 语句示例
|
CASE 语句
SQL PL 支持两种类型的 CASE 语句，以根据一个条件的状态实现逻辑的分支：
- simple
- searched
清单 12 显示了使用 searched CASE 语句的一个存储过程的例子。
清单 12. 使用 searched CASE 语句的存储过程
|
迭代语句
SQL PL 支持一些重复执行某个逻辑的方法，包括简单的 LOOP、WHILE 循环、REPEAT 循环和 FOR 循环：
- LOOP 循环 -- 简单的循环
- L1: LOOP
- SQL statements;
- LEAVE L1;
- END LOOP L1;
- WHILE 循环 -- 进入前检查条件
- WHILE condition
- DO
- SQL statements
- END WHILE;
- REPEAT 循环 -- 退出前检查条件
- REPEAT
- SQL statements;
- condition
- END REPEAT;
- FOR 循环 -- 结果集上的隐式循环
- FOR loop_name
- SELECT … FROM
- DO
- SQL statements;
- END FOR;
请注意，FOR 语句不同于其他的迭代语句，因为它用于迭代一个定义好的结果集中的行。
为了演示这些循环技巧的使用，我们来编写一个过程，该过程从一个 EMPLOYEE 表中获取每个雇员的姓氏、工作年限和年龄，并将其插入到新表 REPORT_INFO_DEPT 中，这些信息分别被声明为 lname varchar(15)、hiredate date 和 birthdate date。
请注意，使用一个简单的 SQL 语句也可以做同样的事情，但是在这个例子中我们使用 3 种不同的循环语句。
清单 13. 简单的循环例子
|
现在，我们使用 WHILE 循环语句来做同样的事情。
清单 14. WHILE 循环的例子
|
REPEAT 循环非常类似于 WHILE 循环，只不过条件是在最后检查的（因此，它实际上是一个 UNTIL 循环）。
现在，我们使用包含 FOR 循环语句的一个过程来填充 REPORT_INFO_DEPT 表。
清单 15. FOR 循环的例子
|
请注意，最后一个过程没有打开游标、从游标中取数据或关闭游标 — 所有这些都是由 FOR 循环语句隐式进行的。而且，可以引用循环中隐式地获取的值，使用循环名称限定列（例如dept_loop.lastname ）— 而不必使用本地变量来存储这些值。
在本教程中，您学习了用于编写过程、用户定义函数和触发器的 SQL Procedural Language。您学习了 SQL Procedure Language 的所有基本要素，包括变量声明和赋值、语法和使用以及用于控制过程逻辑的流程的条件语句和迭代语句。您还学习了如何使用错误处理和结果集。这使您能够构建可集成到数据库应用程序中的定制的、复杂的业务逻辑。
异常处理机制
DECLARE 有名称的条件
SQL PL 允许为给定的 SQLSTATE 声明用户命名的条件，以用于之后的错误处理。条件名称在整个复合语句中必须是惟一的，并且只能在声明它的复合语句中引用它。
清单 16 显示了声明一个有名称的条件的语法。
清单 16. 声明一个有名称的条件的语法
|
下面是条件声明的例子：
|
DECLARE 条件处理程序
如果发生一个错误，存储过程的行为是根据条件处理程序来决定的。在一个存储过程中，可以为一个普通的或有名称的条件和特定的 SQLSTATE 声明一个或多个条件处理程序。当一个 SQL 语句产生一个 SQLEXCEPTION 或 SQLWARNING（SQLCODE <> 0）时，控制被转移到为一个声明的处理程序中，以获取普通的异常或特定的 SQLSTATE 值。
清单 17 显示了受支持的处理程序声明的语法。
清单 17. 处理程序声明的语法
|
下面是演示它如何工作的一些例子。在下面的过程中，如果 UPDATE 语句失败，则控制被转移到 EXIT 处理程序。结果，该过程被终止，但是它的输出参数包含 SQLCODE 和 SQLSTATE 的值。
清单 18. 返回 SQLCODE 和 SQLSTATE 的处理程序的例子
|
请注意，SQLCODE 和 SQLSTATE 应该被显式地声明为本地变量。
清单 19 演示了一个场景，在此场景中，当一个给定的错误发生时，不是退出过程，而是继续某个动作。为了理解这个例子，请注意表 TAB1 被定义为（col1 int, col2 char(5)），在默认情况下，如果发生值溢出，DB2 不是截短一个值，而是产生 SQLSTATE ‘22001’。
清单 19. 具有 CONTINUE 处理程序的过程
|
如果以 ‘Too many’ 作为输入参数 new_status 的值调用这个过程，那么在 INSERT 语句执行期间会产生 SQLSTATE ‘22001’，控制被转移到 CONDITION HANDLER。结果，v_trunc 指示符将被设置为 2，新行将被插入到 TAB1 表中，插入时对 COL2 列使用了截短后的值，该过程最终成功完成。
强制发出异常 -- SIGNAL SQLSTATE
SQL PL 支持发出一个错误或警告条件。这导致一个具有指定 SQLSTATE 的错误或警告被返回，同时返回的还有可选的消息文本。清单 20 显示了 SIGNAL 语句的语法。
清单 20. SIGNAL 语句的语法
|
可以以包含 5 个字符的字符串常量的形式发出一个用户定义的 SQLSTATE。它必须以数字 7、8 或 9 或者字母 I 到 Z 开始。
还可以发出一个特定的条件名称，但是必须在包含 SIGNAL 语句的复合语句中声明它，如下面的清单所示。
|
清单 21 演示了一个 SIGNAL 语句的使用。
清单 21. SIGNAL 语句的使用
|