PL/SQL Transactions   «Prev  Next»

Lesson 3COMMIT, ROLLBACK, and SAVEPOINT commands
Objective Incorporate COMMIT, ROLLBACK, and SAVEPOINT commands within PL/SQL.


The three transaction control commands available to you within PL/SQL are:
Command What it does
COMMIT This command saves all database changes permanently to the database. A COMMIT ends the transaction.
ROLLBACK This command removes all database changes that were pending in the transaction, returning the database to it state prior to the beginning of the transaction. A ROLLBACKROLLBACK ends the transaction.
SAVEPOINT This command marks a point somewhere within a transaction. You can roll back to a SAVEPOINT and only the database changes processed after that SAVEPOINT are removed. A SAVEPOINT does not end the transaction.

Look at the following series of images to see how to use these three commands.


1) Commit Commands 1
1) COMMIT, ROLLBACK, and SAVEPOINT code examples

2) Commit Commands 2
2) The window shows the syntax of the SAVEPOINT command. When you execute the command you mark a point in the trasaction.
You can establish any number of SAVEPOINTs within one transaction. You can reset a SAVEPOINT to a different position by repeating the SAVEPOINT command by using the same SAVEPOINT_name.

3) Commit Commands 3
3) Here is an example of a procedure that use the COMMIT and ROLLBACk commadns to control the transaction. Without the COMMIT, the update might not-saved.

4) Commit Commands 4
4) This example builds on the previous window. I have a dded a scond updated command at the beginning of the procedure to update the price stored in the SALE_ITEM table.

The primary use of the SAVEPOINT is for a single transaction that updates more than one table. Using the SAVEPOINT allows you to ROLLBACK parts of the transaction while preserving other parts that succeeded.
The next lesson discusses another issue concerning transaction control: locking.

Commit Rollback- Exercise

Click here to add transaction controls to the package you created in the previous module.
Commit Rollback- Exercise