Verzweigungsanweisungen im SELECT-Befehl. NVL-Konvertierungen für verschiedene Datentypen. Nvl-SQL-Beschreibung

NVL-Funktion

Die NVL-Funktion wird im Allgemeinen am häufigsten verwendet. Die Funktion empfängt zwei Parameter: NVL(expr1, expr2). Wenn der erste Parameter expr1 nicht NULL ist, gibt die Funktion ihren Wert zurück. Wenn der erste Parameter NULL ist, gibt die Funktion stattdessen den Wert des zweiten Parameters expr2 zurück.

Schauen wir uns ein praktisches Beispiel an. Das COMM-Feld in der EMP-Tabelle kann NULL-Werte enthalten. Beim Ausführen einer Abfrage wie:

SELECT EMPNO, ENAME, COMM, NVL(COMM, 0) NVL_COMM

VON SCOTT.EMP

der NULL-Wert wird durch Null ersetzt. Beachten Sie, dass einem Wert, der mithilfe einer Funktion generiert wird, ein Alias ​​zugewiesen wird. Die Abfrageergebnisse sehen folgendermaßen aus:

Funktion CEIL(n)

Die CEIL-Funktion gibt die kleinste Ganzzahl zurück, die größer oder gleich der als Parameter übergebenen Zahl n ist. Zum Beispiel:

WÄHLEN SIE DECKE(100) X1, DECKE(-100) X2, DECKE(100,2) X3, DECKE(-100,2) X4

VON DUAL

Funktion TRUNC(n [,m])

Die TRUNC-Funktion gibt die auf m Dezimalstellen gekürzte Zahl n zurück. Der Parameter m darf nicht angegeben werden; in diesem Fall wird n auf eine Ganzzahl gekürzt.

SELECT TRUNC(100.25678) X1, TRUNC(-100.25678) X2, TRUNC(100.99) X3,

TRUNC(100.25678, 2) X4

VON DUAL

Funktion SIGN(n)

Die Funktion SIGN bestimmt das Vorzeichen einer Zahl. Wenn n positiv ist, gibt die Funktion 1 zurück. Wenn n negativ ist, gibt sie -1 zurück. Wenn gleich Null, wird 0 zurückgegeben. Beispiel:

SELECT SIGN(100.22) X1, SIGN(-100.22) X2, SIGN(0) X3

VON DUAL

Ein interessantes Merkmal dieser Funktion ist die Fähigkeit, m gleich Null zu übertragen, ohne einen Fehler bei der Division durch 0 zu verursachen.

Funktion POWER(n, m)

Die POWER-Funktion erhöht die Zahl n auf die Potenz m. Der Grad kann gebrochen und negativ sein, was die Möglichkeiten dieser Funktion erheblich erweitert.

SELECT POWER(10, 2) X1, POWER(100, 1/2) X2,

LEISTUNG(1000, 1/3) X3, LEISTUNG(1000, -1/3) X4

VON DUAL

In einigen Fällen kann es beim Aufruf dieser Funktion zu einer Ausnahme kommen. Zum Beispiel:

WÄHLEN SIE LEISTUNG (-100, 1/2) X2

VON DUAL

In diesem Fall wird versucht, die Quadratwurzel einer negativen Zahl zu berechnen, was zu einem ORA-01428-Fehler „Argument out of range“ führt.

Funktion SQRT(n)

Diese Funktion gibt die Quadratwurzel von n zurück. Zum Beispiel:

SELECT SQRT(100) X

VON DUAL

EXP(n)- und LN(n)-Funktionen

Die EXP-Funktion potenziert e mit n und die LN-Funktion berechnet den natürlichen Logarithmus von n (n muss größer als Null sein). Beispiel:

SELECT EXP(2) X1, LN(1) X2, LN(EXP(2)) X3

Wir haben verschachtelte Funktionen etwas früher angesprochen, jetzt werden wir sie etwas detaillierter betrachten. Wir betrachten auch Funktionen zum Arbeiten mit dem NULL-Wert und Funktionen, die bei der Implementierung der Verzweigungsoperation in einer Abfrage helfen.

