Oracle nvl 在 where 子句中显示奇怪的结果？

Question

我有一个 Web 表单，允许用户根据传递到过程的参数搜索和编辑 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 应该仅返回匹配的记录，如果没有参数具有值，则返回所有记录。一切都很标准，我是这么想的。但是，当我在没有任何参数值的情况下进行搜索时，查询不会返回具有 null SEC_CODE 的记录，即仅返回记录 1、2、3 和 6。上面的 where 子句不应该包含具有空 SEC_CODE 值的记录吗？

Answer 1

问题是表中的 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，则仍应用过滤器。

Answer 2

不，不应该。

数据库中的 SEC_CODE 为空，因此 UPPER(SEC_CODE) 为空，因此在 LIKE 匹配或除 IS NULL 之外的几乎任何其他比较中都会失败。从技术上讲，它是未知的而不是错误的，但不足以通过测试。

Answer 3

表达式 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 列中。

Answer 4

我们还可以编写 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'

具体化并提高性能。