Oracle对空值(NULL)的5种处理方式[详细]

我不是女神ヾ 2024-03-24 19:43 67阅读 0赞

#### 文章目录 ####

*  总结

--------------------

*  数据库中的空值（NULL）经常会导致一些不可预知的错误，我们需要使用数据库提供的方法对空值进行处理，确保数据查询的准确性。

如下表所示，红色标记的值为空值。

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5qKB6JCM_size_20_color_FFFFFF_t_70_g_se_x_16][]

分别使用5种方法对数据库的空值进行判断和处理：

#### 1.使用COALESCE函数 ####

COALESCE（expr1，expr2，expr3，…）函数接受一个输入参数的列表，返回第1个非空的参数。如果所有的参数都为空，则返回空值。

**案例：**

假如我们现在需要查找员工的联系电话，查找的规则如下：先找移动电话；如果没有移动电话，就找工作电话；如果没有工作电话，就找家庭电话；如果没有家庭电话，就找紧急联系人电话；如果以上电话都没有找到，则返回“N/A”。

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5qKB6JCM_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

实现SQL：

SELECT t.emp_id,
     COALESCE(t.mobile_phone,t.work_phone,t.home_phone,t.emergency_phone,'N/A') AS phone
     FROM emp_contact t;

**执行结果：**

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5qKB6JCM_size_20_color_FFFFFF_t_70_g_se_x_16 2][]

#### 2.使用NULLIF函数 ####

NULLIF（expr1，expr2）函数接受两个输入参数，如果第1个参数等于第2个参数，返回空值；否则，返回第1个参数的值。

实例SQL：

SELECT NULLIF(1,2),NULLIF(2,2)
     FROM DUAL;

**执行结果：**

![2495e93f5d114e0882714c28449cc774.png][]

1和2不相等，故返回第一个参数值1。

2和2相等，故返回空。

NULLIF函数的一个常见用途是防止除零错误，例如：

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5qKB6JCM_size_20_color_FFFFFF_t_70_g_se_x_16 3][]

通过NULLIF函数处理后就不会报错了：

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5qKB6JCM_size_20_color_FFFFFF_t_70_g_se_x_16 4][]

#### 3.使用CASE表达式 ####

**案例：**

查询部门ID为2的部门员工全年收入（包括工资和奖金）

实现SQL：

SELECT t.emp_name,
      t.salary AS "工资",
      t.bonus AS "奖金",
      CASE WHEN t.bonus IS NULL THEN t.salary*12 
           ELSE  t.salary*12 + t.bonus 
      END AS "全年收入"    
    FROM employee t WHERE t.dept_id=2;

**执行结果：**  
![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5qKB6JCM_size_20_color_FFFFFF_t_70_g_se_x_16 5][]

#### 4.使用NVL（expr1，expr2）函数 ####

NVL（expr1，expr2）函数返回第1个非空的参数值，等价于只有两个参数的COALESCE函数。

**案例：**

查询部门ID为2的部门员工全年收入（包括工资和奖金）

在不使用NVL函数对空值进行处理的情况下，会查询出错误的结果：

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5qKB6JCM_size_20_color_FFFFFF_t_70_g_se_x_16 6][]

使用NVL函数对奖金的值进行处理

实现SQL：

SELECT t.emp_name,
    t.salary AS "工资",t.bonus AS "奖金",
    t.salary*12 + NVL(t.bonus,0) AS "全年收入"
    FROM employee t WHERE t.dept_id=2;

**执行结果：**

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5qKB6JCM_size_20_color_FFFFFF_t_70_g_se_x_16 7][]

这个结果才是我们正确的，即使奖金为空，也不影响全年收入的出值。

#### 5.使用NVL2（expr1，expr2，expr3）函数 ####

NVL2（expr1，expr2，expr3）函数包含3个参数，如果第1个参数不为空，返回第2个参数的值；否则，返回第3个参数的值。

**案例：**

查询部门ID为2的部门员工全年收入（包括工资和奖金）

实现SQL：

SELECT t.emp_name,
    t.salary AS "工资",t.bonus AS "奖金",
    NVL2(t.bonus,t.salary*12 + t.bonus,t.salary*12 ) AS "全年收入"
    FROM employee t WHERE t.dept_id=2;

**执行结果：**

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5qKB6JCM_size_20_color_FFFFFF_t_70_g_se_x_16 8][]

\---

## 总结 ##

> 如果此篇文章有帮助到您, 希望打大佬们能`关注`、`点赞`、`收藏`、`评论`支持一波，非常感谢大家！  
> 如果有不对的地方请指正!!!

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5qKB6JCM_size_20_color_FFFFFF_t_70_g_se_x_16]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/7d93208bc58747d2a4c3d86f05332f8e.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5qKB6JCM_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/f3cc290dbe4747c883993acc4bbd53a1.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5qKB6JCM_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/29a613df3e0c4217a63bab89647c849f.png
[2495e93f5d114e0882714c28449cc774.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/e1cc747d242f4d85a7c4c9357c69bc56.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5qKB6JCM_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/14db46ac1502456eb338cb908b486694.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5qKB6JCM_size_20_color_FFFFFF_t_70_g_se_x_16 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/b2b3cabc41914c16a36d5e33140d5701.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5qKB6JCM_size_20_color_FFFFFF_t_70_g_se_x_16 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/9d4ff1e2f8d14248881e12952b495bb5.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5qKB6JCM_size_20_color_FFFFFF_t_70_g_se_x_16 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/686bb19a3bb44f84b5e02dc34d5fa0ad.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5qKB6JCM_size_20_color_FFFFFF_t_70_g_se_x_16 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/47579114e702432aa98267fd11e048af.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5qKB6JCM_size_20_color_FFFFFF_t_70_g_se_x_16 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/4e52691bee46471b8bd47dc54470984a.png