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

---

关于 null 比较：

根据 Oracle 12c 关于 NULL 的文档，使用 null 比较IS NULL 或 IS NOT NULL 的计算结果为 TRUE 或 FALSE。然而，所有其他比较的结果都是UNKNOWN，不是FALSE。该文件进一步指出：

评估为 UNKNOWN 的条件几乎就像 FALSE 一样。例如，WHERE 子句中的条件为 UNKNOWN 的 SELECT 语句不返回任何行。然而，评估为 UNKNOWN 的条件与 FALSE 的不同之处在于，对 UNKNOWN 条件评估的进一步操作将评估为 UNKNOWN。因此，NOT FALSE 的计算结果为 TRUE，但 NOT UNKNOWN 的计算结果为 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 数据库当前将长度为零的字符值视为空。但是，在未来的版本中，这种情况可能不会继续存在，Oracle 建议您不要将空字符串与 null 视为相同的值。

Answer 2

让我们通过在逻辑中添加其他分支来填补代码中的空白，然后看看会发生什么：

SQL> DECLARE
  2   str1  varchar2(4000);
  3   str2  varchar2(4000);
  4  BEGIN
  5     str1:='';
  6     str2:='sdd';
  7     IF(str1<>str2) THEN
  8      dbms_output.put_line('The two strings is not equal');
  9     ELSIF (str1=str2) THEN
 10      dbms_output.put_line('The two strings are the same');
 11     ELSE
 12      dbms_output.put_line('Who knows?');
 13     END IF;
 14   END;
 15  /
Who knows?

PL/SQL procedure successfully completed.

SQL>

那么这两个字符串既不相同也不不相同？啊？

归根结底就是这样。 Oracle 将空字符串视为 NULL。如果我们尝试比较 NULL 和另一个字符串，结果既不是 TRUE 也不是 FALSE，而是 NULL。即使另一个字符串也是 NULL，情况仍然如此。

Answer 3

我使用 = 而不是 <> 来比较字符串。我发现在这种情况下 = 似乎比 <> 更合理。我已指定两个空（或 NULL）字符串相等。真正的实现返回 PL/SQL 布尔值，但这里我将其更改为 pls_integer（0 为 false，1 为 true）以便能够轻松演示该函数。

create or replace function is_equal(a in varchar2, b in varchar2)
return pls_integer as
begin
  if a is null and b is null then
    return 1;
  end if;

  if a = b then
    return 1;
  end if;

  return 0;
end;
/
show errors

begin
  /* Prints 0 */
  dbms_output.put_line(is_equal('AAA', 'BBB'));
  dbms_output.put_line(is_equal('AAA', null));
  dbms_output.put_line(is_equal(null, 'BBB'));
  dbms_output.put_line(is_equal('AAA', ''));
  dbms_output.put_line(is_equal('', 'BBB'));

  /* Prints 1 */
  dbms_output.put_line(is_equal(null, null));
  dbms_output.put_line(is_equal(null, ''));
  dbms_output.put_line(is_equal('', ''));
  dbms_output.put_line(is_equal('AAA', 'AAA'));
end;
/

Answer 4

为了解决核心问题，“当其中一个为空时，我应该如何检测这两个变量不具有相同的值？”，我不喜欢 nvl(my_column, 'some value that永远、永远、永远不会出现在数据中，我可以绝对确定这一点')，因为你不能总是保证某个值不会出现......尤其是数字。

我使用了以下内容：

if (str1 is null) <> (str2 is null) or str1 <> str2 then
  dbms_output.put_line('not equal');
end if;

免责声明：我不是 Oracle 奇才，我自己想出了这个方案，并且在其他地方没有见过它，所以可能有一些微妙的原因说明它是一个坏主意。但它确实避免了 APC 提到的陷阱，即将 null 与其他内容进行比较既不会给出 TRUE 也不会给出 FALSE，而是给出 NULL。因为子句 (str1 is null) 将始终返回 TRUE 或 FALSE，而绝不会返回 null。

（请注意，PL/SQL 执行短路评估，如此处所述。 ）

