关闭

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

209人阅读 评论(0) 收藏 举报
分类:

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

1
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:311028次
    • 积分:7525
    • 等级:
    • 排名:第2972名
    • 原创:442篇
    • 转载:12篇
    • 译文:7篇
    • 评论:36条
    文章分类
    最新评论