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 get factorial in oracle

Discussion in 'Other Development Tools' 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>