DEV Community

Cover image for Funções de Conversão e Expressões Condicionais em SQL Oracle
Kauê Matos
Kauê Matos

Posted on

Funções de Conversão e Expressões Condicionais em SQL Oracle

#webdev #oracle #data #database

Este artigo explora de forma detalhada as funções de conversão e expressões condicionais no Oracle SQL, essenciais para manipular dados, formatar resultados e implementar lógica condicional em consultas. Através de exemplos práticos baseados na tabela employees, você aprenderá a utilizar cada função em situações reais do dia a dia.

1. Função TO_CHAR com Datas

A função TO_CHAR converte valores de data em strings formatadas, permitindo apresentar datas de diversas formas.

1.1 Formato Básico com Data e Hora 

SELECT last_name, TO_CHAR(hire_date, 'DD/MM/YYYY  HH24:MI:SS') DT_ADMISSÃO
FROM employees;
Enter fullscreen mode Exit fullscreen mode

Explicação detalhada:

  • DD: Dia do mês (01 a 31)
  • MM: Mês numérico (01 a 12)
  • YYYY: Ano com quatro dígitos
  • HH24: Hora no formato 24 horas (00 a 23)
  • MI: Minutos (00 a 59)
  • SS: Segundos (00 a 59)

Resultado exemplo:

LAST_NAME        DT_ADMISSÃO
King             17/06/2003  08:00:00
Kochhar          21/09/2005  09:30:00
Enter fullscreen mode Exit fullscreen mode

1.2 Obtendo Data Atual do Sistema 

SELECT sysdate, TO_CHAR(sysdate, 'DD/MM/YYYY  HH24:MI:SS') DATA
FROM dual;
Enter fullscreen mode Exit fullscreen mode

Explicação:

  • SYSDATE: Função que retorna a data e hora atual do servidor Oracle
  • DUAL: Tabela especial do Oracle com uma única linha, usada para testar funções

Resultado exemplo:

SYSDATE              DATA
17/12/2025 14:35:22  17/12/2025  14:35:22
Enter fullscreen mode Exit fullscreen mode

1.3 Formato por Extenso 

SELECT last_name, TO_CHAR(hire_date, 'DD, "de" Month "de" YYYY') DT_ADMISSÃO
FROM employees;
Enter fullscreen mode Exit fullscreen mode

Explicação detalhada:

  • Month: Nome completo do mês (em inglês por padrão)
  • "de": Texto literal entre aspas duplas que será incluído exatamente como está
  • Este formato é útil para relatórios formais

Resultado exemplo:

LAST_NAME    DT_ADMISSÃO
King         17, de June      de 2003
Kochhar      21, de September de 2005
Enter fullscreen mode Exit fullscreen mode

Observação: Note os espaços extras após o nome do mês.

1.4 Formato com FM (Fill Mode) 

SELECT last_name, TO_CHAR(hire_date, 'FMDD, "de" Month "de" YYYY') DT_ADMISSÃO
FROM employees;
Enter fullscreen mode Exit fullscreen mode

Explicação:

  • FM (Fill Mode): Remove espaços em branco extras e zeros à esquerda
  • Torna a saída mais limpa e compacta

Resultado exemplo:

LAST_NAME    DT_ADMISSÃO
King         17, de June de 2003
Kochhar      21, de September de 2005
Enter fullscreen mode Exit fullscreen mode

Diferença: Os espaços extras após o nome do mês foram removidos.

2. Função TO_CHAR com Números

A função TO_CHAR também formata valores numéricos, especialmente útil para valores monetários.

2.1 Formatação de Salário 

SELECT first_name, last_name, TO_CHAR(salary, 'L99G999G999D99') SALARIO
FROM employees;
Enter fullscreen mode Exit fullscreen mode

Explicação detalhada dos elementos de formato:

Elemento Significado Exemplo
L Símbolo da moeda local R$ (Brasil), $ (EUA)
9 Posição de dígito numérico Se não houver valor, não aparece
0 Posição de dígito obrigatório Sempre exibe, mesmo que seja zero
G Separador de grupo (milhares) . (ponto no Brasil)
D Separador decimal , (vírgula no Brasil)

