Oracle nvl 在 where 子句中显示奇怪的结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5174673/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle nvl in where clause showing strange results?
提问by Ciaran Bruen
I have a web form that allows users to search on and edit records from an Oracle table based on parameters passed in to a proc. Here's my data:
我有一个 Web 表单，它允许用户根据传递给 proc 的参数搜索和编辑 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
And here's the where clause:
这是 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)
Using nvl on the parameters should return only the matched records if any of the parameters have values, and all records if none of the parameters have values. All pretty standard or so I thought. However when I do a search without any parameter values the query isn't returning records with a null SEC_CODE i.e. only records 1, 2, 3, and 6 are being returned. Shouldn't the where clause above include records with null SEC_CODE values?
如果任何参数有值，则在参数上使用 nvl 应仅返回匹配的记录，如果没有参数有值，则应返回所有记录。一切都很标准，我想。但是，当我在没有任何参数值的情况下进行搜索时，查询不会返回带有空 SEC_CODE 的记录，即仅返回记录 1、2、3 和 6。上面的 where 子句不应该包含具有空 SEC_CODE 值的记录吗？
回答by Justin Cave
The problem is that the SEC_CODEvalue in the table is NULL. That means that UPPER(sec_code)is NULL and your second predicate simplifies to
问题是SEC_CODE表中的值为NULL。这意味着这UPPER(sec_code)是 NULL 并且您的第二个谓词简化为
and NULL LIKE '%%'
Just like NULL is not equal to anything and not unequal to anything, it is not like anything. Most likely, you want something 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))
That will return every row if P_SEC_CODEis NULL but still apply the filter if P_SEC_CODEis non-NULL.
如果P_SEC_CODE为 NULL，则将返回每一行，但如果P_SEC_CODE为非NULL，则仍应用过滤器。
回答by Gary Myers
No it shouldn't.
不，不应该。
The SEC_CODE in the database is null, so the UPPER(SEC_CODE) is null and so it will fail on a LIKE match or pretty much any other comparison beyond IS NULL. Technically it is a UNKNOWN rather than a FALSE but is isn't enough to pass the test.
数据库中的 SEC_CODE 为空，因此 UPPER(SEC_CODE) 为空，因此它将在 LIKE 匹配或除 IS NULL 之外的几乎任何其他比较时失败。从技术上讲，它是 UNKNOWN 而不是 FALSE，但不足以通过测试。
回答by J?rn Horstmann
The expression NULL = NULLevaluates to NULL, which is not trueand so these rows won't be returned. If I understand the requirement correctly, you only want to filter if a parameter is different from null, so I would rewrite the query like this to make the filter more explicit:
表达式的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)
Setting the p_sec_code parameter to null will now return all rows, ignoring the value in the SEC_CODE column.
将 p_sec_code 参数设置为 null 现在将返回所有行，忽略 SEC_CODE 列中的值。
回答by Arunsiva05
We can also write the Jorn's query like
我们也可以编写 Jorn 的查询，如
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'
to make it concrete and increase the performance .
使其具体并提高性能。
