1，abs(n) 返回n的绝对值
- SQL> select abs(-1),abs(1) from dual;
- ABS(-1) ABS(1)
- ---------- ----------
- 1 1
2，ceil(n) 返回大于等于n的最小整数
- SQL> select ceil(10),ceil(10.5),ceil(-10.5) from dual;
- CEIL(10) CEIL(10.5) CEIL(-10.5)
- ---------- ---------- -----------
- 10 11 -10
3，floor(n) 返回小于等于n的最大整数
- SQL> select floor(10),floor(10.5),floor(-10.5) from dual;
- FLOOR(10) FLOOR(10.5) FLOOR(-10.5)
- ---------- ----------- ------------
- 10 10 -11
4，greatest(expr[,expr...])返回参数列表的最大值，可用于数字，字符串，日期
- SQL> select greatest(1,2,3) from dual;
- GREATEST(1,2,3)
- ---------------
- 3
- SQL> select greatest('a','b') from dual;
- GR
- --
- b
- SQL> select greatest(to_date('2011','yyyy'),to_date('2012','yyyy')) from dual;
- GREATEST(TO_DA
- --------------
- 01-11月-12
如果参数列表为混合类型，Oracle在选择最大值之前将选择第一个参数类型作为基准类型并试图将其他参数转换为第一个参数的类型
- SQL> select greatest(1,'abc') from dual;
- select greatest(1,'abc') from dual
- *
- ERROR at line 1:
- ORA-01722: invalid number
- SQL> select greatest(1,'111') from dual;
- GREATEST(1,'111')
- -----------------
- 111
5，least(expr[,expr...]) 选择参数列表最小值，和greatest函数相反，参数规则相同
6，mod(m,n) m除以n的余数
- SQL> select mod(11,10) from dual;
- MOD(11,10)
- ----------
- 1
7，power(m,n) m的n次方，m为非0数字，如果m为正数，n可以为正数或负数，如果m为负数，n必须为正整数
- SQL> select power(2,2),power(-1,2),power(1,0) from dual;
- POWER(2,2) POWER(-1,2) POWER(1,0)
- ---------- ----------- ----------
- 4 1 1
- SQL> select power(-2,0.5) from dual;
- select power(-2,0.5) from dual
- *
- ERROR at line 1:
- ORA-01428: argument '-2' is out of range
8，round(m,n) 对十进制数字m，根据n进行四舍五入计算
- SQL> select round(123.5),round(123.46,1),round(126.45,-1) from dual;
- ROUND(123.5) ROUND(123.46,1) ROUND(126.45,-1)
- ------------ --------------- ----------------
- 124 123.5 130
9，sign(n) 返回n的符号，n为负数返回-1，n为0返回0，n为正数返回1
- SQL> select sign(-11),sign(0),sign(99) from dual;
- SIGN(-11) SIGN(0) SIGN(99)
- ---------- ---------- ----------
- -1 0 1
10，sqrt(n) n的平方根，n不能为负数
- SQL> select sqrt(4),sqrt(0),sqrt(80) from dual;
- SQRT(4) SQRT(0) SQRT(80)
- ---------- ---------- ----------
- 2 0 8.94427191
- SQL> select sqrt(-3) from dual;
- select sqrt(-3) from dual
- *
- ERROR at line 1:
- ORA-01428: argument '-3' is out of range
11，trunc(m[,n]) 根据指定的位数n截取数字m（只截取，不进行四舍五入）
- SQL> select trunc(999.99),trunc(999.99,1),trunc(999.99,-2) from dual;
- TRUNC(999.99) TRUNC(999.99,1) TRUNC(999.99,-2)
- ------------- --------------- ----------------
- 999 999.9 900
REFS:http://blog.csdn.net/a19881029/article/details/8161323
