oracle单行函数之nvl、nvl2、nullif、coalesce、case、decode函数

1、单行函数分类：一行记录，返回一行结果；

2、其他常用函数：空值处理函数

测试数据如下：

1）nvl(a,b)：a为空，就用b值填充。

• a是什么数据类型，填充值b就必须是相同的数据类型。

SQL> select id,nvl(id,'0'),
  2  name,nvl(name,'哈哈'),
  3  comm,nvl(comm,0)
  4  from test;
 
ID                   NVL(ID,'0')          NAME                 NVL(NAME,'哈哈')          COMM NVL(COMM,0)
-------------------- -------------------- -------------------- -------------------- --------- -----------
1                    1                    唐僧                 唐僧                                     0
                     0                    孙悟空               孙悟空                  400.00         400
3                    3                                         哈哈                    200.00         200
4                    4                    刘备                 刘备                    300.00         300
                     0                                         哈哈                    200.00         200
6                    6                                         哈哈                                     0
 
6 rows selected

2）nvl2(a,b,c)：a为空，返回c，否则返回b。

SQL> select nvl2(name,name,'无名氏'),sal,comm,sal+nvl2(comm,comm,0)"总工资" from test;
 
NVL2(NAME,NAME,'无名氏')       SAL      COMM        总工资
------------------------ --------- --------- ----------
唐僧                       1200.00                 1200
孙悟空                     1500.00    400.00       1900
无名氏                     1300.00    200.00       1500
刘备                       1400.00    300.00       1700
无名氏                     1200.00    200.00       1400
无名氏                     1300.00                 1300
 
6 rows selected

3）nullif(a,b)：比较a和b是否相等，相等返回空，不相等返回a。

SQL> select nullif(2,2) from dual;
 
NULLIF(2,2)
-----------
 
SQL> select nullif(2,5) from dual;
 
NULLIF(2,5)
-----------
          2

4）coalesce(a,b,c,d…)：可以传入n个参数，返回第一个不为空的值。

-从左到右依次检测a,b,c,d哪一个不为空。当遇到第一个不为空的值，就返回该值。

SQL> select coalesce(null,null,3,null,4,5) from dual;
 
COALESCE(NULL,NULL,3,NULL,4,5)
------------------------------
                             3
                             
SQL> select sal,comm,sal+coalesce(comm,0)"总工资" from test;
 
      SAL      COMM        总工资
--------- --------- ----------
  1200.00                 1200
  1500.00    400.00       1900
  1300.00    200.00       1500
  1400.00    300.00       1700
  1200.00    200.00       1400
  1300.00                 1300
 
6 rows selected

3、case when函数

• 判断工资等级：[1000,1300)及格；[1300,1500)中等；[1500,2000)优秀；

----------------------- 第一种使用方式
SQL> select sal,
  2  case
  3  when sal>=1500 and sal<2000 then '优秀'
  4  when sal>=1300 and sal<1500 then '中等'
  5  when sal>=1000 and sal<1300 then '及格'
  6  end "工资等级"
  7  from test;
 
      SAL 工资等级
--------- --------
  1200.00 及格
  1500.00 优秀
  1300.00 中等
  1400.00 中等
  1200.00 及格
  1300.00 中等
 
6 rows selected
----------------------- 第二种使用方式
SQL> select sal,
  2  case when sal>=1000 and sal<1300 then 1 else 0 end "及格",
  3  case when sal>=1300 and sal<1500 then 1 else 0 end "中等",
  4  case when sal>=1500 and sal<2000 then 1 else 0 end "优秀"
  5  from test;
 
      SAL         及格         中等         优秀
--------- ---------- ---------- ----------
  1200.00          1          0          0
  1500.00          0          0          1
  1300.00          0          1          0
  1400.00          0          1          0
  1200.00          1          0          0
  1300.00          0          1          0

4、decode函数：常与sign()符号函数合起来使用。

• 语法：decode(value,if1,then1,if2,then2,if3,then3,…,else)

1）等值判断

SQL> select
  2      decode(name,null,'无名氏',name),
  3      sal
  4  from test;
 
DECODE(NAME,NULL,'无名氏',NAME                 SAL
---------------------------------------- ---------
唐僧                                       1200.00
孙悟空                                     1500.00
无名氏                                     1300.00
刘备                                       1400.00
无名氏                                     1200.00
无名氏                                     1300.00
 
6 rows selected
 
SQL> select
  2      decode(name,null,'无名氏','唐僧','唐僧1','孙悟空','孙悟空1','刘备','刘备1'),
  3      sal
  4  from test;
 
DECODE(NAME,NULL,'无名氏','唐?       SAL
------------------------------ ---------
唐僧1                            1200.00
孙悟空1                          1500.00
无名氏                           1300.00
刘备1                            1400.00
无名氏                           1200.00
无名氏                           1300.00
 
6 rows selected

2）非等值判断

• 讲述这个案例之前，先来说明符号函数sign(x)的作用。
• 当x < 0，返回结果是-1；
• 当x = 0，返回结果是0；
• 当x > 0，返回结果是1；

SQL> select sign(-5) from dual;
 
  SIGN(-5)
----------
        -1
 
SQL> select sign(0) from dual;
 
   SIGN(0)
----------
         0
 
SQL> select sign(5) from dual;
 
   SIGN(5)
----------
         1

• 判断工资等级：<1300)不及格；[1300,1500)中等；>=1500优秀；

SQL> select sal,
  2  decode(sign(sal-1300),
  3      -1,
  4      '不及格',
  5      0,
  6      '中等',
  7      1,
  8      decode(sign(sal-1500),
  9          -1,
 10          '中等',
 11          0,
 12          '优秀',
 13          1,
 14          '优秀'))
 15  from test;
 
      SAL DECODE(SIGN(SAL-1300),-1,'不及
--------- ------------------------------
  1200.00 不及格
  1500.00 优秀
  1300.00 中等
  1400.00 中等
  1200.00 不及格
  1300.00 中等
 
6 rows selected