DB2存储过程--基础详解

DB2存储过程-基础详解2010-12-20 来源：网络简介DB2 SQL Procedural Language（SQL PL）是SQL Persistent Stored Module 语言标准的一个子集。

该标准结合了SQL 访问数据的方便性和编程语言的流控制。

通过SQL PL 当前的语句集合和语言特性，可以用SQL 开发综合的、高级的程序，例如函数、存储过程和触发器。

这样便可以将业务逻辑封装到易于维护的数据库对象中，从而提高数据库应用程序的性能。

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

这些话题将在本教程中讨论。

变量声明SQL 过程允许使用本地变量赋予和获取SQL 值，以支持所有SQL 逻辑。

在SQL 过程中，在代码中使用本地变量之前要先进行声明。

清单 1 中的图演示了变量声明的语法：清单 1. 变量声明的语法.-,-----------------.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--[---------->2.3. .-2147483647-------.4.>--+------------------+--]-------------------------------------><5. '-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[];请注意，整数“constant” 指定数组的最大基数，它是可选的。

数组元素可以通过ARRAY-VARIABLE(subindex) 来引用，其中subindex 必须介于 1 到数组的基数之间。

现在可以在SQL 过程中使用这个数据类型：清单 3. 在过程中使用数组数据类型Sql代码1.CREATE PROCEDURE PROC_VARRAY_test (out mynames names)2. BEGIN3.DECLARE v_pnumb numbers;4.SET v_pnumb = ARRAY[1,2,3,5,7,11];5.SET mynames(1) =’MARINA’;6.7.…8.ENDDB2 支持一些操作数组的方法。

例如，函数CARDINALITY(myarray) 返回一个数组中元素的个数。

赋值SQL PL 提供了SET 语句来为变量和数组元素赋值。

下面是一个SET 语句的简化的语法：SET variable_name = value/expression/NULL;这个变量名可以是一个本地变量、全局变量或数组元素的名称。

下面是一些例子：如果关闭该游标，则结果集将不能返回给调用者应用程序。

清单10 演示了一个游标的声明，该游标从一个过程中返回一个结果集：清单10. 返回一个结果集的游标的声明CREATE PROCEDURE emp_from_dept()DYNAMIC RESULT SETS 1P1: BEGINDECLARE c_emp_dept CURSOR WITH RETURNFOR SELECT empno, lastname, job, salary, comm.FROM employeeWHERE 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 THENSET gl_sal_increase = 15000;ELSEIF years_of_serv > 20 THENSET gl_sal_increase = 12000;ELSESET 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)) BEGINDECLARE 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_servFROM empl1WHERE empno = empid;CASEWHEN years_of_serv > 30 THENSET v_incr_rate = 0.08;WHEN years_of_serv > 20 THENSET v_incr_rate = 0.07;WHEN years_of_serv > 10 THENSET v_incr_rate = 0.05;ELSESET v_incr_rate = 0.04;END CASE;UPDATE empl1SET salary = salary+salary*v_incr_rateWHERE empno = empid;END迭代语句SQL PL 支持一些重复执行某个逻辑的方法，包括简单的LOOP、WHILE 循环、REPEAT 循环和FOR 循环：•LOOP 循环-- 简单的循环o L1: LOOPo SQL statements;o LEAVE L1;o END LOOP L1;•WHILE 循环-- 进入前检查条件o WHILE conditiono DOo SQL statementso END WHILE;•REPEAT 循环-- 退出前检查条件o REPEATo SQL statements;o UNTIL conditiono END REPEAT;•FOR 循环-- 结果集上的隐式循环o FOR loop_name ASo SELECT … FROMo DOo SQL statements;o 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: BEGINDECLARE v_at_end , v_counter INTEGER DEFAULT 0;DECLARE v_lastname VARCHAR(15);DECLARE v_birthd, v_hired DATE;DECLARE c1 CURSORFOR SELECT lastname, hiredate, birthdate FROM employeeWHERE WORKDEPT = deptin;DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_at_end = 1;OPEN c1;FETCH_LOOP: LOOPFETCH 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;|--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-----------------------'下面是演示它如何工作的一些例子。