Oracle nvl в предложении where показывает странные результаты?

У меня есть веб-форма, которая позволяет пользователям искать и редактировать записи из таблицы Oracle на основе параметров, переданных процессу. Вот мои данные:

CAE_SEC_ID  SEC_CODE  APPR_STATUS
1           ABC1      100
2           ABC2      100
3           ABC3      101
4           (null)    101
5           (null)    102
6           ABC4      103

А вот предложение where:

select foo 
  from bar 
 where CAE_SEC_ID = NVL(p_cae_sec_id,CAE_SEC_ID)
   and Upper(SEC_CODE) like '%' || Upper(NVL(p_sec_code,SEC_CODE)) || '%'
   and APPR_STATUS = NVL(p_appr_status, APPR_STATUS)

Использование nvl для параметров должно возвращать только совпавшие записи, если какой-либо из параметров имеет значения, и все записи, если ни один из параметров не имеет значений. Все довольно стандартно, или мне так казалось. Однако, когда я выполняю поиск без каких-либо значений параметров, запрос не возвращает записи с нулевым значением SEC_CODE, т.е. возвращаются только записи 1, 2, 3 и 6. Разве предложение where выше не должно включать записи с нулевыми значениями SEC_CODE?


oracle plsql nvl
person Ciarán Bruen    schedule 02.03.2011    source источник

Ответы (4)


arrow_upward
4
arrow_downward

Нет, не должно.

SEC_CODE в базе данных имеет значение NULL, поэтому UPPER (SEC_CODE) имеет значение NULL, и поэтому он завершится ошибкой при совпадении LIKE или почти любом другом сравнении, кроме IS NULL. Технически это НЕИЗВЕСТНО, а не ЛОЖЬ, но этого недостаточно для прохождения теста.

person Gary Myers    schedule 02.03.2011

arrow_upward
7
arrow_downward

Проблема в том, что значение SEC_CODE в таблице равно NULL. Это означает, что UPPER(sec_code) равен NULL, и ваш второй предикат упрощается до

and NULL LIKE '%%'

Подобно тому, как NULL ничему не равен и не равен чему-либо, он ни на что не похож. Скорее всего, вы хотите что-то вроде

and (Upper(SEC_CODE) like '%' || Upper(NVL(p_sec_code,SEC_CODE)) || '%' or
     (sec_code is null and p_sec_code is null))

Это вернет каждую строку, если P_SEC_CODE равно NULL, но все равно применит фильтр, если P_SEC_CODE не равно NULL.

person Justin Cave    schedule 02.03.2011

arrow_upward
3
arrow_downward

Выражение NULL = NULL оценивается как NULL, что не равно true, поэтому эти строки не будут возвращены. Если я правильно понимаю требование, вы хотите фильтровать только если параметр отличается от null, поэтому я бы переписал запрос, подобный этому, чтобы сделать фильтр более явным:

select foo from bar 
where (p_cae_sec_id is null or CAE_SEC_ID = p_cae_sec_id)
and (p_sec_code is null or Upper(SEC_CODE) like '%' || Upper(p_sec_code) || '%')
and (p_appr_status is null or APPR_STATUS = p_appr_status)

Установка для параметра p_sec_code значения null теперь будет возвращать все строки, игнорируя значение в столбце SEC_CODE.

person Jörn Horstmann    schedule 02.03.2011

arrow_upward
0
arrow_downward

Мы также можем написать запрос Йорна как

select foo from bar 
where (CASE WHEN p_cae_sec_id is null THEN 'Y'
            WHEN CAE_SEC_ID = p_cae_sec_id THEN 'Y'
                    ELSE 'N'
                    END)='Y'
and   (CASE WHEN p_sec_code is null THEN 'Y'
            WHEN Upper(SEC_CODE) like '%' || Upper(p_sec_code) || '%' THEN 'Y'
                    ELSE 'N'
                    END)='Y'
and (CASE WHEN p_appr_status is null THEN 'Y'
                    WHEN APPR_STATUS = p_appr_status THEN 'Y'
                ELSE 'N'
                END)='Y'

сделать его конкретным и повысить производительность.

person Arunsiva05    schedule 03.03.2011
comment
Интересно, не могли бы вы уточнить, почему это увеличивает производительность? - person Jörn Horstmann; 05.03.2011