Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

SAVEPOINT

Purpose

Use the SAVEPOINT statement to identify a point in a transaction to which you can later roll back.

See Also:
  • ROLLBACK for information on rolling back transactions
  • SET TRANSACTION for information on setting characteristics of the current transaction

Prerequisites

None.

Syntax

savepoint::=

Text description of statements_104.gif follows
Text description of savepoint


Semantics

savepoint

Specify the name of the savepoint to be created.

Savepoint names must be distinct within a given transaction. If you create a second savepoint with the same identifier as an earlier savepoint, then the earlier savepoint is erased. 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.

Example

Creating Savepoints: Example

To update Banda's and Greene's salary in the sample table hr.employees, check that the total department salary does not exceed 314,000, then reenter Greene's salary, enter:

UPDATE employees 
    SET salary = 7000 
    WHERE last_name = 'Banda';
SAVEPOINT banda_sal;

UPDATE employees 
    SET salary = 12000 
    WHERE last_name = 'Greene';
SAVEPOINT greene_sal;

SELECT SUM(salary) FROM employees;

ROLLBACK TO SAVEPOINT banda_sal;
 
UPDATE employees 
    SET salary = 11000 
    WHERE last_name = 'Greene';
 
COMMIT;