sql - Oracle PL/SQL 字符串比较问题

Question

我有以下 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还有另一种比较两个字符串的常用方法吗？

Answer 1

正如 Phil 所指出的，空字符串被视为 NULL，并且 NULL 不等于或不等于任何内容。如果您期望空字符串或 NULL，则需要使用 NVL() 处理它们:

 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 进行 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.