CASE в Oracle SQL — как писать условные выражения

CASE в Oracle SQL. Введение

CASE в Oracle SQL — это мощный оператор для реализации логики в запросах. Он позволяет возвращать разные значения в зависимости от условий. CASE используется в SELECT, ORDER BY, WHERE и даже в PL/SQL. В этой статье ты найдёшь 50 примеров практического применения CASE.

🖋️ Синтаксис оператора CASE в Oracle

Стандартный формат:

CASE
  WHEN условие1 THEN результат1
  WHEN условие2 THEN результат2
  ELSE результат_по_умолчанию
END

Сравнительный формат:

CASE выражение
  WHEN значение1 THEN результат1
  WHEN значение2 THEN результат2
  ELSE результат_по_умолчанию
END

🔄 Где используется CASE

Условная логика в SELECT
Динамическое группирование или фильтрация
Подстановка значений по условиям
Агрегаты с фильтрацией по CASE
CASE внутри PL/SQL и процедур

📊 50 примеров использования CASE в Oracle

Простой CASE в SELECT:

SELECT first_name,
  CASE department_id
    WHEN 10 THEN 'Администрация'
    WHEN 20 THEN 'Продажи'
    ELSE 'Другие'
  END AS отдел
FROM employees;

CASE с условиями:

SELECT salary,
  CASE
    WHEN salary > 10000 THEN 'Высокая'
    WHEN salary BETWEEN 5000 AND 10000 THEN 'Средняя'
    ELSE 'Низкая'
  END AS уровень
FROM employees;

CASE в ORDER BY:

SELECT first_name FROM employees
ORDER BY
  CASE department_id
    WHEN 10 THEN 1
    WHEN 20 THEN 2
    ELSE 3
  END;

CASE в WHERE:

SELECT * FROM employees
WHERE
  CASE WHEN job_id = 'IT_PROG' THEN 1 ELSE 0 END = 1;

CASE с NULL:

SELECT first_name,
  CASE
    WHEN commission_pct IS NULL THEN 'Без бонуса'
    ELSE 'С бонусом'
  END AS бонус
FROM employees;

CASE в агрегатной функции:

SELECT
  COUNT(CASE WHEN salary > 10000 THEN 1 END) AS высокие,
  COUNT(CASE WHEN salary <= 10000 THEN 1 END) AS остальные
FROM employees;

CASE с TO_CHAR:

SELECT first_name,
  CASE
    WHEN TO_CHAR(hire_date, 'YYYY') = '2023' THEN 'Новый'
    ELSE 'Старый'
  END AS статус
FROM employees;

CASE в GROUP BY:

SELECT
  CASE WHEN department_id IN (10, 20) THEN 'Управление' ELSE 'Прочее' END AS категория,
  COUNT(*)
FROM employees
GROUP BY CASE WHEN department_id IN (10, 20) THEN 'Управление' ELSE 'Прочее' END;

CASE внутри CASE:

SELECT salary,
  CASE
    WHEN salary > 10000 THEN 'Высокая'
    WHEN salary > 5000 THEN
      CASE
        WHEN department_id = 60 THEN 'Средняя в отделе 60'
        ELSE 'Средняя'
      END
    ELSE 'Низкая'
  END AS уровень
FROM employees;

CASE с NULLIF:

SELECT NULLIF(department_id, 10) AS проверка,
  CASE WHEN NULLIF(department_id, 10) IS NULL THEN 'Совпадает' ELSE 'Различается' END
FROM employees;

CASE и LENGTH:

SELECT first_name,
  CASE
    WHEN LENGTH(first_name) <= 4 THEN 'Короткое имя'
    ELSE 'Длинное имя'
  END AS длина
FROM employees;

CASE в JSON_VALUE:

SELECT
  CASE JSON_VALUE(data, '$.region')
    WHEN 'EU' THEN 'Европа'
    WHEN 'US' THEN 'Америка'
    ELSE 'Другое'
  END AS континент
FROM employees_json;

CASE в подзапросе:

