DB2存储过程-基础详解

关键字: 存储过程

学习 DB2 9.5 SQL Procedural Language，包括变量、条件、处理程序声明、控制流和迭代语句以及错误处理机制。

您将：

• 学习 SQL PL 的基本要素。
• 理解如何声明变量、条件和处理程序。
• 学习控制流语句。
• 学习游标处理和如何返回结果集。
• 理解错误处理机制。

简介

DB2 SQL Procedural Language（SQL PL）是 SQL Persistent Stored Module 语言标准的一个子集。该标准结合了 SQL 访问数据的方便性和编程语言的流控制。通过 SQL PL 当前的语句集合和语言特性，可以用 SQL 开发综合的、高级的程序，例如函数、存储过程和触发器。这样便可以将业务逻辑封装到易于维护的数据库对象中，从而提高数据库应用程序的性能。

SQL PL 支持本地和全局变量，包括声明和赋值，还支持条件语句和迭代语句、控制语句的转移、错误管理语句以及返回结果集的方法。这些话题将在本教程中讨论。

变量声明

SQL 过程允许使用本地变量赋予和获取 SQL 值，以支持所有 SQL 逻辑。在 SQL 过程中，在代码中使用本地变量之前要先进行声明。

清单 1 中的图演示了变量声明的语法：
清单 1. 变量声明的语法

Sql代码

1. .-,-----------------.
2. V |
3. |--DECLARE----SQL-variable-name-+------------------------------->
5. .-DEFAULT NULL------.
6. >--+-data-type--+-------------------+-+-------------------------|
7. | '-DEFAULT--constant-' |

					           .-,-----------------.
V                   |
|--DECLARE----SQL-variable-name-+------------------------------->
.-DEFAULT NULL------.
>--+-data-type--+-------------------+-+-------------------------|
|            '-DEFAULT--constant-' |

SQL-variable-name 定义本地变量的名称。该名称不能与其他变量或参数名称相同，也不能与列名相同。

图 1 显示了受支持的 DB2 数据类型：

DEFAULT 值 – 如果没有指定，在声明时将赋值为 NULL。

下面是变量声明的一些例子：

• 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代码

1. >>-CREATE TYPE—array-type-name--AS--| data-type |--ARRAY--[---------->
3. .-2147483647-------.
4. >--+------------------+--]-------------------------------------><
5. '-integer-constant-'

>>-CREATE TYPE—array-type-name--AS--| data-type |--ARRAY--[---------->
.-2147483647-------.
>--+------------------+--]-------------------------------------><
'-integer-constant-'

数组类型的名称应该用模式加以限定，并且在当前服务器上应该是惟一的。LONG VARCHAR、LONG VARGRPAHIC、XML 和用户定义类型不能作为数组元素的数据类型。

下面是数组类型的例子：

Sql代码

1. CREATE TYPE numbers as INTEGER ARRAY[100];
2. CREATE TYPE names as VARCHAR(30) ARRAY[];
3. CREATE TYPE MYSCHEMA.totalcomp as DECIMAL(12,2) ARRAY[];

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代码

1. CREATE PROCEDURE PROC_VARRAY_test (out mynames names)
2. BEGIN
3. DECLARE v_pnumb numbers;
4. SET v_pnumb = ARRAY[1,2,3,5,7,11];
5. SET mynames(1) =’MARINA’;
7. …
8. END

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 语句的简化的语法：

SET variable_name = value/expression/NULL;

这个变量名可以是一个本地变量、全局变量或数组元素的名称。

下面是一些例子：

清单 4. SET 语句的例子

SET var1 = 10; SET total = (select sum(c1) from T1); SET var2 = POSSTR(‘MYTEST’,’TEST’); SET v_numb(10) = 20; -- assign value of 20 to the 10th element of the array v_numb SET v_numb = ARRAY[1,2,3,4]; -- fill up array with values

为变量赋值的其他方法有：

VALUES INTO SELECT (or FETCH) INTO

下面的例子演示了这些方法的使用：

清单 5. VALUE INTO 和 SELECT INTO 的例子

