简单学习--DB2存储过程

最新推荐文章于 2022-07-01 10:45:06 发布

chiyuelu1120

最新推荐文章于 2022-07-01 10:45:06 发布

阅读量140

点赞数

原文链接：https://my.oschina.net/u/225831/blog/85419

版权

  DB2 SQL Procedural Language（SQL PL）是SQL Persistent Stored Module 语言标准的一个子集。该标准结合了SQL 访问数据的方便性和编程语言的流控制。通过SQL PL 当前的语句集合和语言特性，可以用SQL 开发综合的、高级的程序，例如函数、存储过程和触发器。这样便可以将业务逻辑封装到易于维护的数据库对象中，从而提高数据库应用程序的性能。
    SQL PL 支持本地和全局变量，包括声明和赋值，还支持条件语句和迭代语句、控制语句的转移、错误管理语句以及返回结果集的方法。这些话题将在本教程中讨论。
变量声明
    SQL 过程允许使用本地变量赋予和获取SQL 值，以支持所有SQL 逻辑。在SQL 过程中，在代码中使用本地变量之前要先进行声明。
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 INTEGER DEFAULT 0;

请注意：从 DB2 version 9.5 开始才支持在一个DECLARE 语句中声明多个相同数据类型的变量。

数组数据类型
    SQL 过程从9.5 版开始支持数组类型的变量和参数。要定义一个数组类型的变量，需要先在数据库中创建该类型，然后在过程或应用程序中声明它。数组是临时的值，可以在存储过程和应用程序中操纵它，但是不能将它存储到表中。
    数组类型的名称应该用模式加以限定，并且在当前服务器上应该是惟一的。LONG VARCHAR、LONG VARGRPAHIC、XML 和用户定义类型不能作为数组元素的数据类型。
    下面是数组类型的例子：

CREATE TYPE number 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)
BEGIN
DECLARE v_pnumb numbers;
SET v_pnumb = ARRAY[1,2,3,5,7,11];
SET mynames(1) = 'MARINA';
....
END

DB2 支持一些操作数组的方法。例如，函数CARDINALITY(myarray) 返回一个数组中元素的个数。
赋值
SQL PL 提供了SET 语句来为变量和数组元素赋值。
下面是一个 SET 语句的简化的语法：

SET variable_name = value/expression/NULL;

这个变量名可以是一个本地变量、全局变量或数组元素的名称。
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
SET v_numb = ARRAY[1,2,3,4]; --fill up array with values of the array v_numb

为变量赋值的其他方法有：

VALUES INTO
SELECT (or FETCH) INTO

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。

返回当前日期和时间的过程的例子

CREATE PROCEDURE get_datetime(out cdate date, out ctime time)
P1:BEGIN
VALUES CURRENT DATE INTO cdate;
VALUES CURRENT TIME INTO ctime;
END P1

执行后，该过程返回

Name Output
cdate 2012-10-26
ctime 15:40:23

有些专用寄存器的值可以通过SET 语句来更新。例如，为了更新正在访问的模式，需要像下面这样更改专用寄存器CURRENT SCHEMA。

SET CURRENT_SCHEMA = MYSCHEMA

若要更改默认函数路径，则需要更新专用寄存器CURRENT PATH。

游标
声明
SQL 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 子句，以便将游标用于定位更新或删除。
WITHOUT 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) DEFAULT ' ';
DECLARE myres_set CURSOR
FOR SELECT emp, 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 状态。

如果关闭该游标，则结果集将不能返回给调用者应用程序。
返回一个结果集的游标的声明
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 语句中将对此加以解释）。
关闭该游标。（注意：如果现在不关闭游标，当过程终止时将隐式地关闭游标）。

条件语句
SQL PL 中支持两种类型的条件语句— IF 语句和CASE 语句。
IF 语句
通过 IF 语句可以根据一个条件的状态来实现逻辑的分支。IF 语句支持使用可选的ELSEIF 子句和默认的 ELSE 子句。END IF 子句是必需的，它用于表明 IF 语句的结束。
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 语句，以根据一个条件的状态实现逻辑的分支：

simple CASE 语句用于根据一个字面值进入某个逻辑。
searched CASE 语句用于根据一个表达式的值进入某个逻辑。

使用searched CASE 语句的存储过程

CREATE PROCEDURE sal_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 > 30 THEN
SET v_incr_rate = 0.08;
WHEN years_of_serv > 20 THEN
SET v_incr_rate = 0.07;
WHEN years_of_serv > 10 THEN
SET v_incr_rate = 0.05;
ELSE
SET v_incr_rate = 0.04;
END CASE;
UPDATE empl1
SET salary = salary + salary * v_incr_rate;
WHERE empno = empid;
END

迭代语句
SQL PL 支持一些重复执行某个逻辑的方法，包括简单的LOOP、WHILE 循环、REPEAT 循环和FOR 循环：

LOOP 循环-- 简单的循环

L1: LOOP
SQL statements;
LEAVE L1;
END LOOP L1;

WHILE 循环-- 进入前检查条件

WHILE condition
DO
SQL statements
END WHILE;

REPEAT 循环-- 退出前检查条件

REPEAT
SQL statements;
UNTIL condition
END REPEAT;

