SavePoint (BZSZP)

Source: Internet
Author: User
Tags define insert rollback savepoint


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.











Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.