savepoint [Oracle SQL]

-- DML statements 
savepoint savepoint_name_1;
-- DML statements 
savepoint savepoint_name_2;
-- further DML statements 
savepoint savepoint_name_n;
-- further DML statements
rollback to savepoint savepoint_name_n

A little demonstration

create table savepoint_test (
  n number
);

Inserting some values:

insert into savepoint_test values (1);
insert into savepoint_test values (2);
insert into savepoint_test values (3);

Establishing a savepoint named sp_one:

savepoint sp_one;

Inserting three more rows:

insert into savepoint_test values (10);
insert into savepoint_test values (20);
insert into savepoint_test values (30);

Establishing a 2^nd savepoint_test, this time named sp_two:

savepoint sp_two;

Again inserting some values:

insert into savepoint_test values (100);
insert into savepoint_test values (200);
insert into savepoint_test values (300);

Let's check what we have. Not surprisingly, there are nine rows:

select * from savepoint_test;

Rolling back to savepoint sp_two:

rollback to sp_two;

Checking once more the table's content:

select * from savepoint_test;

All DMLS (data manipulated) after savepoint sp_2 have been rolled back.

Yet again, inserting some values ...

insert into savepoint_test values (111);
insert into savepoint_test values (222);
insert into savepoint_test values (333);

... and rolling back to sp_two;

rollback to sp_two;

select * from savepoint_test;

We're at savepoint sp_2 again, so we have the same result set again.

Rolling back to an even earlier time:

rollback to sp_one;

What do we have in the table now?

select * from savepoint_test;

Of course, we only find the values that have existed before savepoint sp_1:

         N
----------
         1
         2
         3

Links

See also On solving a sudoku with Oracle for a creative use of savepoints.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger