Как заменить оператор CASE на функцию/процедуру?

Question

У меня есть оператор case, который имеет очень длинную запись, например:

select b1.colum1,b1.colum2, b1.colum3, b2.colum4,
       
       case 
       when  b1.colum1  is not null  then b2.colum4
       when  b1.colum2 like 'value1 %' or b1.colum2 like 'value2 %'  ... or b1.colum2 like 'value150 %'
       than SUBSTR(b1.colum3, 1, 5 )
       else 'error'
       end as colum5
       
from base1 as b1 left join  base2 b2 on b1.colum0=b2.colum0

Он занимает очень много места в коде, из-за чего затрудняет чтение кода.

Чем его можно заменить (функция/процедура) или как изменить чтобы в основном коде он не отображался полностью?

Answer 1

Да, CASE можно выделить в отдельную функцию:

--придумайте для нее говорящее название
create or replace function to_column5(colum1 in /*здесь тип*/,
                                      colum2 in /*здесь тип*/,
                                      colum3 in /*здесь тип*/,
                                      colum4 in /*здесь тип*/) return /*здесь тип сolum5*/ is
begin

  return case 
       when  colum1  is not null  then colum4
       when  colum2 like 'value1 %' or colum2 like 'value2 %'  ... or colum2 like 'value150 %'
       then SUBSTR(colum3, 1, 5 )
       else 'error'
       end;
end to_column5;

Затем эту функцию использовать в запросах, других процедурах.

select b1.colum1,b1.colum2, b1.colum3, b2.colum4, 
       to_column5(b1.colum1, b1.colum2, b1.colum3, b2.colum4) as colum5      
from base1 as b1 left join  base2 b2 on b1.colum0=b2.colum0

При желании, можно вынести 150 разных значений в отдельную таблицу и обращаться к ней в подзапросе (Существует ли комбинация операторов LIKE и IN в условии запроса?). Так будет легче править список значений. Только нужно будет проверить как это отразится на производительности.

Answer 2

В дополнение к правильному и принятому ответу.

Функции, особенно реализованые под конкретное бизнес требование, следует собирать в пакеты. Чтобы избежать длинных выражений col2 like 'val1%'[...] or col2 like 'valN%', следует также объявить функцию.

Воспроизводимый пример:

create or replace package pack as
    function applyColumnExpr (
        col1 varchar2, col2 varchar2, patterns strlist, col3 varchar2) return varchar2; 
end;
/
create or replace package body pack as
    function valueLike (val varchar2, patterns strlist) return boolean is
    begin 
        for i in 1..patterns.count loop 
            if val like patterns(i) then return true; end if; 
        end loop;
        return false;
    end;
    function applyColumnExpr (
        col1 varchar2, col2 varchar2, patterns strlist, col3 varchar2) return varchar2 is
    begin
        return case 
            when col1 is not null then col2
            when valueLike (col2, patterns) then col3
            else 'error' end;
    end;
end;
/

Вызвать в запросе и результат:

with patterns (list) as (
    select strlist ('a%','b%') from dual 
) 
select col1, col2, col3, 
    pack.applyColumnExpr (col1, col2, patterns.list, col2) res 
from t1, patterns 
/

COL1 COL2 COL3 RES     
---- ---- ---- --------
     aaa  bbb  aaa     
     bbb  ccc  bbb     
     ccc  ddd  error   
xx   ccc  ddd  ccc

Подготовка схемы данных для примера:

create table t1 (col1, col2, col3) as
    select null, 'aaa', 'bbb' from dual union all
    select null, 'bbb', 'ccc' from dual union all
    select null, 'ccc', 'ddd' from dual union all
    select 'xx', 'ccc', 'ddd' from dual
/
create or replace type strlist is table of varchar2 (96)
/

БЛОГ НА HUSL

Как заменить оператор CASE на функцию/процедуру?

Ответы (2 шт):