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

# user-defined factorial

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
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
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
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
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
jeneesh wrote:
MODEL:
Pure Evil.

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

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