将提交置于DML之后的过程中会产生什么影响? [英] What is the effect of placing the commit after DML in procedure?

问题描述

我已经在一个包中创建了一个过程，该过程正在表中进行插入/删除，并且在成功事务处理之后，提交就完成了.

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

像这样:

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 ;

我应该以AUTONOMOUS_TRANSACTION的身份进行交易吗?将这样的提交放入程序有什么作用?

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?

Oracle文档将COMMIT描述为:

目的

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

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.

如果您有三个PROCEDURE，并且每个包含一个COMMIT语句，那么您将无法运行全部三个，然后，如果后一个出现异常，则将它们全部作为ROLLBACK，因为前两个的更改已经存在被COMMIT设置.

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.

作为一般规则，您不要在PROCEDURE或FUNCTION中使用COMMIT，但应将其留给调用方进行COMMIT交易，以便他们可以捆绑多个交易一起行动.

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.

我应该以AUTONOMOUS_TRANSACTION身份进行此交易吗?

Should I make this transaction as AUTONOMOUS_TRANSACTION?

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

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.

因此，如果始终需要一个PROCEDURE的COMMIT，并且与调用方COMMIT的其他数据无关，则将PROCEDURE设为AUTONOMOUS_TRANSACTION.如果PROCEDURE可以捆绑在一起，然后将ROLLBACK捆绑在一起，则您不希望将它们组合为AUTONOMOUS_TRANSACTION.

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屋！