TCL Commands in Oracle

Transaction Control Language(TCL) commands are used to manage transactions in the database. These are used to manage the changes made to the data in a table by DML statements. It also allows statements to be grouped together into logical transactions.

COMMIT command

COMMIT command is used to permanently save any transaction into the database.
When we use any DML command like INSERT, UPDATE or DELETE, the changes made by these commands are not permanent, until the current session is closed, the changes made by these commands can be rolled back.
To avoid that, we use the COMMIT command to mark the changes as permanent.

Syntax
Commit;

SAVEPOINT command

SAVEPOINT command is used to temporarily save a transaction so that you can rollback to that point whenever required.
In short, using this command we can name the different states of our data in any table and then rollback to that state using the ROLLBACK command whenever required.

Syntax
Savepoint savepoint_name;

ROLLBACK command

This command restores the database to last commited state. It is also used with SAVEPOINT command to jump to a savepoint in an ongoing transaction.
If we have used the UPDATE command to make some changes into the database, and realise that those changes were not required, then we can use the ROLLBACK command to rollback those changes, if they were not commited using the COMMIT command.

Syntax
Rollback;
Rollback to savepoint_name;

Let us understand this command with example

Selecting student table



Deleting one row



Selecting student table



Use of commit command



Selecting student table



Use of Rollback command



Selecting student table



NOTE: - Here it is important to note that if we fired COMMIT command then ROLLBACK command will show changes till last COMMIT command. In our example student table has no effect of ROLLBACK command.

Updating one row in student table



Selecting student table



Updating another row in student table



Selecting student table



Use of Rollback command



Selecting student table



NOTE:- ROLLBACK undo all changes till last COMMIT command. In our example two updates command undo and we get student table with previous data.But in situation when we want undo only one update command data?
Then we can use SAVEPOINT command as shown below.

Updating one row in student table



Selecting student table



Use of Savepoint command



Updating another row in student table



Selecting student table



ROLLBACK using SAVEPOINT command



Selecting student table



NOTE:- Here it is noticed that only ONE update command changes undo and we get solution of question raised above.

I hope this article will help you.