Verschachtelte Funktionen

Verschachtelte Funktionen verwenden den Rückgabewert einer Funktion als Eingabeparameter für eine andere Funktion. Funktionen geben immer nur einen Wert zurück. Daher können Sie das Ergebnis eines Funktionsaufrufs als Wertliteral behandeln, wenn Sie es als Parameter für einen anderen Funktionsaufruf verwenden. String-Funktionen können auf jeder Verschachtelungsebene verschachtelt werden. Ein Funktionsaufruf sieht so aus

Funktion1(Parameter1, Parameter2, …) = Ergebnis

Das Ersetzen eines Funktionsparameters durch einen Aufruf einer anderen Funktion kann zu Ausdrücken wie führen

F1(param1.1, F2(param2.1, param2.2, F3(param3.1)), param1.3)

Verschachtelte Funktionen werden zunächst ausgewertet, bevor ihre Ergebnisse als Eingabewerte für andere Funktionen verwendet werden. Funktionen werden von der tiefsten bis zur obersten Verschachtelungsebene von links nach rechts ausgewertet. Der vorherige Ausdruck wird wie folgt ausgeführt

1. Funktion F3(param1) wird ausgewertet und der Rückgabewert wird als dritter Parameter für Funktion 2 verwendet, nennen wir ihn param2.3
2. Anschließend wird die Funktion F2(param1, param2.2, param2.3) ausgewertet und der Rückgabewert als zweiter Parameter der Funktion F1 - param1.2 verwendet
3. Abschließend wird die Funktion F1(param1, param2, param1.3) ausgewertet und das Ergebnis an das aufrufende Programm zurückgegeben.

Somit befindet sich die F3-Funktion auf der dritten Verschachtelungsebene.

Betrachten wir die Anfrage

select next_day(last_day(sysdate)-7, ‚di‘) from dual;

1. In dieser Abfrage gibt es drei Funktionen, von der unteren zur oberen Ebene: SYSDATE, LAST_DAY, NEXT_DAY. Die Anfrage wird wie folgt durchgeführt
2. Die verschachtelte SYSDATE-Funktion wird ausgeführt. Es gibt den Strom zurück Systemzeit. Nehmen wir an, das aktuelle Datum ist der 28. Oktober 2009
3. Als nächstes wird das Ergebnis der Second-Level-Funktion LAST_DAY berechnet. LAST_DATE('28-OCT-2009') gibt den letzten Tag im Oktober 2009 zurück, also den 31. Oktober 2009.
4. Dann werden von diesem Datum sieben Tage abgezogen – es ergibt sich der 24. Oktober.
5. Abschließend wird die Funktion NEXT_DAY('24-OCT-2009', 'tue') ausgewertet und die Abfrage gibt den letzten Dienstag im Oktober zurück – in unserem Beispiel der 27.OCT-2009.

Es ist ziemlich schwierig, komplexe Ausdrücke mit vielen verschachtelten Funktionsaufrufen zu verstehen und zu erstellen, aber das erfordert Zeit und Übung. Sie können solche Ausdrücke in Teile aufteilen und sie separat testen. Die DUAL-Tabelle ist sehr nützlich zum Testen von Abfragen und Funktionsaufrufergebnissen. Sie können kleine Komponenten testen und debuggen, die dann zu einem großen gewünschten Ausdruck kombiniert werden.

Verzweigungsfunktionen

Verzweigungsfunktionen, auch bekannt als IF-THEN-ELSE, werden verwendet, um den Ausführungspfad abhängig von bestimmten Umständen zu bestimmen. Verzweigungsfunktionen geben je nach Ergebnis der Bedingungsauswertung unterschiedliche Ergebnisse zurück. Die Gruppe solcher Funktionen umfasst Funktionen zum Arbeiten mit dem NULL-Wert: NVL, NVL2, NULLIF und COALESCE. Und auch allgemeine Funktionen, dargestellt durch die DECODE-Funktion und den CASE-Ausdruck. Die DECODE-Funktion ist eine Oracle-Funktion, während der CASE-Ausdruck im ANSI SQL-Standard vorhanden ist.

