SQL TCL Operations

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