A transaction in PL/SQL is an ATOMIC unit that may consist of one or more related SQL statements.

There is a difference between an executed SQL statement and a committed transaction. On successful execution of SQL statement, if transaction CONTAINING the statement is not committed, then it can be rolled back and the changes made by the statement can be undone. Here, committed means permanent in the database. However, rolled back means the changes can be undone.

Beginning with transactions, let us understand its concepts:

• COMMIT

The COMMIT statement saves all the changes SINCE the last COMMIT or ROLLBACK. An explicit or implicit request is made to commit a transaction.

• ROLLBACK

The Rollback undone all the changes since the last COMMIT or ROLLBACK.

• ROLLBACK TO SAVEPOINT

Undone all the changes made since the specified savepoint was made.

• SAVEPOINT

The SAVEPOINT statement forms a savepoint. This allows in performing partial ROLLBACKs. A transaction begins when the first SQL statement is encountered after connecting to the database.

To commit a transaction in PL/SQL, use the COMMIT command:

Let’s say the following is the INSERT in the table. At the end, execute the COMMIT command if you want to commit the transaction:

To rollback the transactions, use the ROOLBACK command. The changes you made to the database without COMMIT can be undone with ROLLBACK.

The syntax:

Here, name_of_savepoint is the name of the savepoint.

If savepoint is not used, then simply use the “ROLLBACK” statement to rollback all the changes.

Savepoints splits a LONG transaction into smaller UNITS. This is simply done using checkpoints. These checkpoints allow you to roll back to a checkpoint in a long transaction using the following command:

Let us see an example of SAVEPOINT:

Above, we have created a SAVEPOINT s1, which will allow us to rollback.