Oracle PL/SQL字符串比较问题 [英] Oracle PL/SQL string compare issue

问题描述

我有以下Oracle PL/SQL代码，从你们的角度来看可能会生锈:

I have the following Oracle PL/SQL codes that may be rusty from you guys perspective:

 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是否有另一种比较两个字符串的常用方法?

This is very obvious that two strings str1 and str2 are not equal, but why 'The two strings are not equal' was not printed out? Do Oracle have another common method to compare two string?

推荐答案

正如Phil所指出的，空字符串被视为NULL，并且NULL不等于或不等于任何东西.如果您希望使用空字符串或NULL，则需要使用NVL():

As Phil noted, the empty string is treated as a NULL, and NULL is not equal or unequal to anything. If you expect empty strings or NULLs, you'll need to handle those with 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;
 /

---

关于空比较:

根据关于NULL的Oracle 12c文档，为空使用IS NULL或IS NOT NULL进行的比较确实会评估为TRUE或FALSE.但是，所有其他比较的结果均为UNKNOWN，不是 FALSE.该文档进一步指出:

According to the Oracle 12c documentation on NULLS, null comparisons using IS NULL or IS NOT NULL do evaluate to TRUE or FALSE. However, all other comparisons evaluate to UNKNOWN, not FALSE. The documentation further states:

评估为未知的条件的行为几乎类似于FALSE.例如，在WHERE子句中具有条件的SELECT语句的值等于UNKNOWN的SELECT语句不返回任何行.但是，对UNKNOWN进行条件评估与FALSE的不同之处在于，对UNKNOWN进行条件评估的进一步操作将对UNKNOWN进行评估.因此，NOT FALSE评估为TRUE，但NOT UNKNOWN评估为UNKNOWN.

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提供的参考表:

A reference table is provided by 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:

I also learned that we should not write PL/SQL assuming empty strings will always evaluate as NULL:

Oracle数据库当前将长度为零的字符值视为空值.但是，在将来的发行版中，情况可能不会继续如此，因此Oracle建议您不要将空字符串与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.

这篇关于Oracle PL/SQL字符串比较问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！