VALUES 2 INTO v1; VALUES ‘TEST’ INTO var2; SELECT SUM(c1) INTO var1 FROM T1; SELECT POSSTR(‘MYTEST’,’TEST’) INTO v1 FROM SYSIBM.SYSDUMMY1;

专用寄存器

专用寄存器（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. 返回当前日期和时间的过程

CREATE PROCEDURE get_datetime (out cdate date, out ctime time ) P1: BEGIN VALUES CURRENT DATE INTO cdate; VALUES CURRENT TIME INTO ctime; END P1

执行后，该过程返回：

Name Input Output cdate 2008-08-28 ctime 13:47:41

有些专用寄存器的值可以通过 SET 语句来更新。例如，为了更新正在访问的模式，需要像下面这样更改专用寄存器 CURRENT SCHEMA。

SET CURRENT_SCHEMA = MYSCHEMA

若要更改默认函数路径，则需要更新专用寄存器 CURRENT PATH。

游标

声明

SQL PL 提供 DECLARE cursor 语句来定义一个游标，并提供其他语句来支持返回其他结果集和游标处理。

下面是游标声明的语法：

清单 7. 游标声明的语法

>>-DECLARE--cursor-name --CURSOR----------> >--FOR--+-select-statement -+------------->< .-WITHOUT HOLD-. |--+--------------+---------------------------------------------| '-WITH HOLD----' .-WITHOUT RETURN-------------. |--+----------------------------+-------------------------------| | .-TO CALLER-. | '-WITH RETURN--+-----------+-' '-TO CLIENT-'

Select-statement 是一条有效的 SQL SELECT 语句。可以指定 FOR UPDATE 子句，以便将游标用于定位更新或删除。

WITHOUT HOLD/WITH HOLD 选项定义 COMMIT 操作之后的游标状态（open/close）。默认情况下为 WITHOUT HOLD。如果使用了 WITH HOLD 选项定义一个游标，那么在 COMMIT 操作之后，该游标保持 OPEN 状态。在 ROLLBACK 操作之后，所有游标都将被关闭。

下面是一个显式声明游标的例子，它可以用于过程中后面的迭代处理：

清单 8. 游标声明的例子

DECLARE mycur1 CURSOR FOR SELECT e.empno, e.lastname, e.job FROM employee e, department d WHERE e.workdept = d.deptno AND deptname =’PLANNING’;

虽然 SQL 语句不能包含参数占位符，但是它可以引用在游标之前声明的本地变量。例如：

清单 9. 使用本地变量的游标声明

DECLARE v_dept CHAR(3) DEAFULT ‘ ‘; DECLARE myres_set CURSOR FOR SELECT empno, lastname, job, salary, comm. FROM employee WHERE workdept = v_dept;

游标和结果集

在 SQL 过程中，除了迭代结果集中的行以外，游标还可以做更多的事情。游标还可用于将结果集返回给调用程序或其他过程。

• WITHOUT RETURN/WITH return 选项指定游标的结果表是否用于作为从一个过程中返回的结果集。
• WITH RETURN TO CALLER 选项指定将来自游标的结果集返回给调用者，后者可以是另一个过程或一个客户机应用程序。这是默认选项。
• WITH RETURN TO CLIENT 选项指定将来自游标的结果集返回给客户机应用程序，绕过任何中间的嵌套过程。

若要从一个过程中返回结果集，需要：

1. 创建一个过程，创建时指定 DYNAMIC RESULT SETS 子句。
2. 声明游标，声明时指定 WITH RETURN 子句。
3. 打开该游标，并使之保持 open 状态。

如果关闭该游标，则结果集将不能返回给调用者应用程序。

清单 10 演示了一个游标的声明，该游标从一个过程中返回一个结果集：

清单 10. 返回一个结果集的游标的声明

CREATE PROCEDURE emp_from_dept() DYNAMIC RESULT SETS 1 P1: BEGIN DECLARE c_emp_dept CURSOR WITH RETURN FOR SELECT empno, lastname, job, salary, comm. FROM employee WHERE workdept = ‘E21’; OPEN c_emp_dept; END P1

游标处理

