NVL
A função NVL funciona da seguinte forma:
NVL(e1, e2)
Se e1 tiver um valor NULL, o NVL retornará o valor para e2. Caso contrário, ele retornará e1.
Parâmetros: e1 e e2 são expressões, ambos são obrigatórios e devem ser do mesmo tipo de dados, mas a conversão automática de tipos de dados se aplica aqui, portanto, os valores podem ser diferentes desde que sejam capazes de serem convertidos para o mesmo tipo de dados automaticamente. Vejamos um exemplo:
SELECT NVL(NULL,0) FIRST_ANSWER,
14+NULL-4 SECOND_ANSWER,
14+NVL(NULL,0)-4 THIRD_ANSWER
FROM DUAL;
O resutlado da primeira coluna FIRST_ANSWER será zero, da coluna SECOND_ANSWER será NULL pois qualquer operação matemática com NULL sempre será NULL. A terceira coluna THIRD_ANSWER será 10, veja que é uma operação matemática e tem NULL, só que o NULL foi tratado pela função NVL que verificou o primeiro parâmetro NULL então trouxe o segundo parâmetro de valor zero da mesma forma que fez para a coluna FIRST_ANSWER.
SELECT NVL(2,'renan') FROM DUAL;
O comando acima resultará num erro: ORA-01722: invalid number. O que ocorreu é que o primeiro parâmetro é um número e dessa forma o segundo parâmetro deverá ser um número e se não for o Oracle tentará converter para número e no caso não conseguirá pois é um tipo de dados texto ‘renan’, caso fosse um caractere com um número por exemplo ’20’ daria certo pois mesmos sendo um caractere pois há aspas duplas esse caractere ’20’ pode ser convertido para um número inteiro 20 funcionando assim sem problemas. Vejamos um exemplo que daria certo:
SELECT NVL(2,'1') FROM DUAL;
Esse comando acima funciona pois o SGBD faz um CAST Implícito, uma conversão de tipos interna, convertendo assim o caractere ‘1’ para o tipo número. Como resultado da expressão acima temos o número 2.
NULLIF
A função NULLIF verifica dois parâmetros, se ambos forem iguais o retorno é NULL, se forem diferentes retorna o primeiro parâmetro
NULLIF(e1, e2)
Ambos parâmetros e1 e e2 devem ser informados e devem ser do mesmo tipo de dados.
SELECT NULLIF(3, 3) FROM DUAL
O retorno da consulta acima é o valor nulo NULL.
SELECT NULLIF('3', 3) FROM DUAL
A consulta acima retorna o erro ORA-00932: inconsistent datatypes: expected CHAR got NUMBER. Como a expressão necessita que os parâmetros sejam do mesmo tipo de dados a função encontrou o primeiro parâmetro caractere e o segundo parâmetro um número, por isso o erro.
OBS: A função NULLIF não faz uma conversão explícita de tipo de dados, por exemplo se fosse informado na consulta acima NULLIF(3, '3') também daria erro: ORA-00932: inconsistent datatypes: expected NUMBER got CHAR.
COALESCE
Vamos ver mais uma função que é bem parecida com NVL, é a função COALESCE, possui números indefinidos de parâmetros e ele traz o primeiro parâmetro não nulo como retorno, vejamos um exemplo:
SELECT COALESCE(A, B, C) FROM DUAL
O COALESCE ao menos deverá passar dois parâmetros, mas pode passar vários, ele irá analisar em ordem conforme consulta acima, vai verificar se a coluna A possui valor e se possuir vai mostrar seu valor como retorno, caso seja nulo então vai analisar o valor B se tem algum valor e se tiver mostra seu valor se não tiver valor, ou seja, se for nulo então ele vai analisar a coluna C, e assim por diante.
Atenção para as funções COALESCE e NULLIF pois os tipos de dados devem ser exatamente os mesmos, pois o Oracle não irá fazer conversão implícita e vai dar erro se usar tipos de dados diferentes como parâmetro. Isso pode ser usado como pegadinha na prova de certificação.
Referência Bibliográfica
Database SQL Language Reference
OCA Oracle Database SQL Exam Guide (Exam 1Z0-071) – páginas 355 até 357
desenvoracle.home.blog 