Usando funções de conversão

Além das funções do utilitário SQL, a biblioteca de funções integradas do Oracle contém funções de conversão de tipo. Pode haver cenários em que a consulta espera entrada em um tipo de dados específico, mas a recebe em um tipo de dados diferente. Nesses casos, o Oracle tenta implicitamente converter o valor inesperado em um tipo de dados compatível que pode ser substituído no local e a continuidade do aplicativo não é comprometida. A conversão de tipo pode ser feita implicitamente pela Oracle ou explicitamente pelo programador.

A conversão implícita de tipo de dados funciona com base em uma matriz que mostra o suporte do Oracle para conversão de tipo interno. Além dessas regras, o Oracle oferece funções de conversão de tipo que podem ser utilizadas nas consultas para conversão e formatação explícita. Na verdade, é recomendável realizar a conversão explícita em vez de depender da inteligência do software. Embora a conversão implícita funcione bem, mas para eliminar as chances de distorção em que entradas ruins podem ser difíceis de tipificar internamente.

Conversão de tipo de dados implícita

Um valor VARCHAR2 ou CHAR pode ser convertido implicitamente para o valor do tipo NUMBER ou DATE pelo Oracle. Da mesma forma, um valor de tipo NUMBER ou DATA pode ser convertido automaticamente em dados de caractere pelo servidor Oracle. Observe que a interconversão impícita ocorre apenas quando o caractere representa um número válido ou um valor de tipo de data respectivamente.

Por exemplo, examine as consultas SELECT abaixo. Ambas as consultas darão o mesmo resultado porque o Oracle trata internamente 15000 e '15000' como o mesmo.

Query-1

SELECT employee_id,first_name,salary
FROM employees
WHERE salary > 15000;

Query-2

SELECT employee_id,first_name,salary
FROM employees
WHERE salary > '15000';

Conversão de tipo de dados explícita

As funções de conversão de SQL são funções de linha única que são capazes de criar um valor de coluna, literal ou uma expressão. TO_CHAR, TO_NUMBER e TO_DATE são as três funções que realizam modificação cruzada de tipos de dados.

Função TO_CHAR

A função TO_CHAR é usada para converter uma entrada numérica ou de data em um tipo de caractere com um modelo de formato (opcional).

Sintaxe 

TO_CHAR(number1, [format], [nls_parameter])

Para conversão de número em caractere, os parâmetros nls podem ser usados ​​para especificar caracteres decimais, separador de grupo, modelo de moeda local ou modelo de moeda internacional. É uma especificação opcional - se não estiver disponível, as configurações nls de nível de sessão serão usadas. Para conversão de data em caractere, o parâmetro nls pode ser usado para especificar os nomes do dia e mês, conforme aplicável.

As datas podem ser formatadas em vários formatos após a conversão para tipos de caracteres usando a função TO_CHAR. A função TO_CHAR é usada para que o Oracle 11g exiba datas em um formato específico. Os modelos de formato diferenciam maiúsculas de minúsculas e devem ser colocados entre aspas simples.

Considere a consulta SELECT abaixo. A consulta formata as colunas HIRE_DATE e SALARY da tabela EMPLOYEES usando a função TO_CHAR.

SELECT first_name,
       TO_CHAR (hire_date, 'MONTH DD, YYYY') HIRE_DATE,
	   TO_CHAR (salary, '$99999.99') Salary
FROM employees
WHERE rownum < 5;

FIRST_NAME           HIRE_DATE          SALARY
-------------------- ------------------ ----------
Steven               JUNE      17, 2003  $24000.00
Neena                SEPTEMBER 21, 2005  $17000.00
Lex                  JANUARY   13, 2001  $17000.00
Alexander            JANUARY   03, 2006   $9000.00

O primeiro TO_CHAR é usado para converter a data de locação para o formato de data MONTH DD, YYYY, ou seja, mês soletrado e preenchido com espaços, seguido pelo dia de dois dígitos do mês e, em seguida, pelo ano de quatro dígitos. Se você preferir exibir o nome do mês em maiúsculas e minúsculas (ou seja, "dezembro"), basta usar este caso no argumento de formato: ('Mês DD, AAAA').

