oracle 批量收集 ...适用于所有用途
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17087020/
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
bulk collect ...for all usage
提问by user125687
I want to understand the usage and need for bulk collect forallstatements.
我想了解批量收集forall语句的用法和需要。
An example mentioned here
这里提到的一个例子
In most examples in different web pages; authors first fetch data from a table by using bulk collect statements. After that, they are inserting it into target table by using the forallstatement.
在不同网页中的大多数示例中；作者首先使用批量收集语句从表中获取数据。之后，他们使用该forall语句将其插入到目标表中。
DECLARE
TYPE prod_tab IS TABLE OF products%ROWTYPE;
products_tab prod_tab := prod_tab();
BEGIN
-- Populate a collection - 100000 rows
SELECT * BULK COLLECT INTO products_tab FROM source_products;
FORALL i in products_tab.first .. products_tab.last
INSERT INTO target_products VALUES products_tab(i);
But I do not understand when to choose this method. I think I can write the code like below:
但我不明白什么时候选择这种方法。我想我可以编写如下代码：
INSERT INTO target_products
SELECT * FROM SOURCE_PRODUCTS;
And I think, insert into select statement can work more performance.
而且我认为，插入到 select 语句中可以提高性能。
So why we need to choose bulk collect forallstatements? For error logging or any other advantages?
那么为什么我们需要选择批量收集forall语句呢？对于错误记录或任何其他优势？
回答by Erkan Haspulat
If you can implement your requirement by an INSERT INTO SELECTclause, you don't even need to use foralland bulk collectstatements. There is a saying, "if you can do it in SQL, do it in SQL".
如果您可以通过INSERT INTO SELECT子句实现您的要求，您甚至不需要使用forallandbulk collect语句。有句话说，“如果你能用 SQL 来做，那就用 SQL 来做”。
But in some situations, you may need to process your data row-by-row, which can force you to code a loop. This is actually terrible, it means that your operations within that loop will be executed as single statements over an over. But if you use forall, PL/SQL engine will run your loop in a set-based fashion, which would give you a real good performance boost.
但在某些情况下，您可能需要逐行处理数据，这可能会迫使您编写循环代码。这实际上很糟糕，这意味着您在该循环中的操作将作为单个语句执行。但是，如果您使用forall，PL/SQL 引擎将以基于集合的方式运行您的循环，这会给您带来真正良好的性能提升。
回答by haki
Well, the answer to your question is "when ever you possibly can !".
好吧，你的问题的答案是“只要你有可能！”。
The problem with your question is that in the scenario you described you don't even need plsql.
您的问题的问题在于，在您描述的场景中，您甚至不需要 plsql。
forallis for situations you need to perform some operations with plsql on the data you retrieved, before you insert it to the target table. in this case you'l have a large amount of data in a plsql collection that you would want to bulk insert into the target table. this is what forallis for and it's much more efficient then insert in a loop.
forall用于需要在将检索到的数据插入到目标表之前使用 plsql 执行某些操作的情况。在这种情况下，您将在 plsql 集合中有大量数据，您希望将这些数据批量插入到目标表中。这就是forallfor 并且它比插入循环更有效率。
回答by Dayakark
Whenever, we are submitting PL/SQL blocks into oracle server always SQL statements are executed. Through SQL engine and also procedural statements are executed. Through Procedural statement executor. This procedural statement executor is available in PL/SQL engine, whenever we are using large amount of loading through SQL, PL/SQL statements always oracle server executes these statements separately through these engines. This type of execution methodology always content switching execution methodology degrades performance of the application. To overcome this problem, oracle introduced “bulk bind” process using collections, i.e. in this method oracle server executes all SQL statements at a time.
每当我们将 PL/SQL 块提交到 oracle 服务器时，总是会执行 SQL 语句。通过 SQL 引擎和过程语句执行。通过程序语句执行器。这个过程语句执行器在PL/SQL引擎中是可用的，每当我们通过SQL进行大量加载时，PL/SQL语句总是oracle服务器通过这些引擎分别执行这些语句。这种类型的执行方法总是内容切换执行方法会降低应用程序的性能。为了克服这个问题，oracle 引入了使用集合的“批量绑定”过程，即在这种方法中oracle 服务器一次执行所有SQL 语句。
- select…into…clause
- cursor fetch statement
- DML returning clauses
- 选择……进入……子句
- 游标获取语句
- DML 返回子句
PL/SQL Bulk Collect And Bulk bind
PL/SQL 批量收集和批量绑定
For more information >>>>>>>http://www.oraappdata.com/2017/02/plsql-bulk-collect-and-bulk-bind.html
更多信息 >>>>>> http://www.oraappdata.com/2017/02/plsql-bulk-collect-and-bulk-bind.html
回答by Rene
Here is a nice article about the use of bulk collect and forall:
这是一篇关于使用批量收集和 forall 的好文章：
http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html
http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html
