how to write loop in pl/sql that goes over numbers

Question

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;

Answer 1

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;

Answer 2

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.

Answer 3

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;
/

Answer 4

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?