A segunda função TO_CHAR na Figura 10-39 é usada para formatar o SALÁRIO para exibir o símbolo da moeda e duas casas decimais.

A Oracle oferece um conjunto abrangente de modelos de formato. A tabela abaixo mostra a lista de modelos de formato que podem ser usados ​​para definir datas e valores numéricos como caracteres usando TO_CHAR.

Modelo de formato Descrição
,(vírgula) Ele retorna uma vírgula na posição especificada. Você pode especificar várias vírgulas em um modelo de formato de número. Restrições: Um elemento de vírgula não pode iniciar um modelo de formato de número. Uma vírgula não pode aparecer à direita de um caractere decimal ou ponto em um modelo de formato de número.
.(período) Retorna um ponto decimal, que é um ponto (.) Na posição especificada. Restrição: Você pode especificar apenas um período em um modelo de formato de número
$ Retorna o valor com um cifrão à esquerda
0 Retorna zeros à esquerda. Retorna zeros à direita.
9 Retorna o valor com o número especificado de dígitos com um espaço inicial se positivo ou com um menos inicial se negativo. Os zeros à esquerda ficam em branco, exceto para um valor zero, que retorna um zero para a parte inteira do número de ponto fixo.
B Retorna espaços em branco para a parte inteira de um número de ponto fixo quando a parte inteira é zero (independentemente de "0" s no modelo de formato).
C Retorna na posição especificada o símbolo de moeda ISO (o valor atual do parâmetro NLS_ISO_CURRENCY).
D Retorna na posição especificada o caractere decimal, que é o valor atual do parâmetro NLS_NUMERIC_CHARACTER. O padrão é um ponto (.). Restrição: Você pode especificar apenas um caractere decimal em um modelo de formato de número.
EEE Retorna um valor usando em notação científica.
FM Retorna um valor sem espaços em branco à esquerda ou à direita.
G Retorna na posição especificada o separador de grupo (o valor atual do parâmetro NLS_NUMERIC_CHARACTER). Você pode especificar vários separadores de grupo em um modelo de formato de número. Restrição: Um separador de grupo não pode aparecer à direita de um caractere decimal ou ponto em um modelo de formato de número
eu Retorna na posição especificada o símbolo da moeda local (o valor atual do parâmetro NLS_CURRENCY).
MI Retorna um valor negativo com um sinal de menos (-) à direita. Retorna um valor positivo com um espaço em branco à direita. Restrição: O elemento de formato MI pode aparecer apenas na última posição de um modelo de formato numérico.
PR Retorna valor negativo em. Ele pode aparecer apenas no final de um modelo de formato de número.
RN, rm Retorna um valor como algarismos romanos em maiúsculas. Retorna um valor como algarismos romanos em letras minúsculas. O valor pode ser um número inteiro entre 1 e 3999.
S Retorna um valor negativo com um sinal de menos (-) à esquerda ou à direita. Retorna um valor positivo com um sinal de mais (+) à esquerda ou à direita. Restrição: O elemento de formato S pode aparecer apenas na primeira ou na última posição de um modelo de formato de número.
TM "Texto mínimo". Retorna (na saída decimal) o menor número de caracteres possível. Este elemento não diferencia maiúsculas de minúsculas.
você Retorna na posição especificada o símbolo de moeda dupla "Euro" (ou outro) (o valor atual do parâmetro NLS_DUAL_CURRENCY).
V Retorna um valor multiplicado por 10n (e se necessário, arredonde para cima), onde n é o número de 9's após o "V".
X Retorna o valor hexadecimal do número especificado de dígitos.

TO_NUMBER função

A função TO_NUMBER converte um valor de caractere em um tipo de dados numérico. Se a string sendo convertida contiver caracteres não numéricos, a função retornará um erro.

Sintaxe

TO_NUMBER (string1, [format], [nls_parameter])

A tabela abaixo mostra a lista de modelos de formato que podem ser usados ​​para definir valores de caracteres como números usando TO_NUMBER.

