PL/SQL:关于隐式回滚的Oracle文档中的矛盾 [英] PL/SQL: Contradiction in Oracle document on Implicit Rollbacks

问题描述

在文档 Oracle数据库PL/SQL语言参考11g第2版(11.2)，隐式回滚"部分以以下文本开头:

In the document Oracle Database PL/SQL Language Reference 11g Release 2 (11.2), the "Implicit Rollbacks" section begins with this text:

"在运行INSERT，UPDATE，DELETE或MERGE语句之前， 数据库标记一个隐式保存点(您不可用). 如果 语句失败，数据库将回滚到保存点.通常， 只是失败的SQL语句被回滚，而不是整个 交易."

"Before running an INSERT, UPDATE, DELETE, or MERGE statement, the database marks an implicit savepoint (unavailable to you). If the statement fails, the database rolls back to the savepoint. Usually, just the failed SQL statement is rolled back, not the whole transaction."

因此，如果我在PL/SQL程序中运行SQL语句，但该语句失败，则该语句将自动回滚.没关系.

So if I run a SQL sentence into an PL/SQL program, and the sentence fails, then the sentence will be automatically rolled-back. That is ok.

但是同一部分的结尾是以下文本:

But the same section ends with this text:

"如果您退出具有未处理异常的存储子程序，则PL/SQL 不为OUT参数分配值，并且不执行任何操作 回滚."

"If you exit a stored subprogram with an unhandled exception, PL/SQL does not assign values to OUT parameters, and does not do any rollback."

这似乎与第一个文本相反:如果我的程序以未捕获的异常结束，则不会回滚.但是第一篇文章说如果SQL语句失败，回滚将自动完成.

It seems to be contrary to the first text: No rollback is done if my program ends with an untrapped exception. But the first text says that the rollback is done automatically if the SQL sentence fails.

因此，如果我的存储程序包含SQL语句，则该语句失败，未捕获异常，并且程序结束，那么是否应回滚该SQL语句?文件有矛盾吗?

So if my stored program contains a SQL sentence, the sentence fails, the exception is not trapped, and my program ends, then should the SQL sentence be rolled-back or not? Does the document have a contradiction?

堆栈溢出中的相关问题:

Related questions in Stack Overflow:

• Oracle是否在发生错误时回滚事务?
• 有关pl/sql异常的问题

• Does Oracle roll back the transaction on an error?
• question about pl/sql exception

更新(已解决):感谢DrabJay提供的示例，现在更加清楚了:

Update (solved): Thank DrabJay for the example, it is clearer now:

• 一件事就是SQL语句的回滚.
• 另一件事是包含SQL语句的程序的回滚.

总是执行失败的SQL语句的回滚(与是否进入程序无关).程序的回滚取决于调用者:

The rollback of a SQL statement that fails is ALWAYS done (independently of being into a program or not). The rollback of the program depends of the caller:

• 如果将回滚应用于调用者，则将回滚应用于程序.
• 如果没有回滚应用于调用者，则没有回滚应用于程序.

如果程序是一个匿名块(不存在调用者)，则等效于从用户语句中调用它，并且失败的用户语句将自动回滚，因此该匿名块将被回滚.

If the program is an anonymous block (no caller exists), it is equivalent to being called from a user statement, and a user statement that fails is rolled back automatically, so the anonymous block is rolled back.

我认为该文档应该更清晰，尤其是在并且不进行任何回滚"一词上:

I think the document should be clearer, specially on the words "and does not do any rollback":

如果您退出带有未处理异常的存储子程序，则PL/SQL 不会为OUT参数赋值，，并且不执行任何操作 回滚."

"If you exit a stored subprogram with an unhandled exception, PL/SQL does not assign values to OUT parameters, and does not do any rollback."

推荐答案

没有矛盾，但是文档必须准确阅读，例如

There is no contradiction, but the documentation must be read precisely e,.g.

CREATE TABLE t
  (col NUMBER(1) NOT NULL)
/

Table created.

CREATE PROCEDURE insert_t1
AS
BEGIN
  INSERT INTO t
    (col)
  SELECT 1 FROM dual
  UNION ALL
  SELECT 2 FROM dual;
  INSERT INTO t
    (col)
  SELECT 9 FROM dual
  UNION ALL
  SELECT 10 FROM dual;
END;
/

Procedure created.

SELECT col
FROM t
/

no rows selected.

INSERT INTO t
SELECT 9 FROM dual
UNION ALL
SELECT 10 FROM dual
/

INSERT INTO t
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SELECT col
FROM t
/

no rows selected.

这表明，假设尝试按指定的顺序插入两个记录，则DML语句回滚到执行该语句之前建立的隐式保存点，因为数据库中都不存在该记录.如果我们然后继续:

This shows, assuming that the attempt to insert the two records is in the order specified, that a DML statement rolls back to the implicit savepoint established before executing the statement, as neither record exists in the database. If we then continue:

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  CURSOR csr
  IS
    SELECT col
    FROM t
    ORDER BY col;
BEGIN
  BEGIN
    insert_t1;
  EXCEPTION
    WHEN OTHERS THEN
      FOR rec IN csr LOOP
        dbms_output.put_line('COL: ' || rec.col);
      END LOOP;
      RAISE;
  END;
END;
/

COL: 1
COL: 2
DECLARE
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at line 15

这表明，如果您退出带有未处理异常的存储子程序，Oracle不会进行任何回滚，因为第一个insert语句插入的记录仍在表中.但是，如上所述，当直接执行DML时，整个第二条insert语句已回滚到执行第二条语句之前建立的隐式保存点.

This shows that if you exit a stored subprogram with an unhandled exception, Oracle does not do any rollback as the records inserted by the first insert statement are still in the table. However, as above when executing the DML directly, the whole of the second insert statement has been rolled back to the implicit savepoint established before the second statement was executed.

但是，如果我们随后尝试查询该表.

However, if we then attempt to query the table.

SELECT col
FROM t
/

no rows selected.

这表明如果您退出带有未处理异常的匿名块，Oracle会进行回滚.这将再次返回到执行匿名块之前建立的隐式保存点.

This shows that if you exit an anonymous block with an unhandled exception Oracle does do rollback. This will again be to the implicit savepoint established before the anonymous block was executed.

这篇关于PL/SQL:关于隐式回滚的Oracle文档中的矛盾的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！