How to loop through numbers in SQL with varying increments

Question

I am trying to loop through numbers in SQL that have have a start value of 10 and increment by a value of 7 up to 59. In a C-based language this would be incredibly simple, but I'm having trouble implementing it in Oracle SQL.

I have tried using a for loop and multiplying each iteration by 7, but I'm at a loss for how to start and end the loop at specific values.

DECLARE
incrementor PLS_INTEGER := 7;
BEGIN
    FOR i IN 10..59 LOOP
        dbms_output.put_line('x = '|| i * incrementor);
    END loop;
END;

The desired output would be:

x = 10
x = 17
x = 24
x = 31
x = 38
x = 45
x = 52
x = 59

But instead I am getting:

x = 70
x = 77
x = 84
x = 91

and so on...

Any ideas on how I would do this? Thanks in advance for your help!

Answer 1

The for-loop doesn't allow you to specify the increment, unfortunately, only the number of iterations.

Your code is looping over every integer between 10 and 59, so you go around the loop 50 times, with i being set to 10, 11, ... 58, 59; and then for each iteration you multiply that value of i by 7 - hence your getting 50 results with values 70, 77, ... 406, 413.

To get the result you want you can calculate the actual number of iterations needed, and then add the range-start value to the multiplication result inside the loop:

DECLARE
    incrementor PLS_INTEGER := 7;
BEGIN
    FOR i IN 0..floor((59 - 10)/incrementor) LOOP
        dbms_output.put_line('x = '|| (10 + i * incrementor));
    END loop;
END;
/

x = 10
x = 17
x = 24
x = 31
x = 38
x = 45
x = 52
x = 59


PL/SQL procedure successfully completed.

You can use more variables to avoid repeating yourself of course:

DECLARE
    incrementor PLS_INTEGER := 7;
    low_value PLS_INTEGER := 10;
    high_value PLS_INTEGER := 59;
BEGIN
    FOR i IN 0..floor((high_value - low_value)/incrementor) LOOP
        dbms_output.put_line('x = '|| (low_value + i * incrementor));
    END loop;
END;
/

---

You tagged the question for PL/SQL, and that's what you used in your attempt; but as your question refers to SQL; you can do this in plain SQL with a hierarchical query:

select 10 + (7 * (level - 1)) as result
from dual
connect by level <= floor((59 - 10)/7) + 1;

    RESULT
----------
        10
        17
        24
        31
        38
        45
        52
        59

8 rows selected. 

or with recursive subquery factoring:

with rcte (result) as (
  select 10
  from dual
  union all
  select result + 7
  from rcte
  where result < 59
)
select result
from rcte;