NVL-Funktion

Die NVL-Funktion testet den Wert einer Spalte oder eines Ausdrucks eines beliebigen Datentyps gegen NULL. Wenn der Wert NULL ist, wird ein alternativer Standardwert ungleich NULL zurückgegeben, andernfalls wird der ursprüngliche Wert zurückgegeben.

Die NVL-Funktion verfügt über zwei erforderliche Parameter und die Syntax lautet NVL(original, ifnull), wobei original der zu prüfende Originalwert und ifnull das von der Funktion zurückgegebene Ergebnis ist, wenn der Originalwert NULL ist. Der Datentyp der Parameter ifnull und original muss kompatibel sein. Das heißt, entweder muss der Datentyp derselbe sein oder es muss möglich sein, Werte implizit von einem Typ in einen anderen zu konvertieren. Die NVL-Funktion gibt einen Wert desselben Datentyps zurück wie der Datentyp des ursprünglichen Parameters. Betrachten wir drei Abfragen

Abfrage 1: select nvl(1234) from dual;

Abfrage 2: select nvl(null, 1234) from dual;

Abfrage 3: select nvl(substr('abc', 4), 'No substring exist') from dual;

Da die NVL-Funktion zwei Parameter erfordert, gibt Anfrage 1 den Fehler ORA-00909 zurück: ungültige Anzahl von Argumenten. Abfrage 2 gibt 1234 zurück, da der NULL-Wert überprüft wurde und dieser NULL ist. Abfrage drei verwendet eine verschachtelte SUBSTR-Funktion, die versucht, das vierte Zeichen aus einer drei Zeichen langen Zeichenfolge zu extrahieren, wobei NULL zurückgegeben wird, und die NVL-Funktion die Zeichenfolge „Kein Sbusstring vorhanden“ zurückgibt.

Die NVL-Funktion ist beim Arbeiten mit Zahlen sehr nützlich. Es wird verwendet, um NULL-Werte in 0 umzuwandeln, sodass arithmetische Operationen mit Zahlen nicht NULL zurückgeben

NVL-Funktion2

Die NVL2-Funktion bietet mehr Funktionalität als NVL, dient aber auch der Verarbeitung von NULL-Werten. Es testet den Wert einer Spalte oder eines Ausdrucks eines beliebigen Typs gegen NULL. Wenn der Wert nicht NULL ist, wird der zweite Parameter zurückgegeben, andernfalls wird der dritte Parameter zurückgegeben, im Gegensatz zur NVL-Funktion, die in diesem Fall den ursprünglichen Wert zurückgibt.

Die NVL2-Funktion verfügt über drei erforderliche Parameter und die Syntax lautet NVL2(original, ifnotnull, ifnull), wobei original der getestete Wert ist, ifnotnull der zurückgegebene Wert ist, wenn original nicht NULL ist, und ifnull der zurückgegebene Wert ist, wenn original NULL ist. Die Datentypen der Parameter ifnotnull und ifnull müssen kompatibel sein und dürfen nicht vom Typ LONG sein. Der von der NVL2-Funktion zurückgegebene Datentyp entspricht dem Datentyp des Parameters ifnotnull. Schauen wir uns ein paar Beispiele an

Abfrage 1: select nvl2(1234, 1, ‚a string‘) from dual;

Abfrage 2: select nvl2(null, 1234, 5678) from dual;

Abfrage 3: select nvl2(substr('abc', 2), 'Not bc', 'No substring') from dual;

