Olá! Hoje vamos explorar duas funções muito úteis no Oracle PL/SQL: NULLIF e COALESCE. Essas funções são como atalhos para expressões CASE, tornando nosso código mais limpo e eficiente. Vamos mergulhar nesse assunto?
Introdução
Antes de começarmos, é importante saber que tanto NULLIF quanto COALESCE são definidas pelo padrão ANSI 1999 como “abreviações de CASE”. Isso significa que elas podem ser usadas como variações da expressão CASE, mas de uma forma mais concisa e fácil de ler.
A função NULLIF
|
1 |
NULLIF(EXPRESSAO1, EXPRESSAO2) |
- Se EXPRESSAO1 for igual a EXPRESSAO2, a função retorna NULL.
- Se forem diferentes, ela retorna o valor de EXPRESSAO1.
É quase como o oposto da função NVL, que você já deve conhecer. A função NVL é usada para substituir um valor NULL por um valor padrão. Relembrando a sintaxe da NVL:
|
1 |
NVL(expressao1, expressao2) |
- Se expressao1 for NULL, retorna expressao2
- Se expressao1 não for NULL, retorna expressao1
Exemplo de NVL:
|
1 2 3 4 5 6 7 |
DECLARE v_valor1 NUMBER := NULL; v_valor2 NUMBER := 10; BEGIN DBMS_OUTPUT.PUT_LINE('NVL(v_valor1, 0): ' || NVL(v_valor1, 0)); DBMS_OUTPUT.PUT_LINE('NVL(v_valor2, 0): ' || NVL(v_valor2, 0)); END; |
A função NULLIF, por outro lado, compara duas expressões e retorna NULL se forem iguais.
Veja um exemplo prático:
|
1 2 3 4 5 6 7 |
DECLARE v_num NUMBER := &sv_user_num; v_remainder NUMBER; BEGIN v_remainder := NULLIF(MOD(v_num, 2), 0); DBMS_OUTPUT.PUT_LINE('v_remainder: ' || v_remainder); END; |
Neste exemplo, estamos verificando se um número é par ou ímpar. Se for par (resto 0), o NULLIF retorna NULL. Se for ímpar, retorna o resto.
Se você rodar isso com o número 5, verá:
|
1 2 |
v_remainder: 1 |
Mas se rodar com 4:
|
1 2 |
v_remainder: |
Interessante, não é? Com o 4, o resto é 0, então o NULLIF retorna NULL, que aparece como um espaço em branco.
Comparação
Agora, vamos ver um exemplo que mostra como elas podem ser vistas como complementares:
|
1 2 3 4 5 6 7 8 9 |
DECLARE v_valor NUMBER := 0; BEGIN -- Usando NVL para substituir 0 por NULL DBMS_OUTPUT.PUT_LINE('NVL: ' || NVL(v_valor, NULL)); -- Usando NULLIF para transformar 0 em NULL DBMS_OUTPUT.PUT_LINE('NULLIF: ' || NULLIF(v_valor, 0)); END; |
Resultado:
|
1 2 3 |
NVL: 0 NULLIF: |
Neste exemplo:
- NVL mantém o valor 0 porque ele não é NULL
- NULLIF transforma o valor 0 em NULL porque ele é igual ao segundo argumento
Veja como elas agem de forma oposta neste cenário específico:
- NVL tenta evitar NULL, substituindo-o por um valor padrão
- NULLIF pode criar um NULL quando encontra um valor específico
Ah, e tem um detalhe importante: você não pode usar NULL diretamente como primeiro argumento do NULLIF. Por exemplo, isso dará erro:
|
1 2 3 4 5 6 |
DECLARE v_remainder NUMBER; BEGIN v_remainder := NULLIF(NULL, 0); DBMS_OUTPUT.PUT_LINE('v_remainder: ' || v_remainder); END; |
Isso resultará em um erro de sintaxe. O Oracle não permite que você use um literal NULL como primeiro operando da expressão NULLIF.
Embora NULLIF e NVL não sejam exatamente opostos em todos os casos, eles podem ter efeitos opostos em certas situações:
- NVL transforma NULL em um valor não-nulo
- NULLIF pode transformar um valor não-nulo em NULL
A chave para entender a diferença está nos seus propósitos:
- Use NVL quando quiser um valor padrão no lugar de NULL
- Use NULLIF quando quiser transformar um valor específico em NULL
A função COALESCE
Agora, vamos falar da COALESCE. Essa função é como uma detetive em busca do primeiro valor não-nulo em uma lista de expressões. Ela funciona assim:
|
1 |
COALESCE(EXPRESSAO1, EXPRESSAO2, ..., EXPRESSAON) |
A COALESCE avalia cada expressão da esquerda para a direita e retorna o valor da primeira expressão que não seja NULL. Se todas forem NULL, ela retorna NULL.
É como se fosse uma série de funções NVL aninhadas:
|
1 |
NVL(EXPRESSAO1, NVL(EXPRESSAO2, NVL(EXPRESSAO3, ...))) |
Antes de aplicarmos um exemplo prático, vamos criar uma tabela para aplicar o conceito
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
-- Criação da tabela ENROLLMENT CREATE TABLE enrollment ( student_id NUMBER, section_id NUMBER, final_grade NUMBER ); -- Criação da tabela GRADE CREATE TABLE grade ( student_id NUMBER, section_id NUMBER, grade_type_code VARCHAR2(2), numeric_grade NUMBER ); -- Inserção de dados na tabela ENROLLMENT INSERT INTO enrollment (student_id, section_id, final_grade) VALUES (102, 86, NULL); INSERT INTO enrollment (student_id, section_id, final_grade) VALUES (102, 89, 92); -- Inserção de dados na tabela GRADE INSERT INTO grade (student_id, section_id, grade_type_code, numeric_grade) VALUES (102, 86, 'FI', 85); INSERT INTO grade (student_id, section_id, grade_type_code, numeric_grade) VALUES (102, 89, 'FI', 92); -- Commit para salvar as alterações COMMIT; |
Agora que temos nossas tabelas criadas e populadas, vamos ver como a função COALESCE funciona na prática. Aqui está nossa consulta:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT e.student_id, e.section_id, e.final_grade, g.numeric_grade, COALESCE(e.final_grade, g.numeric_grade, 0) AS grade FROM enrollment e, grade g WHERE e.student_id = g.student_id AND e.section_id = g.section_id AND e.student_id = 102 AND g.grade_type_code = 'FI'; |
Quando executamos esta consulta, obtemos o seguinte resultado:
|
1 2 3 |
STUDENT_ID SECTION_ID FINAL_GRADE NUMERIC_GRADE GRADE 102 86 (null) 85 85 102 89 92 92 92 |
Vamos analisar o que está acontecendo aqui:
- Na primeira linha do resultado:
final_gradeé NULL- COALESCE passa para a próxima expressão,
numeric_grade, que é 85 - Portanto, COALESCE retorna 85
- Na segunda linha do resultado:
final_gradenão é NULL (é 92)- COALESCE retorna imediatamente este valor, sem precisar olhar para
numeric_grade
Observe como a COALESCE nos ajuda a lidar elegantemente com o valor NULL na primeira linha, fornecendo um valor alternativo da tabela grade. Isso é particularmente útil quando temos várias fontes possíveis para um valor e queremos usar a primeira que não seja NULL.
Este exemplo demonstra a flexibilidade da função COALESCE em situações reais de banco de dados, onde podemos ter valores ausentes ou incompletos em algumas tabelas e precisamos de uma maneira consistente de lidar com esses casos.
Lembre-se de que você pode adaptar este exemplo para suas próprias necessidades, alterando as condições do WHERE ou adicionando mais expressões à função COALESCE conforme necessário.
COALESCE e CASE
É interessante notar que a COALESCE pode ser reescrita como uma expressão CASE. Por exemplo:
|
1 |
COALESCE(EXPRESSAO1, EXPRESSAO2) |
é equivalente a:
|
1 2 3 4 |
CASE WHEN EXPRESSAO1 IS NOT NULL THEN EXPRESSAO1 ELSE EXPRESSAO2 END |
E para múltiplas expressões:
|
1 |
COALESCE(EXPRESSAO1, EXPRESSAO2, ..., EXPRESSAON) |
é equivalente a:
|
1 2 3 4 5 6 |
CASE WHEN EXPRESSAO1 IS NOT NULL THEN EXPRESSAO1 WHEN EXPRESSAO2 IS NOT NULL THEN EXPRESSAO2 ... ELSE EXPRESSAON END |
Conclusão
NULLIF e COALESCE são ferramentas poderosas para lidar com valores nulos de forma elegante e eficiente. A NULLIF nos ajuda a transformar igualdades em NULL, enquanto a COALESCE nos permite encontrar o primeiro valor não-nulo em uma lista de expressões.
Lembre-se:
- Use NULLIF quando quiser comparar duas expressões e retornar NULL se forem iguais.
- Use COALESCE quando quiser encontrar o primeiro valor não-nulo em uma série de expressões.
Pratique com essas funções e você verá como elas podem tornar seu código PL/SQL mais limpo e eficiente. Na próxima vez que você se deparar com valores nulos, terá duas novas armas no seu arsenal!
