1. The Forum has been upgraded to a modern and advanced system. Please read the announcement about this update.
  2. 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 get factorial in oracle

Discussion in 'Oracle Developer' started by Arju, Oct 11, 2008.

  1. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    SQL PL/SQL Codes to Calculate Factorial of a Number

    We all know factorial of a non-negative number n, is the product of all positive integers less than or equal to n. For example factorial of 4 is 4*3*2*1=24, factorial of 5 is 5*4*3*2*1=120.

    Now from oracle how we can get the factorial value. Below is the some ways,

    Way 1: Using PL/SQL
    The following is an example of calculating factorial of 9 and 6.
    Code (Text):
    SQL> create or replace function factorial_calc(value integer)
      2  return  integer
      3  as
      4  minus1 pls_integer;
      5  product pls_integer;
      6  begin
      7     dbms_output.enable(999999);
      8     minus1 := value - 1;
      9     if ( minus1 > 0 )
     10     then
     11        product := value*factorial_calc(minus1);
     12        return product;
     13     end if;
     14     return value;
     15  end factorial_calc;
     16  /

    Function created.

    SQL> declare
      2  value integer;
      3  begin
      4  value:=factorial_calc(9);
      5  dbms_output.put_line(value);
      6  end;
      7  /

    PL/SQL procedure successfully completed.

    SQL> set serverout on
    SQL> /
    362880
    362880

    PL/SQL procedure successfully completed.

    SQL> list 4
      4* value:=factorial_calc(9);
    SQL> c/9/6
      4* value:=factorial_calc(6);
    SQL>/
    720

    PL/SQL procedure successfully completed.
    SQL> select factorial_calc(12) from dual;

    FACTORIAL_CALC(12)
    ------------------
             479001600
     

    Way 2: Using SQL.

    Code (Text):
    SQL> select round(exp(sum(ln(n))))
      2  from (
      3    select level AS n
      4    from dual
      5    connect by level <= &n
      6  )
      7  ;
    Enter value for n: 6
    old   5:   connect by level <= &n
    new   5:   connect by level <= 6

    ROUND(EXP(SUM(LN(N))))
    ----------------------
                       720
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Two more variants in PL/SQL

    Code (Text):

    SQL>    CREATE OR REPLACE FUNCTION FACTORIAL_1(factstr varchar2 )
      2     RETURN NUMBER AS
      3       new_str VARCHAR2(4000) :=  factstr||'*' ;
      4       fact number := 1 ;
      5     BEGIN
      6
      7        WHILE new_str IS NOT NULL
      8        LOOP
      9          fact := fact * TO_NUMBER(SUBSTR(new_str,1,INSTR(new_str,'*')-1));
     10          new_str := substr( new_str,INSTR(new_str,'*')+1);
     11        END LOOP;
     12
     13        RETURN fact;
     14
     15     END;
     16  /

    Function created.

    SQL> variable n number
    SQL> define n=10
    SQL> select  FACTORIAL_1(LTRIM(max(sys_connect_by_path(level,'*')),'*'))  AS FAC
    TOR
      2    from dual
      3    connect by level <= &n;
    old   3:   connect by level <= &n
    new   3:   connect by level <= 10

        FACTOR
    ----------
       3628800
     
    Code (Text):

    SQL> CREATE OR REPLACE FUNCTION FACTORIAL_2(num NUMBER )
      2    RETURN NUMBER AS
      3      fact number := 1 ;
      4      newnum  number := num ;
      5    BEGIN
      6
      7       WHILE newnum >= 1
      8       LOOP
      9         fact    := fact * newnum;
     10         newnum  :=newnum-1;
     11       END LOOP;
     12
     13       RETURN fact;
     14
     15    END;
     16  /

    Function created.

    SQL> select FACTORIAL_2(&n) FACTOR from DUAL;
    old   1: select FACTORIAL_2(&n) FACTOR from DUAL
    new   1: select FACTORIAL_2(10) FACTOR from DUAL

        FACTOR
    ----------
       3628800

    SQL>
     
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Got to know One more variant for the same ( by accident ) . It uses permutation technique to get the factorial of a number and uses connect by clause to generate the rows. This can be used from Oracle 8i itself as a Straight SQL method to get the factorial of a number .

    Code (Text):

    SQL> var x number
    SQL> define x =5
    SQL> select count(*) FACTORIAL from
      2     (
      3  select level  b
      4   from
      5   (select level n from dual connect by level <= &x ) YourTable
      6   connect by nocycle n != prior n
      7  )where b = &x
      8  ;
    old   5:  (select level n from dual connect by level <= &x ) YourTable
    new   5:  (select level n from dual connect by level <= 5 ) YourTable
    old   7: )where b = &x
    new   7: )where b = 5

     FACTORIAL
    ----------
           120

    SQL>