Compreendendo a conversão implícita e explícita de tipos de dados

As conversões de tipos de dados executam a transformação do tipo de dados em outro como por exemplo converter um tipo numérico para um tipo de dados de texto. Há dois tipos de conversão de tipos de dados, a conversão implícita que é feita automática pelo Oracle e a conversão explícita que é o programador de banco de dados que faz usando funções disponíveis do banco de dados. As conversões não alteram o valor, alteram apenas o tipo de dados.

Por exemplo, se tiver um valor financeiro armazenado no banco de dados na forma: R$ 500,00; caso use nesse padrão de texto que está junto com R$ e tentar fazer um cálculo matemático vai dar erro, precisa extrair o valor, pra isso utiliza conversão explícita onde pega o valor texto e transforma em valor numérico através de uma função.

Conversão Implícita de tipo de dados

Vamos ver um exemplo de conversão implícita que é realizada automática pelo banco de dados:

Vimos na Figura 1, a primeira linha começa com a palavra reservada SELECT seguido de um texto entre aspas simples depois dois pipes que indica concatenação || depois o número um que é um tipo de dados numérico depois novamente dois pipes e na linha 2 continuamos a concatenar mais um texto, na linha 3 temos a palavra chave AS que serve para apelidar a coluna COLUNA_TESTE, na linha 4 temos a palavra chave FROM que serve para indicar qual tabela estamos fazendo a consulta que é na tabela DUAL, que é uma tabela de teste.

O ponto chave é a linha 1 e 2, nela concatenamos um texto com um número, o Oracle permite através da conversão implícita que o próprio Oracle faz ele converte o tipo numérico 1 em texto e assim consegue unir o texto e o número, mas só depois de fazer a conversão implícita. É mais indicado usarmos uma função que converta o número em texto para fazer a concatenação, evitando assim possíveis erros que podem ocorrer ao executar a instrução SQL.

Quando comparamos texto com número como em um operador = por exemplo o texto será convertido implicitamente para número mas nem sempre, veja um exemplo:

Na linha 1 foi comparado o texto 3 se é maior que o texto 20 pois estão com aspas simples sendo assim são do tipo de texto. Isso é verdadeiro o caractere 3 é maior que 20 se formos no dicionário teremos o 3 depois de 20 ou seja, maior. já na linha 6 o caractere 3 foi convertido para número, dessa forma o número 3 é menor que 20 e então não traz nenhuma linha como resultado do SQL.

A seguir veremos algumas regras adicionais para a conversão automática de tipo de dados:

Geralmente, os valores numéricos não são convertidos automaticamente em datas.
Os dados numéricos e de texto geralmente não são convertidos automaticamente para tipos de tamanho muito grande, como LOB, CLOB, RAW e outros.
Tipos muito grandes geralmente não se convertem automaticamente, como de BLOB para CLOB, por exemplo. Alguns vão, mas muitos não.
Se usar uma coluna do tipo texto que contém um número e comparar com uma coluna do tipo numérico vai funcionar, pois vai converter implicitamente o texto para número para fazer a comparação.
Conversão implícita de texto para número: apenas funcionará se o texto for um número.
Conversão implícita de data e número para texto: vai funcionar sempre desde que o tamanho permita.

Conversão Explícita de tipo de dados

A conversão explícita de tipos de dados é clara, mais fácil de ler e resulta em melhor desempenho; conversão implícita não é tão eficiente. Por exemplo, um índice pode acelerar o desempenho de uma instrução SELECT com base em como a cláusula WHERE é formulada, mas se essa cláusula WHERE usar sintaxe que depende de uma conversão implícita, o índice poderá não ser usado ou até incorretamente. Isso não afeta os dados resultantes, é claro, mas pode resultar em degradação do desempenho, o que resulta em maior tempo de processamento.

A documentação da Oracle alerta que o comportamento da conversão implícita está sujeito a alterações nas versões de software. A linha inferior é que certos tipos de conversões de tipo de dados são executados automaticamente. Mas não dependa deles no seu código. Use a conversão explícita, conforme iremos analisar nesta seção.

Funções de conversão explícita

TO_NUMBER | TO_CHAR | TO_DATE

A Conversão explícita ocorre por meio das seguintes funções:
TO_NUMBER = Que converte um texto em um número.
TO_CHAR = Que converte um texto, número ou data em texto.
TO_DATE = Que converte um texto para uma data.

TO_NUMBER

Sintaxe: TO_NUMBER(e1, format_model, nls_parms)