Resultado exemplo:

FIRST_NAME   LAST_NAME    SALARIO
Steven       King         R$ 24.000,00
Neena        Kochhar      R$ 17.000,00
Lex          De Haan      R$ 17.000,00
Alexander    Hunold       R$  9.000,00
Enter fullscreen mode Exit fullscreen mode

Observações importantes:

  • O símbolo da moeda depende das configurações regionais (NLS) do banco de dados
  • O formato 99G999G999D99 suporta valores até 99.999.999,99
  • Se o valor tiver mais dígitos que o formato especificado, aparecerão caracteres #

2.2 Exemplo Duplicado 

SELECT first_name, last_name, TO_CHAR(salary, 'L99G999G999D99') SALARIO
FROM employees;
Enter fullscreen mode Exit fullscreen mode

Este exemplo é idêntico ao anterior e produz o mesmo resultado.

3. Função TO_NUMBER

Converte uma string em um valor numérico que pode ser usado em cálculos matemáticos.

3.1 Conversão Simples 

SELECT TO_NUMBER('12000,50')
FROM dual;
Enter fullscreen mode Exit fullscreen mode

Explicação:

  • Converte a string '12000,50' em um número decimal
  • O formato deve corresponder às configurações regionais (NLS_NUMERIC_CHARACTERS)
  • No Brasil, vírgula é o separador decimal

Resultado:

TO_NUMBER('12000,50')
12000.5
Enter fullscreen mode Exit fullscreen mode

Nota importante: Internamente, o Oracle armazena decimais com ponto, mas a conversão respeita as configurações regionais da sessão.

Uso prático:

SELECT TO_NUMBER('1500') + TO_NUMBER('2500') AS TOTAL
FROM dual;

-- Resultado: 4000
Enter fullscreen mode Exit fullscreen mode

4. Função TO_DATE

Converte uma string em um valor de data válido do Oracle.

4.1 Conversão Básica 

SELECT TO_DATE('06/02/2020', 'DD/MM/YYYY') DATA
FROM dual;
Enter fullscreen mode Exit fullscreen mode

Explicação:

  • Primeiro parâmetro: string com a data
  • Segundo parâmetro: formato que descreve como a string está organizada
  • Retorna um tipo de dado DATE do Oracle

Resultado:

DATA
06/02/2020
Enter fullscreen mode Exit fullscreen mode

4.2 Uso em Filtros WHERE 

SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date = TO_DATE('17/06/2003', 'DD/MM/YYYY');
Enter fullscreen mode Exit fullscreen mode

Explicação detalhada:

  • Fundamental usar TO_DATE ao comparar datas em filtros
  • Sem a conversão, o Oracle pode não interpretar corretamente a string
  • Garante comparação precisa considerando apenas a data (sem hora)

Resultado exemplo:

FIRST_NAME   LAST_NAME    HIRE_DATE
Steven       King         17/06/2003
Enter fullscreen mode Exit fullscreen mode

Importância:

  • Evita erros de comparação de datas
  • Torna o código independente das configurações regionais da sessão
  • Garante portabilidade do código

5. Funções Aninhadas

O Oracle permite combinar múltiplas funções em uma única expressão, executando de dentro para fora.

5.1 Exemplo Completo 

SELECT first_name, last_name, ROUND(MONTHS_BETWEEN(SYSDATE, hire_date), 0) NUMERO_MESES
FROM employees
WHERE hire_date = TO_DATE('17/06/2003', 'DD/MM/YYYY');
Enter fullscreen mode Exit fullscreen mode

Explicação passo a passo:

  1. TO_DATE('17/06/2003', 'DD/MM/YYYY'): Converte a string em data
  2. MONTHS_BETWEEN(SYSDATE, hire_date): Calcula o número de meses entre hoje e a data de contratação
  3. ROUND(..., 0): Arredonda o resultado para zero casas decimais (número inteiro)

Ordem de execução:

TO_DATE → MONTHS_BETWEEN → ROUND
Enter fullscreen mode Exit fullscreen mode

