Monday, January 19, 2009

How to generate fibonacci series in Oracle.

Way 1:

with data as (select level levels from dual
connect by level <= &how_may_rows)
select f from data
model dimension by (levels)
measures ( 0 f)
rules ( f[1] = 0 , f[2] = 1 , f[levels>2]=f[cv(levels)-2]+f[cv(levels)-1]
);

Enter value for how_may_rows: 10
old 2: connect by level <= &how_may_rows)
new 2: connect by level <= 10)

F
----------
0
1
1
2
3
5
8
13
21
34

10 rows selected.



Way 2:

Just a variant of way 1,

SQL> select s seq from dual
model return all rows
dimension by ( 0 d ) measures ( 0 s )
rules iterate (&n) (
s[iteration_number ] = decode(
iteration_number, 0, 0, 1, 1, s[iteration_number-2]
) + nvl(s[iteration_number-1],0)
)
/

Enter value for n: 8
old 4: rules iterate (&n) (
new 4: rules iterate (8) (

SEQ
----------
0
1
1
2
3
5
8
13

8 rows selected.


Way 3: Using Math Formula

SQL> select round ((power ((1 + sqrt (5)) / 2, level - 1) - power ((1 - sqrt (5)) / 2, level - 1)) / sqrt (5)) fib
from dual
connect by level <=&n;

Enter value for n: 8
old 3: connect by level <=&n
new 3: connect by level <=8

FIB
----------
0
1
1
2
3
5
8
13

8 rows selected.

No comments:

Post a Comment