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.