Resultado exemplo (considerando SYSDATE = 17/12/2025):

FIRST_NAME   LAST_NAME    NUMERO_MESES
Steven       King         270
Enter fullscreen mode Exit fullscreen mode

Cálculo:

  • De 17/06/2003 até 17/12/2025 = 22 anos e 6 meses = 270 meses

6. Função NVL

A função NVL substitui valores NULL por um valor específico.

6.1 Sintaxe 

NVL(expressão, valor_se_null)
Enter fullscreen mode Exit fullscreen mode

6.2 Cálculo de Remuneração Anual 

SELECT last_name, salary, NVL(commission_pct, 0), salary*12 SALARIO_ANUAL, 
       (salary*12) + (salary*12*NVL(commission_pct, 0)) REMUNERACAO_ANUAL
FROM employees;
Enter fullscreen mode Exit fullscreen mode

Explicação detalhada:

  1. NVL(commission_pct, 0): Se commission_pct for NULL, usa 0
  2. salary*12: Salário anual sem comissão
  3. salary*12*NVL(commission_pct, 0): Valor da comissão anual
  4. Soma total: Remuneração anual completa

Por que usar NVL aqui?

  • Evita que cálculos com NULL retornem NULL
  • Em SQL, qualquer operação matemática com NULL resulta em NULL
  • Exemplo: 5000 * NULL = NULL

Resultado exemplo:

LAST_NAME   SALARY  NVL(...)  SALARIO_ANUAL  REMUNERACAO_ANUAL
King        24000   0         288000         288000
Abel        11000   0.30      132000         171600
Enter fullscreen mode Exit fullscreen mode

Cálculo para Abel:

  • Salário anual: 11000 × 12 = 132000
  • Comissão anual: 11000 × 12 × 0.30 = 39600
  • Total: 132000 + 39600 = 171600

7. Função COALESCE

A função COALESCE retorna o primeiro valor não-NULL de uma lista de expressões.

7.1 Sintaxe 

COALESCE(exp1, exp2, exp3, ..., expN)
Enter fullscreen mode Exit fullscreen mode

7.2 Demonstração Básica 

SELECT COALESCE(NULL, NULL, 'Expresssão 3'), 
       COALESCE(NULL, 'Expressão 2', 'Expresssão 3'),
       COALESCE('Expressão 1', 'Expressão 2', 'Expresssão 3')
FROM dual;
Enter fullscreen mode Exit fullscreen mode

Resultado:

COL1          COL2          COL3
Expresssão 3  Expressão 2   Expressão 1
Enter fullscreen mode Exit fullscreen mode

Explicação:

  • Primeira coluna: Retorna 'Expresssão 3' pois é o primeiro valor não-NULL
  • Segunda coluna: Retorna 'Expressão 2' pois é o primeiro não-NULL
  • Terceira coluna: Retorna 'Expressão 1' (primeiro argumento já é não-NULL)

7.3 Aplicação Prática 

SELECT last_name, employee_id, commission_pct, manager_id, 
       COALESCE(TO_CHAR(commission_pct), TO_CHAR(manager_id),
       'Sem percentual de comissão e sem gerente')
FROM employees;
Enter fullscreen mode Exit fullscreen mode

Explicação detalhada:

  1. TO_CHAR(commission_pct): Tenta converter comissão em string
  2. TO_CHAR(manager_id): Se comissão for NULL, tenta converter ID do gerente
  3. Mensagem final: Se ambos forem NULL, exibe a mensagem

Lógica de prioridade:

  • Se tem comissão → mostra comissão
  • Se não tem comissão mas tem gerente → mostra ID do gerente
  • Se não tem nenhum dos dois → mostra mensagem

Resultado exemplo:

LAST_NAME   EMPLOYEE_ID  COMMISSION_PCT  MANAGER_ID  COALESCE(...)
King        100          NULL            NULL        Sem percentual de comissão e sem gerente
Abel        174          0.30            149         0.3
Hunold      103          NULL            102         102
Enter fullscreen mode Exit fullscreen mode

