Transaction Control Language

Transaction Control Language is used to control DML statements performed.

1.COMMIT: Used to save changes made by DML statements.
Types of commits: a. Explicit Commit: Given by user, valid for DML. Command used for commit: sql>commit. b. Commit automatically performed by oracle, valid for DDL statements.

Create, Alter, Drop and Truncate not required to save explicitly. Oracle automatically perform implicit commit. By default commit is off.

sql> set autocommit on;

2. ROLLBACK: Used to cancel transactions which are not saved.

Example:
Delete the records from emp table of department number 10 and 30.

sql> delete from emp
where deptno=30;

sql> delete from emp
where deptno=30;

Check the data available in emp table.

sql> select*from emp;

Now rollback the changes by running following command.

sql> rollback;

Check the data available in emp table again.

sql>select*from emp;

3. SAVEPOINT: Savepoints are used to identify a point in the transaction to which we can rollback rather than cancel the complete transaction.

Example:
creating savepoint

sql> savepoint p1;

insert data into emp table

sql> inert into emp values(1000, ‘Pinnapa’, ‘Administrator’, 4000,….)

create another savepoint.

sql>savepoint p2;

delete the data from emp table of emp number is 1000 and create save point p3

sql> delete …. empno=1000;

sql> savepoint p3;

Now rollback the transactions to p2.

sql> rollback to savepoint p2;

Powered by k2schools