ORACLE中sql语句常用的函数

--向下取整
select floor(5.534) from dual          结果：5
union all
select trunc(5.534) from dual;        结果：5

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

--向上取整
select ceil(5.534) from dual;          结果：6

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

 

--四舍五入
select round(5.534) from dual     --四舍五入                                  结果：6
union all
select round(5.534,-1) from dual  --四舍五入取整数十位值        结果：10
union all
select round(5.534,0) from dual   --四舍五入取整数                     结果：6
union all
select round(5.534,1) from dual   --四舍五入取小数位为1位      结果：5.5
union all
select round(5.534,2) from dual   --四舍五入取小数位为2位      结果：5.53
union all
select round(5.534,3) from dual   --四舍五入取小数位为3位      结果：5.534
union all
select round(5.534,4) from dual   --四舍五入取小数位为4位，如果位数多余，还取原值     结果：5.534

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

--保留N位小数，不四舍五入
select trunc(5.534,0) from dual  --取整数                     结果：5
union all
select trunc(5.534,1) from dual  --取小数位为1位        结果：5.5
union all
select trunc(5.534,2) from dual  --取小数位为2位        结果：5.53
union all
select trunc(5.534,3) from dual  --取小数位为3位        结果：5.534
union all
select trunc(5.534,4) from dual  --取小数位为4位，如果位数多余，还取原值        结果：5.534
------------------------------------------------------------------------------------------------------------

--数字格式化  to_char
--不足补零。
select to_char(12345.123,'99999999.9999') from dual        结果：12345.1230
union all
select to_char(12345.123,'99999999.9900') from dual        结果：12345.1230
union all
select to_char(0.123,'99999999.9900') from dual                 结果：.1230
union all
select to_char(0.123,'99999990.9999') from dual                 结果：0.1230