Problem mit Oracle PL/SQL-Zeichenfolgenvergleich
1) .Hallo, ich habe die folgenden Oracle PL/SQL-Codes, die aus Ihrer Sicht möglicherweise rostig sind:
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;
/
Es ist sehr offensichtlich, dass die beiden Strings str1 und str2 nicht gleich sind, aber warum wurde "Die zwei Strings sind nicht gleich" nicht gedruckt? Verfügen Oracle über eine andere übliche Methode, um zwei Zeichenfolgen zu vergleichen?
Wie Phil bemerkt hat, wird der leere String als NULL behandelt, und NULL ist nicht gleich oder ungleich. Wenn Sie leere Zeichenfolgen oder NULL-Werte erwarten, müssen Sie diese mit NVL() behandeln:
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;
/
Bezüglich Nullvergleiche:
Gemäß der Oracle 12c-Dokumentation zu NULLS werden Nullvergleiche mit IS NULL oder IS NOT NULL zu TRUE oder FALSE ausgewertet. Alle anderen Vergleiche werden jedoch zu UNKNOWN, nichtFALSE ausgewertet. In der Dokumentation heißt es weiter:
Eine Bedingung, die als UNBEKANNT ausgewertet wird, wirkt fast wie FALSCH. Beispielsweise gibt eine SELECT-Anweisung mit einer Bedingung in der WHERE-Klausel, die zu UNKNOWN ausgewertet wird, keine Zeilen zurück. Eine Bedingung, die zu UNKNOWN ausgewertet wird, unterscheidet sich von FALSE jedoch darin, dass weitere Operationen bei einer UNKNOWN-Zustandsbewertung zu UNKNOWN ausgewertet werden. Daher wird NOT FALSE zu TRUE ausgewertet, NOT UNKNOWN jedoch zu UNKNOWN.
Eine Referenztabelle wird von Oracle bereitgestellt:
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
Ich habe auch gelernt, dass wir PL/SQL nicht schreiben sollten, vorausgesetzt leere Strings werden immer als NULL ausgewertet:
Oracle Database behandelt derzeit einen Zeichenwert mit einer Länge von Null als Null. In zukünftigen Versionen ist dies jedoch möglicherweise nicht weiterhin der Fall. Oracle empfiehlt, leere Zeichenfolgen nicht als Nullen zu behandeln.
Füllen Sie die Lücken in Ihrem Code aus, indem Sie die anderen Zweige in die Logik einfügen und sehen, was passiert:
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>
Die beiden Saiten sind also weder gleich noch sind sie nicht gleich? Huh
Es kommt darauf an. Oracle behandelt eine leere Zeichenfolge als NULL. Wenn wir versuchen, einen NULL-Wert und einen anderen String zu vergleichen, ist das Ergebnis weder TRUE noch FALSE, sondern NULL. Dies bleibt auch dann der Fall, wenn der andere String auch NULL ist.
Ich vergleiche Strings mit = und nicht mit <>. Ich habe herausgefunden, dass = in diesem Zusammenhang in vernünftigerer Weise zu funktionieren scheint als <>. Ich habe angegeben, dass zwei leere (oder NULL) Zeichenfolgen gleich sind. Die echte Implementierung gibt PL/SQL boolean zurück, aber hier habe ich das in pls_integer geändert (0 ist falsch und 1 ist wahr), um die Funktion leicht demonstrieren zu können.
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;
/
Um die Kernfrage zu beheben: "Wie kann ich feststellen, dass diese beiden Variablen nicht den gleichen Wert haben, wenn eine von ihnen null ist?", Mag ich den Ansatz von nvl(my_column, 'some value that will never, ever, ever appear in the data and I can be absolutely sure of that') nicht, weil Sie nicht immer einen Wert garantieren können erscheint nicht ... besonders bei NUMBERs.
Ich habe folgendes verwendet:
if (str1 is null) <> (str2 is null) or str1 <> str2 then
dbms_output.put_line('not equal');
end if;
Haftungsausschluss: Ich bin kein Oracle-Zauberer und habe mir diesen selbst ausgedacht und er habe ihn anderswo nicht gesehen. Es gibt also einen subtilen Grund, warum dies eine schlechte Idee ist. Die von APC erwähnte Falle wird jedoch vermieden, da der Vergleich einer Null mit etwas anderem weder WAHR noch FALSCH, sondern NULL ergibt. Da die Klauseln (str1 is null) immer TRUE oder FALSE zurückgeben, niemals NULL.
(Beachten Sie, dass PL/SQL eine Kurzschlussbewertung durchführt, wie in hier angegeben.)
Ändern Sie nur die Zeile str1: = ''; bis str1: = '';
Ich habe eine gespeicherte Funktion für diesen Textvergleich erstellt:
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;
Zum Beispiel:
SELECT * FROM MyTable WHERE TextCompare(MyTable.a, '>=', MyTable.b) = 1;