Oracle實現fibonacci數列

發布時間: 2012/9/17 16:48:10

Oracle實現fibonacci數列方法一：

SELECT REPLACE(MAX(SYS_CONNECT_BY_PATH(fib||', ', '/')),'/','')||'...' fiblist
FROM (
   SELECT n, fib, ROW_NUMBER()    -

     OVER (ORDER BY n) r
     FROM (select n, round((power((1+sqrt(5))*0.5, n)-power((1-sqrt(5))*0.5, n))/sqrt(5)) fib
             from (select level n
                     from dual
                  connect by level <= 16) t1) t2
)
START WITH r=1
CONNECT BY PRIOR r = r-1;
/*
FIBLIST
--------------------------------------------------------------------------------
1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377, 610, 987, ...
*/

方法二：

DECLARE
       A NUMBER;
       B NUMBER;
       C NUMBER;
    BEGIN
       A:=0;
       B:=1;
       C:=1;
       FOR i IN 1..20 LOOP
         DBMS_OUTPUT.PUT_LINE('the '||i||' number is:'||C);
         C:=A+B;
         A:=B;
         B:=C;
       END LOOP;
    END;
/*
the 1 number is:1
the 2 number is:1
the 3 number is:2
the 4 number is:3
the 5 number is:5
the 6 number is:8
the 7 number is:13
the 8 number is:21
the 9 number is:34
the 10 number is:55
the 11 number is:89
the 12 number is:144
the 13 number is:233
the 14 number is:377
the 15 number is:610
the 16 number is:987
the 17 number is:1597
the 18 number is:2584
the 19 number is:4181
the 20 number is:6765
*/

方法三：

select max(s) || ', ...' fibonacci_list
from
(select s
   from dual
   model
     return all rows
     dimension by ( 0 d )
     measures ( cast(' ' as varchar2(200)) s, 0 f)
     rules iterate (16)
     ( f[iteration_number] = decode(iteration_number, 0, 1, 1, 1, f[iteration_number-1] + f[iteration_number-2]),
        s[iteration_number] = decode(iteration_number, 0, to_char(f[iteration_number]), s[iteration_number-1] || ', ' || to_char(f[iteration_number]))
     )
)
/*
FIBONACCI_LIST
--------------------------------------------------------------------------------
1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377, 610, 987, ...