TCL - Transaction Control Language

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.

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.

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