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

 SQL Fundamentals || Oracle SQL语言

SQL Fundamentals: Using Single-Row Functions to Customize Output使用单行函数自定义输出

SQL Fundamentals || Single-Row Functions || 字符函数 character functions

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

SQL Fundamentals || Single-Row Functions || 日期函数date functions

SQL Fundamentals || Single-Row Functions || 转换函数 Conversion function

SQL Fundamentals || Single-Row Functions || 通用函数 General function

             数字函数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