问题描述
我有以下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:
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 PL/SQL字符串比较问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！