BEGIN - 结束 PL/SQL 中的块原子事务 [英] BEGIN - END block atomic transactions in PL/SQL

问题描述

这个信息应该很容易找到，但我没有运气.

This information should be easy to find, but I haven't had any luck.

当我在 PL/SQL 中有一个 BEGIN - END 块时，它是否表现得像一个原子事务，它将尝试提交点击 END 块和如果出现任何问题，是否回滚更改?

When I have a BEGIN - END block in a PL/SQL, does it behave as an atomic transaction, that will try to commit on hitting the END block and if anything goes wrong rolls back the changes?

如果不是，我如何确保 BEGIN - END 块中的代码表现得像一个原子事务，以及该块默认"的表现如何?

If not, how do I make sure that the code inside the BEGIN - END block behaves like an atomic transaction and how does the block behave "by default"?

我从存储过程运行，我认为我使用的是隐式块.

I am running from a stored procedure and I am using an implicit block, I think.

推荐答案

首先，BEGIN..END只是句法元素，与事务无关.

Firstly, BEGIN..END are merely syntactic elements, and have nothing to do with transactions.

其次，在 Oracle 中，所有单独的 DML 语句都是原子的(即它们要么完全成功，要么在第一次失败时回滚任何中间更改)(除非您使用 EXCEPTIONS INTO 选项，我不会在这里讨论).

Secondly, in Oracle all individual DML statements are atomic (i.e. they either succeed in full, or rollback any intermediate changes on the first failure) (unless you use the EXCEPTIONS INTO option, which I won't go into here).

如果您希望将一组语句视为单个原子事务，您可以这样做:

If you wish a group of statements to be treated as a single atomic transaction, you'd do something like this:

BEGIN
  SAVEPOINT start_tran;
  INSERT INTO .... ; -- first DML
  UPDATE .... ; -- second DML
  BEGIN ... END; -- some other work
  UPDATE .... ; -- final DML
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO start_tran;
    RAISE;
END;

这样，任何异常都会导致这个块中的语句被回滚，但是任何在这个块之前运行的语句都不会被回滚.

That way, any exception will cause the statements in this block to be rolled back, but any statements that were run prior to this block will not be rolled back.

请注意，我不包括 COMMIT - 通常我更喜欢调用过程来发出提交.

Note that I don't include a COMMIT - usually I prefer the calling process to issue the commit.

确实，没有异常处理程序的 BEGIN..END 块会自动为您处理:

It is true that a BEGIN..END block with no exception handler will automatically handle this for you:

BEGIN
  INSERT INTO .... ; -- first DML
  UPDATE .... ; -- second DML
  BEGIN ... END; -- some other work
  UPDATE .... ; -- final DML
END;

如果抛出异常，所有的插入和更新都会回滚；但是只要您想添加异常处理程序，它就不会回滚.所以我更喜欢使用保存点的显式方法.

If an exception is raised, all the inserts and updates will be rolled back; but as soon as you want to add an exception handler, it won't rollback. So I prefer the explicit method using savepoints.

这篇关于BEGIN - 结束 PL/SQL 中的块原子事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！