数值及其他函数

数值函数

		**主要针对于数值做处理的函数**

1.取绝对值

ABS(NUM):获取数值NUM对应的绝对值。

示例：
1).取-2的绝对值

   SELECT ABS(-2) FROM DUAL;

2).MONTHS_BETWEEN(),不知道谁大，嵌套一个绝对值函数

   SELECT ABS(MONTHS_BETWEEN(SYSDATE,SYSDATE+31)) FROM DUAL;  

3).函数只有一个参数，不能为含有其他字符

   SELECT ABS(10) FROM DUAL;  
   SELECT ABS('10') FROM DUAL;
   SELECT ABS('10A') FROM DUAL;--报错  无效数字
   SELECT ABS(10,2) FROM DUAL;--报错 参数个数无效

2.向上取整与向下取整

CEIL(NUM) ：向上取整，获取离NUM相近的两个整数中较大的整数
FLOOR(NUM)：向下取整，获取离NUM相近的两个整数中较小的整数

示例：

SELECT FLOOR(1.3265) FROM DUAL;--1
SELECT CEIL(1.3265) FROM DUAL;--2

SELECT FLOOR(-1.3265) FROM DUAL;-- -2
SELECT CEIL(-1.3265) FROM DUAL;-- -1

3.ROUND()/TRUNC() 对数值进行四舍五入或截取

ROUND(NUM,V):将数值NUM按照四舍五入的原则保留V位小数
TRUNC(NUM,V):将数值NUM截取到第V位小数

个 十 百 千 万
十分位 百分位 千分位

–当精度为负数时，按照小数点前N为进行四舍五入、截取

示例：
1).对数据123.456做处理

SELECT ROUND(123.556) FROM DUAL;--124
SELECT ROUND(123.556,0) FROM DUAL;--124\
SELECT ROUND(123.556,2) FROM DUAL;--123.56

SELECT ROUND(123.556,-2) FROM DUAL;--100
SELECT ROUND(153.556,-2) FROM DUAL;--200
SELECT ROUND(123.556,-1) FROM DUAL;--120

SELECT TRUNC(123.556) FROM DUAL;--123
SELECT TRUNC(123.556,0) FROM DUAL;--123
SELECT TRUNC(123.556,2) FROM DUAL;--123.55

SELECT TRUNC(123.556,-2) FROM DUAL;--100
SELECT TRUNC(153.556,-2) FROM DUAL;--100
SELECT TRUNC(123.556,-1) FROM DUAL;--120

–既然两个函数可以对数值操作，可不可以对日期做操作？？ 如何进行的？？

4.ASCII码值

ASCII(N): 将字符N转化为ASCII码值
CHR(N)： 将ASCII码值N转化为字符

示例：

 SELECT ASCII('李') FROM DUAL;
 SELECT CHR(15113614) FROM DUAL;
 
 SELECT ASCII('李名') FROM DUAL;

5.取余函数

MOD(VALUE1,VALUE2) 取value1/value2 的余数

示例：
假设有四间教室，30名学生，怎样合理的将学生分配到教室中？

   SELECT MOD(8,5) FROM DUAL;--3

6.SQRT()一个参数,返回该参数的平方根.

示例：

SELECT SQRT(5) FROM DUAL;
SELECT SQRT(4) FROM DUAL;
SELECT SQRT(-4) FROM DUAL;--报错

7.POWER(X,Y)：幂指函数 X的Y次方

SELECT POWER(6,'L') FROM DUAL;--报错  无效数字
SELECT POWER(6,1) FROM DUAL;
SELECT POWER(6,2) FROM DUAL;

/其他函数/

1.ROWID/ROWNUM

ROWID:自动生成一串18位字符串，已知rowID，查询时效率比较高
ROWNUM:自动生成一组递增的数值

ROWID:18字符串，唯一的
SELECT EMP.*,ROWID FROM EMP;
SELECT * FROM EMP WHERE ROWID=‘AAAThdAAEAAAAHUAAA’;

数据去重

 SELECT *
   FROM EMP
  WHERE ROWID IN
        (SELECT MIN(ROWID) FROM EMP GROUP BY EMPNO, ENAME, SAL, JOB);

–多组重复的数据中，rowID不重复，利用分组取出一个最大或最小的ROWID，然后过让外表中的rowID=最大或最小的ROWID
–将重复的数据去掉

ROWNUM:一组递增的数组,不唯一的，只在当前语句起效

SELECT EMP.*,ROWNUM FROM EMP;
SELECT EMP.*,ROWNUM FROM EMP WHERE ROWNUM<=5;
SELECT EMP.*,ROWNUM FROM EMP WHERE ROWNUM>5;--无结果

–用于排序，查找数据

2.DISTINCT 去重函数

SELECT DISTINCT JOB FROM EMP;
SELECT DISTINCT JOB,SAL FROM EMP;

—去重，当且仅当全部重复时，才会去重，将多条重复数据保留为一条

3.CASE WHEN THEN END 判断函数

DECODE(COLUMN,VALUE1,VALUES1,VALUE2,VALUES2,VALUE3,VALUES3,VALUES) --往往用于等值比较

示例：
1.将10号部门的员工标记为10号部门，20/30均如此

SELECT EMP.*,CASE WHEN DEPTNO=10 THEN '10号部门'
                  WHEN DEPTNO=20 THEN '20号部门'
                  WHEN DEPTNO=30 THEN '30号部门' 
                    ELSE 
                      '1111'
                      END FROM EMP;  

---

   SELECT EMP.*,CASE DEPTNO WHEN 10 THEN '10号部门'
                      WHEN 20 THEN '20号部门'
                      WHEN 30 THEN '30号部门' 
                        ELSE 
                          '1111'
                          END FROM EMP;

---

SELECT EMP.*,DECODE(DEPTNO,10,'10号部门',20,'20号部门',30,'30号部门','211') FROM EMP;--只能用作等着比较

4.NVL()/NVL2()

NVL(COLUMN,VALUE):若当COLUMN列为非空时，值为其本身，为空时赋值VALUE；
NVL2(COLUMN1,VALUE1,VALUE2) 当COLUMN1列为非空时，赋值VALUE1，为空时赋值VALUE2；

示例：

 SELECT COMM, NVL(COMM,0) FROM  EMP;
 SELECT COMM, NVL2(COMM,'非空','空值') FROM  EMP;

5.返回集合中第一个不为空的内容

COALESCE(c1,c2,c3,c4,…cn):返回括号中第一个非空表达式，如果都为空，则返回空

示例：

  SELECT COALESCE(NULL,NULL,'A','B') FROM DUAL;