为了在一个过程中处理一个游标的结果，需要做以下事情：

1. 在存储过程块的开头部分 DECLARE 游标。
2. 打开该游标。
3. 将游标的结果取出到之前已声明的本地变量中（隐式游标处理除外，在下面的 FOR 语句中将对此加以解释）。
4. 关闭该游标。（注意：如果现在不关闭游标，当过程终止时将隐式地关闭游标）。

条件语句

SQL PL 中支持两种类型的条件语句 — IF 语句和 CASE 语句。

IF 语句

通过 IF 语句可以根据一个条件的状态来实现逻辑的分支。IF 语句支持使用可选的 ELSEIF 子句和默认的 ELSE 子句。END IF 子句是必需的，它用于表明 IF 语句的结束。

清单 11 展示了一个示例 IF 语句。

清单 11. IF 语句示例

IF years_of_serv > 30 THEN SET gl_sal_increase = 15000; ELSEIF years_of_serv > 20 THEN SET gl_sal_increase = 12000; ELSE SET gl_sal_increase = 10000; END IF;

回页首

CASE 语句

SQL PL 支持两种类型的 CASE 语句，以根据一个条件的状态实现逻辑的分支：

• simple CASE 语句用于根据一个字面值进入某个逻辑。
• searched CASE 语句用于根据一个表达式的值进入某个逻辑。

清单 12 显示了使用 searched CASE 语句的一个存储过程的例子。

清单 12. 使用 searched CASE 语句的存储过程

CREATE PROCEDURE sal_increase_lim1 (empid CHAR(6)) BEGIN DECLARE years_of_serv INT DEFAULT 0; DECLARE v_incr_rate DEC(9,2) DEFAULT 0.0; SELECT YEAR(CURRENT DATE) - YEAR(hiredate) INTO years_of_serv FROM empl1 WHERE empno = empid; CASE WHEN years_of_serv > 30 THEN SET v_incr_rate = 0.08; WHEN years_of_serv > 20 THEN SET v_incr_rate = 0.07; WHEN years_of_serv > 10 THEN SET v_incr_rate = 0.05; ELSE SET v_incr_rate = 0.04; END CASE; UPDATE empl1 SET salary = salary+salary*v_incr_rate WHERE empno = empid; END

迭代语句

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;
  • UNTIL condition
  • END REPEAT;

• FOR 循环 -- 结果集上的隐式循环
  • FOR loop_name AS
  • SELECT … FROM
  • DO
  • SQL statements;
  • END FOR;

请注意，FOR 语句不同于其他的迭代语句，因为它用于迭代一个定义好的结果集中的行。

为了演示这些循环技巧的使用，我们来编写一个过程，该过程从一个 EMPLOYEE 表中获取每个雇员的姓氏、工作年限和年龄，并将其插入到新表 REPORT_INFO_DEPT 中，这些信息分别被声明为 lname varchar(15)、hiredate date 和 birthdate date。

请注意，使用一个简单的 SQL 语句也可以做同样的事情，但是在这个例子中我们使用 3 种不同的循环语句。

清单 13. 简单的循环例子

CREATE PROCEDURE LEAVE_LOOP (DEPTIN char(3), OUT p_counter INTEGER) Ll: BEGIN DECLARE v_at_end , v_counter INTEGER DEFAULT 0; DECLARE v_lastname VARCHAR(15); DECLARE v_birthd, v_hired DATE; DECLARE c1 CURSOR FOR SELECT lastname, hiredate, birthdate FROM employee WHERE WORKDEPT = deptin; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_at_end = 1; OPEN c1; FETCH_LOOP: LOOP FETCH c1 INTO v_lastname, v_hired, v_birthd; IF v_at_end <> 0 THEN -- loop until last row of the cursor LEAVE FETCH_LOOP; END IF; SET v_counter = v_counter + 1; INSERT INTO REPORT_INFO_DEPT values(v_lastname, v_hired, v_birthd); END LOOP FETCH_LOOP; SET p_counter = v_counter; END Ll

现在，我们使用 WHILE 循环语句来做同样的事情。

清单 14. WHILE 循环的例子