O retorno da função TO_NUMBER será sempre um número. O primeiro parâmetro da função TO_NUMBER e1 é uma expressão e é obrigatório. format_model é o modelo de formato e é opcional. Consulte a Tabela 1 abaixo para obter uma lista completa dos elementos que compõem o modelo de formato.

Elemento	Exemplo	Descrição (TABELA 1)
, .	9,999.99	Vírgulas e pontos decimais Aviso: apenas um ponto da casa decimal é permitido por máscara.
$	$999.99	Sinal de dólar
0	0099.99	zero quando informado na máscara preencherá zero caso não tem informação correspondente.
9	999	Qualquer dígito
B	B999	Espaço em branco
C	C999	Símbolo da moeda ISO definido pelo parâmetro NLS_ISO_CURRENCY
D	999D99	Preenche com o caractere de decimal que é definido em NLS_NUMERIC_CHARACTERS
EEEE	9.9EEEE	Retorna o valor em notação científica
G	9G999	Retorna o separador de grupo, geralmente por padrão americano é a vírgula
L	L999	Retorna o símbolo da moeda local
MI	999MI	Retorna sinal de positivo ou negativo do número.
PR	999PR	Caso o número seja negativo retorna no formato <123>
RN	RN	Numero romano, RN trará em maiúsculo, rn retornará o número romano em minúsculo
S	S999 999S	Retorna o sinal de positivo ou negativo, pode informar no início ou no fim da máscara apenas.
TM	TM	Retorno no formato de número menor possível, em notação científica.
U	U999	Por padrão retorna a moeda Euro ou outra indicada no parâmetro NLS_DUAL_CURRENCY.
V	999V99	Retorna um valor multiplicado por 10n, onde n é o número de 9 depois do V. Deve ser usado com a função TO_CHAR. Ex: select TO_CHAR(5, ’99V99′) from dual; Resultado: 500
X	XXXX	Retorna valor hexadecimal

Há um terceiro parâmetro opcional que representa as configurações de NLS. Ele permite identificar qualquer um dos três parâmetros NLS definidos na Tabela 2 abaixo.

Parâmetro NLS	Descrição
NLS_NUMERIC_CHARACTERS=’dg’	d = decimal character (ver D da Tabela 1) g = grupo separador (ver G da tabela 1)
NLS_CURRENCY = ‘texto’	texto = símbolo da moeda local (ver L na Tabela 1)
NLS_ISO_CURRENCY = ‘moeda’	moeda = símbolo da moeda internacional (ver C tabela 1)

Se incluído, o terceiro parâmetro para TO_NUMBER consiste em uma única sequência que abrange qualquer um ou mais desses três parâmetros NLS. Por exemplo, a seguir está um exemplo do parâmetro nls_parms que fornece uma especificação de dois parâmetros NLS:

Vamos analisar a instrução acima, a linha 1 começa pela palavra reservada SELECT depois a coluna FIRST_NAME que foi apelidado de NOME usando a palavra reservada AS, depois temos a coluna SALARIO, na linha 2 temos a função TO_CHAR.

A função TO_CHAR serve para converter um campo numérico ou de data em um texto formatado,

O primeiro parâmetro é o SALARY que é um campo numérico que grava o salário do funcionário mas não grava formatado.

O segundo parâmetro L9G999G999D99 é a máscara da nossa formatação, significa que da direita para esquerda temos 99 que representa as casas decimais depois temos o D que é o separador decimal definido como vírgula (,), depois 999 pois queremos 3 números para o grupo de centena, seguido de G que é o separador de grupo que foi definido como ponto (.), segue mais 999 que é o grupo de milhar depois G para colocar o ponto e mais um 9 para o grupo do milhão seguido de L que é o símbolo da moeda Local.

O terceiro parâmetro é referente ao nls_parans que vimos na tabela Parâmetros NLS, podemos usar os três parâmetros para alterar dados da formatação, usamos dois que são:

'NLS_CURRENCY=''R$ '' NLS_NUMERIC_CHARACTERS='',.'' ')

O NLS_CURRENCY serve para alterar o símbolo da moeda Local, por padrão se usássemos apenas o L da máscara já iria trazer a moeda Local, mas a moeda Local do banco pode ser outra e não o R$, por isso recorremos ao NLS_CURRENCY, para garantirmos que seja esse símbolo de reais, em seguida usamos o NLS_NUMERIC_CHARACTERS que serve para alterar a pontuação padrão, por padrão a pontuação do banco vem no padrão norte americano o número seria assim 1,234.56 e nosso formato brasileiro já usa a vírgula para separar as casas decimais ex: 1.234,56 sendo assim precisamos alterar a pontuação que é o que o NLS_NUMERIC_CHARACTERS faz invertendo essa pontuação e veja que é atribuído '',.'' para ele.

