在程序中将提交放在 DML 之后有什么影响? [英] What is the effect of placing the commit after DML in procedure?

问题描述

我在一个包中创建了一个程序，该程序在表中执行插入/删除操作，并在成功事务后提交.

像这样:

创建或替换包pac是程序 pr_test(emp_id 编号)是开始-- 一些代码如果有那么插入别的删除犯罪;结束 pr_test;结束 pac ;

我应该以 AUTONOMOUS_TRANSACTION 的身份进行此交易吗?在程序中放置这样的提交有什么影响?

解决方案

把这样的commit放在程序里有什么作用?

Oracle 文档 描述了COMMIT 为:

<块引用>

目的

使用 COMMIT 语句来结束您当前的事务并使永久事务中执行的所有更改.交易是一个Oracle 数据库将其视为单个 SQL 语句的序列单元.此语句还会删除事务中的所有保存点并释放事务锁.

如果你有三个 PROCEDURE 并且每个都包含一个 COMMIT 语句，那么你不能运行所有三个然后，如果在后一个中发生异常，ROLLBACK 它们全部，因为前两个的更改已经 COMMITted.

作为一般规则，您应该不要在 PROCEDURE 或 FUNCTION 中使用 COMMIT 但应该保留它由调用者COMMIT事务，以便他们可以将多个操作捆绑在一起.

当然，有些用例您会想要违反此规则，但您应该分别考虑每种情况，并在违反此规则之前花时间充分了解您的业务逻辑，以便您知道什么是 COMMIT在每个实例中都进行了标记.

<块引用>

我应该以AUTONOMOUS_TRANSACTION的身份进行这笔交易吗?

一个用例是记录 - 您可能有一个 PROCEDURE 调用另一个 PROCEDURE 来记录用户的操作，无论初始操作是成功还是失败想要保留操作的日志并确保日志是 COMMITted.在这种情况下，日志记录 PROCEDURE 应该是一个 AUTONOMOUS_TRANSACTION 并包含一个 COMMIT 语句，而调用语句应该(可能)两者都没有.>

因此，如果一个 PROCEDURE 的 COMMIT 始终是必需的，并且与调用者 COMMIT 的其他数据是否无关，则使 PROCEDURE 一个 AUTONOMOUS_TRANSACTION.如果 PROCEDURE 可以捆绑在一起，然后 ROLLBACK 作为一个组，那么您不想让它们成为 AUTONOMOUS_TRANSACTION.

I have created a procedure in a package which is doing insert/delete in the table and after successful transaction, commit is done.

like this:

create or replace package pac is    
procedure pr_test(emp_id number)
is
begin
-- some code
if something then
  insert
else
  delete

commit;
end pr_test;
end pac ;

Should I make this transaction as AUTONOMOUS_TRANSACTION? What is the effect of placing the commit like this in program?

解决方案

What is the effect of placing the commit like this in program?

The Oracle Documentation describes COMMIT as:

Purpose

Use the COMMIT statement to end your current transaction and make permanent all changes performed in the transaction. A transaction is a sequence of SQL statements that Oracle Database treats as a single unit. This statement also erases all savepoints in the transaction and releases transaction locks.

If you have three PROCEDURE and each one contains a COMMIT statement then you cannot run all three then, if an exception occurs in a latter one, ROLLBACK them all as the changes from the first two will already be COMMITted.

As a general rule, you should not use COMMIT in a PROCEDURE or FUNCTION but should leave it up to the caller to COMMIT the transaction so they can bundle multiple actions together.

There are, of course, use cases where you will want to violate this rule but you should consider each case separately and take time to fully understand your business logic before you break this rule so you know what is COMMITted in each instance.

Should I make this transaction as AUTONOMOUS_TRANSACTION?

One use-case is logging - you may have a PROCEDURE which calls another PROCEDURE to log the user's actions and, regardless of whether the initial action succeeds or fails you want to keep a log of the action and ensure that log is COMMITted. In this case, the logging PROCEDURE should be an AUTONOMOUS_TRANSACTION and contain a COMMIT statement and the calling statement should (probably) have neither.

So, if the COMMIT of one PROCEDURE is always required and is independent of whether the caller COMMITs other data then make the PROCEDURE an AUTONOMOUS_TRANSACTION. If the PROCEDUREs can be bundled together and then ROLLBACK as a group then you do not want to make them AUTONOMOUS_TRANSACTIONs.

这篇关于在程序中将提交放在 DML 之后有什么影响?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！