批量收集...适用于所有用途 [英] bulk collect ...for all usage

问题描述

我想了解用法和批量收集forall语句的需要.

I want to understand the usage and need for bulk collect forall statements.

提到的示例此处

在大多数示例中，在不同的网页中；作者首先使用批量收集语句从表中获取数据.之后，他们将使用forall语句将其插入目标表中.

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 forall statement.

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;

我认为，插入select语句可以提高性能.

And I think, insert into select statement can work more performance.

那么为什么我们需要选择批量收集forall语句?记录错误还是有其他优势?

So why we need to choose bulk collect forall statements? For error logging or any other advantages?

推荐答案

如果可以通过INSERT INTO SELECT子句实现要求，则甚至不需要使用forall和bulk collect语句.俗话说:" 如果可以用SQL进行，则可以用SQL 进行."

If you can implement your requirement by an INSERT INTO SELECT clause, you don't even need to use forall and bulk collect statements. There is a saying, "if you can do it in SQL, do it in SQL".

但是在某些情况下，您可能需要逐行处理数据，这可能会迫使您编写循环.这实际上是可怕的，这意味着您在该循环中的操作将作为over语句中的单个语句执行.但是，如果使用forall，PL/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.

这篇关于批量收集...适用于所有用途的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！