webentwicklung-frage-antwort-db.com.de

Oracle Unterschiede zwischen NVL und Coalesce

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

Die offensichtlichen Unterschiede bestehen darin, dass Coalesce das erste Nicht-Null-Element in seiner Parameterliste zurückgibt, wohingegen nvl nur zwei Parameter akzeptiert und das erste zurückgibt, wenn es nicht Null ist, andernfalls das zweite.

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

Vermisse ich etwas?

sqloraclecoalescenvl
192
Tom Hubbard

COALESCE ist eine modernere Funktion, die Teil des Standards ANSI-92 ist.

NVL ist Oracle spezifisch, es wurde in 80 eingeführt, bevor es Standards gab.

Bei zwei Werten handelt es sich um Synonyme.

Sie sind jedoch unterschiedlich implementiert.

NVL wertet immer beide Argumente aus, während COALESCE normalerweise die Auswertung stoppt, wenn das erste Nicht-NULL gefunden wird (es gibt einige Ausnahmen, wie z. B. die Sequenz NEXTVAL) :

SELECT  SUM(val)
FROM    (
        SELECT  NVL(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )

Dies dauert fast 0.5 Sekunden, da es SYS_GUID() generiert, obwohl 1 Kein NULL ist.

SELECT  SUM(val)
FROM    (
        SELECT  COALESCE(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )

Dies versteht, dass 1 Kein NULL ist und das zweite Argument nicht auswertet.

SYS_GUID Werden nicht generiert und die Abfrage erfolgt sofort.

289
Quassnoi

NVL führt eine implizite Konvertierung in den Datentyp des ersten Parameters durch, sodass Folgendes nicht fehlerhaft ist

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

COALESCE erwartet konsistente Datentypen.

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

wird einen inkonsistenten Datentypfehler auslösen

164
Gary Myers

NVL und COALESCE werden verwendet, um die gleiche Funktionalität wie beim Bereitstellen eines Standardwerts für den Fall zu erzielen, dass die Spalte NULL 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 basierend auf dem ersten Argument eine implizite Datentypkonvertierung durch. COALESCE erwartet, dass alle Argumente denselben Datentyp haben.
  4. COALESCE gibt Probleme in Abfragen an, 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 Konvertierung der Zahl 10 in einen String vornimmt.

select coalesce('abc',10) from dual; schlägt mit fehlerhaft inkonsistenten Datentypen fehl: erwarteter CHAR hat NUMBER

Beispiel für einen UNION-Anwendungsfall

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

scheitert 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

19
Brahmareddy K

Es gibt auch Unterschiede in der Planabwicklung.

Oracle kann einen optimierten Plan mit Verkettung von Verzweigungsfiltern erstellen, wenn die Suche den Vergleich von nvl Ergebnissen mit einer indizierten Spalte enthält.

create table tt(a, b) as
select level, mod(level,10)
from dual
connect by level<=1e4;

alter table tt add constraint ix_tt_a primary key(a);
create index ix_tt_b on tt(b);

explain plan for
select * from tt
where a=nvl(:1,a)
  and b=:2;

explain plan for
select * from tt
where a=coalesce(:1,a)
  and b=:2;

nvl:

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     2 |    52 |     2   (0)| 00:00:01 |
|   1 |  CONCATENATION                |         |       |       |            |          |
|*  2 |   FILTER                      |         |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| TT      |     1 |    26 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_TT_B |     7 |       |     1   (0)| 00:00:01 |
|*  5 |   FILTER                      |         |       |       |            |          |
|*  6 |    TABLE ACCESS BY INDEX ROWID| TT      |     1 |    26 |     1   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN         | IX_TT_A |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:1 IS NULL)
   3 - filter("A" IS NOT NULL)
   4 - access("B"=TO_NUMBER(:2))
   5 - filter(:1 IS NOT NULL)
   6 - filter("B"=TO_NUMBER(:2))
   7 - access("A"=:1)

verschmelzen:

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    26 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TT      |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TT_B |    40 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"=COALESCE(:1,"A"))
   2 - access("B"=TO_NUMBER(:2))

Credits gehen an http://www.xt-r.com/2012/03/nvl-coalesce-concatenation.html .

16
Vadzim

Eigentlich kann ich nicht jeder Aussage zustimmen.

"COALESCE erwartet, dass alle Argumente denselben Datentyp haben."

Das ist falsch, siehe unten. Argumente können verschiedene Datentypen sein, das heißt auch dokumentiert : Wenn alle Vorkommen von expr numerische Datentypen oder beliebige nicht numerische Datentypen sind, die implizit in numerische Datentypen konvertiert werden können Datentyp, dann bestimmt Oracle Database das Argument mit der höchsten numerischen Priorität, konvertiert implizit die verbleibenden Argumente in diesen Datentyp und gibt diesen Datentyp zurück. . Tatsächlich widerspricht dies sogar dem allgemeinen Ausdruck "COALESCE stoppt beim ersten Auftreten eines Nicht-Null-Wertes", da sonst der Testfall Nr. 4 keinen Fehler auslösen sollte.

Auch gemäß Testfall Nr. 5 führt COALESCE eine implizite Umwandlung 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!
5
Wernfried Domscheit

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

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

Führe dies aus und überprüfe my_sequence.currval;

4
Herb Swift

Obwohl dieser offensichtlich ist und sogar auf eine Weise erwähnt wird, die von Tom aufgestellt wurde, der diese Frage stellte. Aber lassen Sie uns noch einmal aufstellen.

NVL kann nur 2 Argumente haben. Coalesce kann mehr als 2 haben.

select nvl('','',1) from dual; // Ergebnis: ORA-00909: ungültige Anzahl von Argumenten
select coalesce('','','1') from dual; // Ausgabe: gibt 1 zurück

3
Neel

NVL: Ersetzen Sie die Null durch einen Wert.

COALESCE: Gibt den ersten Nicht-Null-Ausdruck aus der Ausdrucksliste zurück.

Tabelle: PRICE_LIST

+----------------+-----------+
| Purchase_Price | Min_Price |
+----------------+-----------+
| 10             | null      |
| 20             |           |
| 50             | 30        |
| 100            | 80        |
| null           | null      |
+----------------+-----------+

Unten ist das Beispiel von

[1] Legen Sie den Verkaufspreis fest, indem Sie 10% Gewinn zu allen Produkten hinzufügen.
[2] Wenn es keinen Kauflistenpreis gibt, ist der Verkaufspreis der Mindestpreis. Für den Ausverkauf.
[3] Wenn es auch keinen Mindestpreis gibt, legen Sie den Verkaufspreis als Standardpreis "50" fest.

SELECT
     Purchase_Price,
     Min_Price,
     NVL(Purchase_Price + (Purchase_Price * 0.10), Min_Price)    AS NVL_Sales_Price,
COALESCE(Purchase_Price + (Purchase_Price * 0.10), Min_Price,50) AS Coalesce_Sales_Price
FROM 
Price_List

Erklären Sie dies anhand eines praktischen Beispiels.

+----------------+-----------+-----------------+----------------------+
| Purchase_Price | Min_Price | NVL_Sales_Price | Coalesce_Sales_Price |
+----------------+-----------+-----------------+----------------------+
| 10             | null      | 11              |                   11 |
| null           | 20        | 20              |                   20 |
| 50             | 30        | 55              |                   55 |
| 100            | 80        | 110             |                  110 |
| null           | null      | null            |                   50 |
+----------------+-----------+-----------------+----------------------+

Sie können sehen, dass wir mit NVL Regeln erreichen können [1], [2]
Aber mit COALSECE können wir alle drei Regeln erreichen.

2
sandip