TCL (Transaction Control Language)

            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 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.



            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.



            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;