Transaction Control Language (TCL)

Transaction Control (TCL) statements are used to manage the changes made by DML statements(INSERT,UPDATE etc).

TCL Statements available in Oracle are:

COMMIT 

Make changes done in  transaction permanent.

ROLLBACK

Rollbacks the state of database to the last commit point.

SAVEPOINT 

Use to specify a point in transaction to which later you can rollback.

COMMIT

To make the changes done in a transaction permanent issue the COMMIT statement.

The syntax of COMMIT Statement is

COMMIT  [WORK]  [COMMENT ‘your comment’];

WORK is optional.

COMMENT is also optional, specify this if you want to identify this transaction in data dictionary DBA_2PC_PENDING.

Example :

insert into emp (empno,ename,sal) values (001,’Dayan’,25000);

commit;

ROLLBACK

To rollback the changes done in a transaction give rollback statement. Rollback restore the state of the database to the last commit point.

Example :

delete from emp_details;

rollback;         

SAVEPOINT

Specify a point in a transaction to which later you can roll back.

Example

insert into emp values (002,’Hirosh’,30000);

savepoint a;

insert into dept values (20,’IT’,’ITSD’);

savepoint b;

insert into salgrade values (‘A’,25000,45000);

Now if you give

rollback to a;

Then  row from salgrade table and dept will be roll backed. Now you can commit the row inserted into emp table or rollback the transaction.

If you give

rollback to b;

Then row inserted into salgrade table will be roll backed. Now you can commit the row inserted into dept table and emp table or rollback to savepoint a or completely roll backed the transaction.

If you give

rollback;

Then the whole transactions is roll backed.

If you give

commit;

Then the whole transaction is committed and all savepoints are removed.