SAVEPOINT

Savepoint is a part of Transaction Control Language (TCL). We use save points to rollback portions of your current set of transactions.

These save points are effectively used when we write huge programming code for better control on the program.

Syntax:

     Savepoint <savepoint_name>;

Ex:

  SQL> savepoint s1;

  SQL> INSERT INTO EMP values (7369,'SMITH','CLERK', 7902,'10-JUN-1980', 800, NULL, 20);

  SQL> savepoint s2;

  SQL> INSERT INTO EMP values (7999,'MARK','MGR', NULL,'14-JUN-1970', 2000, NULL, 30);

  SQL> savepoint s3;

  SQL> INSERT INTO EMP values (7100,'SCOTT','CLERK', 7902,'10-JUN-1980', 900, NULL, 40);

  SQL> savepoint s4;

  SQL> INSERT INTO EMP values (7127,'LUKE','CONS', 7999,'17-JAN-1983', 1500, NULL, 10);

Before rollback

SQL> select * from emp;

EMPNO ENAME   JOB     MGR HIREDATE                SAL     COMM       DEPTNO 

7369  SMITH      CLERK     7902   6/10/1980            800          null                20     

7999  MARK       MGR       nul    l 6/14/1970            2000        null                30     

7100  SCOTT      CLERK     7902   6/10/1980            900          null                40     

7127  LUKE       CONS      7999    1/17/1983            1500        null                10     

         

SQL> rollback to s3;     

   

This will rollback last two records.

SQL> select * from emp;

EMPNO ENAME      JOB       MGR  HIREDATE             SAL     COMM    DEPTNO 

    7369  SMITH      CLERK     7902 6/10/1980            800     null    20     

    7999  MARK       MGR       null 6/14/1970            2000    null    30     

In the same example if we would have ‘COMMIT’ the transaction after the last savepoint (s4) then even if we rollback then there will be no use. ‘Select * from EMP ‘will fetching all inserted records from EMP.