oracle pl/sql 中数字的阶乘
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22372570/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
factorial of a number in pl/sql
提问by rippy
The following pl/sql program generates an error on execution on line the sum :=temp*sum; encountered symbol ; when expecting ( . Please explain my mistake.
以下 pl/sql 程序在 sum :=temp*sum; 线上执行时产生错误。遇到符号；当期待 ( 。请解释我的错误。
declare
n number;
temp number;
sum number := 1;
begin
n := &n;
temp := n;
while temp>0 loop
sum := temp*sum;
temp := temp-1;
end loop;
dbms_output.put_line('Factorial of '||n||' is '||sum);
end;
/
回答by smnbbrv
Maybe not the answer to your question, but there is no need for PL/SQL here:
也许不是你问题的答案，但这里不需要 PL/SQL：
select round(exp(sum(ln(level))))
from dual
connect by level <= 5;
where 5 is your number (5!).
其中 5 是您的号码 ( 5!)。
Additionally, if you like to operate faster in PL/SQL use pls_integerinstead of number.
此外，如果您想在 PL/SQL 中更快地操作，请使用pls_integer而不是number.
UPDATE
更新
So according to comments I felt free to test:
所以根据评论，我可以随意测试：
create or replace package test_ is
function by_query(num number) return number deterministic;
function by_plsql(num number) return number deterministic;
end test_;
/
create or replace package body test_ is
function by_query(num number) return number deterministic
is
res number;
begin
select round(exp(sum(ln(level))))
into res
from dual
connect by level <= num;
return res;
end;
function by_plsql(num number) return number deterministic
is
n number := 0;
begin
for i in 1..num loop
n := n + ln(i);
end loop;
return round(exp(n));
end;
end test_;
So there are two functions with different content. Test query:
所以有两个不同内容的函数。测试查询：
declare
dummy number;
begin
for i in 1..10000 loop
dummy := test_.by_query(5);
end loop;
end;
0.094 sec.
0.094 秒
declare
dummy number;
begin
for i in 1..10000 loop
dummy := test_.by_plsql(5);
end loop;
end;
0.094 sec.
0.094 秒
You'll say I am cheater and using deterministickeyword but here it is obvious and is needed by logic. If I remove it, the same scripts are working 1.7 sec vs 1.3 sec, so procedure is only a bit faster, there is no even double-win in performance. The totally opposite effect you will get if you use the function in a query so it is a fair trade.
你会说我是骗子并使用deterministic关键字，但在这里很明显并且是逻辑需要的。如果我删除它，相同的脚本运行 1.7 秒 vs 1.3 秒，所以过程只是快了一点，甚至没有双赢的性能。如果您在查询中使用该函数，您将获得完全相反的效果，因此这是一项公平交易。
回答by hkutluay
Sum is reserved word in sql. Change variable name like
Sum 是 sql 中的保留字。更改变量名称，如
declare
n number;
temp number;
sum_ number := 1;
begin
n := &n;
temp := n;
while temp>0 loop
sum_ := temp*sum_;
temp := temp-1;
end loop;
dbms_output.put_line('Factorial of '||n||' is '||sum_);
end;
/
回答by Bill
declare
n number;
i number;
sum_of_log_10s number;
exponent number;
base number;
begin
n := &n;
i := 1;
sum_of_log_10s := 0;
while i <= n loop
-- do stuff
sum_of_log_10s := sum_of_log_10s + log(10,i);
i := i + 1;
end loop;
dbms_output.put_line('sum of logs = '||sum_of_log_10s);
exponent := floor(sum_of_log_10s);
base := power(10,sum_of_log_10s - exponent);
dbms_output.put_line(n||'! = '||base||' x 10^'||exponent);
end;
回答by Bill
I came up with this code that I like even better than @smnbbrv's answer. It's a great way to check the speed of a machine. I've been using a variation of this since my Atari 800
我想出了这个代码，我比@smnbbrv 的答案更喜欢它。这是检查机器速度的好方法。自从我的 Atari 800 以来，我一直在使用它的变体
ALTER SESSION FORCE PARALLEL DDL PARALLEL 16;
ALTER SESSION FORCE PARALLEL DML PARALLEL 16;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 16;
with t as (
select /*+materialize*/
rownum i
from dual connect by rownum < 100000 -- put number to calculate n! here
)
,t1 as (
select /*+parallel(t,16)*/ /*+materialize*/
sum(log(10,i)) logsum
from t
)
select
trunc(power(10,(mod(logsum,1))),3) ||' x 10^'||trim(to_char(floor(logsum),'999,999,999,999')) factorial
-- logsum
from t1
;
-- returns 2.824 x 10^456,568
-- 返回 2.824 x 10^456,568
回答by bilal Tashfeen
Here is the simple code for finding factorial of number at run time...
这是在运行时查找数字阶乘的简单代码...
declare
-- it gives the final answer after computation
fac number :=1;
-- given number n
-- taking input from user
n number := &1;
-- start block
begin
-- start while loop
while n > 0 loop
-- multiple with n and decrease n's value
fac:=n*fac;
--dbms_output.put(n||'*');
n:=n-1;
end loop;
-- end loop
-- print result of fac
dbms_output.put_line(fac);
-- end the begin block
end;