SELECT * FROM employees
WHERE department_id = (
  SELECT CASE WHEN location_id = 1700 THEN 10 ELSE 20 END FROM departments WHERE department_name = 'Sales'
);

CASE с аналитикой:

SELECT first_name,
  CASE WHEN RANK() OVER (ORDER BY salary DESC) <= 3 THEN 'Топ-3' ELSE 'Остальные' END AS категория
FROM employees;

CASE с BETWEEN:

SELECT salary,
  CASE
    WHEN salary BETWEEN 0 AND 3000 THEN 'Низкая'
    WHEN salary BETWEEN 3001 AND 7000 THEN 'Средняя'
    ELSE 'Высокая'
  END AS уровень
FROM employees;

CASE в UPDATE:

UPDATE employees SET bonus =
  CASE
    WHEN department_id = 80 THEN 500
    ELSE 100
  END;

CASE в DELETE:

DELETE FROM employees
WHERE
  CASE WHEN department_id = 50 THEN 1 ELSE 0 END = 1;

CASE в MERGE:

MERGE INTO employees e USING dual d
ON (e.employee_id IS NOT NULL)
WHEN MATCHED THEN UPDATE SET bonus =
  CASE
    WHEN department_id = 10 THEN 300
    ELSE 100
  END;

CASE с INITCAP:

SELECT INITCAP(first_name),
  CASE WHEN INITCAP(first_name) LIKE 'A%' THEN 'A-группа' ELSE 'Другое' END
FROM employees;

CASE с ROWNUM:

SELECT * FROM (
  SELECT e.*, ROWNUM AS rnum FROM employees e
)
WHERE
  CASE WHEN rnum <= 5 THEN 1 ELSE 0 END = 1;

CASE с TO_DATE:

SELECT first_name,
  CASE
    WHEN hire_date < TO_DATE('2020-01-01','YYYY-MM-DD') THEN 'Старый'
    ELSE 'Недавний'
  END AS стаж
FROM employees;

CASE и MOD:

SELECT employee_id,
  CASE MOD(employee_id, 2)
    WHEN 0 THEN 'Чётный'
    ELSE 'Нечётный'
  END AS чётность
FROM employees;

CASE с COALESCE:

SELECT first_name,
  CASE WHEN COALESCE(commission_pct, 0) > 0 THEN 'Есть бонус' ELSE 'Нет бонуса' END
FROM employees;

CASE и NVL2:

SELECT NVL2(commission_pct, 'Бонус', 'Без бонуса') AS статус,
  CASE WHEN NVL2(commission_pct, 1, 0) = 1 THEN 'Да' ELSE 'Нет' END
FROM employees;

CASE с JSON_TABLE:

SELECT * FROM JSON_TABLE(
  '{"employees": [{"id":1,"region":"EU"},{"id":2,"region":"US"}]}'
  , '$.employees[*]'
  COLUMNS (id NUMBER PATH '$.id', region VARCHAR2(10) PATH '$.region')
) WHERE
  CASE region WHEN 'EU' THEN 1 ELSE 0 END = 1;

CASE с аналитикой NTILE:

SELECT first_name,
  CASE NTILE(3) OVER (ORDER BY salary DESC)
    WHEN 1 THEN 'Высший'
    WHEN 2 THEN 'Средний'
    ELSE 'Нижний'
  END AS сегмент
FROM employees;

CASE в RANK PARTITION:

SELECT *,
  CASE WHEN RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) = 1 THEN 'Топ в отделе' ELSE NULL END AS лидер
FROM employees;

CASE и EXTRACT:

SELECT first_name,
  CASE EXTRACT(MONTH FROM hire_date)
    WHEN 1 THEN 'Январь'
    WHEN 12 THEN 'Декабрь'
    ELSE 'Другой месяц'
  END AS месяц_приёма
FROM employees;

CASE в DUMP:

SELECT job_id,
  CASE WHEN DUMP(job_id) LIKE '%73%' THEN 'Содержит 73' ELSE 'Нет' END AS проверка
FROM employees;

CASE с JOIN:

