Oracle

郝8332

　　 Oracle存储过程中的事务是如何处理的？通常情况下。整个过程是作为一个事务整体被提交或回滚的，这属于数据库的基本知识，这里简单说明：
  1通常情况
通常情况下，oralce将整个存储过程作为一个事务整体，整个过程内的事务，要么都提交，要么都回滚。
如下例所示：
例1

• SQL*Plus: Release 11.2.0.1.0 Production on 星期三 1月 4 13:24:22 2012
  
• Copyright (c) 1982, 2010, Oracle.  All rights reserved.
  
• 请输入用户名:  SYSTEM
  
• 输入口令:
  
• 连接到:
  
• Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
  
• With the Partitioning, OLAP, Data Mining and Real Application Testing options
  
• SQL> CREATE TABLE T1 (C INT,D INT NOT NULL);
  
• 表已创建。
  
• SQL> create or replace procedure pt1 is begin
  
•   2    insert into t1 values(1,1);
  
•   3    INSERT INTO T1 VALUES(2,NULL);
  
•   4    end;
  
•   5  /
  
• 过程已创建。
  
• SQL> CALL PT1();
  
• CALL PT1()
  
•      *
  
• 第 1 行出现错误:
  
• ORA-01400: 无法将 NULL 插入 ("SYSTEM"."T1"."D")
  
• ORA-06512: 在 "SYSTEM.PT1", line 3
  
•  
  
• SQL> SELECT * FROM T1;
  
• 未选定行
  

　　在这个例子中，我们构造了D列不允许为空的表，并故意在过程中执行一个违反约束的插入语句，造成过程调用的回滚操作，最终通过查询表T1，我们发现执行过程的时候，整个过程内的2条INSERT语句都被回滚了。这证明了在通常情况下，存储过程是作为一个事务整体的。
另一方面需要说明的是，在非自动提交的情况下，存储过程除非内部显式的执行了commit操作，否则即便整个过程执行成功，当前事务也是未提交的，这是存储过程的另一个特点：存储过程并不会自动提交。 例子如下：
例2

• SQL> create or replace procedure pt1 is begin
  
•   2    insert into t1 values(1,1);
  
•   3   -- INSERT INTO T1 VALUES(2,NULL);
  
•   4    end;
  
•   5  /
  
• 过程已创建。
  
• SQL> select * from t1;
  
• 未选定行
  
• SQL> call pt1();
  
• 调用完成。
  
• SQL> select * from t1;
  
•          C          D
  
• ---------- ----------
  
•          1          1
  
• SQL> commit; --在这里显式提交
  
• 提交完成。
  
• SQL> select * from t1; --提交后能够看到过程插入的记录
  
•          C          D
  
• ---------- ----------
  
•          1          1
  
• SQL> call pt1();
  
• 调用完成。
  
• SQL> rollback; --在这里显式回滚
  
• 回退已完成。
  
• SQL> select * from t1; --发现回滚导致第二次执行存储过程的操作未生效
  
•          C          D
  
• ---------- ----------
  
•          1          1
  
• SQL>
  

　　但是如果过程内部使用了commit命令或rollback命令，则存储过程就能够自行控制整个事务的结束时机，而不受整个会话的影响 。例如：
例3

• SQL> drop table t1;
  
• 表已删除。
  
• SQL> create table t1(c int,d int);
  
• 表已创建。
  
• SQL> create or replace procedure pt1 is begin
  
•   2    insert into t1 values(1,1);
  
•   3      savepoint  pt1;
  
•   4    insert into t1 values(2,2);
  
•   5      savepoint  pt2;
  
•   6    commit;
  
•   7    end;
  
•   8  /
  
• 过程已创建。
  
• SQL> truncate table t1;
  
• 表已截断。
  
• SQL> call pt1();
  
• 调用完成。
  
• SQL> select * from t1;
  
•          C          D
  
• ---------- ----------
  
•          1          1
  
•          2          2
  
• SQL> rollback;-- 此时即使在过程外执行ROLLBACK，由于过程内已经COMMIT，因此这个ROLLBACK的作用范围将不会影响到过程内部。
  
• 回退已完成。
  
• SQL> select * from t1;
  
•          C          D
  
• ---------- ----------
  
•          1          1
  
•          2          2
  

　　所以总结一下，我们可以在存储过程内执行COMMIT或ROLLBACK，使过程能够自行结束事务。但如果存储过程使用了异常处理呢？这个规律就不是那么显而易见了。
　　
2 异常处理对事务的影响
异常处理对存储过程的事务管理有什么影响？我们看下面的例子，并先来猜一猜。
例4

• --首先创建表
  
