Creating a savepoint
The savepoints are used to divide a big transaction into smaller sub-transactions. It gives the developer the ability to do partial rollbacks, saving re-execution time. Multiple savepoints can be created in a transaction and we can roll back to any savepoint without affecting the processing done before the savepoint. In this recipe, we will see how to create a savepoint.
How to do it...
We can use a SAVEPOINT statement to create a savepoint. This statement can be embedded in any host language within a transaction:
SAVEPOINT svpnt_temp
UNIQUE
ON ROLLBACK RETAIN CURSORS
ON ROLLBACK RETAIN LOCKS
How it works...
UNIQUE:If this clause is specified, then we cannot create another savepoint with the same name until the savepoint is still active. We can always create more than one savepoint in multiple transactions. The savepoint level is started when the transaction starts, and finishes when the transaction completes.ON ROLLBACK RETAIN CURSORS:The cursors are unaffected...
