SAVEPOINT

Save point: 
 

• Marks the current point in the processing of a transaction.
• A SAVEPOINT is a marker within a transaction that allows for a partial rollback.
• AS changes are made in a transaction, we can create SAVEPOINTS to mark different points with in the transaction.

Syntax:

Savepoint <savepoint name>;

Ex:

SQL>savepoint s1;

Savepoint created.

Note:

We can’t rollback the committed transactions but we can commit the rollback transactions.

consider the emp table having the following records.

SQL>select * from emp;

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	800		20
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7566	JONES	MANAGER	7839	2-Apr-81	2975		20
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7698	BLAKE	MANAGER	7839	1-May-81	2850		30
7782	CLARK	MANAGER	7839	9-Jun-81	2450		10
7788	SCOTT	ANALYST	7566	9-Dec-82	3000		20
7839	KING	PRESIDENT		17-Nov-81	5000		10
7844	TURNER	SALESMAN	7698	8-Sep-81	1500		30
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20
7900	JAMES	CLERK	7698	3-Dec-81	950		30

14 rows selected.

SQL>savepoint sp1;

Savepoint created.

Following is the example which would delete records from the table having salary less then 1500, and commit the changes in the database.

SQL>delete from emp where sal<1500;

2 rows deleted.

As a result, two rows from the table would be deleted and select statement would produce the following result.

SQL>select * from emp;

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	3000		20
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7566	JONES	MANAGER	7839	2-Apr-81	2975		20
7698	BLAKE	MANAGER	7839	1-May-81	2850		30
7782	CLARK	MANAGER	7839	9-Jun-81	2450		10
7788	SCOTT	ANALYST	7566	9-Dec-82	3000		20
7839	KING	PRESIDENT		17-Nov-81	5000		10
7844	TURNER	SALESMAN	7698	8-Sep-81	1500		30
7876	ADAMS	CLERK	7788	12-Jan-83	3000		20
7900	JAMES	CLERK	7698	3-Dec-81	3000		30
7902	FORD	ANALYST	7566	3-Dec-81			20
7934	MILLER	CLERK	7782	23-Jan-82	3000		10

12 rows selected.

SQL>savepoint sp2;

Savepoint created.

SQL>delete from emp where sal<2500;

3 rows deleted.

SQL>select * from emp;

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	3000		20
7566	JONES	MANAGER	7839	2-Apr-81	2975		20
7698	BLAKE	MANAGER	7839	1-May-81	2850		30
7788	SCOTT	ANALYST	7566	9-Dec-82	3000		20
7839	KING	PRESIDENT		17-Nov-81	5000		10
7876	ADAMS	CLERK	7788	12-Jan-83	3000		20
7900	JAMES	CLERK	7698	3-Dec-81	3000		30
7902	FORD	ANALYST	7566	3-Dec-81			20
7934	MILLER	CLERK	7782	23-Jan-82	3000		10

9 rows selected.

SQL>rollback to sp2;

Rollback complete.

SQL>select * from emp;

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	3000		20
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7566	JONES	MANAGER	7839	2-Apr-81	2975		20
7698	BLAKE	MANAGER	7839	1-May-81	2850		30
7782	CLARK	MANAGER	7839	9-Jun-81	2450		10
7788	SCOTT	ANALYST	7566	9-Dec-82	3000		20
7839	KING	PRESIDENT		17-Nov-81	5000		10
7844	TURNER	SALESMAN	7698	8-Sep-81	1500		30
7876	ADAMS	CLERK	7788	12-Jan-83	3000		20
7900	JAMES	CLERK	7698	3-Dec-81	3000		30
7902	FORD	ANALYST	7566	3-Dec-81			20
7934	MILLER	CLERK	7782	23-Jan-82	3000		10

12 rows selected.

SQL>rollback to sp2;

Rollback complete.

SQL>select * from emp;

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	3000		20
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7566	JONES	MANAGER	7839	2-Apr-81	2975		20
7698	BLAKE	MANAGER	7839	1-May-81	2850		30
7782	CLARK	MANAGER	7839	9-Jun-81	2450		10
7788	SCOTT	ANALYST	7566	9-Dec-82	3000		20
7839	KING	PRESIDENT		17-Nov-81	5000		10
7844	TURNER	SALESMAN	7698	8-Sep-81	1500		30
7876	ADAMS	CLERK	7788	12-Jan-83	3000		20
7900	JAMES	CLERK	7698	3-Dec-81	3000		30
7902	FORD	ANALYST	7566	3-Dec-81			20
7934	MILLER	CLERK	7782	23-Jan-82	3000		10

12 rows selected.

SQL>rollback to sp1;

Rollback complete.

SQL>select * from emp;

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	800		20
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7566	JONES	MANAGER	7839	2-Apr-81	2975		20
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7698	BLAKE	MANAGER	7839	1-May-81	2850		30
7782	CLARK	MANAGER	7839	9-Jun-81	2450		10
7788	SCOTT	ANALYST	7566	9-Dec-82	3000		20
7839	KING	PRESIDENT		17-Nov-81	5000		10
7844	TURNER	SALESMAN	7698	8-Sep-81	1500		30
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20
7900	JAMES	CLERK	7698	3-Dec-81	950		30

14 rows selected.