DB2存储过程.docx

1、DB2存储过程DB2 存储过程SQL Procedural Language简介DB2 SQL Procedural Language（SQL PL）是 SQL Persistent Stored Module 语言标准的一个子集。该标准结合了 SQL 访问数据的方便性和编程语言的流控制。通过 SQL PL 当前的语句集合和语言特性，可以用 SQL 开发综合的、高级的程序，例如函数、存储过程和触发器。这样便可以将业务逻辑封装到易于维护的数据库对象中，从而提高数据库应用程序的性能。 SQL PL 支持本地和全局变量，包括声明和赋值，还支持条件语句和迭代语句、控制语句的转移、错误管理语句以及返回结

2、果集的方法。变量声明SQL 过程允许使用本地变量赋予和获取 SQL 值，以支持所有 SQL 逻辑。在 SQL 过程中，在代码中使用本地变量之前要先进行声明。清单 1 中的图演示了变量声明的语法：清单 1. 变量声明的语法DECLARE SQL-variable-name-+- .-DEFAULT NULL-. -+-data-type-+-+-+-| -DEFAULT-constant- |SQL-variable-name 定义本地变量的名称。该名称不能与其他变量或参数名称相同，也不能与列名相同。 图 1 显示了受支持的 DB2 数据类型：图 1. DB2 数据类型DEFAULT 值 如果没

3、有指定，在声明时将赋值为 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 版开始支持数组类型的变量和参数。要定义一个数组类型的变量，需要先在数据库中创建该类型，然后在过程或应用程序

4、中声明它。数组是临时的值，可以在存储过程和应用程序中操纵它，但是不能将它存储到表中。DB2 支持以下创建数组数据类型的语法：清单 2. 创建数组数据类型的语法-CREATE TYPEarray-type-name-AS-| data-type |-ARRAY-.-2147483647-. -+-+-DECLARE-cursor-name-CURSOR- -FOR-+-select-statement-+- 30 THEN SET gl_sal_increase = 15000; ELSEIF years_of_serv 20 THEN SET gl_sal_increase = 12000;

5、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 (IN empid CHAR(6)BEGIN DECLARE years_o

6、f_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_in

7、cr_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 循环 - 简单的循环 o L1: LOOP o SQL statements; o LEAVE L1; o END LOOP L1; WHILE 循环 - 进入前检查条件 o WHILE con

8、dition o DO o SQL statements o END WHILE; REPEAT 循环 - 退出前检查条件 o REPEAT o SQL statements; o UNTIL condition o END REPEAT; FOR 循环 - 结果集上的隐式循环 o FOR loop_name AS o SELECT FROM o DO o SQL statements; o END FOR; 请注意，FOR 语句不同于其他的迭代语句，因为它用于迭代一个定义好的结果集中的行。为了演示这些循环技巧的使用，我们来编写一个过程，该过程从一个 EMPLOYEE 表中获取每个雇员的姓氏、

9、工作年限和年龄，并将其插入到新表 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_las

10、tname 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 - lo

11、op 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; CLOSE c1; SET p_counter = v_counter; END Ll现在，我们使用 WHILE 循环语句来做同样的事情。清单 14. WHILE 循环的例子CREATE PROCEDURE DEPT_REPT (DEPTIN

12、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_e

13、nd = 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 P1REPEAT 循环非常类似于 WHILE 循环，只不

14、过条件是在最后检查的（因此，它实际上是一个 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

15、 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 有名称的

16、条件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 显示了受支持的处理程序声