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

COMMIT, ROLLBACK, and SAVEPOINT Commands

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.

COMMIT, ROLLBACK, SAVEPOINT Code Examples

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