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，仍应用过滤器。