How to write a loop in pl / sql that iterates 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 connect the number in the request and insert it into the 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 a shorter, though no less ugly, option:

 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 that you only need to change the IN list and possibly the limit of the CONNECT BY clause to change your results.

Answer 2

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 3

Although there are several solutions to your questions, based on your descriptor, I will tell you that I think that you are approaching this incorrectly - you are not using the database capabilities.

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 to do?

Answer 4

Here's an option using 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;