Modelo de formato Descrição
CC Século
SCC Século aC prefixado com -
AAAA Ano com 4 números
SYYY Ano AC prefixado com -
IYYY Ano ISO com 4 números
AA Ano com 2 números
RR Ano com 2 números com compatibilidade Y2k
ANO Ano em personagens
SYEAR Ano em caracteres, BC prefixado com -
AC Indicador BC / AD
Q Trimestre em números (1,2,3,4)
MILÍMETROS Mês do ano 01, 02 ... 12
MÊS Mês em caracteres (ou seja, janeiro)
SEG JAN, FEB
WW Número da semana (ou seja, 1)
W Número da semana do mês (ou seja, 5)
IW Número da semana do ano no padrão ISO.
DDD Dia do ano em números (ou seja, 365)
DD Dia do mês em números (ou seja, 28)
D Dia da semana em números (ou seja, 7)
DIA Dia da semana em caracteres (ou seja, segunda-feira)
FMDAY Dia da semana em caracteres (ou seja, segunda-feira)
DY Dia da semana em uma breve descrição de caracteres (ou seja, DOM)
J Dia Juliano (número de dias desde 1 de janeiro de 4713 AC, onde 1 de janeiro de 4713 AC é 1 no Oracle)
HH, H12 Número da hora do dia (1-12)
HH24 Número da hora do dia com notação 24 horas (0-23)
MANHÃ TARDE AM ou PM
SENHORITA Número de minutos e segundos (ou seja, 59),
SSSSS Número de segundos neste dia.
DS Formato de data abreviada. Depende das configurações de NLS. Use apenas com carimbo de data / hora.
DL Formato de data extenso. Depende das configurações de NLS. Use apenas com carimbo de data / hora.
E Nome abreviado da era. Válido apenas para calendários: Imperial Japonês, Oficial ROC, Buda Tailandês.
EE O nome completo da era
FF Os segundos fracionários. Use com carimbo de data / hora.
FF1..FF9 Os segundos fracionários. Use com carimbo de data / hora. O dígito controla o número de dígitos decimais usados ​​para segundos fracionários.
FM Modo de preenchimento: suprime os espaços em branco na saída da conversão
FX Formato exato: requer correspondência de padrão exata entre os dados e o modelo de formato.
IYY OU IY OU I Os últimos 3,2,1 dígitos do ano padrão ISO. Somente saída
RM A representação numeral romano do mês (I .. XII)
RR Os últimos 2 dígitos do ano.
RRRR Os últimos 2 dígitos do ano, quando usados ​​para produção. Aceita anos de quatro dígitos quando usado para entrada.
SP Formato escrito. Pode surgir do final de um elemento de número. O resultado é sempre em inglês. Por exemplo, o mês 10 no formato MMSP retorna "dez"
SPTH Formato ortográfico e ordinal; 1 resulta primeiro.
º Converte um número em seu formato ordinal. Por exemplo, 1 se torna o primeiro.
TS Formato de tempo curto. Depende das configurações de NLS. Use apenas com carimbo de data / hora.
TZD Nome abreviado do fuso horário. ou seja, PST.
TZH, TZM Deslocamento de hora / minuto do fuso horário.
TZR Região do fuso horário
X Personagem de raiz local. Na América é um ponto final (.)

As consultas SELECT abaixo aceitam números como entradas de caracteres e os imprime seguindo o especificador de formato.

SELECT  TO_NUMBER('121.23', '9G999D99') 
FROM DUAL

TO_NUMBER('121.23','9G999D99')
------------------------------
                        121.23

SELECT  TO_NUMBER('1210.73', '9999.99') 
FROM DUAL;

TO_NUMBER('1210.73','9999.99')
------------------------------
                       1210.73

Função TO_DATE

A função recebe valores de caractere como entrada e retorna data formatada equivalente ao mesmo. A função TO_DATE permite aos usuários inserir uma data em qualquer formato e, em seguida, converte a entrada para o formato padrão usado pelo Oracle 11g.

Sintaxe:

TO_DATE( string1, [ format_mask ], [ nls_language ] )

Um argumento format_mask consiste em uma série de elementos que representam exatamente a aparência dos dados e devem ser inseridos entre aspas simples.

