Условные функции

Условные функции - это такие функции, которые могут возвращать разные результаты в зависимости от выполнения тех или иных условий.

В качестве тестовых данным будем использовать таблицу из части про функции для работы с NULL.

DECODE

Функция DECODE в общем случае имеет следующий вид:

sql

DECODE(что сравниваем,
       значение1, результат1,
       значение2, результат2,
       значение3, результат3,
       ....
       значениеN, результатN,
       значение по-умолчанию)

DECODE(что сравниваем,
       значение1, результат1,
       значение2, результат2,
       значение3, результат3,
       ....
       значениеN, результатN,
       значение по-умолчанию)

Первым DECODE принимает параметр, значение которого будет сравниваться по очереди со списком значений, и в случае, когда он совпадет с одним из перечисленных, будет возвращен соответствующий результат. Если совпадений не найдено, будет возвращено значение по-умолчанию. Если значение по-умолчанию не указано, будет возвращен NULL.

Аргументы могут быть числового, строкового типа, или датой.

DECODE может сравнивать NULL значения:

sql

select login,
       status,
       decode(status, -- <- Что сравниваем
              null, 'Статус не указан', -- <- пара Значение-Результат
              'Статус указан') has_status -- <- Значение по-умолчанию
from profiles

select login,
       status,
       decode(status, -- <- Что сравниваем
              null, 'Статус не указан', -- <- пара Значение-Результат
              'Статус указан') has_status -- <- Значение по-умолчанию
from profiles

| LOGIN   | STATUS                      | HAS_STATUS       |
| johndoe | -                           | Статус не указан |
| admin   | Я админ. Все вопросы ко мне | Статус указан    |
| nelsol  | -                           | Статус не указан |

Перед сравнением Oracle автоматически приводит первый параметр и все значения к типу первого значения в списке параметров. Результат функции автоматически приводится к типу первого результата в списке параметров. Если первый результат в списке - NULL, результат функции DECODE будет приведен к строковому типу VARCHAR2.

Например, следующий запрос не выполнится из-за ошибки ORA-01722: invalid number:

sql

select decode(login,
              'admin', 10,
              'Администратор') has_status
from profiles

select decode(login,
              'admin', 10,
              'Администратор') has_status
from profiles

Тип возвращаемого значения определяется первым результатом в списке параметров, в данном случае - числом "10". Но значение по-умолчанию имеет строковый тип, что и приводит к ошибке. Чтобы ошибки не было, нужно либо значение по-умолчанию заменить на число, либо заменить число 10 на любой строковый тип.

Любой из следующих запросов отработает без ошибок:

sql

select decode(login,
              'admin', 'Администратор',
              'Не администратор') admin_login
from profiles;

select decode(login,
              'admin', 'Администратор',
              'Не администратор') admin_login
from profiles;

sql

select decode(login,
              'admin', 10,
              20) admin_login_flag
from profiles;

select decode(login,
              'admin', 10,
              20) admin_login_flag
from profiles;

В качестве проверяемого значения не обязательно должна быть колонка таблицы. В следующем примере проверяем длину логина пользователя:

sql

select login,
       length(login) login_length,
       decode(length(login),
              5, 'Пять',
              6, 'Шесть',
              'Не пять и не шесть') admin_login_length
from profiles

select login,
       length(login) login_length,
       decode(length(login),
              5, 'Пять',
              6, 'Шесть',
              'Не пять и не шесть') admin_login_length
from profiles

| LOGIN   | LOGIN_LENGTH | ADMIN_LOGIN_LENGTH |
| admin   |            5 | Пять               |
| johndoe |            7 | Не пять и не шесть |
| nelsol  |            6 | Шесть              |

Максимальное количество параметров в функции DECODE - 255.

Предыдущий пример, только с использованием вложенного DECODE:

sql

select login,
       length(login) login_length,
        decode(length(login),
              5, 'Пять',
              decode(length(login),
                     6, 'Шесть',
                     'Не пять и не шесть')) admin_login_length
from profiles

select login,
       length(login) login_length,
        decode(length(login),
              5, 'Пять',
              decode(length(login),
                     6, 'Шесть',
                     'Не пять и не шесть')) admin_login_length
from profiles

Здесь в качестве значения по-умолчанию выступает еще один DECODE.

На практике вложенных decode следует избегать, ровно как и decode с большим количеством параметров.

Одна из распространенных ошибок - использовать DECODE для того, чтобы преобразовать какие-либо флаги в их строковые эквиваленты (при их большом количестве):

sql

select a.*,
       decode(a.status,
              1, 'Закрыт',
              2, 'Отменен',
              3, 'Новый',
              4, 'В обработке'
       ) status_name
from some_table a

select a.*,
       decode(a.status,
              1, 'Закрыт',
              2, 'Отменен',
              3, 'Новый',
              4, 'В обработке'
       ) status_name
from some_table a

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

sql

select a.*,
       st.status_name
from some_table a
join statuses st on st.status_code = a.status

select a.*,
       st.status_name
from some_table a
join statuses st on st.status_code = a.status

CASE

Выражение CASE во многом похоже на DECODE, но обладает большими возможностями. Данное выражение позволяет реализовать полноценную условную логику в SQL запросе.

CASE может использоваться в двух вариантах - простом(англ. simple case expression) и поисковом(англ. searched case expression).

Простой CASE по принципу работы идентичен DECODE:

sql

select login,
           case login -- <- что сравниваем
               when 'admin' then 'Администратор' -- Результат 1
               when 'johndoe' then 'Джон До' -- Результат 2
               else 'Другой пользователь' -- Значение по-умолчанию
           end user_flag
    from profiles

