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

举报
数据分析与统计学之美 发表于 2021/09/29 10:56:20 2021/09/29
【摘要】 Oracle第五课

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
【版权声明】本文为华为云社区用户原创内容,未经允许不得转载,如需转载请自行联系原作者进行授权。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

举报
请填写举报理由
0/200