使PL / SQL存储过程在发生异常时回滚所有更改？

Anders 发表于 Dev

不同的

我有一个过程，可以对不同的表进行大量更新。如果该过程中的任何地方发生任何错误，我都希望所有更改都可以回滚。因此，我利用了这种结构：

CREATE PROCEDURE foo (x NUMBER) IS
BEGIN
  -- Do some inserts here.
  INSERT INTO bar VALUES (x);
  -- Sometimes there might be an error.
  IF x = 3 THEN
    RAISE_APPLICATION_ERROR(-20000, 'Wooops...');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    --Rollback all the changes and then raise the error again.
    ROLLBACK;
    RAISE;
END foo;

问题在于，这将回滚自上一次提交以来已完成的所有操作，而不仅仅是该过程所做的更改。例如，这将插入4和5，但是1和2将回滚：

BEGIN
  FOR x IN 1..5 LOOP
    BEGIN
      foo(x);
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;

如何使过程仅回退过程在此调用期间所做的更改？我想我应该以某种方式使用事务，但是我不确定如何设置它。

请注意，我想在该过程的代码中而不是在调用它的代码中对其进行修复。

温弗里德（Wernfried Domscheit）

在Oracle中，您可以使用SAVEPOINTS。就是这样的：

CREATE PROCEDURE foo (x NUMBER) IS
BEGIN
  SAVEPOINT update_bar;

  -- Do some inserts here.
  INSERT INTO bar VALUES (x);
  -- Sometimes there might be an error.
  IF x = 3 THEN
    RAISE_APPLICATION_ERROR(-20000, 'Wooops...');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    -- Rollback everything which was made after `SAVEPOINT update_bar`
    ROLLBACK TO update_bar;
    RAISE;
END foo;