TCL Rollback, Commit

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

Related posts:

Leave a Reply