DECODE

📖 Описание

Функция DECODE в Oracle/PLSQL — это мощный инструмент для реализации условной логики непосредственно в SQL-запросах. Она предоставляет удобный способ обработки различных условий без необходимости использования процедурного кода.

Основные сценарии применения DECODE:

  • Преобразование значений: DECODE может быть использована для преобразования внутренних кодов в более понятные значения. Например, если у вас есть столбец с кодами продуктов, вы можете использовать DECODE для преобразования этих кодов в названия продуктов.
  • Условная агрегация: DECODE может быть использована вместе с функциями агрегации, такими как SUM или COUNT, для выполнения условной агрегации. Например, вы можете подсчитать количество заказов только для определенного типа продукта.
  • Создание условных выражений: DECODE может быть использована для создания условных выражений в SQL, аналогичных оператору IF-THEN-ELSE в других языках программирования. Это позволяет создавать более сложные запросы, которые могут выполнять различные действия в зависимости от значения определенного выражения.
  • Обработка NULL значений: DECODE может быть использована для обработки NULL значений, заменяя их на другие значения для упрощения анализа данных.
  • Псевдо-PIVOT операции — преобразование строк в столбцы (до появления PIVOT в Oracle 11g)

🧩 Синтаксис

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

⚙️ Параметры и аргументы

expr — сравниваемое выражение; searchN — искомое значение; resultN — результат при совпадении. Если совпадений нет, возвращается default, а при его отсутствии — NULL.

Сравнение идёт последовательно; после совпадения следующие search не вычисляются. Два NULL в DECODE считаются равными. Тип первого search влияет на сравнение, первого result — на результат; для числовой первой пары также учитывается числовой приоритет. Первый result типа CHAR или NULL приводит к символьному результату VARCHAR2.


💻 Примеры использования

Базовый пример

Преобразуем коды мастер-классов в понятные названия:

WITH workshops (workshop_id, title, kind_id, fee) AS (
  SELECT 11, 'Clay basics', 10, 80 FROM dual
  UNION ALL SELECT 12, 'Paper forms', 20, 180 FROM dual
  UNION ALL SELECT 13, 'Wood joinery', 30, 320 FROM dual
  UNION ALL SELECT 14, 'Open practice', 99, 40 FROM dual
)
SELECT workshop_id, title,
       DECODE(kind_id, 10, 'Ceramics',
                       20, 'Paper',
                       30, 'Wood', 'Other') AS kind_name
FROM workshops
ORDER BY workshop_id;
WORKSHOP_ID TITLE KIND_NAME
11 Clay basics Ceramics
12 Paper forms Paper
13 Wood joinery Wood
14 Open practice Other

Эквивалент в PL/SQL

Для этих ненулевых кодов тот же выбор выражается через IF-THEN-ELSE:

DECLARE
  v_kind_id NUMBER := 20;
  v_kind_name VARCHAR2(20);
BEGIN
  IF v_kind_id = 10 THEN
    v_kind_name := 'Ceramics';
  ELSIF v_kind_id = 20 THEN
    v_kind_name := 'Paper';
  ELSIF v_kind_id = 30 THEN
    v_kind_name := 'Wood';
  ELSE
    v_kind_name := 'Other';
  END IF;
  DBMS_OUTPUT.PUT_LINE(v_kind_name);
END;
/
-- Вывод при включённом DBMS Output: Paper

Условная агрегация

WITH workshop_fees (room_id, kind, fee) AS (
  SELECT 1, 'CLAY', 80 FROM dual
  UNION ALL SELECT 1, 'CLAY', 120 FROM dual
  UNION ALL SELECT 1, 'PAPER', 60 FROM dual
  UNION ALL SELECT 2, 'CLAY', 90 FROM dual
  UNION ALL SELECT 2, 'PAPER', 110 FROM dual
)
SELECT room_id,
       SUM(DECODE(kind, 'CLAY', fee, 0)) AS clay_fee,
       SUM(DECODE(kind, 'PAPER', fee, 0)) AS paper_fee
FROM workshop_fees
GROUP BY room_id
ORDER BY room_id;
ROOM_ID CLAY_FEE PAPER_FEE
1 200 60
2 90 110

DECODE выбирает нужные выплаты, а SUM суммирует их по залам с помощью GROUP BY.

Обработка NULL

WITH members (member_name, discount_rate) AS (
  SELECT 'Aziza', 0.15 FROM dual
  UNION ALL SELECT 'Nodira', NULL FROM dual
  UNION ALL SELECT 'Timur', 0 FROM dual
)
SELECT member_name,
       DECODE(discount_rate, NULL, 'No discount specified',
              TO_CHAR(discount_rate, 'FM0D00',
                      'NLS_NUMERIC_CHARACTERS = ''.,''')) AS discount
