Oracle—常见数值型处理函数
1.abs()---返回绝对值、
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select abs(-21.45) from dual; 输出:21.45
2.round() ----四舍五入值
语法:round(原数值[,小数位数])
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select round(4.37,1) from dual; 输出:4.4 select round(456.37,-1) from dual; 输出:460 精确到小数点之前的位数 select round(456.37) from dual; 输出:456
3.ceil()-----向上取整
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select ceil(23.45) from dual; 输出:24 select ceil(-23.45) from dual; 输出:-23
4.floor()---向下取整
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select floor(23.45) from dual; 输出:23 select floor(-23.45) from dual; 输出:-24
5.mod()—取余
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select mod(5,2) from dual; 输出:1 select mod(5,0) from dual ; 输出:5
6.sign()---返回数字的正负性
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select sign(-10) from dual; 输出:-1 select sign(10) from dual; 输出:1 select sign(0) from dual ; 输出:0
7.sqrt()——返回平方根
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select sqrt(4) from dual; 输出:2 select sqrt(-4) from dual; 输出:报错
8.power()—— 乘方运算
语法:power(底数,指数)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select power(4,3) from dual; 输出:64
9.trunc()—— 截取数字
语法:trunc(原数值[,小数位数])
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select trunc(3.789,2),round(3.789,2) from dual; 输出:3.78 3.79 select trunc(3.789,0) from dual ; 输出:3 select trunc(3.789) from dual ; 输出:3
10.chr()——将ASCII码转为字符
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select chr(65) from dual; 输出:A
11.to_char() ——格式化数值
语法:to_char(原数值,格式)
1)格式字符“0”——0代表一位数字,没有时填充0
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select to_char(12.78,'000.000') from dual; 输出:012.780 select to_char(12.78,'0000.0000') from dual; 输出:0012.7800 select to_char(1112.78,'000.000') from dual; 输出:########
2)格式字符“9”——9代表一个数字,整数部分没有时不进行填充。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select to_char(12.78,'999.999') from dual; 输出:12.780 select to_char(0.78,'999.999') from dual; 输出:.780 select to_char(0.78,'990.999') from dual; 输出: 0.780
3)格式字符“,” ——分组符号,常用于千位分隔符。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select to_char(123456789,'999,999,999.00') from dual; 输出:123,456,789.00
4)格式化字符“FM”——Format Mask,屏蔽所有不必要的空格和0
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select to_char(56789,'999,999.00'),to_char(56789,'FM999,999.00') from dual; 输出:__56789.00 56789.00
5)格式化字符“$”——货币
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select to_char(56789,'$999,999.00') from dual; 输出: $56,789.00 select to_char(56789,'999,9$99.00') from dual; 输出: $56,789.00 select to_char(56789,'FM$999,999.00') from dual; 输出:$56,789.00
FM与$结合使用时,FM要放在最前面
6)格式化字符“L”——本地货币
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select to_char(56789,'FML999,999.00') from dual; 输出:¥56,789.00
7)格式化字符“C”——货币种类符号
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select to_char(56789,'FM999,999.00C') from dual; 输出:56,789.00CNY
8)十进制转十六进制
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select to_char(255,'xxx') from dual; 输出: ff select to_char(255,'x') from dual; 输出:##
X的位数不能小于实际转换后的位数。
整理至来源于《Oracle入门很简单》一书。