Der Parameter ifnotnull in Anfrage 1 ist eine Zahl und der Parameter ifnull eine Zeichenfolge. Da die Datentypen nicht kompatibel sind, wird der Fehler „ORA-01722: ungültige Nummer“ zurückgegeben. Abfrage zwei gibt den Parameter ifnull zurück, da original NULL ist und das Ergebnis 5678 ist. Abfrage drei verwendet die SUBSTR-Funktion, die „bc“ zurückgibt, und ruft NVL2(‘bc‘,‘Not bc‘,‘No substring‘) auf – was zurückgibt der ifnotnull-Parameter – „Nicht bc“.

NULLIF-Funktion

Die NULLIF-Funktion prüft, ob zwei Werte identisch sind. Bei Gleichheit wird NULL zurückgegeben, andernfalls wird der erste Parameter zurückgegeben. Die NULLIF-Funktion verfügt über zwei erforderliche Parameter und die Syntax ist NULLIF(ifunequal,Vergleichselement). Die Funktion vergleicht zwei Parameter und wenn sie identisch sind, wird NULL zurückgegeben, andernfalls ist der Parameter ifunequal. Betrachten wir die Anfragen

Abfrage 1: Wählen Sie nullif(1234, 1234) aus dual aus.

Abfrage eins gibt NULL zurück, da die Parameter identisch sind. Die Strings in Abfrage 2 werden nicht in ein Datum umgewandelt, sondern als Strings verglichen. Da die Zeichenfolgen unterschiedlich lang sind, wird der Parameter ifunequal am 24. Juli 2009 zurückgegeben.

In Abbildung 10-4 ist die NULLIF-Funktion in der NVL2-Funktion verschachtelt. Die NULLIF-Funktion wiederum verwendet die Funktionen SUBSTR und UPPER als Teil eines Ausdrucks im Parameter ifunequal. Die Spalte EMAIL wird mit diesem Ausdruck verglichen, der den ersten Buchstaben des Vornamens kombiniert mit dem Nachnamen für Mitarbeiter zurückgibt, deren Vorname 4 Zeichen lang ist. Wenn diese Werte gleich sind, gibt NULLIF NULL zurück, andernfalls wird der Wert des Parameters ifunequal zurückgegeben. Diese Werte werden als Parameter für die NVL2-Funktion verwendet. NVL2 wiederum gibt eine Beschreibung zurück, ob die verglichenen Elemente übereinstimmten oder nicht.

Abbildung 10-4 – Verwendung der NULLIF-Funktion

COALESCE-Funktion

Die Funktion COALESCE gibt den ersten Nicht-NULL-Wert aus der Parameterliste zurück. Wenn alle Parameter NULL sind, wird NULL zurückgegeben. Die COALESCE-Funktion verfügt über zwei erforderliche Parameter und eine beliebige Anzahl optionaler Parameter. Die Syntax lautet COALESCE(expr1, expr2, ..., exprn), wobei das Ergebnis expr1 ist, wenn der Wert von expr 1 nicht NULL ist, andernfalls lautet das Ergebnis expr2, wenn es nicht NULL ist usw. COALESCE hat die gleiche Bedeutung wie verschachtelte NVL-Funktionen

COALESCE(expr1, expr2) = NVL(expr1, expr2)

COALESCE(expr1, expr2, expr3) = NVL(expr1,NVL(expr2, expr3))

Wenn ein Nicht-NULL-Wert gefunden wird, entspricht der Datentyp des zurückgegebenen Werts dem Datentyp des ersten Nicht-NULL-Werts. Um den Fehler „ORA-00932: inkonsistente Datentypen“ zu vermeiden, müssen alle Nicht-NULL-Parameter mit dem ersten Nicht-NULL-Parameter kompatibel sein. Schauen wir uns drei Beispiele an

Abfrage 1: select coalesce(null, null, null, ‚a string‘) from dual;

Abfrage 2: Wählen Sie „coalesce(null, null, null)“ aus „dual“ aus;

Abfrage 3: select coalesce(substr('abc', 4), 'Not bc', 'No substring') from dual;

