function错误

昨天下班的时候，测试爆出一个错误：
问题出在下面这个functions的第九行：

CALCNPV functions:
CREATE OR REPLACE FUNCTION "GAOFEI"."CALCNPV"
(monthFv in number, recoveryExp in number, monthTime in number, irr in number) 
   RETURN number IS netRec number;
BEGIN
  if monthFv is null or recoveryExp is null or monthTime is null or irr is null then
    return null;
  end if;
  netRec := ISNULL_NUMBER(monthFv, 0) * (1 - ISNULL_NUMBER(recoveryExp, 0));
  return netRec / power(1 + ISNULL_NUMBER(irr, 0), ISNULL_NUMBER(monthTime, 0) / 12);


END;

拿出functions单独进行测试：

select CALCNPV(dv.VALUE_FINAL, dv.RECOVERY_EXP, dv.RECOVERY_TIME, p.IRR)
 as NPV from dual

有2条数据出错。分别测试：
Eg:
1. select CALCNPV(11111,null,null,11.11111) from dual

Error data:
select CALCNPV(1,0.11,3333,11.11111) from dual  -- 3333

POWER
返回n1的n2次方根

SQL 语句：
select CALCNPV(1,0.11,300,11.11111) from dual  -- 3333

结果：

CALCNPV(1,0.11,300,11.11111)              
------------------------------------------
0.0000000000000000000000000007409540738908

估计得到的值太小，控制一下monthTime就可以了。

测试得到临界值为monthTime＞1395

改为新的functions：

CALCNPV functions:
CREATE OR REPLACE FUNCTION "GAOFEI"."CALCNPV"
(monthFv in number, recoveryExp in number, monthTime in number, irr in number)    
  
RETURN number IS netRec number;

BEGIN
  if monthFv is null or recoveryExp is null or monthTime is null or irr is null  
or monthTime > 1395 then

    return null;
  end if;
  netRec := ISNULL_NUMBER(monthFv, 0) * (1 - ISNULL_NUMBER(recoveryExp, 0));
  return netRec / power
   (1 + ISNULL_NUMBER(irr, 0), ISNULL_NUMBER(monthTime, 0) / 12);
END;

考虑了一下，加上限制，只是针对本条数据，如果其他的值很大。有可能也不会出错。
根本解决办法是把返回值加以验证。