DB2存储过程的事务控制以及错误处理

lockend

文章主要内容是：如何去控制存储过程中，出错了该如何去控制整个事务，以保证数据的完整性，以及和你的预期相吻合。（由于本人用DB2的时间不长，文中有什么不对的地方，请大家指点迷津。谢谢）

1：先准备一下测试的环境：表TAA和表TBB：



代码
CREATE TABLE TBB
(ID  INTEGER         NOT NULL  GENERATED BY DEFAULT
    AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20, MINVALUE 1, MAXVALUE 2147483647, NO CYCLE, NO ORDER),
  C1  VARCHAR(12    NOT NULL
)
  DATA CAPTURE NONE
  IN USERSPACE1;

ALTER TABLE TBB
  LOCKSIZE ROW
  APPEND OFF
  NOT VOLATILE;

CREATE TABLE TAA
(ID  INTEGER         NOT NULL  GENERATED BY DEFAULT
    AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20, MINVALUE 1, MAXVALUE 2147483647, NO CYCLE, NO ORDER),
  C1  VARCHAR(12    NOT NULL
)
  DATA CAPTURE NONE
  IN USERSPACE1;

ALTER TABLE TAA
  LOCKSIZE ROW
  APPEND OFF
  NOT VOLATILE;




错误产生原因：不能把null值插入c1字段。

2：下面来创建测试的存储过程，一般情况下，没用过存储过程的人都会这样写：



CREATE PROCEDURE TEST_ZHAOGW ( )
  BEGIN
    INSERT INTO TAA (C1) VALUES ('AAAAAAA');
    INSERT INTO TBB (C1) VALUES (null);
end;


其结果是：控制台提示出错信息，并且TAA表有一条'AAAAAAA'的记录。而TBB表是空的。

很多人都认为存储过程会自动处理，在存储过程中，它如果出错了，会自动帮你回滚存储过程中所执行的事务。来看下下面一段话：

　　SQL procedure body 是存储过程的主体。其核心是一个复合语句。复合语句由关键词 BEGIN 和 END 包围。这些语句可以是 ATOMIC 或 NOT ATOMIC 的。默认情况下，它们是 NOT ATOMIC 的。SQL Procedures 要求复合语句中的声明和可执行语句符合特定的顺序。

3：上面的一段话就很好解析为何刚才的存储过程执行的结果了。我们就根据这段话，修改一下存储过程：



CREATE PROCEDURE TEST_ZHAOGW ( )
  BEGIN
P1: BEGIN ATOMIC
    INSERT INTO TAA (C1) VALUES ('AAAAAAA');
    INSERT INTO TBB (C1) VALUES (null);
END P1;
end;


这里的P1其实可以不要的，如开头的那行可以是“BEGIN ATOMIC”后面的那行可以是“END;”。如果我没理解错，这个P1应该是这个复合语句的名称吧。

其结果是：控制台提示出错信息，并且TAA和TBB表是空的。（事务是整个复合语句块回滚了）。

4：下面，大家来看一下，存储过程中定义错误的处理方式：

代码
CREATE PROCEDURE TEST_ZHAOGW ( )
  BEGIN
DECLARE   CONTINUE    HANDLER   FOR SQLEXCEPTION
P1: BEGIN ATOMIC
    INSERT INTO TAA (C1) VALUES ('AAAAAAA');
    INSERT INTO TBB (C1) VALUES (null);
    INSERT INTO TAA (C1) VALUES ('bbbbbbbbb');
END P1;
end;


从字面上去理解是：出错继续。

其结果是：控制台提示执行成功。但TAA表和TBB表都没有插入数据。

5：把BEGIN ATOMIC去掉看看什么结果（错误定义的处理方式）：



代码
CREATE PROCEDURE TEST_ZHAOGW ( )
  BEGIN
DECLARE   CONTINUE    HANDLER   FOR SQLEXCEPTION

    INSERT INTO TAA (C1) VALUES ('AAAAAAA');
    INSERT INTO TBB (C1) VALUES (null);
    INSERT INTO TAA (C1) VALUES ('bbbbbbbbb');

end;


其结果是：控制台提示执行成功。但TAA表插入了'AAAAAAA'，TBB表插入了'bbbbbbbbb'。

我想这个设置，是用在一个存储过程中，后面的业务与前面的业务没有任何关联的，大家的操作都是相互独立的时候用到的，如设置的定时修复数据的存储过程。

复合语句：我的理解是：复合语句里面（begin 和end 之间）的代码看成是一个sql语句。因此就有了：



代码
CREATE PROCEDURE TEST_ZHAOGW ( )
  BEGIN
    INSERT INTO TAA (C1) VALUES ('AAAAAAA');
P1:BEGIN ATOMIC
    INSERT INTO TAA (C1) VALUES ('bbbbbbbbb');
    INSERT INTO TBB (C1) VALUES (null);
end P1;
end;


其结果是：控制台提示出错信息，并且TAA表有一条'AAAAAAA'的记录。而TBB表是空的。它只回滚了复合语句块里面的

操作，外面的整个存储过程的语句块还是默认的NOT ATOMIC来的。大家还有什么疑问可以自己继续测试下，有什么特殊的发现，可以分享一下。

大梦

你没处理出错信息！
一个begin与end之间是一个整体！
不要用自动提交！
可以看看sample里面的例子！

dandyt_t

你没处理出错信息！
一个begin与end之间是一个整体！
不要用自动提交！
可以看看sample里面的例子！
大梦 发表于 2010-11-25 23:01

    版主英明。