如何在 pl/sql 中编写遍历数字的循环

发布于 2024-09-09 17:49:00 字数 280 浏览 2 评论 0 原文

我想编写一个循环，在每次迭代时迭代数字 105 102 19 17 101 16 106 107

我想将数字插入查询并将其插入表中。

伪：

LOOP (105 102 19 17 101 16 106 107)
   FETCH select * from some_table where value=current_iteration_index --105..etc.
   INTO my_rec_type

END LOOP;

原文

I want to write a loop that iterates over numbers 105 102 19 17 101 16 106 107

for each iteration I want to plug the number in a query and insert it into a table.

pseudo:

LOOP (105 102 19 17 101 16 106 107)
   FETCH select * from some_table where value=current_iteration_index --105..etc.
   INTO my_rec_type

END LOOP;

分享到QQ

分享到微博

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

发布评论

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

最偏执的依靠 2024-09-16 17:49:00

另一种方法：

declare
 type numListType is table of number;
 numList numListType;
begin
numList := numListType(
 105,102,19,17,101,16,106,107 
);
for i in numList.FIRST..numList.LAST loop
 -- your usage of element goes here
 dbms_output.put_line(numList(i));
end loop;
end;
/

Another method:

declare
 type numListType is table of number;
 numList numListType;
begin
numList := numListType(
 105,102,19,17,101,16,106,107 
);
for i in numList.FIRST..numList.LAST loop
 -- your usage of element goes here
 dbms_output.put_line(numList(i));
end loop;
end;
/

回复收藏 0 原文

流年已逝 2024-09-16 17:49:00

这是一个更简洁但同样丑陋的替代方案：

DECLARE 
CURSOR C IS
SELECT val 
  FROM (SELECT LEVEL val FROM dual CONNECT BY LEVEL < 1000)
 WHERE val IN (105,102,19,17,101,16,106,107);
BEGIN
  FOR R IN C LOOP
    select * 
     INTO my_rec_type
     from some_table
    where value=R.val; --105..etc.
    ... more stuff
  END LOOP;
END;

这里的优点（IMO）是您只需要修改 IN 列表，也许还需要修改 CONNECT BY 子句的限制来更改结果。

Here's a more concise, albeit no less ugly, alternative:

DECLARE 
CURSOR C IS
SELECT val 
  FROM (SELECT LEVEL val FROM dual CONNECT BY LEVEL < 1000)
 WHERE val IN (105,102,19,17,101,16,106,107);
BEGIN
  FOR R IN C LOOP
    select * 
     INTO my_rec_type
     from some_table
    where value=R.val; --105..etc.
    ... more stuff
  END LOOP;
END;

The advantage here (IMO) is you only need to modify the IN list and perhaps the limit on the CONNECT BY clause to change your results.

回复收藏 0 原文

那支青花 2024-09-16 17:49:00

虽然你的问题有几个解决方案，但根据你的处理，我要告诉你，我认为你处理这个问题的方式是错误的 - 你没有利用数据库的功能。

你能解释一下为什么

select * from some_table where value in (105, 102, 19, 17, 101, 16, 106, 107)

它不做你想做的事吗？

While there are a couple of solutions to your questions, but based on your handle I'm going to tell you that I think you're approaching this the wrong way - you're not taking advantage of the features of the database.

Can you explain why

select * from some_table where value in (105, 102, 19, 17, 101, 16, 106, 107)

doesn't do what you want it to do?

回复收藏 0 原文

羁〃客ぐ 2024-09-16 17:49:00

这是一个选项，使用光标 FOR 循环，以及％ROWTYPE属性：

DECLARE

  my_rec_type SOME_TABLE%ROWTYPE;

  CURSOR c IS 
    SELECT 105 AS c_index FROM DUAL
    UNION ALL
    SELECT 102 AS c_index FROM DUAL 
    UNION ALL
    SELECT 19 AS c_index FROM DUAL 
    UNION ALL
    SELECT 17 AS c_index FROM DUAL 
    UNION ALL
    SELECT 101 AS c_index FROM DUAL
    UNION ALL
    SELECT 16 AS c_index FROM DUAL 
    UNION ALL
    SELECT 106 AS c_index FROM DUAL
    UNION ALL
    SELECT 107 AS c_index FROM DUAL

BEGIN

  FOR cursor_rec IN c
  LOOP

     SELECT * 
       INTO my_rec_type
       FROM some_table 
       WHERE value = cursor_rec.c_index;

  END LOOP;

END;

Here's an option, using a Cursor FOR LOOP, and the %ROWTYPE attribute:

DECLARE

  my_rec_type SOME_TABLE%ROWTYPE;

  CURSOR c IS 
    SELECT 105 AS c_index FROM DUAL
    UNION ALL
    SELECT 102 AS c_index FROM DUAL 
    UNION ALL
    SELECT 19 AS c_index FROM DUAL 
    UNION ALL
    SELECT 17 AS c_index FROM DUAL 
    UNION ALL
    SELECT 101 AS c_index FROM DUAL
    UNION ALL
    SELECT 16 AS c_index FROM DUAL 
    UNION ALL
    SELECT 106 AS c_index FROM DUAL
    UNION ALL
    SELECT 107 AS c_index FROM DUAL

BEGIN

  FOR cursor_rec IN c
  LOOP

     SELECT * 
       INTO my_rec_type
       FROM some_table 
       WHERE value = cursor_rec.c_index;

  END LOOP;

END;

回复收藏 0 原文

~没有更多了~