看完这章你会学会以下内容：

  • NVL和NVL2语法和常用实例
  • DECODE 和 CASE WHEN 的区别

1.NVL函数的格式如下：NVL(expr1,expr2)
含义：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

 SELECT NVL(NULL,0),     -- 空转0       NVL(NULL,100),   -- 空转100       NVL('', 99),     -- 空字符转99       NVL(10, 100),    -- 非空不会转       NVL('AD',77)     -- 非空不会转    FROM DUAL;

2.NVL2函数的格式如下：NVL2(expr1,expr2, expr3)
含义：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。

 SELECT NVL2(NULL, 0, 1),       -- 空转1       NVL2('', 99, 2),        -- 空字符转2       NVL2(10, 3, 100),       -- 非空转3       NVL2('AD', 'AD', 7)     -- 非空转'AD'    FROM DUAL;

3.应用实例：查看员工信息和其年薪（薪酬+奖金)

SELECT E.*,(SAL + NVL(COMM,0))*12 AS 年薪 FROM EMP E

4.应用实例二：对EMP表中的奖金为空的转换为100,不为空的转换为NULL;

SELECT E.*,NVL2(COMM,NULL,100)AS 奖金 FROM EMP E;

3.DECODE(value,if1,then1,if2,then2,if3,then3,...,else) 含义：如果value与第一个值相等，则与第一个值的匹配值，以此类推，最后都没有的，就返回默认值。
如果有匹配值，则返回对应的匹配值。
如果没有匹配值，则返回空或者默认值。

IF 条件=值1 THEN
RETURN(value 1)
ELSIF 条件=值2 THEN
RETURN(value 2)
......
ELSIF 条件=值n THEN
RETURN(value 3)
ELSE
RETURN(default)
END IF

 SELECT DECODE(12,    -- 原始值           1, 2,           12, 10),    -- 10       DECODE(12,            1, 2,           13, 10),    -- 空       DECODE(12,            1, 2,           13, 10,            7),        -- 给定默认值7       DECODE(12,            1, 2,           12, 10,     -- 找到后就返回值,然后退出,不会向下找          12, 88,           7)   FROM DUAL;

3.1经典案例： 用英文部门名称返回中文部门，然后匹配该部门下的人数

 -- ACCOUNTING   会计部 -- RESEARCH     研究部 -- SALES        销售部 -- OPERATIONS   操作部     SELECT DECODE(DNAME,           'ACCOUNTING','会计部',           'RESEARCH','市场调查部',           'SALES','销售部',           'OPERATIONS','操作部'           ) AS 部门名称,      COUNT(E.EMPNO) AS 部门人数     FROM DEPT D     LEFT JOIN EMP E     ON E.DEPTNO = D.DEPTNO    GROUP BY D.DNAME;

5. CASE WHEN 多条件判断
CASE [COLUMN_NAME] 语法：
WHEN ['条件参数'] THEN ['COLUMN_NAME/显示内容']
WHEN ['条件参数'] THEN ['COLUMN_NAME/显示内容']
......
ELSE ['COLUMN_NAME/显示内容']
END

5.1经典案例：部门名称的转换 用中文对应相应的中文
*与DECODE函数等效，对单字段等值判断。

SELECT DEPT.*,       (CASE       WHEN DNAME = 'ACCOUNTING' THEN '会计部'       WHEN DNAME = 'RESEARCH' THEN '研究部'       WHEN DNAME = 'SALES' THEN '销售部'       ELSE '操作部'       END) AS 部门    FROM DEPT;

5.2经典案例：按照指定的条件，对薪酬划分等级。
*与DECODE函数不同，可以对多字段匹配，而且不需要等值匹配。

SELECT E.*      ,CASE WHEN SAL >=4000 THEN '富'      WHEN SAL>=2500 THEN '良'      WHEN SAL>=1000 THEN '中'      ELSE  '穷'      END AS salgrade       FROM EMP E

5.3经典案例更新数据：job为'CLERK' 转为'职员',将其工资增加10%,

                           'SALESMAN' 转为'销售员',将其工资增加5%,                            'MANAGER' 转为'经理',将其工资增加2000,                            'ANALYST' 转为'分析师',将其工资增加6%,                           'PRESIDENT' 转为'老板',其工资不变;            更新数据： update emp set job =(case when job='CLERK' then '职员'                                    when job='SALESMAN' then '销售员'                                    when job='MANAGER' then '经理'                                    when job='ANALYST' then '分析师'                                    when job='PRESIDENT' then '老板'                                    else job                                    end）as 岗位,                           sal= (case when job='CLERK' then sal*(1+0.1)                             when job='SALESMAN' then sal*(1+0.05)                             when job='PRESIDENT' then sal+2000                             when job='MANAGER' then sal*(1+0.06)                             else sal                              end）as 工资                         from emp;   select emp.*,(case when job='CLERK' then '职员'              when job='SALESMAN' then '销售员'              when job='MANAGER' then '经理'              when job='ANALYST' then '分析师'              when job='PRESIDENT' then '老板'              end）as 岗位,          (case when job='CLERK' then sal*(1+0.1)              when job='SALESMAN' then sal*(1+0.05)              when job='PRESIDENT' then sal+2000              when job='MANAGER' then sal*(1+0.06)              when job='ANALYST' then sal              end）as 工资          from emp;