SQL Fundamentals || Single-Row Functions || 数字函数number functions

 SQL Fundamentals || Oracle SQL语言

 

             数字函数number functions

 


  • Number functions - Accepts numeric input and returns numeric values. Functions under the category are ROUND, TRUNC, and MOD.
    • ROUND and TRUNC functions are used to round and truncate the number value.
    • MOD is used to return the remainder of the division operation between two numbers.

function

result

ROUND

Rounds value to a specified decimal

四舍五入

TRUNC

Truncates value to a specified decimal

截断一个值到一定的小数位,不管大于5小于5

MOD

Returns remainder of division

返回一个除数的余数

 

function

result

ROUND(45.9262)

45.93

TRUNC(45.9262)

45.92

MOD(1600300)

100

 

function

purpose

ROUND(column | expression,n)

Rounds (四舍五入)the column, expression, or value to n decimal places(小数位) or, if n is omitted(省略), no decimal places (if n degative, numbers to the left of decimal point are rounded.)

如果n是负数,小数点左边四舍五入

SQL> SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1) FROM DUAL;

 

ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)

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

          45.92              46               50

 

TRUNC(column | expression,n)

Truncates(截断) the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero.

Like the ROUND function, the TRUC function can be used with date functions.

TRUNC函数也可以用作日期函数.

MOD(m,n)

Returns the remainder(余数) of m divided by n

Note : The MOD function is often used to determine whether a value is odd or even.

The MOD function is also the ORACLE hash function.

MOD函数经常被用于判断一个值是奇数还是偶数.


 

对数字进行处理,例如:四舍五入;

函数名称

描述

ROUND(数字 [,保留位数])

对小数进行四舍五入,可以指定保留位数,如果不指定,则表示将小数点之后的数字全部进行四舍五入

SELECT ROUND(789.652),ROUND(789.652,2),ROUND(789.652,-1) FROM DUAL;

ROUND(789.652) ROUND(789.652,2) ROUND(784.652,-1)

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

           790           789.65               780

 

以上分别是不保留小数,保留两位小数,处理整数进位.

SELECT ename,job,sal,ROUND(sal/30,2) FROM emp;

TRUNC(数字 [,截取位数])

保留指定位数的小数,如果不指定,则表示不保留小数

SQL> SELECT TRUNC(789.652),TRUNC(789.652,2),TRUNC(789.652,-2) FROM DUAL;

 

TRUNC(789.652) TRUNC(789.652,2) TRUNC(789.652,-2)

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

           789           789.65               700

MOD(数字,数字)

取模,求余数

 

SQL> SELECT MOD(10,3) FROM DUAL;

 

 MOD(10,3)

----------

         1

 

 

 

posted @ 2017-07-24 17:00  寻香径  阅读(343)  评论(0编辑  收藏  举报