SavePoint (BZSZP)

The
savepoint (savepoint) is a flag in the transaction process that is used in conjunction with the rollback command (ROLLBACK), and the main purpose is to allow a user to roll back a section of processing without having to roll back the entire transaction, which is useful in pl/sql development.


in the following example, the savepoint is marked before the INSERT statement, and if the INSERT statement attempts to save the duplicate data to the EMP table, the execution of the predefined Dup_val_on_index exception is triggered, where the rollback The To Do_insert command rolls back the insert operation above without affecting any of the previous operations.





DECLARE


emp_id Emp.empno%type;


BEGIN


UPDATE emp SET ... WHERE empno = emp_id;


DELETE from emp WHERE ...


   ...


savepoint Do_insert;


INSERT into EMP VALUES (emp_id, ...);


EXCEPTION


when Dup_val_on_index THEN


ROLLBACK to Do_insert;


end;





If you define multiple savepoint, and when you specify a rollback to a savepoint, the rollback operation rolls back all operations that follow the savepoint (even if n savepoint is later marked). For example, in a section of processing


You define five savepoint, roll back from the third savepoint, and the next four and fifth marks will be rolled back, and if you use rollback without using rollback to savepoint_name, the entire transaction will be rolled.





If you define a savepoint in a recursive subroutine, if each recursive layer is set savepoint. At this point, you can only roll back to one of the nearest savepoint.




The
savepoint declaration can be defined repeatedly within the same transaction process. Its role is to move the savepoint from the previous position to the current position. Thus, performing a rollback is only rolled back to the nearest savepoint.


Below is an example:





BEGIN


   ...


savepoint My_point;


UPDATE emp SET ... WHERE empno = emp_id;


   ...


savepoint My_point; --Move My_point to current point


INSERT into EMP VALUES (emp_id, ...);


EXCEPTION


when others THEN


ROLLBACK to My_point;


end;





In addition, Oracle does not limit the number of savepoint that can be used in each session.