Oracle PL/SQL 字符串比较问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7286047/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle PL/SQL string compare issue
提问by C.c
I have the following Oracle PL/SQL codes that may be rusty from you guys perspective:
从你们的角度来看，我有以下 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;
/
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?
这很明显，两个字符串str1和str2不相等，但是为什么'The两个字符串不相等'没有打印出来呢？Oracle 是否有另一种比较两个字符串的常用方法？
回答by Wolf
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():
正如 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;
/
Concerning null comparisons:
关于空比较：
According to the Oracle 12c documentation on NULLS, null comparisons using IS NULLor IS NOT NULLdo evaluate to TRUEor FALSE. However, all other comparisons evaluate to UNKNOWN, notFALSE. The documentation further states:
根据有关 NULLS的Oracle 12c 文档，使用IS NULLor 的空比较IS NOT NULL计算结果为TRUEor 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.
评估为 UNKNOWN 的条件几乎就像 FALSE。例如，在 WHERE 子句中具有计算结果为 UNKNOWN 的条件的 SELECT 语句不返回任何行。但是，评估为 UNKNOWN 的条件与 FALSE 的不同之处在于，对 UNKNOWN 条件评估的进一步操作将评估为 UNKNOWN。因此，NOT FALSE 评估为 TRUE，但 NOT UNKNOWN 评估为 UNKNOWN。
A reference table is provided by Oracle:
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
I also learned that we should not write PL/SQL assuming empty strings will always evaluate as NULL:
我还了解到我们不应该编写 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.
Oracle 数据库当前将长度为零的字符值视为空值。但是，这在未来版本中可能不再适用，Oracle 建议您不要将空字符串视为空字符串。
回答by APC
Let's fill in the gaps in your code, by adding the other branches in the logic, and see what happens:
让我们通过在逻辑中添加其他分支来填补代码中的空白，看看会发生什么：
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>
So the two strings are neither the same nor are they not the same? Huh?
那么这两个字符串既不相同也不相同？嗯？
It comes down to this. Oracle treats an empty string as a NULL. If we attempt to compare a NULL and another string the outcome is not TRUE nor FALSE, it is NULL. This remains the case even if the other string is also a NULL.
这归结为这一点。Oracle 将空字符串视为 NULL。如果我们尝试比较一个 NULL 和另一个字符串，结果既不是 TRUE 也不是 FALSE，它是 NULL。即使另一个字符串也是 NULL，情况仍然如此。
回答by user272735
I compare strings using =and not <>. I've found out that in this context =seems to work in more reasonable fashion than <>. I have specified that two empty (or NULL) strings are equal. The real implementation returns PL/SQL boolean, but here I changed that to pls_integer (0 is false and 1 is true) to be able easily demonstrate the function.
我使用=和 not比较字符串<>。我发现在这种情况下=似乎比<>. 我已指定两个空（或 NULL）字符串相等。真正的实现返回 PL/SQL 布尔值，但在这里我将其更改为 pls_integer（0 为假，1 为真）以便能够轻松演示该函数。
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;
/
回答by Andrew Spencer
To fix the core question, "how should I detect that these two variables don't have the same value when one of them is null?", I don't like the approach of nvl(my_column, 'some value that will never, ever, ever appear in the data and I can be absolutely sure of that')because you can't always guarantee that a value won't appear... especially with NUMBERs.
为了解决核心问题，“当其中一个为空时，我应该如何检测这两个变量的值不同？”，我不喜欢 的方法，nvl(my_column, 'some value that will never, ever, ever appear in the data and I can be absolutely sure of that')因为你不能总是保证一个值会赢不出现...尤其是数字。
I have used the following:
我使用了以下内容：
if (str1 is null) <> (str2 is null) or str1 <> str2 then
dbms_output.put_line('not equal');
end if;
Disclaimer: I am not an Oracle wizard and I came up with this one myself and have not seen it elsewhere, so there may be some subtle reason why it's a bad idea. But it does avoid the trap mentioned by APC, that comparing a null to something else gives neither TRUE nor FALSE but NULL. Because the clauses (str1 is null)will always return TRUE or FALSE, never null.
免责声明：我不是 Oracle 向导，我自己想出了这个，并且没有在其他地方见过它，所以可能有一些微妙的原因为什么它是一个坏主意。但它确实避免了 APC 提到的陷阱，即将 null 与其他内容进行比较既不给出 TRUE 也不给出 FALSE，而是给出 NULL。因为子句(str1 is null)将始终返回 TRUE 或 FALSE，永远不会为空。
(Note that PL/SQL performs short-circuit evaluation, as noted here.)
（请注意，PL / SQL进行短路评价，如所指出的在这里）。
回答by David Gausmann
I've created a stored function for this text comparison purpose:
我为此文本比较目的创建了一个存储函数：
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;
In example:
例如：
SELECT * FROM MyTable WHERE TextCompare(MyTable.a, '>=', MyTable.b) = 1;
回答by MARIO GARCIA
Only change the line str1:='';to str1:=' ';
只更改行 str1:=''; 到 str1:='';
回答by Jo?o Rafael
To the first question:
对于第一个问题：
Probably the message wasn't print out because you have the output turned off. Use these commands to turn it back on:
可能消息没有打印出来，因为您关闭了输出。使用这些命令将其重新打开：
set serveroutput on
exec dbms_output.enable(1000000);
On the second question:
关于第二个问题：
My PLSQL is quite rusty so I can't give you a full snippet, but you'll need to loop over the result set of the SQL query and CONCAT all the strings together.
我的 PLSQL 很生疏，所以我不能给你一个完整的片段，但你需要循环 SQL 查询的结果集并将所有字符串连接在一起。
