如何在 Oracle 中使用批量收集而不是循环

发布于 2024-09-07 22:13:50 字数 515 浏览 4 评论 0原文

我的程序中有一个如下所示的示例查询：

result_rec              mypkg.mytype;

OPEN CUR1 FOR
  select col1, col2, col3 from table1 where something = 'a'; --rows will always be 50

      LOOP
         FETCH CUR1
          INTO myrectype;
         EXIT WHEN CUR1%NOTFOUND;
         result_rec.col1 := myrectype.col1;
         result_rec.col2 := myrectype.col2;
         result_rec.col3 := myrectype.col3;
         PIPE ROW (result_rec);
      END LOOP;

如您所见，每次我循环 50 次。有更好的方法吗？像“批量收集”之类的东西？我将如何实施？

原文

I have a sample query like below in my procedure:

result_rec              mypkg.mytype;

OPEN CUR1 FOR
  select col1, col2, col3 from table1 where something = 'a'; --rows will always be 50

      LOOP
         FETCH CUR1
          INTO myrectype;
         EXIT WHEN CUR1%NOTFOUND;
         result_rec.col1 := myrectype.col1;
         result_rec.col2 := myrectype.col2;
         result_rec.col3 := myrectype.col3;
         PIPE ROW (result_rec);
      END LOOP;

As you can see, every time I am looping 50 times. Is there a better way to do this? something like BULK COLLECT INTO? how would I implement that?

分享到QQ

分享到微博

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

发布评论

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

陪你搞怪i 2024-09-14 22:13:50

在Oracle 10g（可能是9i）中，Oracle会自动批量收集隐式游标。所以代码如下：

DECLARE
  result_rec              mypkg.mytype;
BEGIN
  for i in (select col1, co2, col3 from table1 where something = 'a')
  loop
    result_rec.col1 := i.col1;
    result_rec.col2 := i.col2;
    result_rec.col3 := i.col3;
    pipe_row (result_rec);
  end loop;
END;

只会将上下文从 PL/SQL 引擎切换到 SQL 引擎，以便每 100 行获取一次记录。在 SQL 跟踪 (dbms_monitor.session_trace_enable()) 下运行它，然后看看！

In Oracle 10g (possibly 9i), Oracle will automatically bulk collect implicit cursors. So code like:

DECLARE
  result_rec              mypkg.mytype;
BEGIN
  for i in (select col1, co2, col3 from table1 where something = 'a')
  loop
    result_rec.col1 := i.col1;
    result_rec.col2 := i.col2;
    result_rec.col3 := i.col3;
    pipe_row (result_rec);
  end loop;
END;

Will only make the context switch from the PL/SQL engine to the SQL engine for fetching records once every 100 rows. Run it under a SQL trace (dbms_monitor.session_trace_enable()) and see!

回复收藏 0 原文

稀香 2024-09-14 22:13:50

您可以尝试以下操作。

DECLARE
  type tab_result_rec IS TABLE OF mypkg.mytype INDEX BY PLS_INTEGER;
  t_result_rec tab_result_rec;
BEGIN
  select col1, col2, col3 bulk collect into t_result_rec
  from table1 where something = 'a'; --rows will always be 50
  --
  for i in 1..t_result_rec.count LOOP
     PIPE ROW (t_result_rec(i));
  end loop;
END;

You can try the following.

DECLARE
  type tab_result_rec IS TABLE OF mypkg.mytype INDEX BY PLS_INTEGER;
  t_result_rec tab_result_rec;
BEGIN
  select col1, col2, col3 bulk collect into t_result_rec
  from table1 where something = 'a'; --rows will always be 50
  --
  for i in 1..t_result_rec.count LOOP
     PIPE ROW (t_result_rec(i));
  end loop;
END;

回复收藏 0 原文

~没有更多了~