CREATE PROCEDURE DEPT_REPT (DEPTIN char(3), OUT p_counter INTEGER) Pl: BEGIN DECLARE v_at_end , v_counter INTEGER DEFAULT 0; DECLARE v_lastname VARCHAR(15); DECLARE v_birthd, v_hired DATE; DECLARE c1 CURSOR FOR SELECT lastname, hiredate, birthdate FROM employee WHERE WORKDEPT = deptin; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_at_end = 1; OPEN c1; FETCH c1 INTO v_lastname, v_hired, v_birthd; WHILE (v_at_end = 0) DO INSERT INTO REPORT_INFO_DEPT values(v_lastname, v_hired, v_birthd); SET v_counter = v_counter + 1; FETCH c1 INTO v_lastname, v_hired, v_birthd; END WHILE; SET p_counter = v_counter; END P1

REPEAT 循环非常类似于 WHILE 循环，只不过条件是在最后检查的（因此，它实际上是一个 UNTIL 循环）。

现在，我们使用包含 FOR 循环语句的一个过程来填充 REPORT_INFO_DEPT 表。

清单 15. FOR 循环的例子

CREATE PROCEDURE DEPT_REPT1 (DEPTIN char(3), OUT p_counter INT) P1:BEGIN DECLARE v_counter INT DEFAULT 0; FOR dept_loop AS SELECT lastname, hiredate, birthdate FROM employee WHERE WORKDEPT = deptin DO INSERT INTO REPORT_INFO_DEPT values (dept_loop.lastname,dept_loop.hiredate,dept_loop.birthdate); SET v_counter = v_counter + 1; END FOR; SET p_counter = v_counter; END P1

请注意，最后一个过程没有打开游标、从游标中取数据或关闭游标 — 所有这些都是由 FOR 循环语句隐式进行的。而且，可以引用循环中隐式地获取的值，使用循环名称限定列（例如dept_loop.lastname ）— 而不必使用本地变量来存储这些值。

异常处理机制

DECLARE 有名称的条件

SQL PL 允许为给定的 SQLSTATE 声明用户命名的条件，以用于之后的错误处理。条件名称在整个复合语句中必须是惟一的，并且只能在声明它的复合语句中引用它。

清单 16 显示了声明一个有名称的条件的语法。

清单 16. 声明一个有名称的条件的语法

|--DECLARE--condition-name --CONDITION--FOR----------------------> .-VALUE-. .-SQLSTATE--+-------+-. >--+---------------------+--string-constant ---------------------|

下面是条件声明的例子：

DECLARE FOREIGN_KEY_VIOLATION CONDITION FOR SQLSTATE ‘23503’; DECLARE overflow CONDITION FOR SQLSTATE '22003';

DECLARE 条件处理程序

如果发生一个错误，存储过程的行为是根据条件处理程序来决定的。在一个存储过程中，可以为一个普通的或有名称的条件和特定的 SQLSTATE 声明一个或多个条件处理程序。当一个 SQL 语句产生一个 SQLEXCEPTION 或 SQLWARNING（SQLCODE <> 0）时，控制被转移到为一个声明的处理程序中，以获取普通的异常或特定的 SQLSTATE 值。

清单 17 显示了受支持的处理程序声明的语法。

清单 17. 处理程序声明的语法

|--DECLARE--+-CONTINUE-+--HANDLER--FOR--------------------------> +-EXIT-----+ '-UNDO-----' >--+-specific-condition-value -+--| SQL-procedure-statement |----| '-general-condition-value --' WHERE specific-condition-value .-,----------------------------------------. V .-VALUE-. | |----+-SQLSTATE--+-------+--string-constant -+-+-----------------| '-condition-name -----------------------'

下面是演示它如何工作的一些例子。在下面的过程中，如果 UPDATE 语句失败，则控制被转移到 EXIT 处理程序。结果，该过程被终止，但是它的输出参数包含 SQLCODE 和 SQLSTATE 的值。

清单 18. 返回 SQLCODE 和 SQLSTATE 的处理程序的例子