FROM members
ORDER BY member_name;
MEMBER_NAME DISCOUNT
Aziza 0.15
Nodira No discount specified
Timur 0.00

NULL совпадает с NULL в DECODE, но нулевая скидка остаётся числом. TO_CHAR задаёт явное представление с десятичной точкой.


Продвинутые техники

Сравнение дат

Три способа получить более раннюю из двух известных дат DATE — через ABS, SIGN и LEAST. Для вывода предполагается григорианский календарь:

WITH booking_dates (first_date, second_date) AS (
  SELECT DATE '2026-09-12', DATE '2026-09-05' FROM dual
)
SELECT TO_CHAR(
         DECODE((first_date-second_date)-ABS(first_date-second_date),
                0, second_date, first_date), 'YYYY-MM-DD') AS by_abs,
       TO_CHAR(
         DECODE(SIGN(first_date-second_date), 1, second_date, first_date),
         'YYYY-MM-DD') AS by_sign,
       TO_CHAR(LEAST(first_date, second_date), 'YYYY-MM-DD') AS by_least
FROM booking_dates;
-- Все три результата: 2026-09-05

Сравнение относится к датам без NULL. При NULL поведение выражений может различаться; обработку неизвестных дат нужно задавать отдельно.

Работа с диапазонами значений

Для положительных целых баллов каждые 50 значений образуют категорию:

WITH practice_scores (member_id, points) AS (
  SELECT 1, 45 FROM dual
  UNION ALL SELECT 2, 85 FROM dual
  UNION ALL SELECT 3, 140 FROM dual
  UNION ALL SELECT 4, 190 FROM dual
  UNION ALL SELECT 5, 220 FROM dual
)
SELECT member_id,
       DECODE(TRUNC((points-1)/50), 0, 'Starter',
                                  1, 'Regular',
                                  2, 'Skilled',
                                  3, 'Expert', 'Extra') AS skill_level
FROM practice_scores
ORDER BY member_id;
MEMBER_ID SKILL_LEVEL
1 Starter
2 Regular
3 Skilled
4 Expert
5 Extra

Границы: 1–50, 51–100, 101–150, 151–200; далее Extra. Формула рассчитана именно на положительные целые значения, поскольку TRUNC усекает к нулю.

Комбинирование условий

WITH kit_orders (order_id, quantity) AS (
  SELECT 31, 5 FROM dual
  UNION ALL SELECT 32, 12 FROM dual
  UNION ALL SELECT 33, 20 FROM dual
  UNION ALL SELECT 34, 60 FROM dual
  UNION ALL SELECT 35, 75 FROM dual
)
SELECT order_id,
       DECODE(SIGN(quantity-12), -1, 'Small',
                                 0, 'Medium',
                                 1, DECODE(SIGN(quantity-60),
                                           -1, 'Large',
                                            0, 'Very large',
                                            1, 'Huge')) AS order_size
FROM kit_orders
ORDER BY order_id;
ORDER_ID ORDER_SIZE
31 Small
32 Medium
33 Large
34 Very large
35 Huge

Первый уровень проверяет границу 12 комплектов, второй — 60. Для NULL количества без отдельной ветви результат тоже NULL.


Часто задаваемые вопросы

Как проверить несколько параметров?

DECODE сравнивает одно выражение с набором значений. Можно вложить DECODE, а для нескольких самостоятельных условий часто понятнее CASE:

WITH studio_staff (staff_id, room_id, role_code) AS (
  SELECT 1, 10, 'HOST' FROM dual
  UNION ALL SELECT 2, 10, 'HELPER' FROM dual
  UNION ALL SELECT 3, 20, 'HOST' FROM dual
  UNION ALL SELECT 4, 30, 'HELPER' FROM dual
)
SELECT staff_id,
       DECODE(room_id,
              10, DECODE(role_code, 'HOST', 'North host', 'North staff'),
              20, 'South staff',
              'Other room') AS staff_group
FROM studio_staff
ORDER BY staff_id;
STAFF_ID STAFF_GROUP
1 North host
2 North staff
3 South staff
4 Other room

Можно ли использовать DECODE для диапазонов?

Непосредственно DECODE проверяет равенство, но диапазон можно сначала преобразовать в код. Например, полки 1–12, 13–24 и 25–36 образуют три секции:

