oracle PLSQL 异常回滚
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8234347/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rollback in PLSQL Exception
提问by Arav
Would like to know whether rollback is required when a SQL exception (exception when others) is detected:
想知道检测到SQL异常（其他人异常）时是否需要回滚：
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;
回答by Justin Cave
First, I'm assuming we can ignore the syntax errors (for example, there is no END LOOP, the dbms_output.put_linecall is missing the first single quote, etc.)
首先，我假设我们可以忽略语法错误（例如，没有END LOOP，dbms_output.put_line调用缺少第一个单引号等）
As to whether it is necessary to roll back changes, it depends.
至于是否需要回滚更改，要看情况。
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 SELECTstatement 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.
通常，您不会在循环中进行临时提交。这通常是一个糟糕的架构，因为它在 I/O 和运行时间方面的成本要高得多。这也使得编写可重新启动的代码变得更加困难。例如，如果您的SELECT语句选择 10 行，您发出（并提交）5 次更新，然后第 6 次更新失败，会发生什么情况？修复异常后能够从第 6 行重新启动的唯一方法是使用单独的表来存储（和更新）代码的进度。它还会为调用此块的任何代码带来问题，然后该块必须处理一半工作已完成（并已提交）而另一半未完成的情况。
In general, you would only put transaction control statements in the outermost blocks of your code. Since a COMMITor a ROLLBACKin 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.
通常，您只会将事务控制语句放在代码的最外层块中。由于过程中的 aCOMMIT或 aROLLBACK提交或回滚会话中完成的任何工作，无论这些工作是否由该过程完成，因此您在添加事务控制语句时要非常谨慎。您通常希望让调用者决定是提交还是回滚。当然，这只是到此为止——最终，您将处于永远不会从其他例程调用的最外层块中，并且您需要有适当的事务控制——但这是非常谨慎的事情关于您是否正在编写可能会重用的代码。
In this case, since you have interim commits, the only effect of your ROLLBACKwould 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.
在这种情况下，由于您有临时提交，您的唯一影响ROLLBACK是如果第一个更新语句失败，则在调用此块之前在会话中完成的工作将被回滚。如果第一个更新语句成功，临时提交将提交那些先前的更改。当人们谈论为什么可重用块中的临时提交和事务控制有问题时，这就是人们担心的那种副作用。
