oracle 每次 EXECUTE IMMEDIATE 后都需要 COMMIT 吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20433082/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is COMMIT required after every EXECUTE IMMEDIATE?
提问by Angelina
I have multiple EXECUTE IMMEDIATE commands within one oracle procedure.
我在一个 oracle 过程中有多个 EXECUTE IMMEDIATE 命令。
EXECUTE IMMEDIATE 'DELETE FROM tbl1';
EXECUTE IMMEDIATE 'INSERT INTO tbl1...';
COMMIT;
EXECUTE IMMEDIATE 'DELETE FROM tbl3';
EXECUTE IMMEDIATE 'INSERT INTO tbl3 ...';
COMMIT;
EXECUTE IMMEDIATE 'DELETE FROM tbl4';
EXECUTE IMMEDIATE 'INSERT INTO tbl4 ...';
COMMIT;
Do I need all of these COMMIT, or just at the end of the procedure?
我是否需要所有这些 COMMIT，或者只是在程序结束时？
回答by David Aldridge
The only times that you're really forced to commit, other thasn at the end of a business transaction, are:
除了在业务交易结束时，您真正被迫提交的唯一时间是：
- When executing DDL: the DDL execution is wrapped in a pair of implicit commits.
- After direct path insert: the table cannot be read until the insert is committed.
- 执行 DDL 时：DDL 执行包含在一对隐式提交中。
- 直接路径插入后：在提交插入之前无法读取表。
As horsey comments, the correct point to commit at is when the business transaction is complete. Otherwise, you need to be writing yourself some code to detect and fix partially completed and commited transactions that have left the database is a logically inconsistent state (eg. An INVOICE record exists without any INVOICE_DETAIL records).
正如horsey 评论的那样，正确的提交点是业务事务完成时。否则，您需要自己编写一些代码来检测和修复已离开数据库的部分完成和提交的事务是逻辑上不一致的状态（例如，存在 INVOICE 记录而没有任何 INVOICE_DETAIL 记录）。
回答by user3752062
Commit is not required after every EXECUTE IMMEDIATE. Certain statements do NOT require a commit; for example, if you truncate a table with TRUNCATE. The truncation is done and there is no need for a commit. no ROLLBACK either. You need to know that COMMIT and ROLLBACK are session attributes. All uncommitted work within the current transaction are committed or rolled back - not just the statement executed by the EXECUTE IMMEDIATE.
每次 EXECUTE IMMEDIATE 后不需要提交。某些语句不需要提交；例如，如果您使用 TRUNCATE 截断表。截断已完成，无需提交。也没有回滚。您需要知道 COMMIT 和 ROLLBACK 是会话属性。当前事务中所有未提交的工作都被提交或回滚——不仅仅是由 EXECUTE IMMEDIATE 执行的语句。
