Являются оператором CASE и эквивалентом DECODE?

Ответ 1

Бен написал длинный ответ о различиях между DECODE и CASE. Он демонстрирует, что DECODE и CASE могут возвращать разные типы данных, по-видимому, для одного и того же набора значений, не объясняя, почему это происходит.

DECODE() достаточно предписывающий: он всегда является типом первого параметра результата. Oracle применяет неявное преобразование ко всем другим параметрам результата. Он выдает ошибку, если (скажем) первый параметр результата является числовым, а значением по умолчанию является дата.

ORA-00932: inconsistent datatypes: expected NUMBER got DATE

Это описано в документации: узнать больше.

В первом сценарии первый параметр результата - NULL, который Oracle решает рассматривать как VARCHAR2. Если мы изменим его так, чтобы первый параметр результата был числовым, а значение по умолчанию равно null, оператор DECODE() вернет NUMBER; DUMP() доказывает, что это так.

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

Разница сводится к этому. Следующий оператор DECODE будет запущен, оператор CASE не будет:

select decode(1, 1, 1, '1') from dual;

select case 1 when 1 then 1 else '1' end from dual;

Обязательный скрипт SQL.

Ответ 2

Короткий ответ, нет.

Немного длинный ответ почти.

Появляется только то, что результат, полученный из каждого утверждения, идентичен. Если мы используем функцию DUMP для оценки возвращаемых типов данных, вы увидите, что я имею в виду:

SQL> select dump(case 1 when 2 then null else 0 end) as simple_case
  2       , dump(case when 1 = 2 then null else 0 end) as searched_case
  3       , dump(decode(1, 2, null, 0)) as decode
  4    from dual;

SIMPLE_CASE        SEARCHED_CASE      DECODE
------------------ ------------------ -----------------
Typ=2 Len=1: 128   Typ=2 Len=1: 128   Typ=1 Len=1: 48

SQL Fiddle

Вы можете видеть, что тип данных DECODE равен 1, тогда как два оператора CASE возвращают тип данных 2. Использование Oracle Тип данных Резюме, DECODE возвращает VARCHAR2 (тип данных 1), тогда как операторы CASE являются "возвращающими" числами (тип данных 2).

Я предполагаю, что это происходит потому, что, как показывают имена, DECODE является функцией, а CASE - нет, что означает, что они реализованы по-разному внутри. Нет реального способа доказать это.

Вы можете подумать, что это ни на что не влияет. Если вам нужно, чтобы число, которое Oracle будет неявным образом преобразовать символ в число под неявные правила преобразования, правильно? Это также неверно, не будет работать в UNION, так как типы данных должны быть одинаковыми; Oracle не будет делать каких-либо неявных преобразований, чтобы облегчить вам работу. Во-вторых, здесь Oracle говорит о неявном преобразовании:

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

• Операторы SQL легче понять, когда вы используете явные функции преобразования типов данных.

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

• Неявное преобразование зависит от контекста, в котором оно происходит, и может не работать одинаково в каждом случае. Например, неявное преобразование из значения datetime в значение VARCHAR2 может вернуть неожиданный год в зависимости от значения NLS_DATE_FORMAT Параметр.

• Алгоритмы для неявного преобразования могут быть изменены во всех версиях программного обеспечения и среди продуктов Oracle. Поведение явных преобразований более предсказуемо.

Это не красивый список; но предпоследний момент приносит мне приятные даты. Если мы возьмем предыдущий запрос и преобразуем его в тот, который использует дату вместо:

select case sysdate when trunc(sysdate) then null 
                    else sysdate 
       end as simple_case
     , case when sysdate = trunc(sysdate) then null 
            else sysdate 
       end as searched_case
     , decode(sysdate, trunc(sysdate), null, sysdate) as decode
  from dual;

Еще раз, используя DUMP для этого запроса, операторы CASE возвращают тип данных 12, DATE. DECODE преобразовал sysdate в VARCHAR2.

SQL> select dump(case sysdate when trunc(sysdate) then null
  2                           else sysdate
  3              end) as simple_case
  4       , dump(case when sysdate = trunc(sysdate) then null
  5                   else sysdate
  6              end) as searched_case
  7       , dump(decode(sysdate, trunc(sysdate), null, sysdate)) as decode
  8    from dual;

