1/28/16

USING TCL

USING COMMIT


This will be used to save the work.

Commit is of two types.

Ø  Implicit

Ø  Explicit


a)  IMPLICIT


This will be issued by oracle internally in two situations.

Ø  When any DDL operation is performed.

Ø  When you are exiting from SQL * PLUS.


b)  EXPLICIT


This will be issued by the user.


Syntax:

Commit or commit work;

* When ever you committed then the transaction was completed.


USING ROLLBACK


This will undo the operation.

This will be applied in two methods.

Ø  Upto previous commit

Ø  Upto previous rollback


Syntax:

Roll or roll work; Or
Rollback or rollback work;

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





USING 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

0 comments:

Post a Comment