首页 文章

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

提问于
浏览
2

我有一个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

这是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值的记录吗?

4 回答

  • 0

    表达式 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列中的值 .

  • 7

    不,不应该 .

    数据库中的SEC_CODE为空,因此UPPER(SEC_CODE)为空,因此它将在LIKE匹配或IS NULL之外的任何其他比较时失败 . 从技术上讲,它是一个UNKNOWN而不是FALSE,但还不足以通过测试 .

  • 3

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

  • 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'
    

    使其具体化并提高性能 .

相关问题