Diferença entre COALESCE e NVL:

  • NVL: Aceita apenas 2 argumentos
  • COALESCE: Aceita múltiplos argumentos (mais flexível)

8. Função NVL2

A função NVL2 permite especificar valores diferentes dependendo se a expressão é NULL ou não.

8.1 Sintaxe 

NVL2(expressão, valor_se_não_null, valor_se_null)
Enter fullscreen mode Exit fullscreen mode

8.2 Exemplo Prático 

SELECT last_name, salary, commission_pct, 
       NVL2(commission_pct, 10, 0) PERCENTUAL_ATERADO
FROM employees;
Enter fullscreen mode Exit fullscreen mode

Explicação detalhada:

  • Se commission_pct NÃO é NULL: Retorna 10
  • Se commission_pct é NULL: Retorna 0

Lógica de negócio:
Este exemplo poderia representar um bônus adicional onde:

  • Funcionários com comissão recebem 10% extra
  • Funcionários sem comissão não recebem nada

Resultado exemplo:

LAST_NAME   SALARY  COMMISSION_PCT  PERCENTUAL_ATERADO
King        24000   NULL            0
Abel        11000   0.30            10
Taylor      8600    0.20            10
Grant       7000    0.15            10
Whalen      4400    NULL            0
Enter fullscreen mode Exit fullscreen mode

Diferença entre NVL e NVL2:

Função Quando usar
NVL(x, y) Substituir NULL por um valor específico
NVL2(x, y, z) Um valor se não-NULL, outro valor se NULL

Exemplo comparativo:

-- NVL: Se NULL, retorna 0; se não-NULL, retorna o próprio valor
SELECT NVL(commission_pct, 0) FROM employees;

-- NVL2: Se NULL, retorna 0; se não-NULL, retorna 10
SELECT NVL2(commission_pct, 10, 0) FROM employees;
Enter fullscreen mode Exit fullscreen mode

9. Função NULLIF

A função NULLIF compara duas expressões e retorna NULL se forem iguais, ou a primeira expressão se forem diferentes.

9.1 Sintaxe 

NULLIF(expressão1, expressão2)
Enter fullscreen mode Exit fullscreen mode

Lógica:

  • Se expressão1 = expressão2 → Retorna NULL
  • Se expressão1 ≠ expressão2 → Retorna expressão1

9.2 Demonstração Básica 

SELECT NULLIF(1000, 1000), NULLIF(1000, 2000)
FROM dual;
Enter fullscreen mode Exit fullscreen mode

Resultado:

NULLIF(1000,1000)  NULLIF(1000,2000)
NULL               1000
Enter fullscreen mode Exit fullscreen mode

Explicação:

  • Primeira coluna: 1000 = 1000, então retorna NULL
  • Segunda coluna: 1000 ≠ 2000, então retorna 1000 (primeiro valor)

9.3 Aplicação Prática 

SELECT first_name, last_name, LENGTH(first_name) "Expressão 1",
       LENGTH(last_name) "Expressão 2", 
       NULLIF(LENGTH(first_name), LENGTH(last_name)) RESULTADO
FROM employees;
Enter fullscreen mode Exit fullscreen mode

Explicação detalhada:

  1. LENGTH(first_name): Calcula o tamanho do primeiro nome
  2. LENGTH(last_name): Calcula o tamanho do sobrenome
  3. NULLIF(...): Compara os tamanhos
    • Se tamanhos iguais → NULL
    • Se tamanhos diferentes → tamanho do primeiro nome

Resultado exemplo:

FIRST_NAME  LAST_NAME  Expressão 1  Expressão 2  RESULTADO
Steven      King       6            4            6
Neena       Kochhar    5            7            5
Lex         De Haan    3            7            3
Bruce       Ernst      5            5            NULL
Enter fullscreen mode Exit fullscreen mode

Caso Bruce Ernst:

  • Primeiro nome: 5 caracteres
  • Sobrenome: 5 caracteres
  • Como são iguais, NULLIF retorna NULL

Uso prático:
Esta função é útil para identificar registros onde dois campos têm valores iguais, transformando a igualdade em NULL para facilitar filtros posteriores.