Modelo de formato Descrição
ANO Ano, explicado
AAAA Ano de 4 dígitos
YYY, YY, Y Últimos 3, 2 ou 1 dígito (s) do ano.
IYY, IY, I Últimos 3, 2 ou 1 dígito (s) do ano ISO.
IYYY Ano de 4 dígitos com base no padrão ISO
RRRR Aceita um ano de 2 dígitos e retorna um ano de 4 dígitos.
Q Trimestre do ano (1, 2, 3, 4; JAN-MAR = 1).
MILÍMETROS Mês (01-12; JAN = 01).
SEG Nome abreviado do mês.
MÊS Nome do mês, preenchido com espaços em branco até 9 caracteres.
RM Mês em algarismos romanos (I-XII; JAN = I).
WW Semana do ano (1-53), em que a semana 1 começa no primeiro dia do ano e continua até o sétimo dia do ano.
W Semana do mês (1-5) em que a semana 1 começa no primeiro dia do mês e termina no sétimo.
IW Semana do ano (1-52 ou 1-53) com base no padrão ISO.
D Dia da semana (1-7).
DIA Nome do dia.
DD Dia do mês (1-31).
DDD Dia do ano (1-366).
DY Nome abreviado do dia.
J Dia juliano; o número de dias desde 1º de janeiro de 4712 AC.
HH12 Hora do dia (1-12).
HH24 Hora do dia (0-23).
SENHORITA Minuto (0-59).
SSSSS Segundos após a meia-noite (0-86399).
FF Segundos fracionários. Use um valor de 1 a 9 após FF para indicar o número de dígitos em segundos fracionários. Por exemplo, 'FF4'.
MANHÃ TARDE Indicador de meridiano
AD, BC Indicador AD, BC
TZD Informações de horário de verão. Por exemplo, 'PST'
TZH, TZM, TZR Hora / minuto / região do fuso horário.

O exemplo a seguir converte uma string de caracteres em uma data:

SELECT TO_DATE('January 15, 1989, 11:00 A.M.',  'Month dd, YYYY, HH:MI A.M.',  'NLS_DATE_LANGUAGE = American')
FROM DUAL;

