Oracle7 Server SQL Reference

SAVEPOINT

Purpose

To identify a point in a transaction to which you can later roll back.

Prerequisites

None.

Syntax

Keywords and Parameters

savepoint

is the name of the savepoint to be created.

Usage Notes

Savepoints are used with the ROLLBACK command to rollback portions of the current transaction.

Savepoints are useful in interactive programs, because you can create and name intermediate steps of a program. This allows you more control over longer, more complex programs. For example, you can use savepoints throughout a long complex series of updates, so that if you make an error, you need not resubmit every statement.

Savepoints are useful in application programs in a similar way. If a program contains several subprograms, you can create a savepoint before each subprogram begins. If a subprogram fails, it is easy to return the data to its state before the subprogram began and then re-execute the subprogram with revised parameters or perform a recovery action.

Savepoint names must be distinct within a given transaction. If you create a second savepoint with the same identifier as an earlier savepoint, the earlier savepoint is erased. After a savepoint has been created, you can either continue processing, commit your work, rollback the entire transaction, or rollback to the savepoint.

Transaction

A transaction (or a logical unit of work ) is a sequence of SQL statements that Oracle7 treats as a single unit. A transaction begins with the first executable SQL statement after a COMMIT, ROLLBACK or connection to Oracle. A transaction ends with a COMMIT statement, a ROLLBACK statement, or disconnection (intentional or unintentional) from Oracle. Oracle7 issues an implicit COMMIT before and after any Data Definition Language statement.

Example

To update BLAKE's and CLARK's salary, check that the total company salary does not exceed 20,000, then re-enter CLARK's salary, enter:

UPDATE emp

	SET sal = 2000

	WHERE ename = 'BLAKE'

SAVEPOINT blake_sal

UPDATE emp

	SET sal = 1500

	WHERE ename = 'CLARK'

SAVEPOINT clark_sal

SELECT SUM(sal) FROM emp

ROLLBACK TO SAVEPOINT blake_sal

UPDATE emp

	SET sal = 1300

	WHERE ename = 'CLARK'

COMMIT