在业务系统中处理小数精度, 四舍五入,全舍弃,全进位的办法[MSSQL防范]

AMOUNTDOT????? NUMBER(4,2) DEFAULT 0.01,? --金额切确度 1: 切确到元? 0.1:切确到角? 0.01:切确到分ROUNDING?????? NUMBER(1) ,???????????????????????????????? ?--舍入分界? -- 9: 全舍? 0: 全入? 4:四舍5入???

成立Oracle函数,传入设置ID,传入金额,返回处理过的金额

存储历程实现代码以下

create or replace function Get_Real_Number (? --按照处理办法ID,获得处理后的金额-计算的金额
??????????? ID?IN?NUMBER,?-- 处理办法ID
??????????? n_Amount IN NUMBER)????
? -----------------------------------------------------------------------
? --? 用? 途?? 自定义数据精度处理.
? --? 成立者? :Andrew
? -----------------------------------------------------------------------
? --? 改正记录列表:（按日期的先后次序逆序布列）
? --? 改正时间???? 改正人????? 实现的功效阐明
?? -- 9 全舍? 0 全入? 4四舍5入?
? -----------------------------------------------------------------------
return?NUMBER-- 返回的金额
IS
?????? v_amountdot VARCHAR2(5);????? --小数位数字符
?????? v_keep????? NUMBER ;????????? --保存的小数位数
?????? v_rounding? NUMBER ;??????? --舍入分界
????? ?v_Amount?NUMBER:=0 ;???????? --返回的金额
BEGIN

???? SELECT? amountdot,? nvl(rounding,0) INTO v_amountdot,v_rounding FROM ?处理办法表
???????????? WHERE id = SALE_PLACE_ID;
???? v_keep := Length(v_amountdot) -? instr(v_amountdot,'.') ;? --要保存的小数位数
???? IF v_rounding = 9 THEN???--处理全舍弃
??????? v_Amount := trunc(n_Amount,v_keep);
???? END IF;
????
???? IF v_rounding = 4 THEN????--处理四舍五入
??????? v_Amount := round(n_Amount,v_keep);
???? END IF;
????
???? IF v_rounding = 0 THEN??--处理全进位
?????????? ?v_Amount := trunc(n_Amount,v_keep);?? --小数为数刚好等于要求保存的数
?????????? IF v_Amount <> n_Amount THEN?? --假如截取后的数与本来的数字差别,进1
?????????? v_Amount := v_Amount + to_number(v_amountdot);
?????END IF;

???? RETURN v_Amount;

????
exception
?when OTHERS then?RETURN n_Amount;????????????????????????????????????????????

END Get_Real_Number;