Oracle PL/SQL字符串比较问题

DECLARE
 str1  varchar2(4000);
 str2  varchar2(4000);
 BEGIN
   str1:='';
   str2:='sdd';
   IF(str1<>str2) THEN
    dbms_output.put_line('The two strings is not equal');
   END IF;
 END;
 /

这很明显，str1和str2两个字符串不相等，但为什么“两个字符串不相等”没有打印出来？ Oracle有另外一种常用的方法来比较两个字符串？

DECLARE
 str1  varchar2(4000);
 str2  varchar2(4000);
 BEGIN
   str1:='';
   str2:='sdd';
-- Provide an alternate null value that does not exist in your data:
   IF(NVL(str1,'X') != NVL(str2,'Y')) THEN
    dbms_output.put_line('The two strings are not equal');
   END IF;
 END;
 /

关于空比较：

根据Oracle 12c documentation on NULLS，使用IS NULL或IS NOT NULL的空比较会计算为TRUE或FALSE。然而，所有其他比较评估为UNKNOWN，而不是FALSE。文件进一步说明：

A condition that evaluates to UNKNOWN acts almost like FALSE. For example,a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows. However,a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus,NOT FALSE evaluates to TRUE,but NOT UNKNOWN evaluates to UNKNOWN.

Oracle提供了一个参考表：

Condition       Value of A    Evaluation
----------------------------------------
a IS NULL       10            FALSE
a IS NOT NULL   10            TRUE        
a IS NULL       NULL          TRUE
a IS NOT NULL   NULL          FALSE
a = NULL        10            UNKNOWN
a != NULL       10            UNKNOWN
a = NULL        NULL          UNKNOWN
a != NULL       NULL          UNKNOWN
a = 10          NULL          UNKNOWN
a != 10         NULL          UNKNOWN

我也知道，我们不应该写PL / SQL，假设空字符串总是会被计算为NULL：

Oracle Database currently treats a character value with a length of zero as null. However,this may not continue to be true in future releases,and Oracle recommends that you do not treat empty strings the same as nulls.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!