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;
 /

<小时>

关于空值比较:

根据 Oracle 12c 关于 NULLS 的文档，null使用 IS NULL 或 IS NOT NULL 的比较结果为 TRUE 或 FALSE.但是，所有其他比较结果为 UNKNOWN，not 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:

评估为 UNKNOWN 的条件几乎与 FALSE 类似.例如，在 WHERE 子句中具有计算结果为 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 建议您不要将空字符串视为空字符串.

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屋！