So how is `ROLLBACK` in Oracle exception handler d

问题:

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;

Final table data:
1) Incase of oracle handling exception

mytable 
_______
1

2) Incase of oracle handling exception

mytable 
_______

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

回答1:

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

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.

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.

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.

回答2:

considering any DML statement as an atomic transaction , you are grouping in BEGIN ... END several statements as an atomic transaction and the exception inside will cause the statements in this block to be rolled back. Use SAVEPOINT to better handle rollback scenario.