DB2存储过程-基础详解 - 加俊 - ITeye技术网站
博客分类：
DB2 9.5 SQL Procedural Language，包括变量、条件、处理程序声明、控制流和迭代语句以及错误处理机制。
学习 SQL PL 的基本要素。
理解如何声明变量、条件和处理程序。
学习控制流语句。
学习游标处理和如何返回结果集。
理解错误处理机制。
SQL Procedural Language（SQL PL）是 SQL Persistent Stored Module 语言标准的一个子集。该标准结合了
SQL 访问数据的方便性和编程语言的流控制。通过 SQL PL 当前的语句集合和语言特性，可以用 SQL
开发综合的、高级的程序，例如函数、存储过程和触发器。这样便可以将业务逻辑封装到易于维护的数据库对象中，从而提高数据库应用程序的性能。
支持本地和全局变量，包括声明和赋值，还支持条件语句和迭代语句、控制语句的转移、错误管理语句以及返回结果集的方法。这些话题将在本教程中讨论。
过程允许使用本地变量赋予和获取 SQL 值，以支持所有 SQL 逻辑。在 SQL 过程中，在代码中使用本地变量之前要先进行声明。
1 中的图演示了变量声明的语法：
.-,-----------------.
|--DECLARE----SQL-variable-name-+-------------------------------&
.-DEFAULT NULL------.
&--+-data-type--+-------------------+-+-------------------------|
'-DEFAULT--constant-' |
SQL-variable-name
定义本地变量的名称。该名称不能与其他变量或参数名称相同，也不能与列名相同。
1 显示了受支持的 DB2 数据类型：
值 – 如果没有指定，在声明时将赋值为 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 语句中声明多个相同数据类型的变量。
过程从 9.5
版开始支持数组类型的变量和参数。要定义一个数组类型的变量，需要先在数据库中创建该类型，然后在过程或应用程序中声明它。数组是临时的值，可以在存储过程和应用程序中操纵它，但是不能将它存储到表中。
支持以下创建数组数据类型的语法：
&&-CREATE TYPE—array-type-name--AS--| data-type |--ARRAY--[----------&
.--------.
&--+------------------+--]-------------------------------------&&
'-integer-constant-'
数组类型的名称应该用模式加以限定，并且在当前服务器上应该是惟一的。LONG
VARCHAR、LONG VARGRPAHIC、XML 和用户定义类型不能作为数组元素的数据类型。
下面是数组类型的例子：
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 过程中使用这个数据类型：
CREATE PROCEDURE PROC_VARRAY_test (out mynames names)
DECLARE v_
SET v_pnumb = ARRAY[1,2,3,5,7,11];
SET mynames(1) =’MARINA’;
支持一些操作数组的方法。例如，函数 CARDINALITY(myarray) 返回一个数组中元素的个数。
PL 提供了 SET 语句来为变量和数组元素赋值。
下面是一个
SET 语句的简化的语法：
SET variable_name = value/expression/NULL;
这个变量名可以是一个本地变量、全局变量或数组元素的名称。
下面是一些例子：
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
array v_numb
SET v_numb = ARRAY[1,2,3,4];
-- fill up array with values
为变量赋值的其他方法有：
VALUES INTO
SELECT (or FETCH) 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）
定义的一个存储块，供一个应用程序过程使用。寄存器中的值可以在 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。
下面的过程返回当前日期和时间：
CREATE PROCEDURE get_datetime (out cdate date, out ctime time )
VALUES CURRENT DATE INTO
VALUES CURRENT TIME INTO
执行后，该过程返回：
Name Input Output
有些专用寄存器的值可以通过
SET 语句来更新。例如，为了更新正在访问的模式，需要像下面这样更改专用寄存器 CURRENT SCHEMA。
SET CURRENT_SCHEMA = MYSCHEMA
若要更改默认函数路径，则需要更新专用寄存器
CURRENT PATH。
PL 提供 DECLARE cursor 语句来定义一个游标，并提供其他语句来支持返回其他结果集和游标处理。
下面是游标声明的语法：
&&-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
子句，以便将游标用于定位更新或删除。
HOLD/WITH HOLD
选项定义 COMMIT
操作之后的游标状态（open/close）。默认情况下为 WITHOUT HOLD。如果使用了 WITH HOLD 选项定义一个游标，那么在 COMMIT
操作之后，该游标保持 OPEN 状态。在 ROLLBACK 操作之后，所有游标都将被关闭。
下面是一个显式声明游标的例子，它可以用于过程中后面的迭代处理：
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 语句不能包含参数占位符，但是它可以引用在游标之前声明的本地变量。例如：
DECLARE v_dept CHAR(3) DEAFULT ‘ ‘;
DECLARE myres_set CURSOR
FOR SELECT empno, lastname, job, salary, comm.
FROM employee
WHERE workdept = v_
SQL 过程中，除了迭代结果集中的行以外，游标还可以做更多的事情。游标还可用于将结果集返回给调用程序或其他过程。
WITHOUT RETURN/WITH return
选项指定游标的结果表是否用于作为从一个过程中返回的结果集。
WITH RETURN TO CALLER
选项指定将来自游标的结果集返回给调用者，后者可以是另一个过程或一个客户机应用程序。这是默认选项。
WITH RETURN TO CLIENT
选项指定将来自游标的结果集返回给客户机应用程序，绕过任何中间的嵌套过程。
若要从一个过程中返回结果集，需要：
创建一个过程，创建时指定 DYNAMIC RESULT SETS 子句。
声明游标，声明时指定 WITH RETURN 子句。
打开该游标，并使之保持 open 状态。
如果关闭该游标，则结果集将不能返回给调用者应用程序。
10 演示了一个游标的声明，该游标从一个过程中返回一个结果集：
CREATE PROCEDURE emp_from_dept()
DYNAMIC RESULT SETS 1
DECLARE c_emp_dept CURSOR WITH RETURN
FOR SELECT empno, lastname, job, salary, comm.
FROM employee
WHERE workdept = ‘E21’;
OPEN c_emp_
为了在一个过程中处理一个游标的结果，需要做以下事情：
在存储过程块的开头部分 DECLARE 游标。
打开该游标。
将游标的结果取出到之前已声明的本地变量中（隐式游标处理除外，在下面的 FOR 语句中将对此加以解释）。
关闭该游标。（注意：如果现在不关闭游标，当过程终止时将隐式地关闭游标）。
PL 中支持两种类型的条件语句 — IF 语句和 CASE 语句。
IF 语句可以根据一个条件的状态来实现逻辑的分支。IF 语句支持使用可选的
子句和默认的
子句。END IF
子句是必需的，它用于表明 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;
SET gl_sal_increase = 10000;
PL 支持两种类型的 CASE 语句，以根据一个条件的状态实现逻辑的分支：
语句用于根据一个字面值进入某个逻辑。
语句用于根据一个表达式的值进入某个逻辑。
12 显示了使用 searched CASE 语句的一个存储过程的例子。
CREATE PROCEDURE sal_increase_lim1 (empid CHAR(6))
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 =
years_of_serv & 30 THEN
SET v_incr_rate = 0.08;
years_of_serv & 20 THEN
SET v_incr_rate = 0.07;
years_of_serv & 10 THEN
SET v_incr_rate = 0.05;
SET v_incr_rate = 0.04;
UPDATE empl1
SET salary = salary+salary*v_incr_rate
WHERE empno =
PL 支持一些重复执行某个逻辑的方法，包括简单的 LOOP、WHILE 循环、REPEAT 循环和 FOR 循环：
-- 简单的循环
END LOOP L1;
WHILE 循环
-- 进入前检查条件
SQL statements
END WHILE;
REPEAT 循环
-- 退出前检查条件
END REPEAT;
-- 结果集上的隐式循环
SELECT … FROM
请注意，FOR
语句不同于其他的迭代语句，因为它用于迭代一个定义好的结果集中的行。
为了演示这些循环技巧的使用，我们来编写一个过程，该过程从一个
EMPLOYEE 表中获取每个雇员的姓氏、工作年限和年龄，并将其插入到新表 REPORT_INFO_DEPT 中，这些信息分别被声明为 lname
varchar(15)、hiredate date 和 birthdate date。
请注意，使用一个简单的
SQL 语句也可以做同样的事情，但是在这个例子中我们使用 3 种不同的循环语句。
CREATE PROCEDURE LEAVE_LOOP (DEPTIN char(3), OUT p_counter INTEGER)
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 =
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_at_end = 1;
FETCH_LOOP: LOOP
FETCH c1 INTO v_lastname, v_hired, v_
IF v_at_end && 0 THEN
-- loop until last row of the cursor
LEAVE FETCH_LOOP;
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_
现在，我们使用
WHILE 循环语句来做同样的事情。
CREATE PROCEDURE DEPT_REPT (DEPTIN char(3), OUT p_counter INTEGER)
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 =
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_at_end = 1;
FETCH c1 INTO v_lastname, v_hired, v_
WHILE (v_at_end = 0)
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_
END WHILE;
SET p_counter = v_
循环非常类似于 WHILE 循环，只不过条件是在最后检查的（因此，它实际上是一个 UNTIL 循环）。
现在，我们使用包含
FOR 循环语句的一个过程来填充 REPORT_INFO_DEPT 表。
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);
SET v_counter = v_counter + 1;
SET p_counter = v_
请注意，最后一个过程没有打开游标、从游标中取数据或关闭游标
— 所有这些都是由 FOR
循环语句隐式进行的。而且，可以引用循环中隐式地获取的值，使用循环名称限定列（例如dept_loop.lastname
而不必使用本地变量来存储这些值。
异常处理机制
PL 允许为给定的 SQLSTATE
声明用户命名的条件，以用于之后的错误处理。条件名称在整个复合语句中必须是惟一的，并且只能在声明它的复合语句中引用它。
16 显示了声明一个有名称的条件的语法。
|--DECLARE--condition-name
--CONDITION--FOR----------------------&
.-SQLSTATE--+-------+-.
&--+---------------------+--string-constant
---------------------|
下面是条件声明的例子：
DECLARE FOREIGN_KEY_VIOLATION CONDITION FOR SQLSTATE ‘23503’;
DECLARE overflow CONDITION FOR SQLSTATE '22003';
如果发生一个错误，存储过程的行为是根据条件处理程序来决定的。在一个存储过程中，可以为一个普通的或有名称的条件和特定的
SQLSTATE 声明一个或多个条件处理程序。当一个 SQL 语句产生一个 SQLEXCEPTION 或 SQLWARNING（SQLCODE &&
0）时，控制被转移到为一个声明的处理程序中，以获取普通的异常或特定的 SQLSTATE 值。
17 显示了受支持的处理程序声明的语法。
|--DECLARE--+-CONTINUE-+--HANDLER--FOR--------------------------&
+-EXIT-----+
'-UNDO-----'
&--+-specific-condition-value
-+--| SQL-procedure-statement |----|
'-general-condition-value
WHERE specific-condition-value
.-,----------------------------------------.
|----+-SQLSTATE--+-------+--string-constant
-+-+-----------------|
'-condition-name
-----------------------'
下面是演示它如何工作的一些例子。在下面的过程中，如果
UPDATE 语句失败，则控制被转移到 EXIT 处理程序。结果，该过程被终止，但是它的输出参数包含 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
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_
请注意，SQLCODE
和 SQLSTATE 应该被显式地声明为本地变量。
19 演示了一个场景，在此场景中，当一个给定的错误发生时，不是退出过程，而是继续某个动作。为了理解这个例子，请注意表 TAB1 被定义为（col1 int,
col2 char(5)），在默认情况下，如果发生值溢出，DB2 不是截短一个值，而是产生 SQLSTATE ‘22001’。
CREATE PROCEDURE proc1 (IN num int, IN new_status varchar(10))
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
INSERT INTO tab1 VALUES (num, substr (new_sataus,1,5));
SET v_trunc = 2;
INSERT INTO tab1 VALUES(num, new_status);
‘Too many’ 作为输入参数 new_status 的值调用这个过程，那么在 INSERT 语句执行期间会产生 SQLSTATE
‘22001’，控制被转移到 CONDITION HANDLER。结果，v_trunc 指示符将被设置为 2，新行将被插入到 TAB1 表中，插入时对 COL2
列使用了截短后的值，该过程最终成功完成。
PL 支持发出一个错误或警告条件。这导致一个具有指定 SQLSTATE 的错误或警告被返回，同时返回的还有可选的消息文本。清单 20 显示了 SIGNAL
语句的语法。
&&-SIGNAL-------------------------------------------------------&
&--+-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 语句的使用。
CREATE PROCEDURE sign_test (IN num int, IN new_status varchar(10))
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';
TAB1 VALUES (num, new_status);
在本教程中，您学习了用于编写过程、用户定义函数和触发器的 SQL Procedural Language。您学习了
SQL Procedure Language
的所有基本要素，包括变量声明和赋值、语法和使用以及用于控制过程逻辑的流程的条件语句和迭代语句。您还学习了如何使用错误处理和结果集。这使您能够构建可集成到数据库应用程序中的定制的、复杂的业务逻辑。
浏览 55965
iammonster
浏览: 1310639 次
来自: 北京
例子都打不开
好东西[color=blue][/color]
不在haoop中是在linux系统中，映射IP的时候，不能使用 ...
挺好的, 谢谢分享匿名用户不能发表回复！|
每天回帖即可获得10分可用分！小技巧：
你还可以输入10000个字符
(Ctrl+Enter)
请遵守CSDN，不得违反国家法律法规。
转载文章请注明出自“CSDN（www.csdn.net）”。如是商业用途请联系原作者。查看:7961|回复：13
助理工程师
下面是stackoverflow中关于数组方法的相关问题中，获得最多票数的12个数组操作方法。
1.&&声明一个数组
String[] aArray = new String[5];&&String[] bArray = {&a&,&b&,&c&, &d&, &e&};&&String[] cArray = new String[]{&a&,&b&,&c&,&d&,&e&};&&
2.&&输出一个数组
int[] intArray = { 1, 2, 3, 4, 5 };&&String intArrayString = Arrays.toString(intArray);&&// print directly will print reference value&&System.out.println(intArray);&&// [I@7150bd4d&&System.out.println(intArrayString);&&// [1, 2, 3, 4, 5]&&
3.&&从一个数组创建数组列表
String[] stringArray = { &a&, &b&, &c&, &d&, &e& };&&ArrayList&String& arrayList = new ArrayList&String&(Arrays.asList(stringArray));&&System.out.println(arrayList);&&// [a, b, c, d, e]&&
4.&&检查一个数组是否包含某个值
String[] stringArray = { &a&, &b&, &c&, &d&, &e& };&&boolean b = Arrays.asList(stringArray).contains(&a&);&&System.out.println(b);&&// true&&
5.&&连接两个数组
int[] intArray = { 1, 2, 3, 4, 5 };&&int[] intArray2 = { 6, 7, 8, 9, 10 };&&// Apache Commons Lang library&&int[] combinedIntArray = ArrayUtils.addAll(intArray, intArray2);&&
6.&&声明一个内联数组（Array inline）
method(new String[]{&a&, &b&, &c&, &d&, &e&});&&
7.&&把提供的数组元素放入一个字符串
// containing the provided list of elements&&// Apache common lang&&String j = StringUtils.join(new String[] { &a&, &b&, &c& }, &, &);&&System.out.println(j);&&// a, b, c&&
8.&&将一个数组列表转换为数组
String[] stringArray = { &a&, &b&, &c&, &d&, &e& };&&ArrayList&String& arrayList = new ArrayList&String&(Arrays.asList(stringArray));&&String[] stringArr = new String[arrayList.size()];&&arrayList.toArray(stringArr);&&for (String s : stringArr)&&& & System.out.println(s);&&
9.&&将一个数组转换为集（set）
Set&String& set = new HashSet&String&(Arrays.asList(stringArray));&&System.out.println(set);&&//[d, e, b, c, a]&&
10.&&逆向一个数组
int[] intArray = { 1, 2, 3, 4, 5 };&&ArrayUtils.reverse(intArray);&&System.out.println(Arrays.toString(intArray));&&//[5, 4, 3, 2, 1]&&
11.&&移除数组中的元素
int[] intArray = { 1, 2, 3, 4, 5 };&&int[] removed = ArrayUtils.removeElement(intArray, 3);//create a new array&&System.out.println(Arrays.toString(removed));&&
12.&&将整数转换为字节数组
byte[] bytes = ByteBuffer.allocate(4).putInt(8).array();&&for (byte t : bytes) {&&& &System.out.format(&0x%x &, t);&&}&&
中级工程师
好深奥啊~~~~~我来学习的
努力向上，天天学习！
楼主威武。就是平常自己没怎么用过。谢过楼主了
小洋(on my way)
good，好帖
51CTOjava板块讨论QQ群：【1000人群】2群：【可加】 & && && &欢迎热爱JAVA的来讨论^_^
我的个人网站--&
不错的分享~
是很全面的总结哈,不错,要好好复习下这个
ArrayUtils怎么引入到eclipse？求解啊
1222挺好的
助理工程师
引用:原帖由 qq 于
23:21 发表
ArrayUtils怎么引入到eclipse？求解啊 import mons.lang.ArrayU
+个commons-lang-2.3.jar差不多的jar包
路过学习赚豆豆。
引用:原帖由 qq 于
23:21 发表
ArrayUtils怎么引入到eclipse？求解啊 不是JDK中的类的话，就把第三方的jar加入到Build path中去就引入了。
haoxuexile
很不错，学习了