当前位置：文江博客话题详情

在 SQL Plus 中运行查询

发布于 2024-08-25 01:28:50 字数 109 浏览 5 评论 0原文

我必须在 SQLPLUS 中的循环中运行查询。循环计数来自其他一些 SQL 查询。所以我必须声明一个变量来获取 count 的值。现在我想在查询中使用这个变量。我怎样才能做到同样的事情。请推荐我提前致谢

原文

分享到QQ

分享到微博

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

发布评论

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

顾挽 2024-09-01 01:28:50

如果我正确理解了这个问题，您可以使用 SQL*Plus 变量与选定的列相结合来完成此操作：

SQL> undefine loop_ctr
SQL> column loop_ctr new_value loop_ctr noprint
SQL> select 5 AS loop_ctr from dual;


SQL> set serveroutput on
SQL> begin
  2  for i in 1..&&loop_ctr
  3  loop
  4  dbms_output.put_line('i = ' || i);
  5  end loop;
  6  end;
  7  /
old   2: for i in 1..&&loop_ctr
new   2: for i in 1..         5
i = 1
i = 2
i = 3
i = 4
i = 5
SQL>

希望有帮助

If I understand the question correctly, you can use a SQL*Plus variable coupled with a selected column to accomplish this:

SQL> undefine loop_ctr
SQL> column loop_ctr new_value loop_ctr noprint
SQL> select 5 AS loop_ctr from dual;


SQL> set serveroutput on
SQL> begin
  2  for i in 1..&&loop_ctr
  3  loop
  4  dbms_output.put_line('i = ' || i);
  5  end loop;
  6  end;
  7  /
old   2: for i in 1..&&loop_ctr
new   2: for i in 1..         5
i = 1
i = 2
i = 3
i = 4
i = 5
SQL>

Hope that helps

回复收藏 0 原文

一笑百媚生 2024-09-01 01:28:50

BEGIN
   DECLARE
      count_loop           NUMBER DEFAULT 0; -- counter coming from some other SQL query...
      progressive_number   NUMBER DEFAULT 0;
      copy_count_loop      NUMBER DEFAULT 0;
   BEGIN
      -- calculus generating the COUNT_LOOP value > 0.
      copy_count_loop := count_loop;

      FOR progressive_number IN 1 .. count_loop
      LOOP
      -- do your operations using copy_count_loop
      END LOOP;
   END;
END;
/

目前还不清楚您想用 COUNT_LOOP 做什么。
我在进入 FOR 循环之前制作了计数器的副本，因此您可以在 FOR 循环内使用 COPY_COUNT_LOOP，而不会影响渐进数变量和 count_loop 变量。

BEGIN
   DECLARE
      count_loop           NUMBER DEFAULT 0; -- counter coming from some other SQL query...
      progressive_number   NUMBER DEFAULT 0;
      copy_count_loop      NUMBER DEFAULT 0;
   BEGIN
      -- calculus generating the COUNT_LOOP value > 0.
      copy_count_loop := count_loop;

      FOR progressive_number IN 1 .. count_loop
      LOOP
      -- do your operations using copy_count_loop
      END LOOP;
   END;
END;
/

It is not so clear what would you like to do with COUNT_LOOP.
I have made a copy of the counter before entering in the FOR cycle, so you can use COPY_COUNT_LOOP inside the FOR cycle, without affecting neither progressive_number variable, nor count_loop variable.

回复收藏 0 原文

~没有更多了~