1 2 Previous Next 15 Replies Latest reply: Nov 14, 2012 4:28 AM by Paul Horth RSS

    user-defined factorial

    974100
      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
          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
            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
              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
                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
                  Billy~Verreynne
                  jeneesh wrote:
                  MODEL:
                  Pure Evil.

                  The MODEL clause leads to the Dark Side of The Force...
                  • 6. Re: user-defined factorial
                    jeneesh
                    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
                      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
                        Billy~Verreynne
                        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
                          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
                            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
                              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
                                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
                                  Agreed!!! I had that in mind... skipped it because, never thought we have such good testers ;-)

                                  Cheers,
                                  Manik.
                                  • 14. Re: user-defined factorial
                                    jeneesh
                                    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