Colocamos todos os nls_parans seguido de espaço em branco e entre aspas simples, quando atribuído um valor deverá usar duas aspas simples, ao adicionar outro nls_parans deverá dar um espaço em branco e digitar o nome do parâmetro como foi feito entre ''R$'' NLS_NUMERIC_CHARACTERS.

O que fizemos na figura 3 foi transformar o salário que é salvo no banco como número em um formato mais apresentável, veja o primeiro registro da consulta o salário: 24000 e após a formatação: R$ 24.000,00 isso foi feito apenas para demonstração para um relatório, o valor salvo no banco continua sendo 24000 sem formatação. Agora e se tivermos um valor formatado e queremos limpar sua formatação? Vamos usar esse mesmo exemplo, se tivéssemos R$ 24.000,00 e queremos transformá-lo em 24000 então devemos usar o TO_NUMBER, como vimos o TO_NUMBER pega um tipo de dados texto e transforma em número, veja o exemplo abaixo:

SELECT TO_NUMBER('R$ 24.000,00', 'L99G999D99',
'NLS_CURRENCY=''R$ '' NLS_NUMERIC_CHARACTERS='',.''') AS SAL
FROM DUAL

O TO_NUMBER pega o primeiro parâmetro em texto R$ 24.000,00 com a máscara L99G999D99 o L é a moeda Local o resto segue a mesma forma do exemplo anterior, o resultado da consulta acima teremos o valor sem formatação 24000 que é o valor que queremos pegar.

Vejamos mais um exemplo de TO_NUMBER:

TO_CHAR

O TO_CHAR tem três utilidades, usamos para converter um texto em texto, um número para texto e uma data para texto, o texto que queremos é um texto formatado e para formatá-lo da forma que queremos devemos usar máscaras de dados.

TO_CHAR – Caractere

Sintaxe: TO_CHAR (c)
Parâmetros: c é um NCHAR, um NVARCHAR2, um CLOB ou um NCLOB. Processo: transforma o parâmetro recebido em um VARCHAR2.
Saída: VARCHAR2. Exemplo:

SELECT TO_CHAR('Hello') FROM DUAL;

O retorno da consulta acimo é a palavra Hello com o tipo de dados VARCHAR2, há situações em que você trabalha com tipos de dados que não podem aceitar, por exemplo, dados CLOB, mas podem aceitar VARCHAR2.

TO_CHAR – Número

Sintaxe: TO_CHAR (n, format_model, nls_parms)
Parâmetros: n é um número (obrigatório).
O parâmetro format_model é opcional. Um modelo de formato consiste em um ou mais elementos de formato, que você viu anteriormente listados na Tabela 1.
O valor nls_parms é o mesmo parâmetro que você viu anteriormente com a função TO_NUMBER.

Processo: transforma um número em um texto formatado com caracteres especiais que podem ser desejados, como cifrões ou outros símbolos financeiros, tratamento especial de números negativos etc. . Exemplo: formate o número 198 com um cifrão e uma especificação de centavo.

SELECT TO_CHAR(198, '$999.99') AS FORMA FROM DUAL

Teremos como saída o valor $198.00 formatado pela consulta acima.

Exemplos de TO_CHAR(NUMBER).

TO_CHAR – Data

Sintaxe: TO_CHAR(d, format_model, nls_parms)
Parâmetros: d é uma data ou um intervalo de datas (obrigatório). O parâmetro format_model é opcional e pode ser usado para formatar dados de várias maneiras. Consulte a Tabela 3 para obter detalhes sobre modelos de formato para tipos de dados de data. O parâmetro nls_parms é o mesmo que você viu anteriormente para a função TO_NUMBER.

