Question

SQL 语句问题：将原来零售价小数点后4位有效数字四舍五入为小数点后两位有效数字，怎么写？

比如原零售价8.4563四舍五入为8.46,用语句怎么写呢？

Answer 1

oracle里的！~

CREATE OR REPLACE FUNCTION KANTA.F_Round( d IN NUMBER,FLG IN NUMBER,M IN NUMBER )
RETURN NUMBER
IS
buf1 NUMBER;
buf2 NUMBER;
Rev number;
Fugo NUMBER;

BEGIN
If d <> 0 And M >= 0 Then

Fugo := 0;

If sign(d) = -1 Then --ﾏｲﾅｽの场合
Fugo := 1; --Fugoﾌﾗｸﾞ := 1
End If;

Rev:= ABS(d); --绝対値に换算
buf1 := power(10, M);

If FLG = 0 Then --切り舍て
buf2 := Rev * buf1;
buf2 := trunc(buf2);
Else
If FLG = 1 Then --四舍五入
buf2 := Rev * buf1 + 0.5;
buf2 := trunc(buf2);

Else
If FLG = 2 Then --切り上げ
buf2 := Rev * buf1 + 0.999999999999999999999;
buf2 := trunc(buf2);
else
IF FLG = 3 THEN --三舍四入
buf2 := Rev * buf1 + 0.6;
buf2 := trunc(buf2);
END IF;
END IF;
END IF;
End If;

If Fugo = 1 Then
RETURN (buf2 / buf1) * -1;
Else
RETURN buf2 / buf1;
End If;
Else
RETURN d;
End If;

EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;
/

Answer 2

to_char(8.4563,'90.99')