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.926，2)

45.93

TRUNC(45.926，2)

45.92

MOD(1600，300)

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  寻香径  阅读(342)  评论(0)  编辑  收藏  举报