This tutorial demonstrates how to generate a series of integers using a generic integer table. Other techniques are discussed in the topics listed in the menu to the left.
One of the most straightforward ways to generate a series of integers is by adding a generic integer table to your application. You can create such a table like this.
create table integers ( integer_value integer primary key ) organization index ; Table created.
Since this table only has a single indexed column we specified
organization index
to make this an
Index-Organized
table and save storage space.
To load the table a simple loop like the following will do the trick.
begin for i in -5 .. 10 loop insert into integers values ( i ); end loop; commit; end; / PL/SQL procedure successfully completed.
We used -5 and 10 as the limits of our series in this example. In practice you would choose limits that anticipate the smallest and largest integers you will ever need.
select * from integers ;
INTEGER_VALUE ------------- -5 -4 -3 -2 -1 0 1 2 3 4 5 6 7 8 9 10
Later, when you need a specific series of integers you can use the INTEGERS table like this.
select i.integer_value as day_of_week , t.val from integers i left outer join t on ( i.integer_value = t.day_of_week ) where i.integer_value between 0 and 6 order by i.integer_value ;
DAY_OF_WEEK VAL ----------- ---------- 0 1 100 2 3 300 4 400 5 500 6