sql - example - nvl oracle

Diferencias de Oracle entre NVL y Coalesce

Question

¿Hay diferencias no obvias entre NVL y Coalesce en Oracle?

Las diferencias obvias son que coalesce devolverá el primer elemento no nulo en su lista de parámetros, mientras que nvl solo toma dos parámetros y devuelve el primero si no es nulo, de lo contrario, devuelve el segundo.

Parece que NVL puede ser simplemente una versión de "Base Case" de fusión.

¿Me estoy perdiendo de algo?

Answer 1

Aunque este es obvio, e incluso se menciona de una manera presentada por Tom que hizo esta pregunta. Pero vamos a ponerlo de nuevo.

NVL puede tener solo 2 argumentos. Coalesce puede tener más de 2.

select nvl('''','''',1) from dual; // Resultado: ORA-00909 : número inválido de argumentos
select coalesce('''','''',''1'') from dual; // Salida: devuelve 1

Answer 2

En realidad, no puedo aceptar cada afirmación.

"COALESCE espera que todos los argumentos sean del mismo tipo de datos".

Esto está mal, mira a continuación. Los argumentos pueden ser tipos de datos diferentes, eso también está documented : si todas las ocurrencias de expr son tipos de datos numéricos o cualquier tipo de datos no numéricos que se pueden convertir implícitamente a un tipo de datos numéricos, Oracle Database determina el argumento con la precedencia numérica más alta, implícitamente convierte los argumentos restantes a ese tipo de datos y devuelve ese tipo de datos. . En realidad, esto está incluso en contradicción con la expresión común "COALESCE se detiene en la primera aparición de un valor no nulo", de lo contrario, el caso de prueba n.º 4 no debería generar un error.

También según el caso de prueba n. ° 5 COALESCE realiza una conversión implícita de argumentos.

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!

Answer 3

NVL realizará una conversión implícita al tipo de datos del primer parámetro, por lo que el siguiente error no

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

COALESCE espera tipos de datos coherentes.

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

lanzará un ''error de tipo de datos inconsistente''

Answer 4

NVL y COALESCE se utilizan para lograr la misma funcionalidad de proporcionar un valor predeterminado en caso de que la columna devuelva un NULL.

Las diferencias son:

1. NVL acepta solo 2 argumentos, mientras que COALESCE puede tomar múltiples argumentos
2. NVL evalúa ambos argumentos y COALESCE se detiene en la primera aparición de un valor no nulo.
3. NVL realiza una conversión de tipo de datos implícita basada en el primer argumento que se le otorga. COALESCE espera que todos los argumentos sean del mismo tipo de datos.
4. COALESCE da problemas en las consultas que usan cláusulas de UNION. Ejemplo a continuación
5. COALESCE es un estándar ANSI donde NVL es específico de Oracle.

Ejemplos para el tercer caso. Otros casos son simples.

select nvl(''abc'',10) from dual; funcionaría ya que NVL hará una conversión implícita de 10 numéricos a cadena.

select coalesce(''abc'',10) from dual; fallará con Error - tipos de datos incoherentes: CHAR esperado obtuvo NÚMERO

Ejemplo de caso de uso de UNION

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

falla con 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
) ;

tiene éxito

Más información: http://www.plsqlinformation.com/2016/04/difference-between-nvl-and-coalesce-in-oracle.html

Answer 5

Otra prueba de que coalesce () no detiene la evaluación con el primer valor no nulo:

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

Ejecute esto, luego verifique my_sequence.currval;

Answer 6

También hay una diferencia en el manejo del plan.

Oracle puede formar un plan optimizado con la concatenación de filtros de bifurcación cuando la búsqueda contiene una comparación del resultado nvl con una columna indexada.

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)

juntarse:

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

Los créditos van a http://www.xt-r.com/2012/03/nvl-coalesce-concatenation.html .

Answer 7

COALESCE es una función más moderna que es parte del ANSI-92 .

NVL es específico de Oracle , se introdujo en los años 80 antes de que hubiera estándares.

En el caso de dos valores, son sinónimos.

Sin embargo, se implementan de manera diferente.

NVL siempre evalúa ambos argumentos, mientras que COALESCE generalmente detiene la evaluación cada vez que encuentra el primero no NULL (hay algunas excepciones, como la secuencia NEXTVAL ):

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

Esto se ejecuta durante casi 0.5 segundos, ya que genera SYS_GUID() , a pesar de que 1 no es NULL .

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

Esto comprende que 1 no es NULL y no evalúa el segundo argumento.

SYS_GUID no se generan y la consulta es instantánea.

Answer 8

NVL: Reemplaza el valor nulo.

COALESCE: devuelve la primera expresión no nula de la lista de expresiones.

Tabla: PRICE_LIST

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

A continuación está el ejemplo de

[1] Establezca el precio de venta agregando un 10% de ganancia a todos los productos.
[2] Si no hay un precio de lista de compras, entonces el precio de venta es el precio mínimo. Para venta de liquidación.
[3] Si no hay un precio mínimo también, establezca el precio de venta como el precio predeterminado "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

Explique con ejemplos prácticos de la vida real.

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

Puedes ver que con NVL podemos lograr reglas [1], [2]
Pero con COALSECE podemos lograr las tres reglas.