oracle学习(五)--数字类型操作函数

oracle学习(五)--数字类型操作函数

--绝对值
SELECT "ABS"(-123) FROM dual;-->123


--向上取整
SELECT "CEIL"(123.22) FROM dual;-->124
SELECT "CEIL"(-123.22) FROM dual;-->-123


--向下取整
SELECT "FLOOR"(123.22) FROM dual;-->123
SELECT "FLOOR"(-123.22) FROM dual;-->-124


--取余数
SELECT "MOD"(5, 2) FROM dual;-->1


--判断正负
SELECT "SIGN"(12) FROM dual;-->1
SELECT "SIGN"(-12) FROM dual;-->-1


--开平方
SELECT "SQRT"(16) FROM dual;-->4


--幂
SELECT "POWER"(3, 4) FROM dual;-->81


--取固定位数
SELECT "TRUNC"(12.123, 2) FROM dual;-->12.12
SELECT "TRUNC"(12.123) FROM dual;-->12


--根据ASCII码返回字符
SELECT "CHR"(65) FROM dual;-->A


--四舍五入
SELECT "ROUND"(467.37, 1) FROM dual;-->467.40
SELECT "ROUND"(467.37, -1) FROM dual;-->470
SELECT "ROUND"(467.37) FROM dual;-->467
SELECT "ROUND"(467.57) FROM dual;-->468


--格式化数字
SELECT "TO_CHAR"(12.78,'000.000') FROM dual;--> 012.780
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
SELECT "TO_CHAR"(1234567980123,'9,999,999,999,990.00') FROM dual;--> 1,234,567,980,123.00


--FM用于去掉多余的空格和0
SELECT "TO_CHAR"(12.78,'FM000.000') FROM dual;-->012.780
SELECT "TO_CHAR"(12.78,'FM999.999') FROM dual;-->12.78
SELECT "TO_CHAR"(0.78,'FM999.999') FROM dual;-->.78
SELECT "TO_CHAR"(0.78,'FM990.999') FROM dual;-->0.78
SELECT "TO_CHAR"(1234567980123,'FM9,999,999,999,990.00') FROM dual;-->1,234,567,980,123.00


--美元符号
SELECT "TO_CHAR"(12.78,'FM$000.000') FROM dual;-->$012.780
SELECT "TO_CHAR"(12.78,'FM$999.999') FROM dual;-->$12.78
SELECT "TO_CHAR"(0.78,'FM$999.999') FROM dual;-->$.78
SELECT "TO_CHAR"(0.78,'FM$990.999') FROM dual;-->$0.78
SELECT "TO_CHAR"(1234567980123,'FM$9,999,999,999,990.00') FROM dual;-->$1,234,567,980,123.00


--本地钱字符
SELECT "TO_CHAR"(12.78,'FML000.000') FROM dual;-->¥012.780
SELECT "TO_CHAR"(12.78,'FML999.999') FROM dual;-->¥12.78
SELECT "TO_CHAR"(0.78,'FML999.999') FROM dual;-->¥.78
SELECT "TO_CHAR"(0.78,'FML990.999') FROM dual;-->¥0.78
SELECT "TO_CHAR"(1234567980123,'FML9,999,999,999,990.00') FROM dual;-->¥1,234,567,980,123.00


--返回数据库所在地区的货币码
SELECT "TO_CHAR"(12.78,'FM000.000C') FROM dual;-->012.780CNY
SELECT "TO_CHAR"(12.78,'FM999.999C') FROM dual;-->12.78CNY
SELECT "TO_CHAR"(0.78,'FM999.999C') FROM dual;-->.78CNY
SELECT "TO_CHAR"(0.78,'FM990.999C') FROM dual;-->0.78CNY
SELECT "TO_CHAR"(1234567980123,'FM9,999,999,999,990.00C') FROM dual;-->1,234,567,980,123.00CNY


--转换成16进制
SELECT "TO_CHAR"(255,'FMx') FROM dual;-->##
SELECT "TO_CHAR"(255,'FMxx') FROM dual;-->ff
SELECT "TO_CHAR"(255,'FMxxxxxxxxxx') FROM dual;-->ff

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/wild46cat/article/details/51554904
上一篇oracle学习(四)--数字型number
下一篇设计模式之简单工厂
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