那么Oracle异常处理程序中的`ROLLBACK`与我的`ROLLBACK`有何不同? [英] So how is `ROLLBACK` in Oracle exception handler different from my `ROLLBACK`?

问题描述

Oracle数据库如何回滚到PL/SQL块的开头，但不回滚到较早的DML指令(我认为所有这些都属于一个事务).因为当我尝试ROLLBACK创建一个异常处理程序时，直到最后一个COMMIT的所有指令都会回滚.

How Oracle Database rollback to the beginning of the PL/SQL block, but not the earlier DML instructions (I think all this come under one single transaction). Because when I try to ROLLBACK creating an exception handler all the instructions until last COMMIT are rolled back.

create table mytable (num int not null primary key);

insert into mytable values(1); // My ROLLBACK, rollbacks to here.

begin       // Oracle exception handler rollbacks to here.
insert into mytable values(3);
  begin
   insert into mytable values(2);
   insert into mytable values(1);
  end;
  /* Incase I  try to ROLLBACK all the updates including the first insert is gone.*/
  --exception when dup_val_on_index then
  --rollback;
end;

最终表数据:
1)如果发生oracle处理异常

Final table data:
1) Incase of oracle handling exception

mytable 
_______
1

2)如果发生oracle处理异常

2) Incase of oracle handling exception

mytable 
_______

那么Oracle异常处理程序中的ROLLBACK与我的ROLLBACK有何不同.

So how is ROLLBACK in Oracle exception handler different from my ROLLBACK.

推荐答案

这是Oracle的工作方式.您的第一个INSERT操作正确完成(即在表中插入了"1").

This is how Oracle works. Your first INSERT was done correctly (i.e. inserted "1" into a table).

然后，您运行一个匿名PL/SQL块，该块先插入"3"，然后插入"2"，由于主键冲突而在尝试插入"1"时失败.

Then you ran an anonymous PL/SQL block which inserts "3", then "2", and fails when trying to insert "1" due to primary key violation.

如果在执行该PL/SQL块的过程中发生了未处理的异常(这就是您所说的"Oracle异常处理程序回滚到此处")，那么Oracle将回滚到PL/SQL块的开始.

If unhandled exception occurs during execution of that PL/SQL block (which is what you say that "Oracle exception handler rollbacks to here"), Oracle rolls back to the beginning of the PL/SQL block.

当您使用EXCEPTION处理程序并发出ROLLBACK时，是您决定如果发生什么事情该怎么办，那就是将所有更改都还原到先前的COMMIT，这是在执行CREATE TABLE语句后隐式完成的COMMIT，因此， INSERT"1"也会回滚.

When you utilize EXCEPTION handler and issue ROLLBACK, then it is YOU who decide what to do if something happens, and that is to revert all changes to previous COMMIT which was an implicitly done COMMIT after executing the CREATE TABLE statement, so following INSERT "1" is also rolled back.

这篇关于那么Oracle异常处理程序中的`ROLLBACK`与我的`ROLLBACK`有何不同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！