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