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 Slide show to see how to use these three commands.

Commit Rollback Savepoint
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