Как в запросе игнорировать параметр в WHERE, если его значение NULL?

Question

Eсть такая процедура:

PROCEDURE ACTIVITY(LEGAL_FORM_ID IN NUMBER,
                   CUST_TYPE_ID  IN NUMBER,
                   FIN_SECT_ID   IN NUMBER,
                   SECTOR_ID     IN NUMBER,
                   ACTIVITY_ID   IN NUMBER,
                   O_RES OUT SYS_REFCURSOR) IS
BEGIN
    OPEN O_RES FOR
        SELECT 
          A.LEGAL_FORM_ID,
          A.CLIENT_TYPE_ID AS CUST_TYPE_ID,
          B.NAME AS CUST_TYPE_NAME,
        FROM RK.RS_COMPANY_ACTIVITY A
        JOIN RK.RS_CLIENT_TYPE B 
        ON A.CLIENT_TYPE_ID = B.ID
        WHERE A.STATUS_ID = 1;
END;

Мне нужно написать такую логику: если один из параметров, которые передаються процедуре, NULL, то тогда надо вернуть все возможные варианты соответствующей колонки.

Например, CUST_TYPE_ID = 2, а остальные параметры NULL, то надо вернуть все колонки, где CUST_TYPE_ID равно 2-ум. А если все параметры NULL, то надо вернуть всё.

Answer 1

На таких данных:

create table t1 (col1 not null, col2, col3) as
    select rownum, mod (rownum,2), mod (rownum,3) from dual connect by level<=9
    union all
    select 0, null, null from dual
/

Стандартное решение:

create or replace procedure proc (col1 int, col2 int, col3 int, rc out sys_refcursor) as
begin
    open rc for 
        select col1, col2, col3
        from t1 t
        where 
            (t.col1 = proc.col1 or proc.col1 is null) and 
            (t.col2 = proc.col2 or proc.col2 is null) and 
            (t.col3 = proc.col3 or proc.col3 is null); 
end;
/

var rc refcursor
exec proc (col1=>null, col2=>null, col3=>0, rc=>:rc)

      COL1       COL2       COL3
---------- ---------- ----------
         3          1          0
         6          0          0
         9          1          0

Для колонок, определённых как NOT NULL, можно упростить условие:

where
    col1 = coalesce (proc.col1, t.col1) and 
    [...]

БЛОГ НА HUSL

Как в запросе игнорировать параметр в WHERE, если его значение NULL?

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