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