DML和异常处理-Oracle [英] DML and Exception Handling - Oracle

问题描述

根据史蒂文·弗伊尔斯坦(Steven Feuerstein)的书

当PL/SQL块中发生异常时，Oracle数据库不会回滚 DML语句在该块中所做的任何更改.您是应用程序逻辑事务的管理者，因此您可以决定应该发生哪种行为.
我尝试一下:

As per Steven Feuerstein book

When an exception occurs in a PL/SQL block, the Oracle database does not roll back any of the changes made by DML statements in that block. You are the manager of the application’s logical transaction, so you decide what kind of behavior should occur.
I give it a try:

CREATE TABLE DML_Exception (exception_name VARCHAR2(20));
INSERT INTO DML_exception  VALUES('CASE_NOT_FOUND');
INSERT INTO DML_exception  VALUES('TOO_MANY_ROWS');

我的桌子上都有两行

Select * from DML_Exception

现在，我从表中删除了两行，并在PL/SQL块中引发了异常.

Now I deleted both rows from the table and raise the exception in PL/SQL block.

BEGIN
  DELETE FROM dml_exception;
  raise value_error;
END;

但是我的表仍然包含这两行.我错过了什么?

But my table still containing the both rows. What I missed?

推荐答案

您错过了本书的其他部分.是的，史蒂文是真的–如果某个块中发生异常，则所有先前的DML效果都会保留在原位.但是，在书中还应该提到的是，任何顶级SQL或PL/SQL语句(即匿名块)的执行都会打开该语句的游标，并且如果在执行游标期间有异常，则所有DML效果均已完成在游标执行期间会回滚.也许一个简单的例子将为您提供线索...

You missed some other parts of the book. Yes, Steven is true – if an exception occurs in a block, all preceding DML effects remain in place. Yet, there should be other mention in the book that any top-level SQL or PL/SQL statement (i.e., anonymous block as well) execution opens a cursor for that statement and if there's an exception during the cursor's execution, all DML effects done during the cursor's execution are rolled back. Perhaps a simple example will give you the clue...

在您的原始示例中，您执行了...

In your original example, you executed ...

BEGIN
    DELETE FROM dml_exception;
    raise value_error;
END;

...作为顶级语句.是的，在该块的末尾，尽管仍在其中，您的delete效果仍然保留.但是，您的代码块引发了一个异常，该异常一直传播到最高级别的游标.因此，为了遵守原子性的原则，Oracle回滚了已打开的所有未决结果.光标.

... as the top-level statement. Yes, at the end of the block, though still within, your delete effects remained in place. Yet, your block raised an exception which got propagated all the way up to the top-level cursor. Thus, in order to adhere to the principles of atomicity, Oracle rolled back all pending effects of the opened cursor.

如果您从另一个顶级PL/SQL块中调用您的PL/SQL块，该顶级PL/SQL块处理并且不重新引发在较低级别PL/SQL块中引发的异常，...

If you call your PL/SQL block from within another top-level PL/SQL block, which handles and does not re-raise the exception raised in the lower-level PL/SQL block, ...

BEGIN
    BEGIN
        DELETE FROM dml_exception;
        raise value_error;
    END;
EXCEPTION
    WHEN others THEN NULL;
END;

...，则您的delete效果应保持不变. (并且由于该块中没有提交，因此您最终正在进行一个事务.)

..., then your delete effects shall remain in place. (And since there's no commit in that block, you end up having a transaction in progress.)

这篇关于DML和异常处理-Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！