條件判斷式

在 Oracle 中 CASE WHEN...ELSE...END 類似一般程式語言中的 if-else，可用於 多條件 判斷。使用情境又可分成: (1)特定欄位條件判斷式 (2)任意欄位多條件判斷式

1.特定欄位條件判斷式
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
.
.
.
ELSE [default_result]
END

• 範例
  員工表 employees中有 department_id，想根據 department_id 顯示部門名稱，則可以寫成下方:

with employees as(
    select 10 department_id, 'Jacky' employee_name from dual
    union
    select 20 department_id, 'Jimmy' employee_name from dual
    union
    select 30 department_id, 'Celine' employee_name from dual
)
SELECT employee_name,
       CASE department_id
           WHEN 10 THEN 'IT'
           WHEN 20 THEN 'CIM'
           ELSE 'OTHER'
       END AS department_name
FROM employees;

2.任意欄位多條件判斷式
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
.
.
.
ELSE [default_result]
END

• 範例
  學生表 students有每個人的分數score，你想根據分數顯示成績等級，則可以寫成下方:

with students as(
    select 80 score, 'Jacky' student_name from dual
    union
    select 40 score, 'Jimmy' student_name from dual
    union
    select 95 score, 'Celine' student_name from dual
)
SELECT student_name,
       CASE
           WHEN score >= 90 THEN 'A'
           WHEN score >= 80 THEN 'B'
           WHEN score >= 70 THEN 'C'
           WHEN score >= 60 THEN 'D'
           ELSE 'F'
       END AS grade
FROM students;