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. 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. 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)
);
jeneesh wrote:Pure Evil.
MODEL:
Billy Verreynne wrote:But is there any other possible simple way from SQL?jeneesh wrote:Pure Evil.
MODEL:
The MODEL clause leads to the Dark Side of The Force...
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.jeneesh wrote:My comment was tongue-in-cheek, Jeneesh. :-)Billy Verreynne wrote:But is there any other possible simple way from SQL?jeneesh wrote:Pure Evil.
MODEL:
The MODEL clause leads to the Dark Side of The Force...
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?
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)
Manik wrote:I dont think, there is much meaning in talking about performance here..
Using Recursion in Oracle 11g r2 and higher version:
But my approach of plain sql which I posted earlier performs better than this one.
------------------------------And your MODEL query is also similar infact only two steps in explain plan when you compare my plain sql plan with model plan.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;
Cheers,
Manik.
Paul Horth wrote:This is not correct.. :)
Unfortunately none of these definitions work for 0!=1
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...Umm.. :(
or -4.5!=-52.3428... :-)