Elemento	Descrição (TABELA 3)
AD/A.D. BC/B.C.	BC (Before Christ) Antes de Cristo, AC => Ano Atual ou Depois de Cristo, pode ser usado com ou sem pontuação.
AM/A.M. PM/P.M.	AM (Horário da manhã) PM (Horário da Tarde) pode ser usado com ou sem pontuação.
CC/SCC	Século
D	Dia da semana (1 até 7)
DAY	Nome do dia da semana soletrado.
DD	Dia do mês, (1 a 31)
DDD	Dia do ano (1 a 366)
DL	Formato de data longa, descrito em NLS_DATE_FORMAT. A aparência é determinada pelos parâmetros: NLS_TERRITORY e NLS_LANGUAGE.
DS	Formato de data abreviado. A aparência é determinada pelos parâmetros: NLS_TERRITORY e NLS_LANGUAGE.
DY	Nome do dia abreviado, SUN, MON, TUE…
E	Nome da era abreviado
EE	Nome da era completo
FF	Segundos fracionais.
FM	É usado combinado com outros elementos para suprimir espaços em branco da direita e esquerda do texto.
FX	Correspondência exata entre os dados dos caracteres e o modelo de formato.
HH, HH12	Hora do dia, 1 a 12 (ambos são idênticos). 12 meia-noite é representada como 12
HH24	Hora do dia, 1 a 24, 12 meia-noite é representado por 00
IW	Semana do Ano, 1 até 53
I IY IYY IYYY	Últimos um, dois, três e quatro dígitos do ano no formato ISO.
J	Juliano Calendário, contado como número de dias desde primeiro de Janeiro de 4712 A.C
MI	Minutos. Zero 00 até 59
MM	Mês em dois dígitos: 01 até 12
MON	Abreviação do mês em 3 dígitos: JAN, FEB, MAR
MONTH	Nome do mês soletrado
PR	Se negativo, os números são colocados entre colchetes angulares <> Se positivo retorna com espaço em branco do lado direito e esquerdo. Exemplo: 9999PR
Q	Trimestre do Ano.
RM	Mês Romano numeral
RR	Aceita datas do século XX no século XXI usando apenas dois dígitos. 00 até 49 são interpretados como 2000 até 2049. 50 a 99 são interpretados como 1950 a 1999
RRRR	Quatro dígitos do ano. Se informado dois dígitos para o ano retornará o mesmo valor que RR.
SS	Segundos, 0 até 59
SSSS	Segundos após meia-noite, 0 até 86399
TS	Formato de horas abreviado, permitido somente quando especificado com o elemento de modelo no formato DL ou DS, separado por espaço em branco.
TZD	Fuso horário abreviado com horário de verão. Válido apenas nos formatos de timestamp e intervals. Exemplos: EST, CMT.
TZH	Hora do fuso horário. Inválido nos tipos de dados DATE; válido apenas nos formatos TIMESTAMP e INTERVAL. 00 a 59
TZM	Minuto de fuso horário. Válido apenas nos formatos TIMESTAMP e INTERVAL. 00 a 59.
TZR	Informações sobre a Região do fuso horário. Não é válido nos tipos de dados DATE; válido apenas nos formatos TIMESTAMP e INTERVAL. Exemplo: America / Los_Angeles.
WW	Semana do ano, 1 até 53.
W	Semana do mês, 1 até 5. A semana começa do primeiro dia do mês até o fim do sétimo dia do mês.
X	Caractere de raiz local. Este é o caractere usado em uma representação numérica para separar um número inteiro de sua parte fracionária. Na notação de base 10, o caractere de raiz é um ponto decimal, como em 17.2, onde o ponto decimal separa o número inteiro 17 da sua parte fracionária de 0.2
Y,YYY	Ano com vírgula na posição
YEAR, SYEAR	Ano soletrado. O S irá indicar caso seja ano antes de cristo aparecerá um sinal de negativo.
YYYY, SYYYY	Quatro dígitos do ano. O S irá indicar caso seja ano antes de cristo aparecerá um sinal de negativo.
YYY, YY, Y	Os últimos três dígitos, dois dígitos e um dígito do ano.
– / , . ; :	Pontuações aceitas
“texto”	Valor literal, mostrado da forma que foi escrita entre aspas duplas.

Veja exemplos do uso de TO_CHAR com DATA.

TO_DATE

Sintaxe: TO_DATE(c, format_model, nls_parms)
Parâmetros: c = uma sequência de caracteres (obrigatório); format_model é um modelo de formato de acordo com a Tabela 3. O valor nls_parms é o mesmo parâmetro que você viu anteriormente com a função TO_NUMBER. Processo: Transforme o valor contido em c em um tipo de dados DATE válido, estruturando format_model para descrever como a cadeia de caracteres é formada, identificando as informações da data adequadamente. Saída: Data. Exemplo: Converter uma representação de data fora do padrão para o formato padrão