1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to generate Fibonacci Series ( Puzzle )

Discussion in 'General' started by rajavu, Oct 18, 2008.

  1. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    This is puzzle . I invite all the possible solution for generating Fibonacci Series in SQL or PL/SQL .

    The output should be as follows .

    Code (Text):

                 0
                 1
                 1
                 2
                 3
                 5
                 8
                13
                21
                34
                55
                89
     
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Excellent Idea, ok I will try out! :)
     
  3. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Code (Text):
    SQL> select s seq from dual
      2  model return all rows
      3  dimension by ( 0 d ) measures ( 0 s )
      4  rules iterate (&n) (
      5  s[iteration_number ] = decode(
      6  iteration_number, 0, 0, 1, 1, s[iteration_number-2]
      7  ) + nvl(s[iteration_number-1],0)
      8  )
      9  /
    Enter value for n: 10
    old   4: rules iterate (&n) (
    new   4: rules iterate (10) (

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

    10 rows selected.
     
  4. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    After searching using math function,
    Code (Text):
    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;  2    3
    Enter value for n: 6
    old   3: connect by level <=&n
    new   3: connect by level <=6

           FIB
    ----------
             0
             1
             1
             2
             3
             5

    6 rows selected.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Nice Arju ,

    By the way , my favourite is method of Table function . This method can be used from Oracle 9i.

    Code (Text):

    SQL> CREATE OR REPLACE TYPE FIBONACCI as table OF NUMBER(10);
      2  /

    Type created.

    SQL> CREATE OR REPLACE FUNCTION gen_fibonacci (series_length IN NUMBER)
      2  RETURN FIBONACCI PIPELINED
      3  AS
      4  n0 NUMBER :=0;
      5  n1 NUMBER :=1;
      6  n2 NUMBER :=0;
      7  BEGIN
      8      PIPE ROW(n0);
      9      PIPE ROW(n1);
     10    FOR i IN n0..series_length-3
     11    LOOP
     12      n2 := n0 + n1 ;
     13      PIPE ROW(n2);
     14      n0 := n1;
     15      n1 := n2;
     16    END LOOP;
     17    RETURN;
     18  END gen_fibonacci;
     19  /

    Function created.

    SQL> select COLUMN_VALUE FIBONACCI
      2  from TABLE(gen_fibonacci(10));

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

    10 rows selected.

    SQL>

     
     
  6. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hi guys,

    a simple PL/SQL function to calculate fibonacci(n)

    Code (Text):

    CREATE OR REPLACE FUNCTION fib (n POSITIVE)
       RETURN INTEGER
    IS
       pos1   INTEGER := 1;
       pos2   INTEGER := 0;
       cum    INTEGER;
    BEGIN
       IF (n = 1) OR (n = 2)
       THEN
          RETURN 1;
       ELSE
          cum := pos1 + pos2;

          FOR i IN 3 .. n
          LOOP
             pos2 := pos1;
             pos1 := cum;
             cum := pos1 + pos2;
          END LOOP;

          RETURN cum;
       END IF;
    END fib;
     
    and then a recursive examlple

    Code (Text):

    create or replace FUNCTION fib (n POSITIVE)
       RETURN INTEGER
    IS
    BEGIN
       IF (n = 1) OR (n = 2)
       THEN
          RETURN 1;
       ELSE
          RETURN fib (n - 1) + fib (n - 2);
       END IF;
    END fib;
     
    But i can't figure out how to generate the series using the above functions.. maybe you guys can help...
     
  7. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Code (Text):
    SQL> CREATE OR REPLACE FUNCTION fib (n POSITIVE)
      RETURN INTEGER
    IS
      pos1  INTEGER := 1;
      pos2  INTEGER := 0;
      cum    INTEGER;
    BEGIN
      IF (n = 1) OR (n = 2)
      THEN
          RETURN 1;
      ELSE
          cum := pos1 + pos2;

          FOR i IN 3 .. n
          LOOP
            pos2 := pos1;
            pos1 := cum;
            cum := pos1 + pos2;
          END LOOP;

          RETURN cum;
      END IF;
    END fib;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23
     24  /

    Function created.

    SQL> select fib(10) from dual;

       FIB(10)
    ----------
            55

    SQL> select fib(-1) from dual;

       FIB(-1)
    ----------
             1
    Just one value. Not series of values. And by the way fib(-1)=1 somehow not logical.
     
  8. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Just one to mention that according to definition the first number of the Fibonacci series sequence is 0, the second number is 1.

    Sadik, you solution starts with 1.
     
  9. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Nice attempt :)

    The Above script can be used to get the fibonacci series . We need to make the function into Procedure as below .

    Code (Text):

    SQL> CREATE OR REPLACE PROCEDURE fib (n POSITIV
      2  IS
      3     pos1   INTEGER := 1;
      4     pos2   INTEGER := 0;
      5     cum    INTEGER := 0;
      6
      7  BEGIN
      8     IF (n >=1)
      9     THEN
     10        DBMS_OUTPUT.PUT_LINE (0);
     11     END IF;
     12     IF (n >=2)
     13     THEN
     14        DBMS_OUTPUT.PUT_LINE (1);
     15     END IF;
     16
     17        FOR i IN 3 .. n
     18        LOOP
     19           cum := pos1 + pos2;
     20           DBMS_OUTPUT.PUT_LINE(cum) ;
     21           pos2 := pos1;
     22           pos1 := cum;
     23
     24        END LOOP;
     25
     26  END fib;
     27  /

    Procedure created.

    SQL> exec fib(10);
    0
    1
    1
    2
    3
    5
    8
    13
    21
    34

    PL/SQL procedure successfully completed.

    SQL> exec fib(-1);
    BEGIN fib(-1); END;

    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at line 1


    SQL>
     
     
  10. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Very Good... raj

    yes my code was only for positive n thanks for the modification. Actually am not so much of a sql expert as you would have guessed by now :)
     
  11. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    Hi.. a simple example

    Code (Text):

    CREATE OR REPLACE PROCEDURE fibonacci_series (n NUMBER)
    IS
       N1   NUMBER := 0;
       N2   NUMBER := 1;
       N3   NUMBER;
       count   NUMBER DEFAULT 0;
       t     NUMBER;
    BEGIN
       DBMS_OUTPUT.put_line (N1);
       DBMS_OUTPUT.put_line (N2);

       WHILE count != n - 2
       LOOP
          N3 := N1 + N2;
          DBMS_OUTPUT.put_line (N3);
          t := N2;
          N2 := N3;
          N1 := t;
          count := count + 1;
       END LOOP;
    EXCEPTION
       WHEN OTHERS
       THEN
          DBMS_OUTPUT.put_line (SQLERRM);
    END;
     
    :D
     
  12. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Here is the test,
    Code (Text):

    SQL> CREATE OR REPLACE PROCEDURE fibonacci_series (n NUMBER)
      2  IS
      3     N1   NUMBER := 0;
      4     N2   NUMBER := 1;
      5     N3   NUMBER;
      6     count   NUMBER DEFAULT 0;
      7     t     NUMBER;
      8  BEGIN
      9     DBMS_OUTPUT.put_line (N1);
     10     DBMS_OUTPUT.put_line (N2);
     11
     12     WHILE count != n - 2
     13     LOOP
     14        N3 := N1 + N2;
     15        DBMS_OUTPUT.put_line (N3);
     16        t := N2;
     17        N2 := N3;
     18        N1 := t;
     19        count := count + 1;
     20     END LOOP;
     21  EXCEPTION
       WHEN OTHERS
     22   23     THEN
     24        DBMS_OUTPUT.put_line (SQLERRM);
     25  END;
     26  /

    Warning: Procedure created with compilation errors.

    SQL> show errors
    Errors for PROCEDURE FIBONACCI_SERIES:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    12/4     PL/SQL: Statement ignored
    12/10    PLS-00204: function or pseudo-column 'COUNT' may be used inside a
             SQL statement only
     
    Code (Text):

    SQL> l 6
      6*    count   NUMBER DEFAULT 0;
    SQL> c/count/c
      6*    c   NUMBER DEFAULT 0;
    SQL> l 19
     19*       count := count + 1;
    SQL> c/count/c
     19*       c := count + 1;
    SQL> c/count/c
     19*       c := c + 1;
    SQL> /

    Warning: Procedure created with compilation errors.

    SQL> show error
    Errors for PROCEDURE FIBONACCI_SERIES:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    12/4     PL/SQL: Statement ignored
    12/10    PLS-00204: function or pseudo-column 'COUNT' may be used inside a
             SQL statement only

     
    Code (Text):

    SQL> l 12
     12*    WHILE count != n - 2
    SQL> c/count/c
     12*    WHILE c != n - 2
    SQL> /

    Procedure created.

    SQL> exec fibonacci_series(3);

    PL/SQL procedure successfully completed.

    SQL> set serverout on
    SQL> /

    Procedure created.

    SQL> exec fibonacci_series(3);
    0
    1
    1

    PL/SQL procedure successfully completed.

    SQL> exec fibonacci_series(7);
    0
    1
    1
    2
    3
    5
    8

    PL/SQL procedure successfully completed.

    SQL> exec fibonacci_series(-1);
     
    And it hangs.

    Whenever you use exception whenever others mean you are inviting problems in your procedure.
     
  13. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    Yes Arju, you are right.. actually i mistakenly used count and didn't test it. Also i was only writing for only positive n. Here's the corrected code


    Code (Text):

    SQL*Plus: Release 10.2.0.1.0 - Production on Sun Oct 19 13:18:24 2008

    Copyright (c) 1982, 2005, Oracle.  All rights reserved.


    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options

    SQL> CREATE OR REPLACE PROCEDURE fibonacci_series (n NUMBER)
      2  IS
      3     N1   NUMBER := 0;
      4     N2   NUMBER := 1;
      5     N3   NUMBER;
      6     cnt   NUMBER DEFAULT 0;
      7     t     NUMBER;
      8  BEGIN
      9     DBMS_OUTPUT.put_line (N1);
     10     DBMS_OUTPUT.put_line (N2);
     11  
     12     WHILE cnt != n - 2
     13     LOOP
     14        N3 := N1 + N2;
     15        DBMS_OUTPUT.put_line (N3);
     16        t := N2;
     17        N2 := N3;
     18        N1 := t;
     19        cnt := cnt + 1;
     20     END LOOP;
     21  END;
     22  /

    Procedure created.

    SQL> set serverout on
    SQL> exec fibonacci_series(10);
    0
    1
    1
    2
    3
    5
    8
    13
    21
    34

    PL/SQL procedure successfully completed.
     
     
  14. itsjustforid

    itsjustforid Guest

    its a great idea..