10. Expressão CASE

A expressão CASE implementa lógica condicional (if-then-else) dentro de consultas SQL.

10.1 Sintaxe 

CASE expressão
  WHEN valor1 THEN resultado1
  WHEN valor2 THEN resultado2
  WHEN valorN THEN resultadoN
  ELSE resultado_padrão
END
Enter fullscreen mode Exit fullscreen mode

10.2 Exemplo de Reajuste Salarial 

SELECT last_name, job_id, salary,
       CASE job_id
         WHEN 'IT_PROG'   THEN 1.10*salary
         WHEN 'ST_CLERK'  THEN 1.15*salary
         WHEN 'SA_REP'    THEN 1.20*salary
         ELSE salary 
       END "NOVO SALARIO"
FROM employees;
Enter fullscreen mode Exit fullscreen mode

Explicação detalhada:

Lógica de negócio: Aplicar reajustes diferenciados por cargo:

  • IT_PROG (Programador): 10% de aumento (multiplica por 1.10)
  • ST_CLERK (Auxiliar de Estoque): 15% de aumento (multiplica por 1.15)
  • SA_REP (Representante de Vendas): 20% de aumento (multiplica por 1.20)
  • Outros cargos: Sem aumento (mantém salário atual)

Fluxo de execução:

  1. Verifica se job_id = 'IT_PROG' → Sim? Retorna 1.10*salary
  2. Senão, verifica se job_id = 'ST_CLERK' → Sim? Retorna 1.15*salary
  3. Senão, verifica se job_id = 'SA_REP' → Sim? Retorna 1.20*salary
  4. Senão, retorna salary (ELSE)

Resultado exemplo:

LAST_NAME   JOB_ID     SALARY  NOVO SALARIO
King        AD_PRES    24000   24000
Hunold      IT_PROG     9000   9900
Taylor      SA_REP      8600   10320
Davies      ST_CLERK    3100   3565
Enter fullscreen mode Exit fullscreen mode

Cálculos:

  • Hunold (IT_PROG): 9000 × 1.10 = 9900
  • Taylor (SA_REP): 8600 × 1.20 = 10320
  • Davies (ST_CLERK): 3100 × 1.15 = 3565

Características importantes:

  • A cláusula ELSE é opcional, mas recomendada
  • Sem ELSE, valores não correspondidos retornam NULL
  • Pode ser usado em SELECT, WHERE, ORDER BY e outras cláusulas

11. Função DECODE

A função DECODE é específica do Oracle e oferece funcionalidade similar ao CASE com sintaxe mais compacta.

11.1 Sintaxe 

DECODE(expressão, 
       valor1, resultado1,
       valor2, resultado2,
       valorN, resultadoN,
       resultado_padrão)
Enter fullscreen mode Exit fullscreen mode

11.2 Exemplo de Reajuste Salarial 

SELECT last_name, job_id, salary,
       DECODE(job_id, 'IT_PROG',  1.10*salary,
                      'ST_CLERK', 1.15*salary,
                      'SA_REP',   1.20*salary,
                      salary) "NOVO SALARIO"
FROM employees;
Enter fullscreen mode Exit fullscreen mode

Explicação detalhada:

Estrutura:

  • Primeiro parâmetro: expressão a ser avaliada (job_id)
  • Pares valor-resultado: se job_id = valor, retorna resultado
  • Último parâmetro: valor padrão se nenhuma condição for satisfeita

Leitura linha por linha:

DECODE(job_id,              ← Avalia esta expressão
  'IT_PROG',  1.10*salary,  ← Se = 'IT_PROG', retorna 1.10*salary
  'ST_CLERK', 1.15*salary,  ← Se = 'ST_CLERK', retorna 1.15*salary
  'SA_REP',   1.20*salary,  ← Se = 'SA_REP', retorna 1.20*salary
  salary)                   ← Caso contrário, retorna salary
Enter fullscreen mode Exit fullscreen mode

Resultado: Idêntico ao exemplo com CASE mostrado anteriormente.

11.3 Comparação CASE vs DECODE

