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 и простых таблиц соответствий.