Oracle Save Point Statement

The Savepoint statement is useful when you want to identify a transaction point to roll back to. This could be a situation where you are updating values and want to confirm aggregate totals of some sort. Here is an example:

UPDATE employees
SET salary = 10000
WHERE last_name = 'Ciarlo';
SAVEPOINT ciarlo_sal;

UPDATE employees
SET salary = 15000
WHERE last_name = 'Doherty';
SAVEPOINT doherty_sal;

SELECT SUM(salary) FROM employees;

ROLLBACK TO SAVEPOINT ciarlo_sal;

UPDATE employees
SET salary = 14000
WHERE last_name = 'Doherty';

COMMIT;

The semantics of the syntax is to specify the name of the savepoint to be created. Savepoint names must be distinct within a given transaction. That is, if you create a second savepoint with the same identifier as an earlier savepoint, then the earlier savepoint is lost. After a savepoint has been created, you can either continue processing, commit your work, roll back the entire transaction, or roll back to the savepoint.