Round in Oracle/VBA

VBA的 Round采用的是银行家算法（rounds to the nearest even number）

Round(1.5) = 2

Round(0.5) = 0

在Oracle中实现银行家算法

SQL> create or replace function bankers_round (val number, rnd_digit number := 0) return number is

2 v_rnd_digit number;

3 v_remainder number;

4 begin

5 v_rnd_digit := trunc(rnd_digit);

6

7 v_remainder := (val - trunc(val,v_rnd_digit)) * power(10,v_rnd_digit + 1);

8

9 if Abs(v_remainder) < 5

10 or (Abs(v_remainder) = 5 and mod(trunc(val * power(10,v_rnd_digit)),2) = 0) then

11 return trunc(val,v_rnd_digit);

12 else

13 return round(val,v_rnd_digit);

14 end if;

15 end;

16 /

 

SQL> select Bankers_Round(-123.45, 1) from dual;

 

BANKERS_ROUND(-123.45,1)

------------------------

-123.4

 

SQL> select Bankers_Round(-123.55, 1) from dual;

 

BANKERS_ROUND(-123.55,1)

------------------------

-123.6

 

SQL> select Bankers_Round(-123.65, 1) from dual;

 

BANKERS_ROUND(-123.65,1)

------------------------

-123.6

 

SQL> select Bankers_Round(-123.75, 1) from dual;

 

BANKERS_ROUND(-123.75,1)

------------------------

-123.8

在VBA中实现普通的四舍五入

Round(12.45+0.000000001,1)

http://blogs.msdn.com/b/ericlippert/archive/2003/09/26/bankers-rounding.aspx