DECODE ФУНКЦИЯ — Oracle PL/SQL

В этой статье вы узнаете, как использовать Oracle/PLSQL функцию DECODE с синтаксисом и примерами.

Описание

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

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

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

Синтаксис

Синтаксис Oracle/PLSQL функции DECODE:

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

Параметры или аргументы

expression - Это выражение, которое вы хотите сравнить с другими значениями. Это может быть любой столбец или выражение.

searchN - Это значение, с которым вы хотите сравнить expression. Функция DECODE сравнивает expression с searchN. Если они совпадают, то возвращается соответствующий результат.

resultN - значение, возвращаемое, если выражение expression совпало с искомым searchN. Вы можете указать разные результаты для разных искомых значений.

default - необязательный. Если совпадений не найдено, функция DECODE вернет значение по умолчанию. Если значение по умолчанию не указано, то функция DECODE вернет NULL (если соответствий не найдено).

Поддержка версий

Функция DECODE доступна во всех современных версиях Oracle:

Oracle 19c, Oracle 18c, Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Примечание: В Oracle 12c и выше рекомендуется использовать более современные аналоги — CASE и стандартные функции ANSI SQL.

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

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

Вы можете использовать функцию DECODE в SQL запросе следующим образом:

SELECT product_name,
       DECODE(category_id, 1, 'Электроника',
                           2, 'Одежда',
                           3, 'Мебель',
                              'Другое') AS category_name
FROM products;

SELECT product_name,

DECODE(category_id, 1, 'Электроника',

2, 'Одежда',

3, 'Мебель',

'Другое') AS category_name

FROM products;

Функция DECODE будет сравнивать каждое значение category_id, одно за другим.

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

IF category_id = 1 THEN
  category_name := 'Электроника';
ELSIF category_id = 2 THEN
  category_name := 'Одежда';
ELSIF category_id = 3 THEN
  category_name := 'Мебель';
ELSE
  category_name := 'Другое';
END IF;

IF category_id = 1 THEN

category_name := 'Электроника';

ELSIF category_id = 2 THEN

category_name := 'Одежда';

ELSIF category_id = 3 THEN

category_name := 'Мебель';

ELSE

category_name := 'Другое';

END IF;

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

SELECT department_id,
       SUM(DECODE(job_id, 'IT_PROG', salary, 0)) AS it_salary,
       SUM(DECODE(job_id, 'SA_REP', salary, 0)) AS sales_salary
FROM employees
GROUP BY department_id;

SELECT department_id,

SUM(DECODE(job_id, 'IT_PROG', salary, 0)) AS it_salary,

SUM(DECODE(job_id, 'SA_REP', salary, 0)) AS sales_salary

FROM employees

GROUP BY department_id;

В этом примере SQL-запрос группирует сотрудников по department_id и для каждого отдела вычисляет две суммы: общую зарплату сотрудников, занимающих должность 'IT_PROG', и общую зарплату сотрудников, занимающих должность 'SA_REP'. Функция DECODE здесь используется для условного выбора зарплаты только для определенных должностей перед суммированием.

Обработка NULL-значений

SELECT employee_name,
       DECODE(commission_pct, NULL, 'Нет комиссии', 
                              TO_CHAR(commission_pct)) AS commission
FROM employees;

SELECT employee_name,

DECODE(commission_pct, NULL, 'Нет комиссии',

TO_CHAR(commission_pct)) AS commission

FROM employees;

Столбец commission в результирующем наборе данных будет содержать либо текст 'Нет комиссии' для сотрудников, у которых не указан процент комиссии (NULL), либо строковое представление их процента комиссии.

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

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

Вариант 1:

DECODE((date1 - date2) - ABS(date1 - date2), 0, date2, date1)

1	DECODE((date1 - date2) - ABS(date1 - date2), 0, date2, date1)

Вариант 2 (с функцией SIGN):

DECODE(SIGN(date1-date2), 1, date2, date1)

1	DECODE(SIGN(date1-date2), 1, date2, date1)

Вариант 3 (с функцией LEAST):

LEAST(date1, date2)

1	LEAST(date1, date2)

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

SELECT customer_id,
       DECODE(TRUNC((credit_score-1)/100), 0, 'Плохой',
                                          1, 'Средний',
                                          2, 'Хороший',
                                          3, 'Отличный',
                                          'Элитный') AS credit_rating
FROM customers;

SELECT customer_id,

DECODE(TRUNC((credit_score-1)/100), 0, 'Плохой',

1, 'Средний',

2, 'Хороший',

3, 'Отличный',

'Элитный') AS credit_rating

FROM customers;

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

SELECT order_id,
       DECODE(SIGN(quantity-10), -1, 'Малый заказ',
                                 0, 'Средний заказ',
                                 1, DECODE(SIGN(quantity-50), -1, 'Большой заказ',
                                                              0, 'Очень большой',
                                                              1, 'Огромный')) AS order_size
