Oracle DECODE 与 CASE 表达式

适用版本：Oracle Database 8i / 9i / 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

条件表达式[1]：

表达式	说明
CASE	ANSI 标准
DECODE	Oracle 专有

---

2. CASE 表达式

2.1 简单 CASE

SELECT 
  name,
  CASE dept_id
    WHEN 10 THEN 'IT'
    WHEN 20 THEN 'HR'
    WHEN 30 THEN 'Sales'
    ELSE 'Other'
  END AS dept_name
FROM employees;

2.2 搜索 CASE

SELECT 
  name,
  salary,
  CASE 
    WHEN salary < 3000 THEN 'Low'
    WHEN salary < 6000 THEN 'Medium'
    WHEN salary < 10000 THEN 'High'
    ELSE 'Top'
  END AS salary_level
FROM employees;

2.3 CASE 在 WHERE

SELECT * FROM employees
WHERE (CASE 
  WHEN dept_id = 10 THEN salary
  ELSE 0
END) > 5000;

2.4 CASE 在 ORDER BY

SELECT * FROM employees
ORDER BY 
  CASE dept_id
    WHEN 10 THEN 1
    WHEN 20 THEN 2
    ELSE 3
  END,
  salary DESC;

2.5 CASE 在 UPDATE

UPDATE employees
SET salary = CASE 
  WHEN dept_id = 10 THEN salary * 1.1
  WHEN dept_id = 20 THEN salary * 1.05
  ELSE salary
END;

2.6 CASE 在 AGGREGATE

-- 条件计数
SELECT 
  dept_id,
  COUNT(CASE WHEN gender = 'M' THEN 1 END) AS male_count,
  COUNT(CASE WHEN gender = 'F' THEN 1 END) AS female_count,
  SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active
FROM employees
GROUP BY dept_id;

---

3. DECODE 函数

3.1 基本语法

DECODE(expr, 
  search1, result1,
  search2, result2,
  ...
  [default]
)

3.2 示例

SELECT 
  name,
  DECODE(dept_id, 
    10, 'IT',
    20, 'HR',
    30, 'Sales',
    'Other'
  ) AS dept_name
FROM employees;

3.3 嵌套

SELECT 
  DECODE(dept_id, 
    10, DECODE(job_id, 
      'IT_PROG', 'Programmer',
      'IT_MGR', 'Manager',
      'Other'
    ),
    20, 'HR',
    'Other'
  ) AS role
FROM employees;

---

4. CASE vs DECODE

维度	CASE	DECODE
标准	ANSI	Oracle
类型检查	严格	自动转换
复杂条件	支持	仅等值
NULL 处理	显式	自动 NULL=NULL
可读性	高	中
推荐	是	兼容

4.1 NULL 处理

-- CASE 需要显式
CASE WHEN x IS NULL THEN 'null' WHEN x = 1 THEN 'one' END

-- DECODE 自动
DECODE(x, NULL, 'null', 1, 'one')

4.2 范围条件

-- CASE 支持范围
CASE WHEN salary > 5000 THEN 'high' END

-- DECODE 仅等值
-- 需要变通
DECODE(SIGN(salary - 5000), 1, 'high', 0, 'equal', 'low')

---

5. 应用场景

5.1 数据转换

SELECT 
  name,
  CASE gender
    WHEN 'M' THEN 'Male'
    WHEN 'F' THEN 'Female'
  END AS gender_text
FROM employees;

5.2 行转列

SELECT 
  dept_id,
  MAX(CASE WHEN job = 'MGR' THEN salary END) AS mgr_sal,
  MAX(CASE WHEN job = 'ANALYST' THEN salary END) AS analyst_sal,
  MAX(CASE WHEN job = 'CLERK' THEN salary END) AS clerk_sal
FROM employees
GROUP BY dept_id;

5.3 分类统计

SELECT 
  COUNT(CASE WHEN salary < 3000 THEN 1 END) AS low,
  COUNT(CASE WHEN salary BETWEEN 3000 AND 6000 THEN 1 END) AS medium,
  COUNT(CASE WHEN salary > 6000 THEN 1 END) AS high
FROM employees;

5.4 多条件

SELECT 
  name,
  CASE 
    WHEN dept_id = 10 AND salary > 5000 THEN 'IT_High'
    WHEN dept_id = 10 AND salary <= 5000 THEN 'IT_Low'
    WHEN dept_id = 20 AND salary > 5000 THEN 'HR_High'
    ELSE 'Other'
  END AS category
FROM employees;

---

6. 常见坑与排错

6.1 CASE 返回类型

-- 返回类型必须一致
CASE 
  WHEN x = 1 THEN 'A'
  WHEN x = 2 THEN 100  -- 错误
END

-- 修复
CASE 
  WHEN x = 1 THEN 'A'
  WHEN x = 2 THEN '100'
END

6.2 ELSE 默认 NULL

-- 无 ELSE 时返回 NULL
CASE WHEN x = 1 THEN 'A' END  -- x != 1 返回 NULL

6.3 DECODE NULL

-- DECODE 中 NULL = NULL 为 TRUE
DECODE(NULL, NULL, 'is_null', 'not_null')  -- is_null

---

7. 最佳实践

1. 优先 CASE：标准
2. 复杂条件用 CASE：灵活
3. 行转列用 CASE：高效
4. 分类统计用 CASE：清晰
5. ELSE 显式：避免 NULL
6. 类型一致：避免错误
7. NULL 处理：显式
8. DECODE 仅等值：简单场景
9. 测试边界：NULL/0
10. 可读性优先：维护

---

8. 参考资料

[1] Oracle Database SQL Language Reference 19c, “CASE Expressions” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CASE-Expressions.html