oracle 如何将数据插入 PL/SQL 表类型而不是 PL/SQL 表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9227407/
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
How to insert data into a PL/SQL table type rather than PL/SQL table?
提问by Arcs
I have a table TDATAMAP which has around 10 million records, I want to fetch all the records into a PL/SQL table type variable, match it with some criteria and finally insert all the required records in a staging table. Please tell me if its possible to do it using PL/SQL table typle variable and BULK INSERT/COLLECT . I am also concerned about the performance of the code.
我有一个包含大约 1000 万条记录的 TDATAMAP 表，我想将所有记录提取到一个 PL/SQL 表类型变量中，将它与某些条件匹配，最后将所有必需的记录插入到临时表中。请告诉我是否可以使用 PL/SQL 表类型变量和 BULK INSERT/COLLECT 来做到这一点。我也关心代码的性能。
回答by APC
" I want to fetch all the records into a PL/SQL table type variable, match it with some criteria and finally insert all the required records in a staging table."
“我想将所有记录提取到一个 PL/SQL 表类型变量中，将它与某些条件匹配，最后将所有需要的记录插入到一个临时表中。”
This may well not be the right approach to take. SQL is a set-basedprogramming language. It has features which allow us to identify and work with just the set of records we want.
这很可能不是正确的方法。SQL 是一种基于集合的编程语言。它具有允许我们仅识别和使用我们想要的记录集的功能。
So, instead of a three stage process - extract, filter, insert - celebrate the joy of sets:
所以，不是三个阶段的过程——提取、过滤、插入——庆祝集合的乐趣：
insert into your_staging_table
select whatver
from your_10m_table
where something = your_matching_criteria;
There will be scenarios when this is not good enough. But this is the approach to start with.
当这还不够好时，会有一些场景。但这是开始的方法。
回答by Tony Andrews
You can, but you probably should not, load 10 million records into memory at once - as long as there is sufficient memory to hold that much. Normally BULK COLLECT is used with the LIMIT clause to process a finite number of rows at a time e.g. 1000.
您可以，但您可能不应该一次将 1000 万条记录加载到内存中 - 只要有足够的内存来容纳那么多。通常 BULK COLLECT 与 LIMIT 子句一起使用以一次处理有限数量的行，例如 1000。
From the documentation:
从文档：
The BULK COLLECT clause lets you fetch entire columns from the result set, or the entire result set at once. The following example, retrieves columns from a cursor into a collection:
BULK COLLECT 子句允许您从结果集中获取整个列，或一次获取整个结果集。以下示例将游标中的列检索到集合中：
DECLARE
TYPE NameList IS TABLE OF emp.ename%TYPE;
names NameList;
CURSOR c1 IS SELECT ename FROM emp WHERE job = 'CLERK';
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO names;
...
CLOSE c1;
END;
The following example uses the LIMIT clause. With each iteration of the loop, the FETCH statement fetches 100 rows (or less) into index-by table acct_ids. The previous values are overwritten.
以下示例使用 LIMIT 子句。每次循环迭代时，FETCH 语句都会将 100 行（或更少）提取到索引表 acct_ids 中。之前的值被覆盖。
DECLARE
TYPE NumList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
CURSOR c1 IS SELECT acct_id FROM accounts;
acct_ids NumList;
rows NATURAL := 100; -- set limit
BEGIN
OPEN c1;
LOOP
/* The following statement fetches 100 rows (or less). */
FETCH c1 BULK COLLECT INTO acct_ids LIMIT rows;
EXIT WHEN c1%NOTFOUND;
...
END LOOP;
CLOSE c1;
END;