FOR 循环-- 结果集上的隐式循环

FOR loop_name AS
SELECT … FROM
DO
SQL statements;
END FOR;

请注意，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)
L1:BEGIN
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 = 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;
END IF;
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_counter;
END L1;

WHILE 循环的例子

CREATE PROCEDURE DEPT_REPT(DEPTIN char(3), OUT p_counter INTEGER)
P1:BEGIN
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 = deptin;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_at_end = 1;
OPEN c1;
FETCH c1 INTO v_lastname, v_hired, v_birthd;
WHILE(v_at_end = 0)
DO
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_birthd;
END WHILE;
SET p_counter = v_counter;
END P1

REPEAT 循环非常类似于WHILE 循环，只不过条件是在最后检查的（因此，它实际上是一个UNTIL 循环）。
FOR 循环的例子

CREATE PROCEDURE DEPT_REPT1(DEPT char(3), OUT p_coumter INTEGER)
P1:BEGIN
DECLARE v_counter INT DEFAULT 0;
FOR dept_loop AS
SELECT lastname, hiredate, birthdate FROM employee
WHERE WORKDEPT = deptin ----注意，这里是没有分号的
DO
INSERT INTO DEPORT_INFO_DEPT values
(dept_loop.lastname, dept_loop.hiredate, dept_loop.birthdate);
SET v_counter = v_counter + 1;
END FOR;
SET p_coumter = v_counter;
END P1

请注意，最后一个过程没有打开游标、从游标中取数据或关闭游标— 所有这些都是由 FOR 循环语句隐式进行的。而且，可以引用循环中隐式地获取的值，使用循环名称限定列（例如dept_loop.lastname ）— 而不必使用本地变量来存储这些值。

异常处理机制
DECLARE 有名称的条件
SQL PL 允许为给定的SQLSTATE 声明用户命名的条件，以用于之后的错误处理。条件名称在整个复合语句中必须是惟一的，并且只能在明它的复合语句中引用它。
声明一个有名称的条件的语法

|--DECLARE--condition-name
--CONDITION--FOR---------------------->
.-VALUE-.
.-SQLSTATE--+-------+-.
>--+---------------------+--string-constant
---------------------|

下面是条件声明的例子

DECLARE FOREIGN_KEY_VIOLATION CONDITION FOR SQLSTATE ‘23503’;
DECLARE overflow CONDITION FOR SQLSTATE '22003';

DECLARE 条件处理程序
如果发生一个错误，存储过程的行为是根据条件处理程序来决定的。在一个存储过程中，可以为一个普通的或有名称的条件和特定的SQLSTATE 声明一个或多个条件处理程序。当一个SQL 语句产生一个SQLEXCEPTION 或SQLWARNING（SQLCODE <> 0）时，控制被转移到为一个声明的处理程序中，以获取普通的异常或特定的SQLSTATE 值。
处理程序声明的语法
|--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
-----------------------'-+-+-----------------|
'-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
BEGIN
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_empno;
END

请注意，SQLCODE 和SQLSTATE 应该被显式地声明为本地变量。
具有CONTINUE 处理程序的过程

CREATE PROCEDURE proc1 (IN num int, IN new_status varchar(10))
P1: BEGIN
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
BEGIN
INSERT INTO tab1 VALUES (num, substr (new_sataus,1,5));
SET v_trunc = 2;
END;
INSERT INTO tab1 VALUES(num,new_status);
RETURN v_trunc;
END P1

    如果以 ‘Too many’ 作为输入参数new_status 的值调用这个过程，那么在INSERT 语句执行期间会产生SQLSTATE ‘22001’，控制被转移到CONDITION HANDLER。结果，v_trunc 指示符将被设置为2，新行将被插入到TAB1 表中，插入时对COL2 列使用了截短后的值，该过程最终成功完成。
强制发出异常-- SIGNAL SQLSTATE
    SQL PL 支持发出一个错误或警告条件。这导致一个具有指定SQLSTATE 的错误或警告被返回，同时返回的还有可选的消息文本。
SIGNAL 语句的语法
>>-SIGNAL------------------------------------------------------->
.-VALUE-.
>--+-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’;

SIGNAL 语句的使用

CREATE PROCEDURE sign_test (IN num int, IN new_status varchar(10))
P1: BEGIN
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';
END IF;
INSERT INTO TAB1 VALUES (num, new_status);
END P1

在本教程中，您学习了用于编写过程、用户定义函数和触发器的SQL Procedural Language。您学习了SQL Procedure Language 的所有基本要素，包括变量声明和赋值、语法和使用以及用于控制过程逻辑的流程的条件语句和迭代语句。您还学习了如何使用错误处理和结果集。这使您能够构建可集成到数据库应用程序中的定制的、复杂的业务逻辑。

转载于:https://my.oschina.net/u/225831/blog/85419

chiyuelu1120

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
简单学习--DB2存储过程

DB2 SQL Procedural Language（SQL PL）是SQL Persistent Stored Module 语言标准的一个子集。该标准结合了SQL 访问数据的方便性和编程语言的流控制。通过SQL PL 当前的语句集合和语言特性，可以用SQL 开发综合的、高级的程...
复制链接

扫一扫