WITH library_shelves (shelf_no) AS (
  SELECT 1 FROM dual
  UNION ALL SELECT 12 FROM dual
  UNION ALL SELECT 13 FROM dual
  UNION ALL SELECT 24 FROM dual
  UNION ALL SELECT 25 FROM dual
  UNION ALL SELECT 36 FROM dual
  UNION ALL SELECT 37 FROM dual
)
SELECT shelf_no,
       DECODE(TRUNC((shelf_no-1)/12),
              0, 'Section A', 1, 'Section B', 2, 'Section C',
              'Other') AS section_name
FROM library_shelves
ORDER BY shelf_no;
SHELF_NO SECTION_NAME
1 Section A
12 Section A
13 Section B
24 Section B
25 Section C
36 Section C
37 Other

Формула:

TRUNC((shelf_no-1)/12)

Она рассчитана на положительные целые номера. Для произвольных границ используйте CASE с явными сравнениями.

Как назначить процент по стажу участия?

Пусть стаж известен и неотрицателен: до 3 лет включительно ставка 0.03, свыше 3 лет — 0.05. Граница 3 задана явно, включая дробные значения:

WITH club_members (member_name, member_years) AS (
  SELECT 'Aziza', 0.5 FROM dual
  UNION ALL SELECT 'Nodira', 1 FROM dual
  UNION ALL SELECT 'Timur', 3 FROM dual
  UNION ALL SELECT 'Zarina', 3.5 FROM dual
)
SELECT member_name,
       DECODE(SIGN(member_years-3), 1, 0.05, 0.03) AS rate
FROM club_members
ORDER BY member_name;
MEMBER_NAME RATE
Aziza 0.03
Nodira 0.03
Timur 0.03
Zarina 0.05

Здесь SIGN проверяет точную границу; усечение приблизительной формулы могло бы неверно классифицировать дробный стаж.

Почему возникает ORA-00939?

В одном DECODE допускается не более 255 компонентов суммарно: expr, все search, все result и необязательный default. Для большого справочника соответствий удобнее отдельная таблица и соединение.


Альтернативы DECODE

CASE для условий и диапазонов

WITH workshops (workshop_id, title, kind_id, fee) AS (
  SELECT 11, 'Clay basics', 10, 80 FROM dual
  UNION ALL SELECT 12, 'Paper forms', 20, 180 FROM dual
  UNION ALL SELECT 13, 'Wood joinery', 30, 320 FROM dual
  UNION ALL SELECT 14, 'Open practice', 99, 40 FROM dual
)
SELECT workshop_id,
       CASE WHEN fee < 100 THEN 'Budget'
            WHEN fee BETWEEN 100 AND 250 THEN 'Standard'
            ELSE 'Extended' END AS fee_group
FROM workshops
ORDER BY workshop_id;
WORKSHOP_ID FEE_GROUP
11 Budget
12 Standard
13 Extended
14 Budget

В поисковом CASE корректна и проверка WHEN value IS NULL. В простом CASE value WHEN NULL используется сравнение равенства, поэтому такой вариант NULL не обнаруживает.

COALESCE для обработки NULL

WITH members (member_name, discount_rate) AS (
  SELECT 'Aziza', 0.15 FROM dual
  UNION ALL SELECT 'Nodira', NULL FROM dual
  UNION ALL SELECT 'Timur', 0 FROM dual
)
SELECT member_name, COALESCE(discount_rate, 0) AS discount_rate
FROM members
ORDER BY member_name;

Результаты для Aziza, Nodira и Timur: 0.15, 0, 0.

NULLIF для сравнения двух выражений

WITH workshop_attendance (workshop_id, actual_count, planned_count) AS (
  SELECT 11, 12, 12 FROM dual
  UNION ALL SELECT 12, 9, 12 FROM dual
)
SELECT workshop_id,
       NULLIF(actual_count, planned_count) AS unequal_actual
FROM workshop_attendance
ORDER BY workshop_id;

Результаты: NULL и 9. NULLIF возвращает первое значение при неравенстве, а не арифметическую разность.


Производительность и практика

  • У DECODE нет гарантированного преимущества в скорости перед CASE: сравнивайте реальные планы и измерения конкретного запроса.
  • Для сложных условий предпочтительнее читаемый CASE; глубокая вложенность затрудняет проверку.
  • Выбирайте совместимые типы аргументов и проверяйте границы диапазонов, дробные значения и NULL.
  • Для NULL подходят DECODE, NVL, COALESCE и поисковый CASE с IS NULL.

Для сводных таблиц см. PIVOT и UNPIVOT; условная агрегация через SUM(DECODE(...)) остаётся самостоятельным приёмом. DECODE полезна для чтения существующего Oracle SQL и простых таблиц соответствий.

См. документацию Oracle: DECODE и CASE Expressions.