我如何使值0是空的ORACLE SQL?

Question

我有一个空值表，如下所示。如何将此表中的空值转换为0?

<表类> 工作工资tbody><<tr>-B15000C-

使用NVL函数

select jobs, nvl(salary, 0) as salary
from your_table

或COALESCE

select jobs, coalesce(salary, 0) as salary
from your_table

或DECODE

select jobs, decode(salary, null, 0, salary) as salary
from your_table

或CASE

select jobs, case when salary is null then 0 else salary end as salary
from your_table

---

例如(基于scott的EMP表):

SQL> select
2    ename,
3    comm,
4    nvl(comm, 0) comm_1,
5    coalesce(comm, 0) comm_2,
6    decode(comm, null, 0, comm) comm_3,
7    case when comm is null then 0 else comm end comm_4
8  from emp;
ENAME            COMM     COMM_1     COMM_2     COMM_3     COMM_4
---------- ---------- ---------- ---------- ---------- ----------
SMITH                          0          0          0          0
ALLEN             300        300        300        300        300
WARD              500        500        500        500        500
JONES                          0          0          0          0
MARTIN           1400       1400       1400       1400       1400
BLAKE                          0          0          0          0
<snip>

您可以应用DDL语句使列

的更改永久生效。

ALTER TABLE tab 
MODIFY salary DEFAULT 0;