（转）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
    
22.  
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是比较宽容的了。

来源：http://www.itpub.net/thread-1563051-1-1.html