SIMPLE_CASE          
---------------------------------- 
Typ=12 Len=7: 120,112,12,4,22,18,7 
SEARCHED_CASE
---------------------------------- 
Typ=12 Len=7: 120,112,12,4,22,18,7
DECODE
---------------------------------- 
Typ=1 Len=19: 50,48,49,50,45,49,50,45,48,52,32,50,49,58,49,55,58,48,54

SQL Fiddle

Примечание (в скрипте SQL), что DATE была преобразована в символ, используя сеансы NLS_DATE_FORMAT.

Наличие даты, которая неявно преобразована в VARCHAR2, может вызвать проблемы. Если вы намерены использовать TO_CHAR, чтобы преобразовать дату в символ, ваш запрос сломается там, где вы этого не ожидаете.

SQL> select to_char( decode( sysdate
  2                         , trunc(sysdate), null
  3                         , sysdate )
  4                 , 'yyyy-mm-dd') as to_char
  5    from dual;
select to_char( decode( sysdate
                *
ERROR at line 1:
ORA-01722: invalid number

SQL Fiddle

Аналогично, арифметика даты больше не работает:

SQL>
SQL>
SQL> select decode(sysdate, trunc(sysdate), null, sysdate) + 1 as decode
  2    from dual;
select decode(sysdate, trunc(sysdate), null, sysdate) + 1 as decode
       *
ERROR at line 1:
ORA-01722: invalid number

SQL Fiddle

Интересно, что DECODE преобразует выражение только в VARCHAR2, если один из возможных результатов равен NULL. Если значение по умолчанию равно NULL, этого не происходит. Например:

SQL> select decode(sysdate, sysdate, sysdate, null) as decode
  2    from dual;

DECODE
-------------------
2012-12-04 21:18:32

SQL> select dump(decode(sysdate, sysdate, sysdate, null)) as decode
  2    from dual;

DECODE
------------------------------------------    
Typ=13 Len=8: 220,7,12,4,21,18,32,0

SQL Fiddle

Обратите внимание, что DECODE вернул тип данных 13. Это не документировано, но я предполагаю, что тип даты как арифметика даты и т.д. работает.

Короче говоря, избегайте DECODE, если возможно; вы можете не обязательно получать типы данных, которые вы ожидаете. К quote Tom Kyte:

Декод несколько неясен - CASE очень понятен. Вещи, которые легко сделать в декодировании, легко сделать в CASE, вещи, которые являются трудными или почти невозможно сделать с декодированием, легко сделать в CASE. CASE, логика мудрый, побеждает руками.

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

• DECODE не может использоваться в PL/SQL.

• CASE не может использоваться для прямого сравнения нулей.

  SQL> select case null when null then null else 1 end as case1
    2        , case when null is null then null else 1 end as case2
    3        , decode(null, null, null, 1) as decode
    4    from dual
    5         ;
  
       CASE1      CASE2 DECODE
  ---------- ---------- ------
           1
  

  SQL Fiddle

Ответ 3

Я знаю, что я слишком поздно, но публикую здесь, потому что если кто-то ищет то, что, надеюсь, это может помочь. Я создал MsSql script для того же самого -

Declare @Var varchar(399)='DECODE(MyColumnName,''A'',''Auto'',''M'',''Manual'')'

Begin
Declare @Count int, @Counter int=1
Declare @TempTable table (ID int identity(1,1),Items varchar(500))
Declare @SqlText varchar(max)
Select @Var=Replace(Replace(@Var,'DECODE(',''),')','')

Insert Into @TempTable
Select * FROM [dbo].[Split] ( @Var ,',')
--Select * from @TempTable
Select @Count=Count(ID) from @TempTable

While(@Counter<[email protected])
Begin
    If(@Counter=1)
    Begin
    Select @SqlText='Case ' +Items from @TempTable Where ID=1
    End

    Else If(@[email protected])
    Begin
    Select @SqlText+=' Then ' +Items +' End' from @TempTable Where [email protected] 
    End

    Else If(@Counter%2=0)
    Begin
    Select @SqlText +=' When ' +Items from @TempTable Where [email protected]
    End

    Else If(@Counter%2=1)
    Begin
    Select @SqlText +=' Then ' +Items from @TempTable Where [email protected]
    End

    Set @Counter+=1
End

Select @SqlText SqlServerCaseStatement
End

Я использовал функцию Split выше script, если вам нужна эта функция, вы можете сослаться на ответ Ромиля - Как разделить значение, разделенное запятыми, на столбцы