如何在Oracle过程中使用保存点 [英] How to use savepoints in oracle procedure

问题描述

我有多个更新并在一个过程中插入语句.

I have multiple updates and insert statements in a procedure.

请参考以下示例:

程序示例

-代码

更新1

插入1

更新2

更新3-假设发生异常

现在我想回滚到第一个更新语句之前，这意味着没有更新或插入影响.

Now i want to rollback to before 1st update statement means no update or insert affects.

推荐答案

BEGIN

  Savepoint do_update_1;

  Update 1;

  insert 1;

  Update 2;

  Update 3; --Suppose exception occurs

EXCEPTION
  WHEN some_exception THEN Rollback To do_update_1;
END;

======编辑=========

工作示例: http://sqlfiddle.com/#!4/b94a93/1

====== edit ==========

Working example: http://sqlfiddle.com/#!4/b94a93/1

create table tttt(
  id int,
  val int
)
/

declare 
  x int := 0;
begin
  insert into tttt values( 1,1);
  insert into tttt values( 2,2);
  Savepoint do_update_1;

  insert into tttt values( 3,3);
  update tttt set val = 0 where id = 2;
  update tttt set val = 10 / val where id = 2;

exception
  when zero_divide then rollback to do_update_1;
end;
/

这篇关于如何在Oracle过程中使用保存点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！