执行时执行PL SQL自动提交 [英] PL SQL Auto Commit on execution

问题描述

我对PL/SQL编程非常陌生.我试图用代码内的一些DML语句(插入)编写一个pl/sql过程.在pl/sql代码中执行插入操作后，我没有进行任何显式提交.但是在执行pl/sql过程之后，事务将被提交.

I am very new to the PL/SQL programming. I tried to write a pl/sql procedure with some DML Statements(insert) inside the code. I am not doing any explicit commit after performing insert operations in the pl/sql code. But the transaction is getting commited after executing pl/sql procedure.

这是默认行为吗?

我该如何控制?

推荐答案

DML语句(INSERT/DELETE/UPDATE/MERGE)在PL/SQL中不执行自动提交. DDL语句确实会提交(ALTER/CREATE等)，即使发生某些错误也会发生这种情况.如果您正在运行像运行DDL的动态语句之类的EXECUTE IMMEDIATE，这也会提交您的事务.自 2000

DML statements (INSERT/DELETE/UPDATE/MERGE) don't do an auto commit in PL/SQL. DDL statements do commit (ALTER/CREATE etc) and this will happen even if something failed. If you're running EXECUTE IMMEDIATE like dynamic statement that runs a DDL, this will also commit your transaction. And its been like that [and will remain] since 2000

SQL * Plus之类的客户端界面具有自动提交功能，可以关闭/打开该功能，请在客户端文档中查找.

Client interfaces like SQL*Plus have an auto commit feature that can be turned off/on , look for it in the client documentations. Something like

SET AUTOCOMMIT OFF

您可以看到此变量的当前状态

You can see the current status of this variable

SHOW AUTCOMMIT 

，这将告诉您是否打开/关闭.

and that will tell you whether its on/off .

浏览此以获取更多自动提交的变化

Go through this for more variations of autocommit

这篇关于执行时执行PL SQL自动提交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！