Oracle 将null值转化为其他值

-- Start

我个人认为数据库中不应该有null值，因为他颠覆了二值逻辑结构（即：真和假），出现了三值逻辑结构（即：真、假和未知）。由于null，我们的SQL语句很有可能出现意想不到的结果。此外null值和其他值进行数值运算的时候也会带来问题。但是，有时候有些事情并不是我们能够控制和改变的，作为一名真正的程序员，应该敢于面对最垃圾的数据库设计。下面给大家介绍如何将null值转化为其他值。

CREATE TABLE EMPLOYEE
(
    NAME      VARCHAR2(20) NOT NULL,   -- 姓名
    SALARY    NUMBER,                  -- 基本工资
    BONUS     NUMBER                   -- 奖金
);

INSERT INTO EMPLOYEE VALUES ('张三', 3000.0, NULL);
INSERT INTO EMPLOYEE VALUES ('李四', 4000.0, 0.0);
INSERT INTO EMPLOYEE VALUES ('王五', 5000.0, 1000.0);

-- 下面几条语句是等价的
SELECT CASE WHEN BONUS IS NULL THEN 0.0 ELSE BONUS END FROM EMPLOYEE;
SELECT COALESCE(BONUS, 0.0) FROM EMPLOYEE;
SELECT NVL(BONUS, 0.0) FROM EMPLOYEE;


-- 下面两条语句是等价的
SELECT CASE WHEN BONUS IS NOT NULL THEN BONUS ELSE 0.0 END FROM EMPLOYEE;
SELECT NVL2(BONUS, BONUS, 0.0) FROM EMPLOYEE;

假设你要查找总工资(基本工资+奖金)大于等于3000元的员工，我们很自然的会写出下面的语句：

SELECT * FROM EMPLOYEE WHERE SALARY + BONUS >= 3000.0;

但是很不幸，这条语句并不是永远正确，当SALARY或BONUS有一个值是null的时候，我们很可能会漏掉部分数据（会漏掉哪些数据呢？朋友们自己思考一下），这就是我认为数据库中不应该有null值的原因之一，如果你不是决策者，无法改变数据库设计，我们可以这样写：

SELECT * FROM EMPLOYEE WHERE NVL(SALARY, 0.0) + NVL(BONUS, 0.0) >= 3000.0;

有没有更简单的办法呢？答案是肯定的。

-- 如果条件是假或未知，LNNVL 函数返回真
SELECT * FROM EMPLOYEE WHERE LNNVL(SALARY + BONUS < 3000.0);

知道了如何将 null 转换成其他值，现在让你把其他值转成 null 该怎么办呢？呵呵试一试下面的语句吧。

SELECT NULLIF(BONUS,0.0) FROM EMPLOYEE;
SELECT CASE WHEN BONUS = 0.0 THEN NULL ELSE BONUS END FROM EMPLOYEE;

还有一个函数用来将 BINARY_FLOAT_NAN 和 BINARY_DOUBLE_NAN 转成其他值。

SELECT NANVL(BINARY_FLOAT_NAN, 0.0) FROM DUAL;
SELECT NANVL(BINARY_DOUBLE_NAN, 0.0) FROM DUAL;

-- 更多参见：Oracle SQL 精萃

-- 声明：转载请注明出处

-- Last Edited on 2015-06-19

-- Created by ShangBo on 2014-12-18

-- End