logo
搜索
我的图书馆

发文章

发文工具

其他工具

留言交流
搜索
分享

Oracle存储过程异常处理事务特性分析

 雪狼11 2014-12-15
 本帖最后由 fffqqqyyy 于 2012-1-5 13:28 编辑

        前2天无意中试验，发现oracle 存储过程中的异常处理，对事务的提交有个出乎意料的影响，时候分析了一下，写了一个文档。给大家分享一下。     附件就不加了，直接把内容发出来。

        Oracle存储过程异常处理事务特性分析
     Oracle存储过程中的事务是如何处理的？通常情况下。整个过程是作为一个事务整体被提交或回滚的，这属于数据库的基本知识，这里简单说明：
  1通常情况
通常情况下，oralce将整个存储过程作为一个事务整体，整个过程内的事务，要么都提交，要么都回滚。
如下例所示：
例1
  1. SQL*Plus: Release 11.2.0.1.0 Production on 星期三 1月 4 13:24:22 2012
  2. Copyright (c) 1982, 2010, Oracle.  All rights reserved.
  3. 请输入用户名:  SYSTEM
  4. 输入口令:
  5. 连接到:
  6. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
  7. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  8. SQL> CREATE TABLE T1 (C INT,D INT NOT NULL);
  9. 表已创建。
  10. SQL> create or replace procedure pt1 is begin
  11.   2    insert into t1 values(1,1);
  12.   3    INSERT INTO T1 VALUES(2,NULL);
  13.   4    end;
  14.   5  /
  15. 过程已创建。
  16. SQL> CALL PT1();
  17. CALL PT1()
  18.      *
  19. 第 1 行出现错误:
  20. ORA-01400: 无法将 NULL 插入 ("SYSTEM"."T1"."D")
  21. ORA-06512: 在 "SYSTEM.PT1", line 3

  23. SQL> SELECT * FROM T1;
  24. 未选定行
复制代码
在这个例子中，我们构造了D列不允许为空的表，并故意在过程中执行一个违反约束的插入语句，造成过程调用的回滚操作，最终通过查询表T1，我们发现执行过程的时候，整个过程内的2条INSERT语句都被回滚了。这证明了在通常情况下，存储过程是作为一个事务整体的。
另一方面需要说明的是，在非自动提交的情况下，存储过程除非内部显式的执行了commit操作，否则即便整个过程执行成功，当前事务也是未提交的，这是存储过程的另一个特点：存储过程并不会自动提交。例子如下：
例2
  1. SQL> create or replace procedure pt1 is begin
  2.   2    insert into t1 values(1,1);
  3.   3   -- INSERT INTO T1 VALUES(2,NULL);
  4.   4    end;
  5.   5  /
  6. 过程已创建。
  7. SQL> select * from t1;
  8. 未选定行
  9. SQL> call pt1();
  10. 调用完成。
  11. SQL> select * from t1;
  12.          C          D
  13. ---------- ----------
  14.          1          1
  15. SQL> commit; --在这里显式提交
  16. 提交完成。
  17. SQL> select * from t1; --提交后能够看到过程插入的记录
  18.          C          D
  19. ---------- ----------
  20.          1          1
  21. SQL> call pt1();
  22. 调用完成。
  23. SQL> rollback; --在这里显式回滚
  24. 回退已完成。
  25. SQL> select * from t1; --发现回滚导致第二次执行存储过程的操作未生效
  26.          C          D
  27. ---------- ----------
  28.          1          1
  29. SQL>
复制代码
但是如果过程内部使用了commit命令或rollback命令，则存储过程就能够自行控制整个事务的结束时机，而不受整个会话的影响。例如：
例3
  1. SQL> drop table t1;
  2. 表已删除。
  3. SQL> create table t1(c int,d int);
  4. 表已创建。
  5. SQL> create or replace procedure pt1 is begin
  6.   2    insert into t1 values(1,1);
  7.   3      savepoint  pt1;
  8.   4    insert into t1 values(2,2);
  9.   5      savepoint  pt2;
  10.   6    commit;
  11.   7    end;
  12.   8  /
  13. 过程已创建。
  14. SQL> truncate table t1;
  15. 表已截断。
  16. SQL> call pt1();
  17. 调用完成。
  18. SQL> select * from t1;
  19.          C          D
  20. ---------- ----------
  21.          1          1
  22.          2          2
  23. SQL> rollback;-- 此时即使在过程外执行ROLLBACK，由于过程内已经COMMIT，因此这个ROLLBACK的作用范围将不会影响到过程内部。
  24. 回退已完成。
  25. SQL> select * from t1;
  26.          C          D
  27. ---------- ----------
  28.          1          1
  29.          2          2
