如何在Oracle CASE语句中使用IS NULL条件？

你原来的CASE写法错误在于：CASE语句是返回一个具体值，而非拼接条件表达式。你试图用CASE生成IS NULL这样的条件关键字，但静态SQL会把它当成字符串值和EMP_ID比较，这显然不符合逻辑，导致语句无法正确运行。

以下是两种可行的修正方案：

方案一：直接用逻辑运算符组合条件（推荐） #

这种写法逻辑清晰，数据库也更容易做执行计划优化：

DECLARE EMP_ID_NULL VARCHAR2(1);
DECLARE EMP_ID_VAL NUMBER;
SELECT * FROM EMPLOYEE 
WHERE 
    (EMP_ID_NULL = 'N' AND EMP_ID IS NULL)
    OR 
    (EMP_ID_NULL != 'N' AND EMP_ID = EMP_ID_VAL);

如果需要处理EMP_ID_NULL为NULL的情况（比如默认走ELSE分支），可以调整为：

DECLARE EMP_ID_NULL VARCHAR2(1);
DECLARE EMP_ID_VAL NUMBER;
SELECT * FROM EMPLOYEE 
WHERE 
    (NVL(EMP_ID_NULL, 'Y') = 'N' AND EMP_ID IS NULL)
    OR 
    (NVL(EMP_ID_NULL, 'Y') != 'N' AND EMP_ID = EMP_ID_VAL);

方案二：用CASE构造可比较的逻辑值 #

如果一定要用CASE语句，可以让CASE返回标识值（比如1/0），再通过比较标识值来筛选数据：

DECLARE EMP_ID_NULL VARCHAR2(1);
DECLARE EMP_ID_VAL NUMBER;
SELECT * FROM EMPLOYEE 
WHERE 
    CASE 
        WHEN EMP_ID_NULL = 'N' THEN 
            CASE WHEN EMP_ID IS NULL THEN 1 ELSE 0 END
        ELSE 
            CASE WHEN EMP_ID = EMP_ID_VAL THEN 1 ELSE 0 END
    END = 1;

或者利用NVL将NULL转换为一个EMP_ID不会出现的特殊值，再和CASE返回值比较（假设EMP_ID不会取-1）：

DECLARE EMP_ID_NULL VARCHAR2(1);
DECLARE EMP_ID_VAL NUMBER;
SELECT * FROM EMPLOYEE 
WHERE 
    NVL(EMP_ID, -1) = 
        CASE 
            WHEN EMP_ID_NULL = 'N' THEN -1
            ELSE EMP_ID_VAL 
        END;

内容的提问来源于stack exchange，提问作者Pat