Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Monday, 28 March 2016

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
Share this article :

0 comments:

Post a Comment