TCL(Transaction Control Language):

 TCL(Transaction Control Language):

***********************************

-->TCL commands will be dealing with transactions only.within the transaction all the TCL commands are valid.

-->Transaction is nothing but a set of DML operations with COMMIT or ROLLBACK that means a group of DML operations with a COMMIT or a group of DML operations with a ROLLBACK.

-->With in the database transaction will start with DML write operations.So DML read operations can be a part of the transaction but DML read operaions is not the starting point of the transaction.

SELECT  -->READ

INSERT  -->WRITE

UPDATE  -->WRITE

DELETE  -->WRITE

MERGE   -->WRITE

-->SELECT IS READ OPERATION.SELECT CAN BE A PART OF THE TRANSACTION BUT SELECT IS NOT THE STARTING POINT OF THE TRANSACTION.IT CAN BE WITHIN THE TRANSACTION.

-->TRANSACTON CAN START WITH EITHER INSERT OR UPDATE OR DELETE OR MERGE.

-->ENDING POINT OF THE TRANSACTION IS EITHER COMMIT OR ROLLBACK.

-->It is used to save the transactions on table.

-->Transaction is nothing but a logical unit of work(insert,update,delete).

COMMIT

ROLLBACK

SAVE POINT

COMMIT:

*******

-->It is used to save the data permanently in database.

Two types of commits.

Implict Commit:

****************

It is applied by the system

Explicit Commit:

*****************

-->It is applied by the user.

EX:

***

CREATE TABLE STD20

(

SNO NUMBER(5),

SNAME VARCHAR2(20)

);

--IMPLICIT COMMIT (DDL COMMANDS HAVE A AUTO COMMIT).

/

INSERT INTO STD20(100,'MOHAN');

COMMIT;

INSERT INTO STD20(101,'MADAN');

INSERT INTO STD20(102,'VINOD');

COMMIT;

SELECT * FROM STD20;

INSERT INTO STD20(103,'AMAR');

INSERT INTO STD20(104,'AJAY');

INSERT INTO STD20(105,'RAJA');

CREATE TABLE STD30

(

SNO NUMBER(5),

SNAME VARCHAR2(20)

);

SELECT * FROM STD20;

--EXPLICIT COMMIT(DML COMMANDS DOES NOT HAVE A AUTO COMMIT)

/

ROLLBACK:

********

-->It is used to cancel the prevous transactions.

-->Rollback is applicable for DML statements only.

-->ROLLBACK is just opposite to COMMIT.

-->ROLLBACK will undo the DML operation that we perform on database which s not commited yet.

EX:

***

CREATE TABLE STD40

(

SNO NUMBER(5),

SNAME VARCHAR2(20)

);

/

INSERT INTO STD40 VALUES (10,'MOHAN');

INSERT INTO STD40 VALUES (20,'MADAN');

COMMIT;

SELECT * FROM STD40;

DELETE FROM STD40;

ROLLBACK;

SELECT * FROM STD40;

/

SAVEPOINT:

***********

-->It is used to divide the large transaction into small transaction.

-->A temporary saving point with in the transaction.

-->Savepoints store within the buffer only(not in database).

-->It is used to create a marked in the current transaction.

-->It is used to save the current point.

-->Using savepoint the transaction can be discarded up to the marked by using the rollback statemnent.

-->If a second savepoint is created with the same name as an earlier savepoint,the earlier savepoint is deleted.

SYNTAX:

*******

SAVEPOINT SAVEPOINTNAME;

/

EX:

***

CREATE TABLE EMP2 AS SELECT * FROM EMP WHERE 1=2;

/

SAVEPOINT S1;

/

INSERT INTO EMP2 VALUES(100,'SMITH','CLERK',7902,'10-JUN-1980',800,NULL,10);

/

SAVEPOINT S2;

/

INSERT INTO EMP2 VALUES(200,'JHON','SALEMAN',7900,'10-DEC-1981',1800,200,20);

/

SAVEPOINT S3;

/

INSERT INTO EMP2 VALUES(300,'MILLER','ANALYST',7946,'10-OCT-1982',3400,0,30);

/

SAVEPOINT S4;

/

INSERT INTO EMP2 VALUES(400,'JONES','PRESIDENT',7900,'04-AUG-1980',2200,NULL,10);

/

SELECT * FROM EMP2;

/

ROLLBACK TO S3;

--This will rollback last two records.i.e will rollback all the operations occured after savepoint s3.

/

EX:

****

SAVEPOINT S1;

DELETE FROM EMP1 WHERE DEPTNO=10;

SAVEPOINT S2;

DELETE FROM EMP1 WHERE DEPTNO=20;

SAVEPOINT S3;

DELETE FROM EMP1 WHERE DEPTNO=30;

ROLLBACK TO S2;

/

create table e1(ename varchar2(20));

savepoint s1;

insert into e1 values('aaa');

insert into e1 values('bbb');

insert into e1 values('ccc');

savepoint s2;

insert into e1 values('ddd');

insert into e1 values('eee');

insert into e1 values('fff');

savepoint s3;

insert into e1 values('ggg');

insert into e1 values('hhh');

insert into e1 values('iii');

select * from e1;  -- 9 rows

rollback to s3;

select * from  e1;  -- 6 rows

rollback to s2;

select * from e1;  -- 3 rows

commit;

select * from e1; -- 3 rows

/

CREATE TABLE EMP2 AS SELECT * FROM EMP WHERE 1=2;

/

INSERT INTO EMP2 VALUES(100,'SMITH','CLERK',7902,'10-JUN-1980',800,NULL,10);

/

SAVEPOINT S1;

/

INSERT INTO EMP2 VALUES(200,'JHON','SALEMAN',7900,'10-DEC-1981',1800,200,20);

/

SAVEPOINT S2;

/

INSERT INTO EMP2 VALUES(300,'MILLER','ANALYST',7946,'10-OCT-1982',3400,0,30);

/

CREATE TABLE EMP3 AS SELECT * FROM EMP;

/

SAVEPOINT S3;

/

INSERT INTO EMP2 VALUES(400,'JONES','PRESIDENT',7900,'04-AUG-1980',2200,NULL,10);

/

SELECT * FROM EMP2;

/

ROLLBACK TO S3;

/

DROP TABLE EMP3;

/