DB2存储过程基础详解Word格式文档下载.docx

1、请注意，从 DB2 version 9.5 开始才支持在一个 DECLARE 语句中声明多个相同数据类型的变量。数组数据类型 SQL 过程从 9.5 版开始支持数组类型的变量和参数。要定义一个数组类型的变量，需要先在数据库中创建该类型，然后在过程或应用程序中声明它。数组是临时的值，可以在存储过程和应用程序中操纵它，但是不能将它存储到表中。DB2 支持以下创建数组数据类型的语法：清单 2. 创建数组数据类型的语法 Sql代码 1. -CREATETYPEarray-type-name-AS-|data-type|-ARRAY- 2. 3. .-2147483647-.4. -+-+- .-214

2、7483647-. 数组类型的名称应该用模式加以限定，并且在当前服务器上应该是惟一的。LONG VARCHAR、LONG VARGRPAHIC、XML 和用户定义类型不能作为数组元素的数据类型。下面是数组类型的例子：1. CREATETYPEnumbersasINTEGERARRAY100;2. CREATEnamesVARCHAR（30）ARRAY;3. CREATEMYSCHEMA.totalcompDECIMAL（12,2）CREATE TYPE numbers as INTEGER ARRAY100;CREATE TYPE names as VARCHAR（30） ARRAY;CREA

3、TE TYPE MYSCHEMA.totalcomp as DECIMAL（12,2） ARRAY;请注意，整数 “constant” 指定数组的最大基数，它是可选的。数组元素可以通过 ARRAY-VARIABLE（subindex） 来引用，其中 subindex 必须介于 1 到数组的基数之间。现在可以在 SQL 过程中使用这个数据类型：清单 3. 在过程中使用数组数据类型 PROCEDUREPROC_VARRAY_test（outmynamesnames）BEGIN3. DECLAREv_pnumbnumbers;4. SET=ARRAY1,2,3,5,7,11;5. SETmyname

4、s（1）=MARINA;6. 7. 8. ENDCREATE PROCEDURE PROC_VARRAY_test （out mynames names） BEGINDECLARE v_pnumb numbers;SET v_pnumb = ARRAY1,2,3,5,7,11;SET mynames（1） =MARINA;ENDDB2 支持一些操作数组的方法。例如，函数 CARDINALITY（myarray） 返回一个数组中元素的个数。赋值 SQL PL 提供了 SET 语句来为变量和数组元素赋值。下面是一个 SET 语句的简化的语法：SET variable_name = value/ex

5、pression/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_numbSET v_numb = ARRAY1,2,3,4; - fill up array with values为变量赋值的其他方法有：VALUE

6、S INTOSELECT （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 语句中访问和引用。在

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

8、: 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 语句来定义

9、一个游标，并提供其他语句来支持返回其他结果集和游标处理。下面是游标声明的语法：清单 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 H

10、OLD 选项定义 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.deptn

11、o 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

12、 RETURN/WITH return 选项指定游标的结果表是否用于作为从一个过程中返回的结果集。 WITH RETURN TO CALLER 选项指定将来自游标的结果集返回给调用者，后者可以是另一个过程或一个客户机应用程序。这是默认选项。 WITH RETURN TO CLIENT 选项指定将来自游标的结果集返回给客户机应用程序，绕过任何中间的嵌套过程。若要从一个过程中返回结果集，需要：1. 创建一个过程，创建时指定 DYNAMIC RESULT SETS 子句。2. 声明游标，声明时指定 WITH RETURN 子句。3. 打开该游标，并使之保持 open 状态。如果关闭该游标，则结果集将

13、不能返回给调用者应用程序。清单 10 演示了一个游标的声明，该游标从一个过程中返回一个结果集：清单 10. 返回一个结果集的游标的声明 CREATE PROCEDURE emp_from_dept（） DYNAMIC RESULT SETS 1 P1: DECLARE c_emp_dept CURSOR WITH RETURN WHERE workdept = E21; OPEN c_emp_dept; END P1游标处理 为了在一个过程中处理一个游标的结果，需要做以下事情：1. 在存储过程块的开头部分 DECLARE 游标。2. 打开该游标。3. 将游标的结果取出到之前已声明的本地变量中（

14、隐式游标处理除外，在下面的 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; ELS

15、EIF 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 s

16、al_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 SET v_incr_rate = 0.08; 20 THEN SET v_incr_rate = 0.07; 10 THEN SE

17、T v_incr_rate = 0.05; ELSE SET v_incr_rate = 0.04; END CASE; UPDATE empl1 SET salary = salary+salary*v_incr_rateEND 迭代语句 SQL PL 支持一些重复执行某个逻辑的方法，包括简单的 LOOP、WHILE 循环、REPEAT 循环和 FOR 循环： LOOP 循环 - 简单的循环 o L1: LOOP o SQL statements;LEAVE L1;o END LOOP L1; WHILE 循环 - 进入前检查条件 o WHILE condition o DO SQL sta

18、tements o END WHILE; REPEAT 循环 - 退出前检查条件 o REPEAT UNTILo END REPEAT; FOR 循环 - 结果集上的隐式循环 o FOR loop_name AS SELECT FROM o END FOR;请注意，FOR 语句不同于其他的迭代语句，因为它用于迭代一个定义好的结果集中的行。为了演示这些循环技巧的使用，我们来编写一个过程，该过程从一个 EMPLOYEE 表中获取每个雇员的姓氏、工作年限和年龄，并将其插入到新表 REPORT_INFO_DEPT 中，这些信息分别被声明为 lname varchar（15）、hiredate date

19、 和 birthdate date。请注意，使用一个简单的 SQL 语句也可以做同样的事情，但是在这个例子中我们使用 3 种不同的循环语句。清单 13. 简单的循环例子 CREATE PROCEDURE LEAVE_LOOP （DEPTIN char（3）, OUT p_counter INTEGER）Ll: 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 las

20、tname, 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; SET v_counter = v_counter + 1; IN

21、SERT 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: WHILE （v_at_end = 0） DO INSERT INTO REPORT_INFO_DEPT END WHILE;RE

22、PEAT 循环非常类似于 WHILE 循环，只不过条件是在最后检查的（因此，它实际上是一个 UNTIL 循环）。现在，我们使用包含 FOR 循环语句的一个过程来填充 REPORT_INFO_DEPT 表。清单 15. FOR 循环的例子 CREATE PROCEDURE DEPT_REPT1 （DEPTIN char（3）, OUT p_counter INT） DECLARE v_counter INT DEFAULT 0; FOR dept_loop AS SELECT lastname, hiredate, birthdate FROM employee WHERE WORKDEPT = deptin INSERT INTO REPORT_INFO_DEPT values（dept_loop.lastname,dept_loop.hiredate,dept_loop.birthdate）; END FOR;请注意，最后一个过程没有打开游标、从