This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Nov 14, 2012 2:28 AM by Paul Horth RSS

user-defined factorial

974100 Newbie
Currently Being Moderated
I am trying to create a procedure that calculates a factorial using a value provided by the user. An anonymous program unit that asks the user to enter a value and executes the procedure.

Does anyone know how to do this? thanks.

Regards,
Mac
  • 1. Re: user-defined factorial
    jeneesh Guru
    Currently Being Moderated
    Googling:

    Factorial in PL/SQL

    User Input in Pl/SQL

    Edited by: jeneesh on Nov 14, 2012 9:34 AM
  • 2. Re: user-defined factorial
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Mac,

    Welcome to the forum!

    PL/SQL doesn't have any kind of keyboard input.
    If you're running the PL/SQL from SQL*Pus, then you can use a substitution variable, like this:
    ACCEPT  user_input  PROMPT  'Please enter a positive integer: '
    
    DECLARE
        n   PLS_INTEGER  := &user_input;
    BEGIN
        ...
    Writing a procedure (or a function, which might be better) for factorial sounds a lot like a school assignment, so I'll try not to spoil it for you. What have you tried? Do what you can, and if you have problems, post your code, any error message you get, and a specific question.
  • 3. Re: user-defined factorial
    Manik Expert
    Currently Being Moderated
    No need to involve PL/SQL for this
    Check this:
    WITH t AS
            (    SELECT ROWNUM n
                   FROM DUAL
             CONNECT BY ROWNUM <= 4)
    SELECT EXP (SUM (LN (n)))
      FROM t
    
    Gives you 4! = 24.
    u may use substition variable & for getting number from user interactively.

    Cheers,
    Manik.
  • 4. Re: user-defined factorial
    jeneesh Guru
    Currently Being Moderated
    MODEL:
    with t as
    (
     select 4 inp from dual
    )
    select fct
    from t
     model 
      dimension by (1 rn)
      measures (inp,1 fct)
      rules iterate(999) until(iteration_number+1 >= inp[1])
      (
       fct[1] = fct[1]*(iteration_number+1)
      );
  • 5. Re: user-defined factorial
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    jeneesh wrote:
    MODEL:
    Pure Evil.

    The MODEL clause leads to the Dark Side of The Force...
  • 6. Re: user-defined factorial
    jeneesh Guru
    Currently Being Moderated
    Billy  Verreynne  wrote:
    jeneesh wrote:
    MODEL:
    Pure Evil.

    The MODEL clause leads to the Dark Side of The Force...
    But is there any other possible simple way from SQL?

    I have seen results using CONNECT BY, XML and EXP and log...

    All looks like evils only, so does MODEL.

    Or should it be done as a function?
  • 7. Re: user-defined factorial
    Manik Expert
    Currently Being Moderated
    Using Recursion in Oracle 11g r2 and higher version:
    But my approach of plain sql which I posted earlier performs better than this one.

    ------------------------------
    WITH num AS (SELECT 5 n FROM DUAL),
         rec (n, factorial) AS
            (SELECT 0 n, 1 factorial FROM DUAL
             UNION ALL
             SELECT (a.n + 1), factorial * (a.n + 1) factorial
               FROM rec a, num b
              WHERE a.n < b.n)
    SELECT factorial
      FROM rec a, num b
     WHERE a.n = b.n;
    And your MODEL query is also similar infact only two steps in explain plan when you compare my plain sql plan with model plan.

    Cheers,
    Manik.
  • 8. Re: user-defined factorial
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    jeneesh wrote:
    Billy  Verreynne  wrote:
    jeneesh wrote:
    MODEL:
    Pure Evil.

    The MODEL clause leads to the Dark Side of The Force...
    But is there any other possible simple way from SQL?

    I have seen results using CONNECT BY, XML and EXP and log...

    All looks like evils only, so does MODEL.

    Or should it be done as a function?
    My comment was tongue-in-cheek, Jeneesh. :-)

    Yes, one can use a recursive query. I would however ask why one want to do factorials in the SQL engine specifically? Not a common requirement in my experience - typically analytics are used to do math "stuff" like moving averages, means, and so on.
  • 9. Re: user-defined factorial
    jeneesh Guru
    Currently Being Moderated
    Billy  Verreynne  wrote:
    My comment was tongue-in-cheek, Jeneesh. :-)
    :)
    Yes, one can use a recursive query. I would however ask why one want to do factorials in the SQL engine specifically? Not a common requirement in my experience - typically analytics are used to do math "stuff" like moving averages, means, and so on.
    I would also prefer to do it in PL/SQL.. (if required)
  • 10. Re: user-defined factorial
    jeneesh Guru
    Currently Being Moderated
    Manik wrote:
    Using Recursion in Oracle 11g r2 and higher version:
    But my approach of plain sql which I posted earlier performs better than this one.

    ------------------------------
    WITH num AS (SELECT 5 n FROM DUAL),
    rec (n, factorial) AS
    (SELECT 0 n, 1 factorial FROM DUAL
    UNION ALL
    SELECT (a.n + 1), factorial * (a.n + 1) factorial
    FROM rec a, num b
    WHERE a.n < b.n)
    SELECT factorial
    FROM rec a, num b
    WHERE a.n = b.n;
    And your MODEL query is also similar infact only two steps in explain plan when you compare my plain sql plan with model plan.

    Cheers,
    Manik.
    I dont think, there is much meaning in talking about performance here..

    Whatever methods discussed here, (or in sites like AMIS Blog) - that methods will not be able to compute the factorial of 84, which will give an error - ORA-01426: numeric overflow

    Means, to calculate upto 83!, talking about performance (by any of the method, the output will come within no time) - Dont know what is the significance.
  • 11. Re: user-defined factorial
    Manik Expert
    Currently Being Moderated
    I was just trying to follow basic mantra of Tom : maximize sql minimize pl/sql .....
    was trying to present different ways..
    :)

    Cheers,
    Manik.
  • 12. Re: user-defined factorial
    Paul Horth Expert
    Currently Being Moderated
    Unfortunately none of these definitions work for 0!=1
    or for that matter 1.2!=1.10180...
    or -4.5!=-52.3428... :-)
  • 13. Re: user-defined factorial
    Manik Expert
    Currently Being Moderated
    Agreed!!! I had that in mind... skipped it because, never thought we have such good testers ;-)

    Cheers,
    Manik.
  • 14. Re: user-defined factorial
    jeneesh Guru
    Currently Being Moderated
    Paul Horth wrote:
    Unfortunately none of these definitions work for 0!=1
    This is not correct.. :)
    with t as
    (
     select 0 inp from dual 
    )
    select fct
    from t
     model  
      dimension by (1 rn)
      measures (inp,1 fct)
      rules iterate(999) until(iteration_number+1 >= inp[1])
      (
       fct[1] = fct[1]*(iteration_number+1)
      );
    
    FCT
    ---
      1 
    
      WITH t AS
            (    SELECT ROWNUM n
                   FROM DUAL
             CONNECT BY ROWNUM <= 0)
    SELECT EXP (SUM (LN (n)))
      FROM t;
    
    EXP(SUM(LN(N)))
    ---------------
                  1 
    or for that matter 1.2!=1.10180...
    or -4.5!=-52.3428... :-)
    Umm.. :(
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points