SELECT e.first_name,
  CASE
    WHEN d.department_name LIKE 'S%' THEN 'Отдел S'
    ELSE 'Прочее'
  END AS тип_отдела
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

CASE и LIKE:

SELECT email,
  CASE WHEN email LIKE '%@oracle.com' THEN 'Корпоративный' ELSE 'Внешний' END AS тип
FROM employees;

CASE и SUBSTR:

SELECT SUBSTR(first_name, 1, 1) AS первая_буква,
  CASE SUBSTR(first_name, 1, 1)
    WHEN 'A' THEN 'Группа A'
    ELSE 'Другое'
  END AS группа
FROM employees;

CASE и REGEXP_LIKE:

SELECT first_name,
  CASE
    WHEN REGEXP_LIKE(first_name, '^[A-Z]') THEN 'Заглавная'
    ELSE 'Другое'
  END AS стиль
FROM employees;

CASE и NULL в агрегатах:

SELECT
  CASE WHEN COUNT(commission_pct) = 0 THEN 'Нет данных' ELSE 'Есть' END AS статус
FROM employees;

CASE и FILTER:

SELECT
  COUNT(*) FILTER (WHERE salary > 5000) AS высокие,
  CASE WHEN COUNT(*) FILTER (WHERE salary > 5000) > 10 THEN 'Много' ELSE 'Мало' END AS оценка
FROM employees;

CASE и PERCENTILE_CONT:

SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS медиана,
  CASE WHEN salary >= PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) THEN 'Выше медианы' ELSE 'Ниже' END AS позиция
FROM employees;

CASE и SYS_CONTEXT:

SELECT SYS_CONTEXT('USERENV','SESSION_USER') AS пользователь,
  CASE WHEN SYS_CONTEXT('USERENV','SESSION_USER') = 'HR' THEN 'Админ' ELSE 'Пользователь' END AS роль
FROM dual;

CASE и EXPLAIN PLAN:

EXPLAIN PLAN FOR
SELECT * FROM employees
WHERE CASE WHEN department_id = 50 THEN 1 ELSE 0 END = 1;

CASE и CONNECT BY:

SELECT employee_id,
  CASE WHEN LEVEL = 1 THEN 'Корень' ELSE 'Подчинённый' END AS иерархия
FROM employees
CONNECT BY PRIOR employee_id = manager_id;

CASE и JSON_EXISTS:

SELECT * FROM employees_json
WHERE CASE WHEN JSON_EXISTS(data, '$.bonus') THEN 1 ELSE 0 END = 1;

CASE в SELECT без таблицы:

SELECT CASE WHEN 1 = 1 THEN 'Истина' ELSE 'Ложь' END FROM dual;

CASE с SIGN:

SELECT salary,
  CASE SIGN(salary - 5000)
    WHEN -1 THEN 'Меньше'
    WHEN 0 THEN 'Равно'
    WHEN 1 THEN 'Больше'
  END AS сравнение
FROM employees;

CASE и GREATEST:

SELECT GREATEST(salary, bonus) AS максимум,
  CASE
    WHEN GREATEST(salary, bonus) = salary THEN 'Оклад выше'
    ELSE 'Бонус выше'
  END AS итог
FROM employees;

CASE и IN:

SELECT job_id,
  CASE
    WHEN job_id IN ('IT_PROG', 'SA_MAN') THEN 'Технический'
    ELSE 'Другой'
  END AS категория
FROM employees;

CASE и SIGN + ABS:

SELECT salary,
  CASE SIGN(ABS(salary - 5000))
    WHEN 0 THEN 'Ровно 5000'
    ELSE 'Отклонение'
  END AS статус
FROM employees;

📆 Заключение: зачем использовать CASE

CASE — незаменимый инструмент для реализации логики в запросах. Он позволяет сделать запросы гибкими, читаемыми и легко расширяемыми. CASE отлично сочетается с фильтрами, агрегацией, сортировкой и даже вложенными подзапросами.

Освоив CASE, ты сможешь создавать универсальные запросы, обрабатывать данные по условиям и делать отчёты более наглядными. Обязательно к освоению для бизнес-аналитиков и SQL-разработчиков.