使 PL/SQL 存储过程在发生异常时回滚所有更改? [英] Make a PL/SQL stored procedure roll back all changes when an exception occurs?
问题描述
我有一个程序可以对不同的表进行多次更新.如果过程中的任何地方发生任何错误,我希望所有更改都回滚.因此我使用了这种结构:
I have a procedure that makes a number of updates to different tables. I want all of the changes to be rolled back should any error occur anywhere in the procedure. Therefore I utilized this structure:
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:
The problem is that this rolls back everything that has been done since the last commit, not just the changes made by the procedure. For instance, this will insert 4 and 5, but 1 and 2 will be rolled back:
BEGIN
FOR x IN 1..5 LOOP
BEGIN
foo(x);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
我怎样才能让过程只回滚过程在这个调用期间所做的更改?我想我应该以某种方式使用事务,但我不确定如何设置.
How can I make the procedure roll back only the changes the procedure did during this call? I guess I should use transactions somehow, but I am not sure how to set it up.
请注意,我想在该过程的代码中修复此问题,而不是在调用它的代码中.
Please note that I want to fix this in the code for the procedure, and not in the code that calls it.
推荐答案
在 Oracle 中,您可以使用 SAVEPOINTS
.应该是这样的:
In Oracle you can use SAVEPOINTS
. It would be this:
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;
这篇关于使 PL/SQL 存储过程在发生异常时回滚所有更改?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!