oracle 在oracle中执行存储过程后是否会发生任何自动提交？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34182975/
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 there any auto commit happens after executing stored procedures in oracle?
提问by Kalyan
I have 3 tables in oracle DB. I am writing one procedure to delete some rows in all the 3 tables based on some conditions.
我在 oracle DB 中有 3 个表。我正在编写一个程序来根据某些条件删除所有 3 个表中的某些行。
I have used all three delete statements one by one in the procedure. While executing the mentioned stored procedure, is there any auto-commit happening in the at the time of execution?
我已经在程序中一一使用了所有三个删除语句。在执行上述存储过程时，执行时是否发生了自动提交？
Otherwise, Should I need to manually code the commit at the end?
否则，我是否需要在最后手动编码提交？
回答by Kris Johnston
There is no auto-commit on the database level, but the API that you use could potentially have auto-commit functionality. From Tom Kyte.
数据库级别没有自动提交，但您使用的 API 可能具有自动提交功能。 来自汤姆·凯特。
That said, I would like to add:
也就是说，我想补充一点：
Unless you are doing an autonomous transaction, you should stay away from committing directly in the procedure: From Tom Kyte.
除非您正在进行自主事务，否则您应该避免在过程中直接提交：来自 Tom Kyte。
Excerpt:
摘抄：
I wish PLSQL didn't support commit/rollback. I firmly believe transaction control MUST be done at the topmost, invoker level. That is the only way you can take these N stored procedures and tie them together in a transaction.
我希望 PLSQL 不支持提交/回滚。我坚信事务控制必须在最顶层的调用者级别完成。这是您可以使用这 N 个存储过程并将它们绑定到一个事务中的唯一方法。
In addition, it should also be noted that for DDL (doesn't sound like you are doing any DDL in your procedure, based on your question, but just listing this as a potential gotcha), Oracle adds an implicit commit before and after the DDL.
此外，还应该注意的是，对于 DDL（根据您的问题，您听起来不像在您的程序中执行任何 DDL，但只是将其列为潜在的问题），Oracle 在之前和之后添加了一个隐式提交DDL。
回答by are
There's no autocommit, but it's possible to set commit command into stored procedure.
没有autocommit，但是可以将提交命令设置到存储过程中。
Example #1: no commit
示例#1： no commit
create procedure my_proc as
begin
insert into t1(col1) values(1);
end;
when you execute the procedure you need call commit
当你执行你需要调用的程序时 commit
begin
my_proc;
commit;
end;
Example #2: commit
示例#2： commit
create procedure my_proc as
begin
insert into t1(col1) values(1);
commit;
end;
When you execute the procedure you don't nee call commitbecause procedure does this
当您执行该过程时，您不需要调用，commit因为过程会执行此操作
begin
my_proc;
end;
回答by Durga Viswanath Gadiraju
There is no autocommit with in the scope of stored procedure. However if you are using SQL Plus or SQL Developer, depending on the settings autocommit is possible.
在存储过程的范围内没有自动提交。但是，如果您使用 SQL Plus 或 SQL Developer，则根据设置自动提交是可能的。
You should handle commit and rollback as part of the stored procedure code.
您应该将提交和回滚作为存储过程代码的一部分进行处理。
