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. BEGIN

3.DECLARE v_pnumb numbers;

4.SET v_pnumb = ARRAY[1,2,3,5,7,11];

5.SET mynames(1) =’MARINA’;

6.

7.…

8.END

DB2 支持一些操作数组的方法。例如，函数CARDINALITY(myarray) 返回一个数组中元素的个数。

赋值

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

下面是一个SET 语句的简化的语法：

SET variable_name = value/expression/NULL;

这个变量名可以是一个本地变量、全局变量或数组元素的名称。

下面是一些例子：

如果关闭该游标，则结果集将不能返回给调用者应用程序。

清单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

游标处理

为了在一个过程中处理一个游标的结果，需要做以下事情：

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 THEN

SET gl_sal_increase = 15000;

ELSEIF years_of_serv > 20 THEN

SET gl_sal_increase = 12000;

ELSE

SET gl_sal_increase = 10000;

END IF;

CASE 语句

SQL PL 支持两种类型的CASE 语句，以根据一个条件的状态实现逻辑的分支：