Orasql.ru

Appearance

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

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

В качестве тестовых данным будем использовать таблицу из части про функции для работы с 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
LOGINSTATUSHAS_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
LOGINLOGIN_LENGTHADMIN_LOGIN_LENGTH
admin5Пять
johndoe7Не пять и не шесть
nelsol6Шесть

Максимальное количество параметров в функции 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
LOGINUSER_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
LOGINIS_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
LOGINLOGIN_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
LOGINLAST_UPDATEDSTATUS
johndoe01-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_LENGTHCNT
= 51
> 52

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

Последний запрос можно переписать с использованием подзапроса, чтобы не дублировать 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