PLSQL异常中的回滚 [英] Rollback in PLSQL Exception

问题描述

想知道在检测到SQL异常(其他异常)时是否需要回滚:

Would like to know whether rollback is required when a SQL exception (exception when others) is detected:

declare
 cursor c_test is
    select    *
    from  tesing;
    begin
       for rec in c_test loop
       begin
           update test1 set test1.name=rec.name where test1.id=rec.id;
           IF sql%rowcount = 1 THEN
                            commit;
           ELSIF   sql%rowcount =0 THEN
                   dbms_output.put_line('No Rows Updated');
           else
                  dbms_output.put_line('More than 1 row exists');
                  rollback;
           END IF;
       exception when others then
                  dbms_output.put_line(Exception');
                  rollback;
      end;      

end;

推荐答案

首先，我假设我们可以忽略语法错误(例如，没有 END LOOP ，dbms_output.put_line 调用缺少第一个单引号，等等)

First, I'm assuming we can ignore the syntax errors (for example, there is no END LOOP, the dbms_output.put_line call is missing the first single quote, etc.)

关于是否必须回滚更改，取决于情况.

As to whether it is necessary to roll back changes, it depends.

通常，您不会在循环中进行临时提交.通常这是一个糟糕的体系结构，因为这在I/O和经过的时间方面要昂贵得多.这也使得编写可重新启动的代码变得更加困难.例如，如果您的 SELECT 语句选择10行，您发出(并提交)5个更新，而第6次更新失败，会发生什么情况?解决异常后，能够从第6行重新启动的唯一方法是拥有一个单独的表，用于存储(和更新)代码的进度.对于调用此块的任何代码，这也都会产生问题，然后必须处理一半的工作已完成(并提交)而另一半未完成的情况.

In general, you would not have interim commits in a loop. That is generally a poor architecture both because it is much more costly in terms of I/O and elapsed time. It also makes it much harder to write restartable code. What happens, for example, if your SELECT statement selects 10 rows, you issue (and commit) 5 updates, and then the 6th update fails? The only way to be able to restart with the 6th row after you've fixed the exception would be to have a separate table where you stored (and updates) your code's progress. It also creates problems for any code that calls this block which has to then handle the case that half the work was done (and committed) and the other half was not.

通常，您只会将事务控制语句放在代码的最外层块中.由于过程中的 COMMIT 或 ROLLBACK 会提交或回滚会话中完成的所有工作，无论该过程是否由该过程完成，因此您要非常谨慎添加事务控制语句.通常，您希望让调用者确定是提交还是回退.当然，这只是到现在为止-最终，您将处于最外层，永远不会从其他例程中调用它，并且您需要具有适当的事务控制-但这是要非常警惕的关于您是否正在编写可能被重用的代码.

In general, you would only put transaction control statements in the outermost blocks of your code. Since a COMMIT or a ROLLBACK in a procedure commits or rolls back any work done in the session whether or not it was done by the procedure, you want to be very cautious about adding transaction control statements. You generally want to let the caller make the determination about whether to commit or roll back. Of course, that only goes so far-- eventually, you're going to be in the outer-most block that will never be called from some other routine and you need to have appropriate transaction control-- but it's something to be very wary about if you're writing code that might be reused.

在这种情况下，由于您具有临时提交，因此 ROLLBACK 的唯一作用是，如果第一个更新语句失败，则在调用此块之前在会话中完成的工作将回滚.如果第一个更新语句成功，则临时提交将提交以前的那些更改.这是人们在谈论为什么可重用块中的临时提交和事务控制有问题时担心的一种副作用.

In this case, since you have interim commits, the only effect of your ROLLBACK would be that if the first update statement failed, the work that had been done in your session prior to calling this block would be rolled back. The interim commit would commit those previous changes if the first update statement was successful. That's the sort of side effect that people worry about when they talk about why interim commits and transaction control in reusable blocks are problematic.

这篇关于PLSQL异常中的回滚的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！