Definition:
In Oracle PL/SQL, SAVEPOINT is a TCL (Transaction Control Language) statement that creates a break point as a specified location in the current transaction. A transaction can be partially rolled back to any one of the savepoints. If multiple SAVEPOINT locations are set in the transaction, they are identified by their names, which must be unique. The name specification is optional for a single SAVEPOINT in the transaction.
Note that a COMMIT removes all the savepoints which may have been set earlier in the transaction.
Example Syntax:
SAVEPOINT [NAME]
Example Usage:
In the example below, SAVEPOINT A is set at the start of the transaction. A row is inserted into the TEST table. Another SAVEPOINT B is set just after the first insert. Now a second row is inserted into the TEST table. The transaction has to be rolled back to exclude the second insert. A rollback to savepoint B would undo the change done after savepoint B.
SQL> SAVEPOINT A
SQL> INSERT INTO TEST VALUES (1,'Savepoint A');
1 row inserted.
SQL> SAVEPOINT B
SQL> INSERT INTO TEST VALUES (2,'Savepoint B');
1 row inserted.
SQL> ROLLBACK TO B;
Rollback complete.
SQL> SELECT * FROM TEST;
ID MSG
-------- -----------
1 Savepoint A
Related Links:
