Différences Oracle entre NVL et Coalesce

Existe-t-il des différences non évidentes entre NVL et Coalesce dans Oracle?

Les différences évidentes sont que la coalescence renvoie le premier élément non nul dans sa liste de parameters alors que nvl ne prend que deux parameters et renvoie le premier s’il n’est pas nul, sinon il renvoie le second.

Il semble que NVL ne soit qu’une version de base de coalesce.

Est-ce que je manque quelque chose?

COALESCE est une fonction plus moderne qui fait partie de la ANSI-92 .

NVL est spécifique à Oracle , il a été introduit dans les 80 avant qu’il y ait des normes.

En cas de deux valeurs, elles sont synonymes.

Cependant, ils sont implémentés différemment.

NVL évalue toujours les deux arguments, tandis que COALESCE arrête généralement l’évaluation chaque fois qu’il trouve le premier COALESCE non NULL (il existe certaines exceptions, telles que la séquence NEXTVAL ):

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

Cela dure presque 0.5 seconde, car il génère des SYS_GUID() , bien que 1 ne soit pas une NULL .

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

Cela comprend que 1 n'est pas un NULL et n'évalue pas le second argument.

SYS_GUID ne sont pas générés et la requête est instantanée.

NVL effectuera une conversion implicite vers le type de données du premier paramètre, de sorte que les erreurs suivantes ne se produisent pas

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

COALESCE attend des types de données cohérents.

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

va lancer une “erreur de type de données incohérente”

Il y a aussi une différence dans la manipulation du plan.

Oracle peut former un plan optimisé avec concaténation de filtres de twig lorsque la recherche contient une comparaison du résultat nvl avec une colonne indexée.

 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) 

se fondre:

 --------------------------------------------------------------------------------------- | 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)) 

Crédits allez à http://www.xt-r.com/2012/03/nvl-coalesce-concatenation.html .

NVL et COALESCE sont utilisés pour obtenir la même fonctionnalité de fournir une valeur par défaut si la colonne renvoie une valeur NULL.

Les différences sont les suivantes:

  1. NVL n’accepte que 2 arguments alors que COALESCE peut prendre plusieurs arguments
  2. NVL évalue à la fois les arguments et COALESCE s’arrête à la première occurrence d’une valeur non Null.
  3. NVL effectue une conversion de type de données implicite en fonction du premier argument qui lui est donné. COALESCE s’attend à ce que tous les arguments soient du même type de données.
  4. COALESCE donne des problèmes dans les requêtes qui utilisent des clauses UNION. Exemple ci-dessous
  5. COALESCE est la norme ANSI où NVL est spécifique à Oracle.

Exemples pour le troisième cas. Les autres cas sont simples.

select nvl('abc',10) from dual; fonctionnerait comme NVL va faire une conversion implicite de numérique en chaîne.

select coalesce('abc',10) from dual; échouera avec une erreur – types de données incohérents: attendu

Exemple de cas d’utilisation UNION

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

échoue avec 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 ) ; 

réussit.

Plus d’informations: http://www.plsqlinformation.com/2016/04/difference-between-nvl-and-coalesce-in-oracle.html

Une autre preuve que coalesce () n’arrête pas l’évaluation avec la première valeur non nulle:

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

Exécutez ceci, puis vérifiez my_sequence.currval;

Bien que celui-ci soit évident, et même mentionné d’une manière mise en place par Tom qui a posé cette question. Mais relâchons-le.

NVL ne peut avoir que 2 arguments. La coalescence peut avoir plus de 2.

select nvl('','',1) from dual; // Résultat: ORA-00909 : nombre d’arguments non valide
select coalesce('','','1') from dual; // Sortie: retourne 1

En fait, je ne peux pas accepter chaque déclaration.

“COALESCE s’attend à ce que tous les arguments soient du même type de données.”

C’est faux, voir ci-dessous. Les arguments peuvent être des types de données différents, qui sont également documentés : Si toutes les occurrences de expr sont un type de données numérique ou tout type de données non numérique pouvant être implicitement converti en type de données numérique, Oracle Database détermine implicitement l’argument avec la priorité numérique la plus élevée convertit les arguments restants en ce type de données et renvoie ce type de données. . En fait, cela est même en contradiction avec l’expression courante “COALESCE s’arrête à la première occurrence d’une valeur non Null”, sinon le test n ° 4 ne devrait pas générer d’erreur.

Toujours selon le test n ° 5, COALESCE effectue une conversion implicite des arguments.

 DECLARE int_val INTEGER := 1; ssortingng_val VARCHAR2(10) := 'foo'; BEGIN BEGIN DBMS_OUTPUT.PUT_LINE( '1. NVL(int_val,ssortingng_val) -> '|| NVL(int_val,ssortingng_val) ); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('1. NVL(int_val,ssortingng_val) -> '||SQLERRM ); END; BEGIN DBMS_OUTPUT.PUT_LINE( '2. NVL(ssortingng_val, int_val) -> '|| NVL(ssortingng_val, int_val) ); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('2. NVL(ssortingng_val, int_val) -> '||SQLERRM ); END; BEGIN DBMS_OUTPUT.PUT_LINE( '3. COALESCE(int_val,ssortingng_val) -> '|| COALESCE(int_val,ssortingng_val) ); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('3. COALESCE(int_val,ssortingng_val) -> '||SQLERRM ); END; BEGIN DBMS_OUTPUT.PUT_LINE( '4. COALESCE(ssortingng_val, int_val) -> '|| COALESCE(ssortingng_val, int_val) ); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('4. COALESCE(ssortingng_val, int_val) -> '||SQLERRM ); END; DBMS_OUTPUT.PUT_LINE( '5. COALESCE(SYSDATE,SYSTIMESTAMP) -> '|| COALESCE(SYSDATE,SYSTIMESTAMP) ); END; 
 Output: 1. NVL(int_val,ssortingng_val) -> ORA-06502: PL/SQL: numeric or value error: character to number conversion error 2. NVL(ssortingng_val, int_val) -> foo 3. COALESCE(int_val,ssortingng_val) -> 1 4. COALESCE(ssortingng_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: remplace le null par la valeur.

COALESCE: renvoie la première expression non nulle de la liste d’expressions.

Table: PRICE_LIST

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

Ci-dessous l’exemple de

[1] Définissez le prix de vente en ajoutant 10% de profit à tous les produits.
[2] S’il n’y a pas de prix d’achat, le prix de vente est le prix minimum. Pour la liquidation.
[3] S’il n’y a pas de prix minimum, définissez alors le prix de vente comme prix par défaut “50”.

 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 

Expliquez avec un exemple concret de la vie réelle.

 +----------------+-----------+-----------------+----------------------+ | 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 | +----------------+-----------+-----------------+----------------------+ 

Vous pouvez voir qu’avec NVL nous pouvons atteindre les règles [1], [2]
Mais avec COALSECE, nous pouvons atteindre les trois règles.