oracle 使用 LIMIT 子句将批量收集到 PL/SQL 中的嵌套表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7328746/
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 collecting with LIMIT clause into nested table in PL/SQL
提问by digdug
As the title says, how to bulk collect into a nested table with LIMIT clause? In the following examples, cur_data is a nested table which gets overwritten on the subsequent BULK COLLECT. I have seen EXTEND used for adding data to a nested table. Is there any way to do something similar with BULIK COLLECT?
正如标题所说，如何使用 LIMIT 子句批量收集到嵌套表中？在以下示例中，cur_data 是一个嵌套表，它会在后续的 BULK COLLECT 中被覆盖。我已经看到 EXTEND 用于向嵌套表添加数据。有没有办法用 BULIK COLLECT 做类似的事情？
OPEN cur;
LOOP
FETCH cur bulk collect INTO cur_data LIMIT 500;
EXIT WHEN cur_data%COUNT=0;
END LOOP;
CLOSE cur;
/*Data gets overwritten with empty cursor--> No data in cur_data here*/
OPEN cur;
FETCH cur bulk collect INTO cur_data;
CLOSE cur;
/*No Problems--> All data fetched into cur_data */
Thanks in advance.
提前致谢。
回答by Justin Cave
I'm not sure that I understand the problem you are trying to solve.
我不确定我是否理解您要解决的问题。
If you want to load every row the is fetched from the cursor into your collection, there is no point to using the LIMIT clause. Simply
如果要将从游标中提取的每一行加载到集合中，则使用 LIMIT 子句没有意义。简单地
OPEN cur;
FETCH cur
BULK COLLECT INTO cur_data;
<<do something with the data>>
CLOSE cur;
If you want to use the LIMIT clause, that implies that you want to process a subset of the data returned from the cursor at a time in order to limit the amount of the server's PGA that is allocated to your collection. Assuming you are doing that
如果您想使用 LIMIT 子句，这意味着您希望一次处理从游标返回的数据的一个子集，以限制分配给您的集合的服务器 PGA 的数量。假设你正在这样做
OPEN cur;
LOOP
FETCH cur
BULK COLLECT INTO cur_data LIMIT 500;
EXIT WHEN cur_data%count = 0;
<<do something with the 500 elements in cur_data>>
END LOOP;
CLOSE cur;
It doesn't make any sense to fetch the data 500 rows at a time inside your loop and then do something with the collection outside of the loop.
在循环内一次获取 500 行数据，然后在循环外对集合执行某些操作是没有任何意义的。
