如何将数据插入PL/SQL表类型而不是PL/SQL表中? [英] How to insert data into a PL/SQL table type rather than PL/SQL table?

问题描述

我有一个表TDATAMAP，它具有大约1000万条记录，我想将所有记录提取到PL/SQL表类型变量中，并使其与某些条件匹配，最后将所有必需的记录插入到临时表中.请告诉我是否有可能使用PL/SQL表类型变量和BULK INSERT/COLLECT做到这一点.我还担心代码的性能.

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万条记录加载到内存中-只要有足够的内存来容纳那么多内存即可.通常，BULK COLLECT与LIMIT子句一起使用，一次可处理有限数量的行，例如1000.

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.

从文档:

BULK COLLECT子句可让您从结果中提取整列 设置，或一次性设置整个结果.下面的例子， 从游标中检索列到集合中:

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:

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;

下面的示例使用LIMIT子句.每次迭代 在循环中，FETCH语句按索引方式获取100行(或更少) 表acct_ids.先前的值将被覆盖.

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.

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;

这篇关于如何将数据插入PL/SQL表类型而不是PL/SQL表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！