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:
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.