（转）oracle常用函数一：数字函数

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