SAVEPOINT

Example

START TRANSACTION;

INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe');

SAVEPOINT my_savepoint;

INSERT INTO employees (first_name, last_name) VALUES ('Jane', 'Doe');

ROLLBACK TO my_savepoint;

COMMIT;

Output

The output for the above code is not directly visible to the user as it affects the database state and not return a result set.

Explanation

The above SQL transaction begins with START TRANSACTION;. Two INSERT statements are then executed.

SAVEPOINT my_savepoint; creates a savepoint that we can later roll back to.

The second INSERT statement is executed after the savepoint.

ROLLBACK TO my_savepoint; undoes the operations done after the savepoint was created. In this case, Jane Doe would not be inserted into the ‘employees’ table as that operation is rolled back.

The COMMIT; statement makes permanent all changes made to the database, up to the last committed savepoint which is ‘my_savepoint’.

In the end, only John Doe would be inserted into the ‘employees’ table as the insert operation for Jane Doe was rolled back.

Example

BEGIN;
INSERT INTO employees (name, department) VALUES ('John Doe', 'Sales');
SAVEPOINT sp1;
UPDATE employees SET department = 'Marketing' WHERE name = 'John Doe';
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

Output

BEGIN
INSERT 0 1
SAVEPOINT
UPDATE 1
ROLLBACK TO SAVEPOINT
COMMIT

Explanation

In the given example, a new employee is added to the ‘employees’ table with ‘Sales’ as their department. A savepoint (sp1) is then created, followed by an operation to update the department of the newly added employee to ‘Marketing’. However, the changes made after the savepoint are discarded by rolling back to the savepoint sp1, hence the employee’s department remains ‘Sales’. The transaction is then committed, finalizing the actions performed within the transaction.

Example

BEGIN TRANSACTION;

-- Statements here...

SAVEPOINT MySavePoint;

-- More statements here...

-- If there's a need to rollback to savepoint
ROLLBACK TRANSACTION MySavePoint;

COMMIT;

Output

Command(s) completed successfully

Explanation

In the above example, a transaction is initiated first. Throughout the confines of the transaction, various database operations may be executed. If a SAVEPOINT is declared, any operations that follow can be rolled back while preserving the initial transaction before the SAVEPOINT. The ROLLBACK TRANSACTION command is used to revert to the state right after the MySavePoint SAVEPOINT was announced. The entire transaction is committed with the COMMIT command.

Example

BEGIN
   INSERT INTO employees (employee_id, first_name, last_name, email)
   VALUES (206, 'John', 'Doe', 'jd@example.com');

   SAVEPOINT sp1;

   UPDATE employees SET email = 'johndoe@example.com' WHERE employee_id = 206;

   ROLLBACK TO SAVEPOINT sp1;

   SELECT email FROM employees WHERE employee_id = 206;
END;
/

Output

'jd@example.com'

Explanation

In the provided SQL block, a new row is inserted into the ‘employees’ table and then a savepoint named ‘sp1’ is created. Afterwards, the email of the newly inserted employee is updated. However, the transaction is rolled back to the savepoint ‘sp1’, undoing the update operation. Thus the SELECT statement retrieves the original email value “jd@example.com” instead of the updated value “johndoe@example.com”. This is how savepoints can be used to selectively undo transactions in Oracle.

Example

BEGIN;
INSERT INTO Customers(Name, City) VALUES('John Doe', 'New York');
SAVEPOINT SP1;
INSERT INTO Customers(Name, City) VALUES('Jane Doe', 'Los Angeles');
ROLLBACK TO SAVEPOINT SP1;
INSERT INTO Customers(Name, City) VALUES('Jake Brown', 'Chicago');
COMMIT;

Output

SELECT * FROM Customers;

Explanation

In the above code example, a transaction begins and initially, one customer (John Doe) is inserted into the table Customers. A savepoint SP1 is then set. After that, another customer (Jane Doe) is inserted. However, the transaction is rolled back to the savepoint SP1. This removes the insertion of ‘Jane Doe’. Another customer (Jake Brown) is then inserted. After a COMMIT statement, the changes that resulted from the transaction are saved, which include the insertions of ‘John Doe’ and ‘Jake Brown’, but exclude the insertion of ‘Jane Doe’. Thus, when the data in the Customers table is retrieved, only the records of ‘John Doe’ and ‘Jake Brown’ appear.