CREATE PROCEDURE simple_error (IN new_job CHAR(8), IN p_empno CHAR(6), OUT p_state_out CHAR(5),OUT p_code_out INT) SPECIFIC simple_error1 BEGIN DECLARE SQLCODE INT DEFAULT 0; DECLARE SQLSTATE CHAR(5) DEFAULT ‘00000’; DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT SQLSTATE, SQLCODE INTO p_sqlstate_out, p_sqlcode_out FROM SYSIBM.SYSDUMMY1; UPDATE EMPLOYEE SET job = new_job WHERE empno = p_empno; END

请注意，SQLCODE 和 SQLSTATE 应该被显式地声明为本地变量。

清单 19 演示了一个场景，在此场景中，当一个给定的错误发生时，不是退出过程，而是继续某个动作。为了理解这个例子，请注意表 TAB1 被定义为（col1 int, col2 char(5)），在默认情况下，如果发生值溢出，DB2 不是截短一个值，而是产生 SQLSTATE ‘22001’。

清单 19. 具有 CONTINUE 处理程序的过程

CREATE PROCEDURE proc1 (IN num int, IN new_status varchar(10)) P1: BEGIN DECLARE SQLCODE INTEGER default 0; DECLARE SQLSTATE CHAR(5) default ‘ ‘; DECLARE v_trunc INTEGER default 0; DECLARE overflow CONDITION FOR SQLSTATE '22001'; DECLARE CONTINUE HANDLER FOR overflow BEGIN INSERT INTO tab1 VALUES (num, substr (new_sataus,1,5)); SET v_trunc = 2; END; INSERT INTO tab1 VALUES(num, new_status); RETURN v_trunc; END P1

如果以 ‘Too many’ 作为输入参数 new_status 的值调用这个过程，那么在 INSERT 语句执行期间会产生 SQLSTATE ‘22001’，控制被转移到 CONDITION HANDLER。结果，v_trunc 指示符将被设置为 2，新行将被插入到 TAB1 表中，插入时对 COL2 列使用了截短后的值，该过程最终成功完成。

强制发出异常 -- SIGNAL SQLSTATE

SQL PL 支持发出一个错误或警告条件。这导致一个具有指定 SQLSTATE 的错误或警告被返回，同时返回的还有可选的消息文本。清单 20 显示了 SIGNAL 语句的语法。

清单 20. SIGNAL 语句的语法

>>-SIGNAL-------------------------------------------------------> .-VALUE-. >--+-SQLSTATE--+-------+--+-sqlstate-string-constant -+-+--------> | '-variable-name ------------' | '-condition-name ------------------------------------' >--+------------------------+---------------------------------->< '|--+-SET MESSAGE_TEXT-- = --diagnostic-string-expression -+------|

可以以包含 5 个字符的字符串常量的形式发出一个用户定义的 SQLSTATE。它必须以数字 7、8 或 9 或者字母 I 到 Z 开始。

还可以发出一个特定的条件名称，但是必须在包含 SIGNAL 语句的复合语句中声明它，如下面的清单所示。

DECLARE condition overflow for SQLSTATE ‘22001’; …. SIGNAL overflow SET MESSAGE_TEXT = ‘Too many characters, truncated’;

清单 21 演示了一个 SIGNAL 语句的使用。

清单 21. SIGNAL 语句的使用

CREATE PROCEDURE sign_test (IN num int, IN new_status varchar(10)) P1: BEGIN DECLARE SQLCODE INTEGER default 0; DECLARE SQLSTATE CHAR(5) default ''; IF length (new_status) > 5 THEN SIGNAL SQLSTATE '72001' SET MESSAGE_TEXT = 'INPUT VALUE TOO LONG'; END IF; INSERT INTO TAB1 VALUES (num, new_status); END P1

在本教程中，您学习了用于编写过程、用户定义函数和触发器的 SQL Procedural Language。您学习了 SQL Procedure Language 的所有基本要素，包括变量声明和赋值、语法和使用以及用于控制过程逻辑的流程的条件语句和迭代语句。您还学习了如何使用错误处理和结果集。这使您能够构建可集成到数据库应用程序中的定制的、复杂的业务逻辑。