//求阶乘
//数学里面最直接的阶乘定义就是,将这个数n乘以n-1,再乘以n-1-1,一直乘到1,
//所得到的结果就是n的阶乘:n!=n*(n-1)*(n-1)*...*2*1,这里要注意:0!=1
//下面定义一个球阶乘的函数:
create or replace function fun_factorial(num number)
return number
is
result number:=1;
begin
if num<0 then
raise_application_error(-20012,' function parameters can not be less than zero');
elsif num=0 or num=1 then
return 1;
else
for i in 2..num loop
result:=i*result;
end loop;
return result;
end if;
end fun_factorial;
/
SQL> select fun_factorial(5) from dual;
FUN_FACTORIAL(5)
----------------
120
//
SQL> select fun_factorial(6) from dual;
FUN_FACTORIAL(6)
----------------
720
//当我们给阶乘函数传递负数时,我们将得到最初设定的错误
select fun_factorial(-6) from dual
ORA-20012: function parameters can not be less than zero
ORA-06512: at "YEEXUN.FUN_FACTORIAL", line 7
//
//如果有这样的要求呢?
//n!+(n-1)!+(n-2)!+...+2!+1!
//我们该怎么处理呢?
create or replace function fun_sum_of_factorial(n number)
return number
as
results number:=0;
begin
//下面使用nvl(n,5)为了保证用户有输入,如果没有输入数据,默认为5
for i in 1..nvl(n,5)
loop
//这里调用了上面我们创建的fun_factorial函数,求每一个数的阶乘
//然后再将每个数的阶乘相加就能得到结果了
results := results + fun_factorial(i);
end loop;
return results;
exception
when others then
dbms_output.put_line(sqlerrm);
end fun_sum_of_factorial;
/
SQL> select fun_sum_of_factorial(5) from dual;
FUN_SUM_OF_FACTORIAL(5)
-----------------------
153
//
SQL> select fun_sum_of_factorial(50) from dual;
FUN_SUM_OF_FACTORIAL(50)
------------------------
3.10350532295462E64
//
//如果能用一句话写出来那该多好啊!
//
select sum(nm)
from (
select power(2,sum(log(2,level)) over(order by level)) nm
from dual
connect by level<=5)
/
SUM(NM)
----------
153