复制代码
所以总结一下，我们可以在存储过程内执行COMMIT或ROLLBACK，使过程能够自行结束事务。但如果存储过程使用了异常处理呢？这个规律就不是那么显而易见了。
2 异常处理对事务的影响
异常处理对存储过程的事务管理有什么影响？我们看下面的例子，并先来猜一猜。
例4
  1. --首先创建表
  2. CREATE TABLE T1(C INT ,D INT NOT NULL);
  3. --其次创建过程，其中设置了2个保存点，并包含1个异常处理器--异常处理方式很简单，直接将事务回滚到保存点PT1
  4. --异常处理完成后，显式执行一个commit命令。
  5. create or replace procedure pt1 is begin
  6.   insert into t1 values(1,1);
  7.   SAVEPOINT PT1;
  8.   INSERT INTO T1 VALUES(2,2);
  9.   SAVEPOINT PT2;
  10.   EXCEPTION
  11.    WHEN OTHERS THEN
  12.     ROLLBACK TO SAVEPOINT PT1;
  13.   
  14.   COMMIT;
  15. end;
  16. CALL PT1();
  17. SELECT * FROM T1; --猜一猜1：请猜猜这里的执行结果将看到什么？
  18. ROLLBACK;
  19. SELECT * FROM T1;-- 猜一猜2：请再猜猜这里又将看到什么结果？
复制代码

题目出来了，请分析一下猜一猜1和猜一猜2的结果吧。
猜一猜1：这一题应该是比较简单的，我们将看到查询结果包含2条记录，正是存储过程中的2个insert语句插入的结果。
猜一猜2：由于存储过程内显式的执行了commit。因此即使再外边再执行rollback， insert 的记录也不会被回滚（类似例3），因此猜一猜2这里仍将看到与猜一猜1一样的结果。
相信不少同学将会得到如上结论。
那么果真如此吗，我们来看看实际结果：
  1. SQL> truncate table t1;
  2. 表被截断。
  3. SQL> create or replace procedure pt1 is begin
  4.   2    insert into t1 values(1,1);
  5.   3    SAVEPOINT PT1;
  6.   4    INSERT INTO T1 VALUES(2,2);
  7.   5    SAVEPOINT PT2;
  8.   6   EXCEPTION
  9.   7     WHEN OTHERS THEN
  10.   8      ROLLBACK TO SAVEPOINT PT1;
  11.   9
  12. 10    COMMIT;
  13. 11  end;
  14. 12  /
  15. 过程已创建。
  16. SQL> call pt1();
  17. 调用完成。
  18. SQL> select * from t1;
  19.          C          D
  20. ---------- ----------
  21.          1          1
  22.          2          2
  23. SQL> rollback;
  24. 回退已完成。
  25. SQL> select * from t1;--我们看到了什么？2条记录被回滚掉了！
  26. 未选定行
复制代码
很奇怪吧，一旦加入了异常处理，过程内部处于异常处理器之后的COMMIT命令就失效了！只有当commit处于异常处理器之前的时候才不失效（这一点同学们可以自己做实验）。
事实上，说这个commit失效也不完全，它仅仅是在不产生异常触发器的情况下才失效，当触发了异常后，这个commit就会被执行到，如下：
  1. SQL> truncate table t1;
  2. 表被截断。
  3. SQL> create or replace procedure pt1 is begin
  4.   2    insert into t1 values(1,1);
  5.   3    SAVEPOINT PT1;
  6.   4    INSERT INTO T1 VALUES(2,null);
  7.   5    SAVEPOINT PT2;
  8.   6   EXCEPTION
  9.   7     WHEN OTHERS THEN
  10.   8      ROLLBACK TO SAVEPOINT PT1;
  11.   9
  12. 10    COMMIT;
  13. 11  end;
  14. 12  /
  15. 过程已创建。
  16. SQL> call pt1();
  17. 调用完成。
  18. SQL> select * from t1;
  19.          C          D
  20. ---------- ----------
  21.          1          1
  22. SQL> rollback;
  23. 回退已完成。
  24. SQL> select * from t1;
  25.          C          D
  26. ---------- ----------
  27.          1          1
复制代码
没想到Oracle的异常处理会对过程内的事务提交产生这种影响？以后写这类存储过程的同学要注意了：
CREATE OR REPLACE PROCEDURE PTST IS
BEGIN
INSERT ……;
PTST2();
PTST3();
EXCEPTION
  WHEN …..THEN….
COMMIT;
END;
为了保证子过程的事务都能够完整提交，所以在主过程里面加了commit，千万要注意commit的位置，不能放在exception之后，数据库的存储过程异常处理可不是java那种try{} catch{} finally{}式的异常处理器哦。
其实从语法上讲，异常处理器应该是一个过程的最后一个组成部分，我们不应该再异常处理器之后再写别的命令。即使写了，Oracle也会报错的，如:
  1. SQL> create or replace procedure pt1 is begin
  2.   2       insert into t1 values(1,1);
  3.   3       SAVEPOINT PT1;
  4.   4       INSERT INTO T1 VALUES(2,2);
  5.   5       SAVEPOINT PT2;
  6.   6      EXCEPTION
  7.   7        WHEN OTHERS THEN
  8.   8         ROLLBACK TO SAVEPOINT PT1;
  9.   9      --commit;
  10. 10      insert into t1 values(3,3)
  11. 11     end;
  12. 12  /
  13. 警告: 创建的过程带有编译错误。
复制代码
但是对于commit命令，看来Oracle是比较宽容的了。

    本站是提供个人知识管理的网络存储空间，所有内容均由用户发布，不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息，谨防诈骗。如发现有害或侵权内容，请点击一键举报。
    转藏 分享 献花（0）

    来自： 雪狼11 > 《ORACLE》

    举报/认领

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多