Aspecto CASE DECODE
Padrão SQL ANSI SQL (portável) Oracle específico
Legibilidade Mais clara Mais compacta
Condições Suporta operadores (<, >, <=, etc.) Apenas igualdade (=)
Flexibilidade CASE pesquisado permite qualquer condição Limitado a comparações de igualdade
Performance Similar Similar

Exemplo que DECODE não consegue fazer:

-- CASE permite condições complexas
SELECT salary,
       CASE 
         WHEN salary < 5000 THEN 'Baixo'
         WHEN salary BETWEEN 5000 AND 10000 THEN 'Médio'
         WHEN salary > 10000 THEN 'Alto'
       END AS faixa
FROM employees;

-- DECODE não pode fazer isso, pois só compara igualdade
Enter fullscreen mode Exit fullscreen mode

Quando usar cada um:

  • DECODE: Quando você precisa apenas comparar igualdade e quer código compacto
  • CASE: Quando precisa de condições complexas ou portabilidade para outros bancos de dados

12. Resumo Comparativo das Funções

12.1 Funções de Conversão

Função Propósito Exemplo
TO_CHAR Converte data/número em string TO_CHAR(sysdate, 'DD/MM/YYYY')
TO_NUMBER Converte string em número TO_NUMBER('1500')
TO_DATE Converte string em data TO_DATE('17/06/2003', 'DD/MM/YYYY')

12.2 Funções para NULL

Função Argumentos Retorna Quando usar
NVL 2 Se NULL, retorna alternativa Substituição simples
NVL2 3 Valores diferentes para NULL e não-NULL Lógica condicional baseada em NULL
COALESCE 2+ Primeiro valor não-NULL Múltiplas alternativas
NULLIF 2 NULL se iguais, senão primeiro valor Comparação gerando NULL

12.3 Expressões Condicionais

Recurso Tipo Portabilidade Complexidade
CASE Expressão SQL ANSI SQL (todos os bancos) Suporta qualquer condição
DECODE Função Oracle Oracle específico Apenas igualdade

13. Boas Práticas

13.1 Conversão de Dados

  • Sempre especifique o formato ao usar TO_CHAR e TO_DATE
  • Use FM para remover espaços extras em formatação de datas
  • Evite depender de configurações regionais (NLS) implícitas

13.2 Tratamento de NULL

  • Use NVL em cálculos matemáticos para evitar resultados NULL
  • Prefira COALESCE quando tiver múltiplas alternativas
  • Use NVL2 quando a lógica depender da existência de valor, não do valor em si

13.3 Lógica Condicional

  • Prefira CASE sobre DECODE para portabilidade
  • Sempre inclua cláusula ELSE em expressões CASE
  • Use aliases descritivos para colunas calculadas

13.4 Performance

  • Funções de conversão em cláusulas WHERE podem impedir uso de índices
  • NVL é ligeiramente mais rápido que COALESCE para 2 argumentos
  • CASE e DECODE têm performance similar

14. Conclusão

As funções de conversão (TO_CHAR, TO_DATE, TO_NUMBER), funções de tratamento de NULL (NVL, COALESCE, NVL2, NULLIF) e expressões condicionais (CASE, DECODE) são ferramentas fundamentais para qualquer desenvolvedor Oracle SQL.

Principais aprendizados:

  1. TO_CHAR formata datas e números para apresentação legível
  2. TO_DATE e TO_NUMBER convertem strings em tipos de dados apropriados para cálculos e comparações
  3. NVL e suas variantes garantem que valores NULL não quebrem cálculos ou lógica
  4. CASE e DECODE implementam lógica condicional diretamente em consultas SQL

Dominar essas funções permite criar consultas mais robustas, relatórios bem formatados e lógica de negócio complexa diretamente no banco de dados, reduzindo a necessidade de processamento adicional na camada de aplicação.

A prática constante com exemplos reais, como os apresentados neste artigo usando a tabela employees, é a melhor forma de internalizar esses conceitos e aplicá-los eficientemente em seu trabalho diário com Oracle SQL.

Top comments (0)