Abfrage 1 gibt den vierten Parameter zurück: eine Zeichenfolge, da es sich um den ersten Nicht-NULL-Parameter handelt. Abfrage zwei gibt NULL zurück, da alle Parameter NULL sind. Abfrage 3 wertet den ersten Parameter aus, ruft den NULL-Wert ab und gibt den zweiten Parameter zurück, da es sich um den ersten Nicht-NULL-Parameter handelt.

Die NVL2-Funktionsparameter können verwirrend sein, wenn Sie bereits mit der NVL-Funktion vertraut sind. NVL(original, ifnull) gibt „original“ zurück, wenn der Wert nicht NULL ist, andernfalls ifnull. NVL2(original, ifnotnull, ifnull) gibt ifnotnull zurück, wenn original nicht NULL ist, andernfalls ifnull. Die Verwirrung entsteht durch die Tatsache, dass der zweite Parameter der NVL-Funktion ifnull ist, während die NVL2-Funktion ifnotnull ist. Verlassen Sie sich also nicht auf die Position des Parameters in der Funktion.

DECODE-Funktion

Die DECODE-Funktion implementiert die Wenn-Dann-Sonst-Logik, indem sie die ersten beiden Parameter auf Gleichheit prüft und einen dritten Wert zurückgibt, wenn sie gleich sind, oder einen anderen Wert, wenn sie nicht gleich sind. Die DECODE-Funktion verfügt über drei erforderliche Parameter und die Syntax lautet DECODE(expr1, comp1, iftrue1, , ). Diese Parameter werden wie im folgenden Pseudocode-Beispiel gezeigt verwendet

WENN expr1=comp1, dann wird iftrue1 zurückgegeben

Andernfalls wenn expr1=comp2, dann iftrue2 zurückgeben

Andernfalls wenn exprN=compN, dann iftrueN zurückgeben

Sonst return NULL|iffalse;

Zuerst wird expr1 mit comp1 verglichen. Wenn sie gleich sind, wird iftrue1 zurückgegeben. Wenn expr1 nicht gleich comp1 ist, hängt das weitere Vorgehen davon ab, ob die Parameter comp2 und iftrue2 angegeben sind. Falls angegeben, wird der Wert von expr1 mit comp2 verglichen. Wenn die Werte gleich sind, wird iftrue2 zurückgegeben. Wenn nicht, werden die Parameterpaare compN, iftrueN, expr1 und compN verglichen und bei Gleichheit wird iftrueN zurückgegeben. Wenn in keinem Parametersatz eine Übereinstimmung gefunden wurde, wird entweder iffalse zurückgegeben, wenn dieser Parameter angegeben wurde, oder NULL.

Alle Parameter in der DECODE-Funktion können Ausdrücke sein. Der Typ des Rückgabewerts entspricht dem Typ des ersten validierenden Elements – des Parameters comp 1. Ausdruck expr 1 wird implizit in den Datentyp des comp-Parameters konvertiert1. Alle anderen Verfügbare Optionen komp 1...kompN werden auch implizit in den Typ comp konvertiert 1. DECODE behandelt einen NULL-Wert als gleich einem anderen NULL-Wert, d. h. Wenn expr1 NULL und comp3 NULL ist und comp2 nicht NULL ist, wird iftrue3 zurückgegeben. Schauen wir uns ein paar Beispiele an

Abfrage 1: select decode(1234, 123, ‚123 is a match‘) from dual;

Abfrage 2: select decode(1234, 123, ‚123 is a match‘, ‚No match‘) from dual;

Abfrage 3: select decode('search', 'comp1', 'true1', 'comp2', 'true2', 'search', 'true3', substr('2search', 2, 6), 'true4', ' false') von dual;

