DECODE ФУНКЦИЯ — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

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

Описание

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

Вот некоторые ситуации, в которых обычно используется функция DECODE:

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

Синтаксис

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

DECODE( expression , search , result [, search , result]... [, default] )

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

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

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

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

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

Применение

Функцию DECODE можно использовать в следующих версиях Oracle/PLSQL:

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

Пример

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

SELECT supplier_name,
DECODE(supplier_id, 10000, 'IBM',
                    10001, 'Microsoft',
                    10002, 'Hewlett Packard',
                    'Gateway') result
  FROM suppliers;

SELECT supplier_name,

DECODE(supplier_id, 10000, 'IBM',

10001, 'Microsoft',

10002, 'Hewlett Packard',

'Gateway') result

FROM suppliers;

Запрос с использованием DECODE эквивалентен конструкции IF-THEN-ELSE.

IF supplier_id = 10000 THEN
result := 'IBM';

ELSIF supplier_id = 10001 THEN
result := 'Microsoft';

ELSIF supplier_id = 10002 THEN
result := 'Hewlett Packard';

ELSE
result := 'Gateway';

END IF;

IF supplier_id = 10000 THEN

result := 'IBM';

ELSIF supplier_id = 10001 THEN

result := 'Microsoft';

ELSIF supplier_id = 10002 THEN

result := 'Hewlett Packard';

ELSE

result := 'Gateway';

END IF;

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

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

Вопрос:

Один из наших читателей хотел узнать, как использовать функцию DECODE, чтобы сравнить две даты (то есть: date1 и date2), где date1 > date2, функция DECODE должна вернуть date2. В противном случае функция DECODE должна вернуть date1.

Ответ:

Для этого, используйте функцию DECODE следующим образом:

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

Приведенная ниже формула будет равна 0, если date1 больше date2:

(date1 - date2) - ABS(date1 - date2)

Полезный совет № 1:

Один из наших читателей предложил объединить функцию SIGN с функцией DECODE следующим образом:

Пример с датами выше, может быть модифицированы следующим образом:

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

Сочетание SIGN / DECODE также полезно для цифровых сравнений например продажа бонусов.

DECODE(SIGN(actual-target), -1, 'Нет бонусов для тебя', 0,'Просто сделай это', 1, 'Поздравляю, вы победитель')

Полезный совет № 2:

Один из наших читателей предложил использовать функцию LEAST (вместо функции DECODE) следующим образом:

Пример с датами выше, может быть модифицированы следующим образом:

LEAST(date1, date2)

Вопрос:

Я хотел бы знать, если это возможно, чтобы использовать функцию 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, search и result аргументы.