• CREATE TABLE T1(C INT ,D INT NOT NULL);
  
• --其次创建过程，其中设置了2个保存点，并包含1个异常处理器--异常处理方式很简单，直接将事务回滚到保存点PT1
  
• --异常处理完成后，显式执行一个commit命令。
  
• create or replace procedure pt1 is begin
  
•   insert into t1 values(1,1);
  
•   SAVEPOINT PT1;
  
•   INSERT INTO T1 VALUES(2,2);
  
•   SAVEPOINT PT2;
  
•   EXCEPTION
  
•    WHEN OTHERS THEN
  
•     ROLLBACK TO SAVEPOINT PT1;
  
•   
  
•   COMMIT;
  
• end;
  
• CALL PT1();
  
• SELECT * FROM T1; --猜一猜1：请猜猜这里的执行结果将看到什么？
  
• ROLLBACK;
  
• SELECT * FROM T1;-- 猜一猜2：请再猜猜这里又将看到什么结果？
  

　　
题目出来了，请分析一下猜一猜1和猜一猜2的结果吧。
猜一猜1：这一题应该是比较简单的，我们将看到查询结果包含2条记录，正是存储过程中的2个insert语句插入的结果。
猜一猜2：由于存储过程内显式的执行了commit。因此即使再外边再执行rollback， insert 的记录也不会被回滚（类似例3），因此猜一猜2这里仍将看到与猜一猜1一样的结果。
相信不少同学将会得到如上结论。
那么果真如此吗，我们来看看实际结果：

• SQL> truncate table t1;
  
• 表被截断。
  
• SQL> create or replace procedure pt1 is begin
  
•   2    insert into t1 values(1,1);
  
•   3    SAVEPOINT PT1;
  
•   4    INSERT INTO T1 VALUES(2,2);
  
•   5    SAVEPOINT PT2;
  
•   6   EXCEPTION
  
•   7     WHEN OTHERS THEN
  
•   8      ROLLBACK TO SAVEPOINT PT1;
  
•   9
  
• 10    COMMIT;
  
• 11  end;
  
• 12  /
  
• 过程已创建。
  
• SQL> call pt1();
  
• 调用完成。
  
• SQL> select * from t1;
  
•          C          D
  
• ---------- ----------
  
•          1          1
  
•          2          2
  
• SQL> rollback;
  
• 回退已完成。
  
• SQL> select * from t1;--我们看到了什么？2条记录被回滚掉了！
  
• 未选定行
  

　　很奇怪吧，一旦加入了异常处理，过程内部处于异常处理器之后的COMMIT命令就失效了！只有当commit处于异常处理器之前的时候才不失效（这一点同学们可以自己做实验）。
事实上，说这个commit失效也不完全，它仅仅是在不产生异常触发器的情况下才失效，当触发了异常后，这个commit就会被执行到 ，如下：

• SQL> truncate table t1;
  
• 表被截断。
  
• SQL> create or replace procedure pt1 is begin
  
•   2    insert into t1 values(1,1);
  
•   3    SAVEPOINT PT1;
  
•   4    INSERT INTO T1 VALUES(2,null);
  
•   5    SAVEPOINT PT2;
  
•   6   EXCEPTION
  
•   7     WHEN OTHERS THEN
  
•   8      ROLLBACK TO SAVEPOINT PT1;
  
•   9
  
• 10    COMMIT;
  
• 11  end;
  
• 12  /
  
• 过程已创建。
  
• SQL> call pt1();
  
• 调用完成。
  
• SQL> select * from t1;
  
•          C          D
  
• ---------- ----------
  
•          1          1
  
• SQL> rollback;
  
• 回退已完成。
  
• SQL> select * from t1;
  
•          C          D
  
• ---------- ----------
  
•          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也会报错的，如:

• SQL> create or replace procedure pt1 is begin
  
•   2       insert into t1 values(1,1);
  
•   3       SAVEPOINT PT1;
  
•   4       INSERT INTO T1 VALUES(2,2);
  
•   5       SAVEPOINT PT2;
  
•   6      EXCEPTION
  
•   7        WHEN OTHERS THEN
  
•   8         ROLLBACK TO SAVEPOINT PT1;
  
•   9      --commit;
  
• 10      insert into t1 values(3,3)
  
• 11     end;
  
• 12  /
  
• 警告: 创建的过程带有编译错误。
  

　　但是对于commit命令，看来Oracle是比较宽容的了。
　　来源：http://www.itpub.net/thread-1563051-1-1.html
　　
　　pl/sql异常学习笔记
　　http://www.blogjava.net/pdw2009/archive/2006/09/19/70595.html