Answer 5

我为此文本比较目的创建了一个存储函数：

CREATE OR REPLACE FUNCTION TextCompare(vOperand1 IN VARCHAR2, vOperator IN VARCHAR2, vOperand2 IN VARCHAR2) RETURN NUMBER DETERMINISTIC AS
BEGIN
  IF vOperator = '=' THEN
    RETURN CASE WHEN vOperand1 = vOperand2 OR vOperand1 IS NULL AND vOperand2 IS NULL THEN 1 ELSE 0 END;
  ELSIF vOperator = '<>' THEN
    RETURN CASE WHEN vOperand1 <> vOperand2 OR (vOperand1 IS NULL) <> (vOperand2 IS NULL) THEN 1 ELSE 0 END;
  ELSIF vOperator = '<=' THEN
    RETURN CASE WHEN vOperand1 <= vOperand2 OR vOperand1 IS NULL THEN 1 ELSE 0 END;
  ELSIF vOperator = '>=' THEN
    RETURN CASE WHEN vOperand1 >= vOperand2 OR vOperand2 IS NULL THEN 1 ELSE 0 END;
  ELSIF vOperator = '<' THEN
    RETURN CASE WHEN vOperand1 < vOperand2 OR vOperand1 IS NULL AND vOperand2 IS NOT NULL THEN 1 ELSE 0 END;
  ELSIF vOperator = '>' THEN
    RETURN CASE WHEN vOperand1 > vOperand2 OR vOperand1 IS NOT NULL AND vOperand2 IS NULL THEN 1 ELSE 0 END;
  ELSIF vOperator = 'LIKE' THEN
    RETURN CASE WHEN vOperand1 LIKE vOperand2 OR vOperand1 IS NULL AND vOperand2 IS NULL THEN 1 ELSE 0 END;
  ELSIF vOperator = 'NOT LIKE' THEN
    RETURN CASE WHEN vOperand1 NOT LIKE vOperand2 OR (vOperand1 IS NULL) <> (vOperand2 IS NULL) THEN 1 ELSE 0 END;
  ELSE
    RAISE VALUE_ERROR;
  END IF;
END;

在示例中：

SELECT * FROM MyTable WHERE TextCompare(MyTable.a, '>=', MyTable.b) = 1;

Answer 6

只需更改线路即可
str1:='';
到
str1:='';

Answer 7

'' 将被视为 NULL，因此，两个字符串都需要检查为 NULL。

功能：

CREATE OR REPLACE FUNCTION str_cmpr_fnc(str_val1_in IN VARCHAR2, str_val2_in IN VARCHAR2) RETURN VARCHAR2
AS
    l_result    VARCHAR2(50);
 BEGIN
    -- string comparison
    CASE
        WHEN str_val1_in IS NULL AND str_val2_in IS NULL THEN
            l_result := 'Both Unknown';
        WHEN str_val1_in IS NULL THEN
            l_result := 'Str1 Unknown';
        WHEN str_val2_in IS NULL THEN
            l_result := 'Str2 Unknown';
        ELSE
            CASE
                WHEN str_val1_in = str_val2_in THEN
                    l_result := 'Both are equel';
                ELSE
                    l_result := 'Both strings are not equal';
            END CASE;
    END CASE;
    -- return result 
    RETURN l_result;
 EXCEPTION
 WHEN OTHERS THEN
    -- set serveroutput on to get the error information
    DBMS_OUTPUT.put_line(SQLERRM||' ,'|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    -- return result
    RETURN l_result;
 END str_cmpr_fnc;

Sql语句：

SELECT str_cmpr_fnc('7', 'd') FROM DUAL;

Answer 8

对于第一个问题：

可能该消息未打印出来，因为您关闭了输出。使用这些命令将其重新打开：

set serveroutput on
exec dbms_output.enable(1000000);

关于第二个问题：

我的 PLSQL 非常生锈，所以我无法给您完整的代码片段，但您需要循环 SQL 查询的结果集并 CONCAT 所有字符串一起。