在PL/SQL中运行forall循环时，是否需要事后提交？
PL/SQL forall 循环是否以一定的时间间隔自动提交，还是需要在循环后提交？
Oracle 10g 和 11g
FORALL i IN x.FIRST .. x.LAST
delete from table where 1=1;
我目前有一个 pl/sql 脚本，它执行批量收集，然后运行 3 个不同的 forall 循环来迭代集合。我目前正在每个 forall 循环完成后进行提交，并在脚本中使用提交语句。这个有必要吗？它是否会减慢执行速度，特别是当集合有数百万个条目时？
谢谢
Does a PL/SQL forall loop commit automatically at certain intervals, or do I need to commit after the loop?
Oracle 10g and 11g
FORALL i IN x.FIRST .. x.LAST
delete from table where 1=1;
I've currently got a pl/sql script that does a bulk collect, and then runs 3 different forall loops that iterate over the collection. I am currently committing after each forall loop completes, with a commit statement in the script. Is this needed? Does it slow down execution, especially when the collection has several million entries?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
FORALL 语句是标准 DML：它只是一批单独的语句。因此，您应该遵循标准规则来决定是否需要提交：仅在事务结束时，当您达到一致状态时才提交，而不是以前。
如果您有 3 个 FORALL 语句，则没有理由提交 3 次，除非每个语句单独是一个事务。
无论如何，如果您的作业在第一个 FORALL 语句之后失败，并且您尚未尚未提交，那么重新启动会容易得多。
A FORALL statement is standard DML: it is just a batch of individual statements. You should therefore follow the standard rules for deciding if you need to commit: Only commit at the end of your transaction when you have achieved a consistent state, never before.
There is no reason to commit 3 times if you have 3 FORALL statements except when each statement taken individually is a single transaction.
In any case, if your job fails after the first FORALL statement, it will be a lot easier to restart if you haven't commited yet.
您必须在 FORALL 之后显式提交。毕竟，您正在使用 FORALL 执行高速 DML，并且您（应该）知道，DML 不会自动提交。
此外，即使 FORALL 迭代集合的所有行，它也不是循环，而是语句。它既没有 LOOP 也没有 END LOOP 语句。
You have to explicitly commit after a FORALL. After all, you are performing high speed DML using FORALL, and as you (should) know, DML does not commit automatically.
Also, even hough FORALL iterates through all the rows of a collection, it is not a loop, it is a statement. It has neither a LOOP nor an END LOOP statement.