advertisements
_____________________________________________________________________________________________________________________
It is a new feature in Oracle 11gR2; you can generate number series in single select command. See some examples below.
How to generate multiplication table using Select Command?
SQL> UNDEF tablefor
SQL> l
with t(slno,tfor) as (select 1 slno, &&tablefor tfor from dual
union all
select slno+1, tfor+&&tablefor from t where slno<10 )
select slno,'x &&tablefor', tfor from t
SQL> /
SLNO 'X4 TFOR
---------- --- ----------
1 x 4 4
2 x 4 8
3 x 4 12
4 x 4 16
5 x 4 20
6 x 4 24
7 x 4 28
8 x 4 32
9 x 4 36
10 x 4 40
Note: UNDEF Command on SQL*Plus is used for resetting the value for && variable.
Multiplication table for 4:
SQL> UNDEF tablefor
SQL>
with t(slno,tfor) as (select 1 slno, &&tablefor tfor from dual
union all
select slno+1, tfor+&&tablefor from t where slno<10 )
select slno,'x &&tablefor', tfor from t
SLNO 'X5 TFOR
---------- --- ----------
1 x 5 5
2 x 5 10
3 x 5 15
4 x 5 20
5 x 5 25
6 x 5 30
7 x 5 35
8 x 5 40
9 x 5 45
10 x 5 50
A simple number series
with t(count) as (select 1 from dual
union all
select count+1 from t where count<10 )
select count from t
SQL> /
COUNT
----------
1
2
3
4
5
6
7
8
9
10
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment