DB2存储过程-基础详解
DB2存储过程-基础详解关键字: 存储过程您将：
[*]学习 SQL PL 的基本要素。
[*]理解如何声明变量、条件和处理程序。
[*]学习控制流语句。
[*]学习游标处理和如何返回结果集。
[*]理解错误处理机制。
Sql代码
[*] .-,-----------------.
[*] V |
[*]
|--DECLARE----SQL-variable-name-+------------------------------->
[*]
[*]
.-DEFAULT NULL------.
[*]
>--+-data-type--+-------------------+-+-------------------------|
[*]
| '-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代码
[*]>>-CREATE TYPE—array-type-name--AS--| data-type |--ARRAY--[---------->
[*]
[*]
.-2147483647-------.
[*]
>--+------------------+--]-------------------------------------><
[*]
'-integer-constant-'
>>-CREATE TYPE—array-type-name--AS--| data-type |--ARRAY--[---------->
.-2147483647-------.
>--+------------------+--]-------------------------------------><
'-integer-constant-'
数组类型的名称应该用模式加以限定，并且在当前服务器上应该是惟一的。LONG VARCHAR、LONG VARGRPAHIC、XML 和用户定义类型不能作为数组元素的数据类型。
下面是数组类型的例子：
Sql代码
[*]CREATE TYPE numbers as INTEGER ARRAY;
[*]
CREATE TYPE names as VARCHAR(30) ARRAY[];
[*]
CREATE TYPE MYSCHEMA.totalcomp as DECIMAL(12,2) ARRAY[];
CREATE TYPE numbers as INTEGER ARRAY;
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代码
[*]CREATE PROCEDURE PROC_VARRAY_test (out mynames names)
[*]
BEGIN
[*]
DECLARE v_pnumb numbers;
[*]
SET v_pnumb = ARRAY;
[*]
SET mynames(1) =’MARINA’;
[*]
[*]…
[*]
END
CREATE PROCEDURE PROC_VARRAY_test (out mynames names)
BEGIN
DECLARE v_pnumb numbers;
SET v_pnumb = ARRAY;
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 thearray v_numb
SET v_numb = ARRAY;-- 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
执行后，该过程返回：
NameInputOutput
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 选项指定将来自游标的结果集返回给客户机应用程序，绕过任何中间的嵌套过程。
若要从一个过程中返回结果集，需要：
[*]创建一个过程，创建时指定 DYNAMIC RESULT SETS 子句。
[*]声明游标，声明时指定 WITH RETURN 子句。
[*]打开该游标，并使之保持 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
游标处理
为了在一个过程中处理一个游标的结果，需要做以下事情：
[*]在存储过程块的开头部分 DECLARE 游标。
[*]打开该游标。
[*]将游标的结果取出到之前已声明的本地变量中（隐式游标处理除外，在下面的 FOR 语句中将对此加以解释）。
[*]关闭该游标。（注意：如果现在不关闭游标，当过程终止时将隐式地关闭游标）。
简介
DB2 SQL Procedural Language（SQL PL）是 SQL Persistent Stored Module 语言标准的一个子集。该标准结合了 SQL 访问数据的方便性和编程语言的流控制。通过 SQL PL 当前的语句集合和语言特性，可以用 SQL 开发综合的、高级的程序，例如函数、存储过程和触发器。这样便可以将业务逻辑封装到易于维护的数据库对象中，从而提高数据库应用程序的性能。
SQL PL 支持本地和全局变量，包括声明和赋值，还支持条件语句和迭代语句、控制语句的转移、错误管理语句以及返回结果集的方法。这些话题将在本教程中讨论。
变量声明
SQL 过程允许使用本地变量赋予和获取 SQL 值，以支持所有 SQL 逻辑。在 SQL 过程中，在代码中使用本地变量之前要先进行声明。
清单 1 中的图演示了变量声明的语法：
清单 1. 变量声明的语法
学习 DB2 9.5 SQL Procedural Language，包括变量、条件、处理程序声明、控制流和迭代语句以及错误处理机制。
页:
[1]