FROM orders;

SELECT order_id,

DECODE(SIGN(quantity-10), -1, 'Малый заказ',

0, 'Средний заказ',

1, DECODE(SIGN(quantity-50), -1, 'Большой заказ',

0, 'Очень большой',

1, 'Огромный')) AS order_size

FROM orders;

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

Вопрос:

Как реализовать сложные условия с несколькими параметрами?

Ответ:

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

Вложенные DECODE
CASE выражения (рекомендуется)
Комбинацию DECODE с логическими функциями (SIGN, NVL и др.)

-- Пример с вложенными DECODE
SELECT employee_id,
       DECODE(department_id, 10, DECODE(job_id, 'MANAGER', 'Руководство IT', 'Сотрудник IT'),
                            20, 'Финансы',
                            'Другой отдел') AS employee_type
FROM employees;

-- Пример с вложенными DECODE

SELECT employee_id,

DECODE(department_id, 10, DECODE(job_id, 'MANAGER', 'Руководство IT', 'Сотрудник IT'),

20, 'Финансы',

'Другой отдел') AS employee_type

FROM employees;

Вопрос:

Я хотел бы знать, если это возможно, чтобы использовать функцию DECODE для диапазонов чисел, то есть 1-10 = 'категория 1', 11-20 = 'категорию 2', вместо того, чтобы индивидуально декодировать каждый номер.

Ответ:

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

Например:

SELECT supplier_id,
DECODE(TRUNC ((supplier_id - 1) / 10), 0, 'category 1',
                                       1, 'category 2',
                                       2, 'category 3',
                                       'unknown') result
  FROM suppliers;

SELECT supplier_id,

DECODE(TRUNC ((supplier_id - 1) / 10), 0, 'category 1',

1, 'category 2',

2, 'category 3',

'unknown') result

FROM suppliers;

Этот пример, на основе формулы:

TRUNC((supplier_id - 1) / 10)

Формула будет оценивать 0, если supplier_id находится между 1 и 10.
Формула будет оценивать в 1, если supplier_id между 11 и 20.
Формула будет оценивать по 2, если supplier_id между 21 и 30.

Вопрос:

Мне нужно написать запрос DECODE, который будет возвращать следующие:

Если yrs_of_service <1, то вернуть 0,04 Если yrs_of_service> = 1 и <5, то вернуть 0,04 Если yrs_of_service> 5 затем вернуть 0,06

Как я могу это сделать?

Ответ:

Вам нужно будет создать формулу, которая рассчитает единичное число для каждого из вашего диапазона.

Например:

SELECT emp_name,
DECODE(TRUNC ((yrs_of_service + 3) / 4), 0, 0.04,
                                          1, 0.04,
                                          0.06) as perc_value
FROM employees;

SELECT emp_name,

DECODE(TRUNC ((yrs_of_service + 3) / 4), 0, 0.04,

1, 0.04,

0.06) as perc_value

FROM employees;

Вопрос:

Есть ли ограничение на количество аргументов, которые вы можете указать в одном операторе DECODE? Я получаю сообщение об ошибке "ORA-00939: слишком много аргументов для функции".

Ответ:

Да, максимальное количество компонентов, которые вы можете иметь в функции DECODE - 255. Это включая expression, searchN и resultN параметры.

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

В современных версиях Oracle рекомендуется использовать:

1. CASE выражение — стандарт SQL, более читаемо и функционально

SELECT product_name,
       CASE WHEN price < 100 THEN 'Дешевый'
            WHEN price BETWEEN 100 AND 500 THEN 'Средний'
            ELSE 'Дорогой' END AS price_category
FROM products;

SELECT product_name,

CASE WHEN price < 100 THEN 'Дешевый'

WHEN price BETWEEN 100 AND 500 THEN 'Средний'

ELSE 'Дорогой' END AS price_category

FROM products;

Функция COALESCE — для обработки NULL значений

SELECT COALESCE(commission_pct, 0) AS commission
FROM employees;

1 2	SELECT COALESCE(commission_pct, 0) AS commission FROM employees;

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

SELECT NULLIF(actual, planned) AS difference
FROM sales;

1 2	SELECT NULLIF(actual, planned) AS difference FROM sales;

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

DECODE обычно работает быстрее, чем CASE, но менее читаем
Для сложных условий предпочтительнее использовать CASE
Избегайте слишком глубокой вложенности DECODE (не более 3-4 уровней)
При работе с большими диапазонами создавайте промежуточные формулы
Для проверки NULL используйте DECODE или NVL (но не CASE с IS NULL)

Заключение

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

Для более сложных сценариев рассмотрите возможность использования:

CASE выражений
Функций COALESCE, NULLIF, NVL2
Фильтров агрегатных функций (Oracle 9i+)
PIVOT/UNPIVOT операторов (Oracle 11g+)