`
uule
  • 浏览: 6359226 次
  • 性别: Icon_minigender_1
  • 来自: 一片神奇的土地
社区版块
存档分类
最新评论

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

 

pl/sql异常学习笔记

http://www.blogjava.net/pdw2009/archive/2006/09/19/70595.html

分享到：
| ContactsContract获取联系人信息
评论
1 楼 yh008 2013-09-10  
mark下
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    Oracle 存储过程异常处理.docx

    Oracle 存储过程异常处理 Oracle 存储过程异常处理是指在 Oracle 数据库中使用存储过程时出现的异常情况的处理方法。异常处理是指在程序执行过程中出现错误或异常时，采取相应的处理措施，以确保程序的可靠性和...

    cx_Oracle-7.3.0.tar.gz

    - `cx_Oracle` 是 Python 开发者用来访问 Oracle 数据库的接口，它允许 Python 程序直接与 Oracle 数据库进行通信，执行 SQL 查询，处理事务等。 - 这个模块支持 Python 2.x 和 3.x 版本，并且兼容 Oracle 的多种...

    cx_Oracle-5.1.2-11g.win32-py2.7.msi

    cx_Oracle还支持更高级的功能，如参数绑定、存储过程调用、事务控制、异常处理等。例如，你可以使用%s占位符来绑定变量，防止SQL注入攻击： ```python query = 'SELECT * FROM table_name WHERE column = %s' ...

    cx_Oracle-5.1.2-11g.win-amd64-py2.7.exe

    例如，它支持绑定变量、游标操作、事务控制、异常处理等特性，极大地简化了开发过程。 在使用cx_Oracle之前，你需要确保已经在系统中安装了Oracle客户端或者Oracle Instant Client，因为cx_Oracle需要这些组件来...

    Java-Oracle存储过程知识

    Java-Oracle存储过程是数据库中一种重要...通过上述知识点的介绍，可以看出Java-Oracle存储过程在处理复杂业务逻辑和数据库操作时的强大优势，尽管存在移植性方面的不足，但合理的应用可以大幅提升程序的性能和安全性。

    cx_Oracle-5.2.1-11g.win-64--py2.7.exe.zip

    7. **异常处理**：cx_Oracle模块提供了一套完整的异常处理机制，如`cx_Oracle.DatabaseError`，`cx_Oracle.IntegrityError`等，可以帮助开发者更好地处理可能出现的问题。 8. **安全考虑**：在使用cx_Oracle时，应...

    oracle-jdbc-12.2.0.1.zip

    `CallableStatement`用于调用存储过程。 5. **事务管理**: JDBC提供`Connection`对象上的`setAutoCommit()`和`commit()`/`rollback()`方法，用于手动控制事务边界。 6. **结果集处理**: `ResultSet`接口用于获取...

    cx_Oracle-5.2.1-11g.win-amd64-py3.5

    6. **异常处理**：cx_Oracle提供了与Oracle相关的异常类，如DatabaseError、OperationalError等，方便捕获和处理数据库操作中的错误。 7. **事务控制**：支持开始、提交、回滚事务，确保数据的一致性和完整性。 8....

    ORACLE存储过程学习源码

    Oracle存储过程是数据库编程的重要组成部分，它允许程序员在数据库中执行复杂的业务逻辑和数据操作。这个"ORACLE存储过程学习源码"集合包含了从基础到高级的30个示例，是学习和掌握Oracle存储过程的理想资源。下面，...

    oracle 存储过程批量提交

    批量提交在Oracle存储过程中是一项非常重要的技术，它可以帮助我们有效地处理大量数据，并且显著提升系统的整体性能。通过合理设计和实现，可以充分发挥其优势，为应用程序带来更好的用户体验。

    cx_Oracle-5.3-11g.x64-py3.6-2.exe.7z

    cx_Oracle库支持各种数据库操作，包括事务控制、参数化查询、游标管理、异常处理等。对于更复杂的操作，如存储过程调用、批量插入、游标的使用，cx_Oracle也提供了相应的接口。 在Python 3.6环境下，cx_Oracle的这...

    cx_Oracle-6.1.tar.gz

    10. **Oracle特性支持**：包括Oracle的LOB（Large Object）操作、ROWID访问、触发器、存储过程调用、PL/SQL包的使用等。 综上所述，cx_Oracle-6.1.tar.gz文件是Python开发者连接Oracle数据库的重要工具，通过这个库...

    oracle存储过程学习经典

    Oracle存储过程是SQL与PL/SQL结合的强大功能，用于封装复杂的数据操作逻辑于数据库内部，从而实现高效的事务处理和数据管理。以下是对Oracle存储过程的一些关键知识点的深入解析： #### Oracle存储过程基本语法 ...

    oracle存储过程函数生成DEMO

    Oracle存储过程和函数是数据库管理中的重要组成部分，它们允许开发者创建复杂的业务逻辑和数据处理流程。在这个"Oracle存储过程函数生成DEMO"中，我们主要关注如何在Oracle数据库环境中设计、编写、测试以及调用存储...

    oracle存储过程实例

    Oracle存储过程是数据库管理系统中的一种重要特性，它允许开发者编写一系列SQL和PL/SQL语句，形成一个可重用的程序单元。存储过程在数据库中预先编译并存储，这样在执行时能提高效率，因为它避免了每次调用时的重新...

    ORACLE-PLSQL及存储过程自学资料

    在本自学资料中，我们将深入探讨PL/SQL的基本概念、结构、流程控制、运算符与表达式、游标、异常处理、存储过程和函数、包以及触发器。 首先，PL/SQL的全称是Procedure Language & Structured Query Language，它是...

    oracle调用存储过程

    调用Oracle存储过程是数据库管理中的常见任务，尤其对于需要执行大量数据处理或事务操作的场景。下面我们将详细探讨如何在Oracle中调用存储过程以及其相关知识点。 1. **创建存储过程**： 在Oracle中，你可以使用`...

    hibernate query调用oracle存储过程

    以下是一个简单的示例，展示了如何调用一个不带参数的Oracle存储过程： ```java Session session = sessionFactory.openSession(); Transaction transaction = session.beginTransaction(); session....

    调用Oracle的存储过程

    通过理解这些内容，你可以学习如何在Java应用中集成Oracle存储过程，从而实现更高效的数据操作和业务逻辑处理。在实际应用中，务必确保代码的健壮性、可读性和可维护性，这对于长期的项目维护至关重要。

Magicbox
Global site tag (gtag.js) - Google Analytics