Data Query/Retrieval Language and Transaction Control Language

We have discussed about DDL and  DML in our earlier posts i.e. Data Definition Language and  Data Manipulation Language now lets check DRL and TCL.


DATA RETRIEVAL LANGUAGE/DATA QUERY LANGUAGE:-

1.3 Data Query Language

DRL/DQL has only one command i.e: SELECT.

Select command is used to retrieve data from table.

Syntax: SELECT * FROM <Table_Name>


Eg: Select * from Student;

TRANSACTION CONTROL LANGUAGE:-

1.4 Transaction Control Language

TCL has following commands in SQL.
1) Commit 2) Rollback 3) Savepoint

1) COMMIT:-

This will be used to save the work. Commit is of two types.
a) Implicit
b) Explicit

a) IMPLICIT

This will be issued by oracle internally in two situations.
1) When any DDL operation is performed.
2) When you are exiting from SQL * PLUS.

b) EXPLICIT

This will be issued by the user.

Syntax: Commit;

* When ever you committed then the transaction was completed.

2) ROLLBACK:-

This will undo the operation.
This will be applied in two methods.
1) Upto previous commit
2) Upto previous rollback

Syntax: rollback;

* While process is going on, if suddenly power goes then oracle will 
rollback the transaction.

3) SAVEPOINT


You can use savepoints to rollback portions of your current set of transactions.

Syntax: Savepoint <savepoint_name>;

Ex: SQL> savepoint s1;
 SQL> insert into student values(1, ‘a’, 100);
 SQL> savepoint s2;
 SQL> insert into student values(2, ‘b’, 200);
 SQL> savepoint s3;
 SQL> insert into student values(3, ‘c’, 300);
 SQL> savepoint s4;
 SQL> insert into student values(4, ‘d’, 400);

 Before rollback

SQL> select * from student;

NO NAME MARKS
 --- ------- ----------
 1 a 100
 2 b 200
 3 c 300
 4 d 400

 SQL> rollback to savepoint s3;
 Or
 SQL> rollback to s3;

 This will rollback last two records.
 SQL> select * from student;

NO NAME MARKS
 --- ------- ----------
 1 a 100
 2 b 200