select login,
           case login -- <- что сравниваем
               when 'admin' then 'Администратор' -- Результат 1
               when 'johndoe' then 'Джон До' -- Результат 2
               else 'Другой пользователь' -- Значение по-умолчанию
           end user_flag
    from profiles

| LOGIN   | USER_FLAG           |
| admin   | Администратор       |
| johndoe | Джон До             |
| nelsol  | Другой пользователь |

user_flag здесь - псевдоним для столбца. Само выражение начинается с ключевого слова case и заканчивается ключевым словом end.

Как и в DECODE, для проверяемого значения начинают производиться сравнения со значениями в блоках WHEN. При первом же совпадении функция завершает работу и возвращает соответствующий результат (указанный после then). В случае, если ни одного совпадения не было найдено, возвращается значение, указанное в блоке ELSE. Если значение по-умолчанию не указано, будет возвращен NULL.

Searched case expression, в отличие от simple case expression, является куда более мощным инструментом. В отличие от последнего, в searched case expression в блоках when указываются условия, а не просто значения для сравнения:

sql

select login,
       case
           when login = 'admin' then 'Администратор'
           else 'Не администратор'
       end is_admin
from profiles

select login,
       case
           when login = 'admin' then 'Администратор'
           else 'Не администратор'
       end is_admin
from profiles

| LOGIN   | IS_ADMIN         |
| admin   | Администратор    |
| johndoe | Не администратор |
| nelsol  | Не администратор |

sql

select login,
           case
               when length(login) = 5 then 'Пять'
               when length(login) > 5 then 'Больше пяти'
               when length(login) between 0 and 4 then 'От 0 до 4'
           end login_length_stats
    from profiles

select login,
           case
               when length(login) = 5 then 'Пять'
               when length(login) > 5 then 'Больше пяти'
               when length(login) between 0 and 4 then 'От 0 до 4'
           end login_length_stats
    from profiles

| LOGIN   | LOGIN_LENGTH_STATS |
| admin   | Пять               |
| johndoe | Больше пяти        |
| nelsol  | Больше пяти        |

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

Условные функции в WHERE части

Условные функции спокойно могут использоваться в WHERE-части запроса, как и другие функции:

sql

-- Выведет профили пользователей, которые
-- не являются администраторами
select *
from profiles
where case
          when login = 'admin' then 1
          else 0
       end = 0

-- Выведет профили пользователей, которые
-- не являются администраторами
select *
from profiles
where case
          when login = 'admin' then 1
          else 0
       end = 0

| LOGIN   | LAST_UPDATED | STATUS |
| johndoe | 01-JAN-09    | -      |
| nelsol  | -            | -      |

В примере выше выражение case вернет 0 в тех случаях, когда логин пользователя не будет логином администратора. Сразу после окончания выражения мы сравниваем его с нулем, тем самым получая только не-администраторов. Подобные способы, конечно, лучше не использовать, а вместо них прибегать к классическому варианту написания запроса, который будет более понятным:

sql

select *
from profiles
where login <> 'admin'

select *
from profiles
where login <> 'admin'

При группировке условные функции, как и все другие, должны быть полностью продублированы в GROUP BY, использовать псевдоним колонки не получится:

sql

-- Этот запрос не сработает
select case
           when length(login) > 5 then '> 5'
           when length(login) < 5 then '< 5'
           when length(login) = 5 then '= 5'
       end login_length,
       count(*) cnt
from profiles
group by login_length;

-- А вот этот отработает корректно
select case
           when length(login) > 5 then '> 5'
           when length(login) < 5 then '< 5'
           when length(login) = 5 then '= 5'
       end login_length,
       count(*) cnt
from profiles
group by case
           when length(login) > 5 then '> 5'
           when length(login) < 5 then '< 5'
           when length(login) = 5 then '= 5'
       end

-- Этот запрос не сработает
select case
           when length(login) > 5 then '> 5'
           when length(login) < 5 then '< 5'
           when length(login) = 5 then '= 5'
       end login_length,
       count(*) cnt
from profiles
group by login_length;

-- А вот этот отработает корректно
select case
           when length(login) > 5 then '> 5'
           when length(login) < 5 then '< 5'
           when length(login) = 5 then '= 5'
       end login_length,
       count(*) cnt
from profiles
group by case
           when length(login) > 5 then '> 5'
           when length(login) < 5 then '< 5'
           when length(login) = 5 then '= 5'
       end

| LOGIN_LENGTH | CNT |
| = 5          |   1 |
| > 5          |   2 |

Запрос выше выведет статистику о количестве логинов пользователей с определенной длиной - меньше пяти символов, больше пяти символов, или с длиной логина ровно в пять символов.

Последний запрос можно переписать с использованием подзапроса, чтобы не дублировать CASE в GROUP BY:

sql

select login_length,
           count(*)
    from (
        select case
                   when length(login) > 5 then '> 5'
                   when length(login) < 5 then '< 5'
                   when length(login) = 5 then '= 5'
               end login_length,
               login,
               status,
               last_updated
        from profiles
    )
    group by login_length

select login_length,
           count(*)
    from (
        select case
                   when length(login) > 5 then '> 5'
                   when length(login) < 5 then '< 5'
                   when length(login) = 5 then '= 5'
               end login_length,
               login,
               status,
               last_updated
        from profiles
    )
    group by login_length

Условные функции ​

DECODE ​

CASE ​

Условные функции в WHERE части ​

Условные функции

DECODE

CASE

Условные функции в WHERE части