oracle常用数值函数总结

to_number()
该函数将一个字符串转化为数字。

语法为:
to_number( string1, [ format_mask ], [ nls_language ] )

参数说明:
string1 is the string that will be converted to a number.

format_mask is optional. This is the format that will be used to convert string1 to a number.

nls_language is optional. This is the nls language used to convert string1 to a number.

示例:
to_number(‘1210.73’, ‘9999.99’) would return the number 1210.73
to_number(‘546’, ‘999’) would return the number 546
to_number(’23’, ’99’) would return the number 23

Since the format_mask and nls_language parameters are optional, you can simply convert a text string to a numeric value as follows:

to_number(‘1210.73”) would return the number 1210.73

trunc(for number)

TRUNC函数返回处理后的数值,其工作机制与ROUND函数极为类似,只是该函数不对指定小数前或后的部分做相应舍入选择处理,而统统截去。

其具体的语法格式如下

TRUNC(number[,decimals])

其中:

number 待做截取处理的数值

decimals 指明需保留小数点后面的位数。可选项,忽略它则截去所有的小数部分
下面是该函数的使用情况:

TRUNC(89.985,2)=89.98

TRUNC(89.985)=89

TRUNC(89.985,-1)=80

ceil()
取大于等于数值n的最小整数

语法为:
ceil( number )

示例:
ceil(32.65) would return 33.
ceil(32) would return 32.
ceil(-32.65) would return -32.
ceil(-32) would return -32.

floor()
取小于等于数值n的最大整数

语法:
floor( number )

示例:
floor(5.9) would return 5
floor(34.29) would return 34
floor(-5.9) would return -6

round()
传回一个数值,该数值是按照指定的小数字元数进行四舍五入运算的结果。

语法:
round( number, [ decimal_places ] )

参数说明:
decimal_places is the number of decimal places rounded to. This value must be an integer. If this parameter is omitted, the round function will round the number to 0 decimal places.

示例:
round(125.315) would return 125
round(125.315, 0) would return 125
round(125.315, 1) would return 125.3
round(125.315, 2) would return 125.32
round(125.315, 3) would return 125.315
round(-125.315, 2) would return -125.32

此条目发表在mysql/oracle/postgresql分类目录,贴了, , 标签。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据