TO_DATE('
---------
15-JAN-89

Funções Gerais

Funções gerais são usadas para lidar com valores NULL no banco de dados. O objetivo das funções gerais de tratamento de NULL é substituir os valores NULL por um valor alternativo. Veremos resumidamente essas funções a seguir.

NVL

A função NVL substitui um valor alternativo por um valor NULL.

Sintaxe:

NVL( Arg1, replace_with )

Na sintaxe, ambos os parâmetros são obrigatórios. Observe que a função NVL funciona com todos os tipos de tipos de dados. E também que o tipo de dados da string original e a substituição devem estar em estado compatível, ou seja, o mesmo ou implicitamente conversível pelo Oracle.

Se arg1 for um valor de caractere, o oracle converte a string de substituição no tipo de dados compatível com arg1 antes de compará-los e retorna VARCHAR2 no conjunto de caracteres de expr1. Se arg1 for numérico, o Oracle determina o argumento com a precedência numérica mais alta, converte implicitamente o outro argumento para esse tipo de dados e retorna esse tipo de dados.

A instrução SELECT abaixo exibirá 'n / a' se um funcionário ainda não tiver sido atribuído a nenhum trabalho, ou seja, JOB_ID é NULL. Caso contrário, ele exibiria o valor real JOB_ID.

SELECT  first_name, NVL(JOB_ID, 'n/a')
FROM employees;

NVL2

Como um aprimoramento em relação ao NVL, o Oracle introduziu uma função para substituir o valor não apenas para valores de colunas NULL, mas também para colunas NOT NULL. A função NVL2 pode ser usada para substituir um valor alternativo para NULL, bem como um valor não NULL.

Sintaxe:

NVL2( string1, value_if_NOT_null, value_if_null )

A instrução SELECT abaixo exibiria 'Banco' se JOB_CODE para um funcionário fosse NULL. Para um valor definido não nulo de CÓDIGO DE TRABALHO, ele mostraria o valor constante 'Trabalho Atribuído'.

SQL> SELECT NVL2(JOB_CODE, 'Job Assigned', 'Bench')
FROM employees;

NULLIF

A função NULLIF compara dois argumentos expr1 e expr2. Se expr1 e expr2 forem iguais, ele retornará NULL; caso contrário, ele retorna expr1. Ao contrário da outra função de tratamento de nulos, o primeiro argumento não pode ser NULL.

Sintaxe:

NULLIF (expr1, expr2)

Observe que o primeiro argumento pode ser uma expressão avaliada como NULL, mas não pode ser o literal NULL. Ambos os parâmetros são obrigatórios para a execução da função.

A consulta abaixo retorna NULL, pois os valores de entrada 12 são iguais.

SELECT	NULLIF (12, 12)
FROM DUAL;

Da mesma forma, a consulta abaixo retorna 'SUN', pois as duas strings não são iguais.

SELECT NULLIF ('SUN', 'MOON')
FROM DUAL;

COALESCE

A função COALESCE, uma forma mais genérica de NVL, retorna a primeira expressão não nula na lista de argumentos. Leva no mínimo dois parâmetros obrigatórios, mas o máximo de argumentos não tem limite.

Sintaxe:

COALESCE (expr1, expr2, ... expr_n )

Considere a consulta SELECT abaixo. Ele seleciona o primeiro valor não nulo alimentado nos campos de endereço de um funcionário.

SELECT COALESCE (address1, address2, address3) Address
FROM  employees;

Curiosamente, o funcionamento da função COALESCE é semelhante à construção IF..ELSIF..ENDIF. A consulta acima pode ser reescrita como -

IF address1 is not null THEN
   result := address1;
ELSIF address2 is not null THEN
   result := address2;
ELSIF address3 is not null THEN
   result := address3;
ELSE
   result := null;
END IF;

Funções Condicionais

O Oracle fornece funções condicionais DECODE e CASE para impor condições até mesmo na instrução SQL.

A função DECODE

A função é a equivalência SQL da instrução procedural condicional IF..THEN..ELSE. DECODE funciona com valores / colunas / expressões de todos os tipos de dados.

Sintaxe:

DECODE (expression, search, result [, search, result]... [, default])

A função DECODE compara a expressão com cada valor de pesquisa em ordem. Se houver igualdade entre a expressão e o argumento de pesquisa, ele retornará o resultado correspondente. Em caso de não correspondência, o valor padrão é retornado, se definido, caso contrário, NULL. No caso de qualquer incompatibilidade de compatibilidade de tipo, o oracle faz internamente uma possível conversão implícita para retornar os resultados.

Na verdade, o Oracle considera dois nulos como equivalentes ao trabalhar com a função DECODE.

SELECT DECODE(NULL,NULL,'EQUAL','NOT EQUAL') 
FROM DUAL;

DECOD
-----
EQUAL

Se a expressão for nula, o Oracle retornará o resultado da primeira pesquisa, que também é nulo. O número máximo de componentes na função DECODE é 255.

SELECT	first_name, salary, DECODE (hire_date, sysdate,'NEW JOINEE','EMPLOYEE')
	FROM employees;

Expressão CASE

As expressões CASE funcionam com o mesmo conceito que DECODE, mas diferem na sintaxe e no uso.

Sintaxe:

CASE  [ expression ]
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n
   ELSE result
END

A pesquisa do Oracle começa da esquerda e avança para a direita até encontrar uma condição verdadeira e, em seguida, retorna a expressão de resultado associada a ela. Se nenhuma condição for considerada verdadeira e existir uma cláusula ELSE, o Oracle retornará o resultado definido com else. Caso contrário, o Oracle retorna nulo.

O número máximo de argumentos em uma expressão CASE é 255. Todas as expressões contam para esse limite, incluindo a expressão inicial de uma expressão CASE simples e a expressão ELSE opcional. Cada par WHEN ... THEN conta como dois argumentos. Para evitar exceder esse limite, você pode aninhar expressões CASE para que a própria return_expr seja uma expressão CASE.

SELECT first_name, CASE	WHEN salary < 200 THEN 'GRADE 1'
			WHEN salary > 200 AND salary < 5000 THEN 'GRADE 2'
			ELSE 'GRADE 3'
		   END CASE
FROM employees;	

ENAM  	CASE
----    -------
JOHN    GRADE 2
EDWIN   GRADE 3
KING    GRADE 1