[Sql] Oracle Unterschiede zwischen NVL und Coalesce


Answers

NVL führt eine implizite Konvertierung in den Datentyp des ersten Parameters durch, so dass der folgende Fehler nicht auftritt

select nvl('a',sysdate) from dual;

COALESCE erwartet konsistente Datentypen.

select coalesce('a',sysdate) from dual;

wird einen 'inkonsistenten Datentypfehler' werfen

Question

Gibt es nicht offensichtliche Unterschiede zwischen NVL und Coalesce in Oracle?

Die offensichtlichen Unterschiede sind, dass coalesce das erste Nicht-Null-Item in seiner Parameterliste zurückgibt, während nvl nur zwei Parameter akzeptiert und das erste liefert, wenn es nicht null ist, andernfalls gibt es das zweite zurück.

Es scheint, dass NVL möglicherweise nur eine "Base Case" -Version von Coalesce ist.

Fehle ich etwas?




Eigentlich kann ich keiner Aussage zustimmen.

"COALESCE erwartet, dass alle Argumente den gleichen Datentyp haben."

Das ist falsch, siehe unten. Argumente können unterschiedliche Datentypen sein, die ebenfalls documented : Wenn alle Ausdrucke von expr numerischer Datentyp oder nicht numerischer Datentyp sind, der implizit in einen numerischen Datentyp konvertiert werden kann, legt Oracle Database implizit das Argument mit der höchsten numerischen Priorität fest konvertiert die verbleibenden Argumente in diesen Datentyp und gibt diesen Datentyp zurück. . Tatsächlich widerspricht dies sogar dem üblichen Ausdruck "COALESCE stoppt beim ersten Auftreten eines Nicht-Null-Wertes", andernfalls sollte Testfall Nr. 4 keinen Fehler auslösen.

Auch nach Testfall Nr. 5 führt COALESCE eine implizite Konvertierung von Argumenten durch.

DECLARE
    int_val INTEGER := 1;
    string_val VARCHAR2(10) := 'foo';
BEGIN

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '1. NVL(int_val,string_val) -> '|| NVL(int_val,string_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('1. NVL(int_val,string_val) -> '||SQLERRM ); 
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '2. NVL(string_val, int_val) -> '|| NVL(string_val, int_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('2. NVL(string_val, int_val) -> '||SQLERRM ); 
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '3. COALESCE(int_val,string_val) -> '|| COALESCE(int_val,string_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('3. COALESCE(int_val,string_val) -> '||SQLERRM ); 
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '4. COALESCE(string_val, int_val) -> '|| COALESCE(string_val, int_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('4. COALESCE(string_val, int_val) -> '||SQLERRM ); 
    END;

    DBMS_OUTPUT.PUT_LINE( '5. COALESCE(SYSDATE,SYSTIMESTAMP) -> '|| COALESCE(SYSDATE,SYSTIMESTAMP) );

END;
Output:

1. NVL(int_val,string_val) -> ORA-06502: PL/SQL: numeric or value error: character to number conversion error
2. NVL(string_val, int_val) -> foo
3. COALESCE(int_val,string_val) -> 1
4. COALESCE(string_val, int_val) -> ORA-06502: PL/SQL: numeric or value error: character to number conversion error
5. COALESCE(SYSDATE,SYSTIMESTAMP) -> 2016-11-30 09:55:55.000000 +1:0 --> This is a TIMESTAMP value, not a DATE value!



NVL und COALESCE werden verwendet, um die gleiche Funktionalität zum Bereitstellen eines Standardwerts für den Fall zu erreichen, dass die Spalte einen NULL-Wert zurückgibt.

Die Unterschiede sind:

  1. NVL akzeptiert nur 2 Argumente, während COALESCE mehrere Argumente annehmen kann
  2. NVL wertet beide Argumente aus, und COALESCE stoppt beim ersten Auftreten eines Nicht-Null-Werts.
  3. NVL führt eine implizite Datentypkonvertierung basierend auf dem ersten Argument durch. COALESCE erwartet, dass alle Argumente denselben Datentyp haben.
  4. COALESCE gibt Probleme in Abfragen, die UNION-Klauseln verwenden. Beispiel unten
  5. COALESCE ist ANSI-Standard, wobei NVL Oracle-spezifisch ist.

Beispiele für den dritten Fall. Andere Fälle sind einfach.

select nvl('abc',10) from dual; würde funktionieren, da NVL eine implizite Umwandlung von numerischer 10 in Zeichenfolge ausführt.

select coalesce('abc',10) from dual; wird fehlschlagen mit Fehler - inkonsistente Datentypen: erwartet CHAR hat NUMBER bekommen

Beispiel für UNION-Anwendungsfall

SELECT COALESCE(a, sysdate) 
from (select null as a from dual 
      union 
      select null as a from dual
      );

schlägt mit ORA-00932: inconsistent datatypes: expected CHAR got DATE

SELECT NVL(a, sysdate) 
from (select null as a from dual 
      union 
      select null as a from dual
      ) ;

gelingt es.

Weitere Informationen: http://www.plsqlinformation.com/2016/04/difference-between-nvl-and-coalesce-in-oracle.html




Ein weiterer Beweis dafür, dass coalesce () die Auswertung nicht mit dem ersten Nicht-Null-Wert beendet:

SELECT COALESCE(1, my_sequence.nextval) AS answer FROM dual;

Führen Sie das aus und überprüfen my_sequence.currval; dann my_sequence.currval;