TCL Commands in SQL

TCL stands for Transaction Control Language.Here transaction means a series of group of SQL statements that are logically related on Oracle table data is called as Transaction. It is used to control and manage transactions to maintain the integrity of data within SQL statements. When we changes the table data then to make those changes as permit we use COMMIT statement has to be given in SQL prompt.To do undo or a part of  the entire Transaction then we use ROLLBACK statement.

The fallowing are TCL statements:

1. COMMIT
2.ROLLBACK
3.SAVE POINT

1.COMMIT:
A COMMIT make changes done in transaction permanent. That means it ends the current transactions and makes permanent any changes made by the time transaction.

Syntax:
COMMIT;
Example:
insert into Student(SID,SName,SAdd) values(1001,'abc','bangalore');

COMMIT;

2. ROllBACK:
It restores the state of the database to the last commit point.That means it undoes any changes made during the transaction. 

Syntax:
ROLLBACK;

Example:

Delete from Student;
ROLLBACK;//it undo the changes

3. SAVEPOINT:

Whenever processing a transaction it specify a point in a transaction to which later you can rollback.An active SAVAPOINT is used to rollback partial transaction,as far as specified SAVEPOINT. 

Syntax:
SAVEPOINT  Savepointname;

Example:

insert into Student(SID,SName,SAdd) values(1001,'abc','bangalore');
SAVEPOINT x;

insert into Group(GID,GName,Status) values(101,'B.tech','pass');
SAVEPOINT y;

insert into Dept(DID,DName,location) values(1003,'HR','hyderabad');
SAVEPOINT z;

After inserting data into table,now  if you give ROLLBACK to x;

Then row from Dept table and Group table will be rollbacked.

TRUNCATE statement:
It is a Data Definition Language(DDL) statement rather than DML statement.It can not easily be undone.It is more efficient method of emptying a table by using the TRUNCATE
statement. This statement is use to quickly remove all rows from a table or cluster. We can remove rows from tables by using TRUNCATE statement very fast than with Delete statement for the fallowing Reasons:

1. TRUNCATE statement is a DDL statement and generates no rollback information.
2. DML requires many resources  to delete all rows that are inserted in the table.

Where Truncate can't be applied:

If the table is a parent of a referential Integrity constraint,you can not TRUNCATE the table,firstly you need to disable the constraint before issuing the truncate statement.

Syntax: TRUNCATE table Table_name;

Example: TRUNCATE table Student;