TCL means Transaction Control Language. These statements are
used to control the transactions made by the DML statements to the database.
Any change which is made to the database by a DML statement will be called as a
transaction. We need to control these transactions made by DML statements.
Whatever change made to the database by a DML statement will not reside permanently in the database, unless autocommit option enabled in the session. Those changes will reside in temporary memory. So, we need to handle these changes.
We have 3 TCL statements available.
COMMIT:
Commit will be used to make the changes made by DML statements
permanent to the database. After performing DML operations, if we issue commit
then those changes will be made permanent to the database. If Auto commit
option for session is enabled, then this is not required as commit takes place
automatically.
Note:
DDL statements will carry implicit commit. So, if we issue DDL statement
against the database, after doing some DML operations, then due to implicit
commit of DDL, previous DML statements also will be committed as commit of DDL
applies for the session till that point.
COMMIT;
ROLLBACK:
Rollback will be used to discard the changes made by DML
statements to the database. If auto commit option enabled in the session, then
there is no use with Rollback statements as transactions will be committed automatically.
So, if we give Rollback then any
pending changes to the database which are made by DML statements will be
discarded.
ROLLBACK;
SAVEPOINT:
To Rollback the changes to a certain transaction
point, we will use SAVEPOINT. The SAVEPOINT statement names and marks
the current point in the processing of a transaction. With
the ROLLBACK TO statement, save points undo parts of a
transaction instead of the whole transaction.
SAVEPOINT my_savepoint;
ROLLBACK TO my_savepoint;