Abfrage eins vergleicht die Werte 1234 und 123. Da sie nicht gleich sind, wird iftrue1 ignoriert und da der Wert iffalse nicht definiert ist, wird NULL zurückgegeben. Anfrage zwei ist identisch mit Anfrage 1, außer dass der Wert iffalse definiert ist. Da 1234 nicht gleich 123 ist, wird iffalse zurückgegeben – „Keine Übereinstimmung“. Abfrage drei prüft, ob die Parameterwerte mit dem Suchwert übereinstimmen. Die Parameter comp1 und comp2 sind nicht gleich „search“, daher werden die Ergebnisse von iftrue1 und iftrue2 übersprungen. Bei der dritten Vergleichsoperation des Elements comp3 (Parameterposition 6) wird eine Übereinstimmung gefunden und der Wert von iftrue3 (Parameter 7) zurückgegeben, der gleich „true3“ ist. Da eine Übereinstimmung gefunden wurde, werden keine weiteren Berechnungen durchgeführt. Das heißt, obwohl der Wert von comp4 (Parameter 8) auch mit expr1 übereinstimmt, wird dieser Ausdruck nie berechnet, da die Übereinstimmung im vorherigen Vergleich gefunden wurde.

CASE-Ausdruck

Alle Programmiersprachen der dritten und vierten Generation implementieren das Fallkonstrukt. Wie die DECODE-Funktion ermöglicht Ihnen der CASE-Ausdruck die Implementierung einer Wenn-Dann-Sonst-Logik. Es gibt zwei Möglichkeiten, den CASE-Ausdruck zu verwenden. Ein einfacher CASE-Ausdruck legt das ursprüngliche Element so fest, dass es einmal verglichen wird, und listet dann alle auf die notwendigen Voraussetzungen Schecks. Der komplexe (durchsuchte) CASE wertet beide Anweisungen für jede Bedingung aus.

Der CASE-Ausdruck verfügt über drei erforderliche Parameter. Die Ausdruckssyntax hängt vom Typ ab. Für einen einfachen CASE-Ausdruck sieht es so aus

CASE search_expr

WANN vergleich_ausdruck1 DANN iftrue1

)

Die TRUNC-Funktion gibt die auf m Dezimalstellen gekürzte Zahl n zurück. Der Parameter m darf nicht angegeben werden; in diesem Fall wird n auf eine Ganzzahl gekürzt.

SELECT TRUNC(100.25678) X1, TRUNC(-100.25678) X2, TRUNC(100.99) X3,

TRUNC(100.25678, 2) X4

VON DUAL

Funktion SIGN(n)

Die Funktion SIGN bestimmt das Vorzeichen einer Zahl. Wenn n positiv ist, gibt die Funktion 1 zurück. Wenn n negativ ist, gibt sie -1 zurück. Wenn gleich Null, wird 0 zurückgegeben. Beispiel:

SELECT SIGN(100.22) X1, SIGN(-100.22) X2, SIGN(0) X3

VON DUAL

Ein interessantes Merkmal dieser Funktion ist die Fähigkeit, m gleich Null zu übertragen, ohne einen Fehler bei der Division durch 0 zu verursachen.

Funktion POWER(n, m)

Die POWER-Funktion erhöht die Zahl n auf die Potenz m. Der Grad kann gebrochen und negativ sein, was die Möglichkeiten dieser Funktion erheblich erweitert.

SELECT POWER(10, 2) X1, POWER(100, 1/2) X2,

LEISTUNG(1000, 1/3) X3, LEISTUNG(1000, -1/3) X4

VON DUAL

In einigen Fällen kann es beim Aufruf dieser Funktion zu einer Ausnahme kommen. Zum Beispiel:

WÄHLEN SIE LEISTUNG (-100, 1/2) X2

VON DUAL

In diesem Fall wird versucht, die Quadratwurzel einer negativen Zahl zu berechnen, was zu einem ORA-01428-Fehler „Argument out of range“ führt.

Funktion SQRT(n)

Diese Funktion gibt die Quadratwurzel der Zahl n zurück. Zum Beispiel:

SELECT SQRT(100) X

VON DUAL

EXP(n)- und LN(n)-Funktionen

Die EXP-Funktion potenziert e mit n und die LN-Funktion berechnet den natürlichen Logarithmus von n (n muss größer als Null sein). Beispiel:

SELECT EXP(2) X1, LN(1) X2, LN(EXP(2)) X3