CREATE FUNCTION prepareMoneyCount ( InsuredYear SMALLINT , Amnt number(12,2), InsuredSex VARCHAR(1), InsuredAge SMALLINT )
RETURNS NUMERIC(12,8)
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN ATOMIC
declare v_Result NUMERIC(12,8) ;
declare v_Prameter1 float;
declare v_Prameter2 float;
declare i SMALLINT;
declare x SMALLINT;
declare w float;
declare s float;
declare y int;
declare z float;
declare q float;
declare p float;
declare n int;
declare m int;
declare v float;
set i =InsuredYear;
set x =InsuredAge;
set s =1;
set z =1;
set y =0;
set p=1;
set v=POWER(1.025,-0.5);
set m=1;
set n=0;
if InsuredYear =10 then
set v_Result = Amnt;
else
while y<10-i do
set w = (select FeeRate from RIFeeRateTable where FeeTableNo='C01006' and StrOne=InsuredSex and NumOne=x+i+ y);
set s=(1-w)*s;
set z=z/1.025;
set y=y+1 ;
end while;
set v_Prameter1=Amnt*s*z;
while n<10-i do
while m<n+1 do
set q = (select FeeRate from RIFeeRateTable where FeeTableNo='C01006' and StrOne=InsuredSex and NumOne=x+i+ m-1 );
set p=(1-q)*p;
set v=v/1.025;
set m=m+1 ;
end while;
set q =(select FeeRate from RIFeeRateTable where FeeTableNo='C01006' and StrOne=InsuredSex and NumOne=x+i+ m-1);
set v_Prameter2=v_Prameter2+Amnt * p * q * v ;
set n=n+1;
set m=1 ;
end while;
set v_Prameter2= v_Prameter2+ Amnt * q * v + i * POWER(1.025,-0.5);
set v_Result=v_Prameter1 + v_Prameter2;
end if;
RETURN v_Result;
end ;
这段代码 提示 NUMBER 未定义的名称
求高手帮忙 看看怎么改
------解决方案--------------------------------------------------------
db2 里数字类型的用decimal表示 number(12,2) 对应 decimal(12,2) 后面的也要改
------解决方案--------------------------------------------------------
db2不存在number类型，你可以用decimal(10,2)
格式12345678.90