数值处理类函数
1.算数运算(abs、mod、sqrt、power)
语法:abs(number)
功能:返回绝对值
例子:select abs(-30.36) from dual; → 30.36
语法:mod(m,n)
功能:m/n 取余
例子:
select mod(6,4) from dual; → 2
select fmod(6,0) from dual; → 0
语法:sqrt(number)
功能:返回平方根
例子:
select sqrt(9) from dual; → 3
select sqrt(-9) from dual; → 报错
select sqrt(8) from dual; → 2.828427…
语法:power(m,n)
功能:乘方运算,m是底数,n是指数
例子:
select power(2,3) from dual; →8
2.四舍五入与格式化(round、ceil、floor、trunc、sign、to_char)
语法:round(number[,n])
功能:四舍五入 保留小数点后n位的值
例子:
select round(30.36,1) from dual; → 30.4
select round(30.36,-1) from dual; → 30 (-1代表精确到小数点之前1位数)
select round(30.36) from dual; → 30
语法:ceil(number)
功能:向上取整
例子:
select ceil(30.36) from dual; → 31
select ceil(-30.36) from dual; → -30
语法:floor(number)
功能:向下取整
例子:
select floor(30.36) from dual; → 30
select floor(-30.36) from dual; → -31
语法:sign(number)
功能:返回数字的正负性
例子:
select sign(-5) from dual; → -1
select sign(5) from dual; → 1
select sign(0) from dual; → 0
语法:trunc(number[,n])
功能:trunc(原数值[,小数位数])
例子:
select trunc(1.2345,3) from dual; →1.234
select trunc(1.2345,0) from dual; →1
select trunc(1.2345) from dual; →1
语法:to_char()
功能:to_char(原数值,格式),格式化数值
例子:
(1)格式字符“0”:0代表一位数字,没有时填充0
select to_char(12.78,‘0000.0000’) from dual; → 0012.7800
(2)格式字符“9”:9代表一个数字,整数部分没有时不进行填充。
select to_char(0.78,‘999.999’) from dual; → .780
select to_char(0.78,‘990.999’) from dual; → 0.780
(3)格式字符“,” :分组符号,常用于千位分隔符。
select to_char(123456789,‘999,999,999.00’) from dual; → 123,456,789.00
(4)格式化字符“FM”:Format Mask,屏蔽所有不必要的空格和0
select to_char(56789,‘999,999.00’),to_char(56789,‘FM999,999.00’) from dual; → __56789.00 56789.00
(5)格式化字符“$”:货币
select to_char(56789,‘FM$999,999.00’) from dual; → $56,789.00
(6)格式化字符“L”:本地货币
select to_char(56789,‘FML999,999.00’) from dual; → ¥56,789.00
(7)格式化字符“C”:货币种类符号
select to_char(56789,‘FM999,999.00C’) from dual; → 56,789.00CNY
to_char() 还有日期方面的应用,在处理日期函数类再展开说。