使用 LIMIT 子句批量收集到 PL/SQL 中的嵌套表中

发布于 2024-12-03 10:19:12 字数 509 浏览 0 评论 0原文

正如标题所说，如何使用 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 */

提前致谢。

原文

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?

  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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

最初的梦 2024-12-10 10:19:12

我不确定我是否理解您想要解决的问题。

如果您想将从游标中获取的每一行加载到集合中，则没有必要使用 LIMIT 子句。简单地

OPEN cur;
FETCH cur
 BULK COLLECT INTO cur_data;
<<do something with the data>>
CLOSE cur;

说，如果您想使用 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;

在循环内一次获取 500 行数据，然后在循环外对集合执行某些操作是没有任何意义的。

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

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

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.

回复收藏 0 原文

~没有更多了~