oracle BEGIN - 结束 PL/SQL 中的块原子事务

BEGIN - END block atomic transactions in PL/SQL

提问by Jan Kratochvil

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

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

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

当我BEGIN - END在 PL/SQL 中有一个块时，它是否表现为原子事务，它将尝试提交命中该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"?

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

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

编辑：我从存储过程运行，我认为我正在使用隐式块。

回答by Jeffrey Kemp

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

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

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).

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

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 blockwill not be rolled back.

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

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

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

---

---

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

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

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.

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

回答by Wolf

BEGIN-ENDblocks are the building blocks of PL/SQL, and each PL/SQL unit is contained within at least one such block. Nesting BEGIN-ENDblocks within PL/SQL blocks is usually done to trap certain exceptions and handle that special exception and then raise unrelated exceptions. Nevertheless, in PL/SQL you (the client) must always issue a commit or rollback for the transaction.

BEGIN-END块是 PL/SQL 的构建块，每个 PL/SQL 单元至少包含在一个这样的块中。嵌套BEGIN- ENDPL/SQL 块中的块通常用于捕获某些异常并处理该特殊异常，然后引发不相关的异常。然而，在 PL/SQL 中，您（客户端）必须始终为事务发出提交或回滚。

If you wish to have atomic transactions within a PL/SQL containing transaction, you need to declare a PRAGMA AUTONOMOUS_TRANSACTIONin the declaration block. This will ensure that any DML within that block can be committed or rolledback independently of the containing transaction.

如果您希望在包含事务的 PL/SQL 中拥有原子事务，则需要PRAGMA AUTONOMOUS_TRANSACTION在声明块中声明 a 。这将确保该块中的任何 DML 都可以独立于包含的事务提交或回滚。

However, you cannot declare this pragma for nested blocks. You can only declare this for:

但是，您不能为嵌套块声明此 pragma。您只能为以下对象声明：

• Top-level (not nested) anonymous PL/SQL blocks
• List item
• Local, standalone, and packaged functions and procedures
• Methods of a SQL object type
• Database triggers

• 顶级（非嵌套）匿名 PL/SQL 块
• 项目清单
• 本地、独立和打包的函数和过程
• SQL 对象类型的方法
• 数据库触发器

Reference: Oracle

参考：甲骨文

回答by OraNob

You don't mention if this is an anonymous PL/SQL block or a declarative one ie. Package, Procedure or Function. However, in PL/SQL a COMMIT must be explicitly made to save your transaction(s) to the database. The COMMIT actually saves all unsaved transactions to the database from your current user's session.

您没有提到这是匿名 PL/SQL 块还是声明性块，即。包、过程或函数。但是，在 PL/SQL 中，必须显式执行 COMMIT 以将您的事务保存到数据库。COMMIT 实际上将所有未保存的事务从您当前用户的会话保存到数据库中。

If an error occurs the transaction implicitly does a ROLLBACK.

如果发生错误，事务会隐式执行 ROLLBACK。

This is the default behaviour for PL/SQL.

这是 PL/SQL 的默认行为。