Funções de Conversão

A conversão de dados no Oracle pode ocorrer de duas formas: Explicitamente ou Implicitamente.

Conversão Implícita de Tipos de Dados

O Oracle faz a conversão implícita de dados, para os casos em que o tipo de dados do valor usado é diferente do tipo esperado. Por exemplo, digamos que você faça uma consulta por um valor de salário, sendo a coluna SALARIO numérica, o tipo que se espera para a comparação é um número, mas a consulta SALARIO = '2000' será reconhecida, pois implicitamente o Oracle converte a string '2000' para o número 2000 e recupera as linhas que se adequam a essa condição. 

O Oracle pode converter automaticamente os tipos:

Para que a expressão VARCHAR2 ou CHAR seja convertida em NUMBER com sucesso, é necessário que não exista caractere diferente de números formando a string, por exemplo, SALARIO = '215A3 7', ocorreria um erro de número inválido. De VARCHAR2  ou CHAR para DATE é necessário que a string esteja em um modelo reconhecido, os quais serão descritos adiante.

Conversão Explícita de Tipos de Dados

As Funções de Conversão Explícitas mais comuns são TO_CHAR, TO_NUMBER e TO_DATE. Segue detalhes sobre o uso de cada uma delas:

• TO_CHAR (number | date, [fmt], [nlsparams]): Converte um número ou data em uma string de caracteres, fmt refere-se ao modelo de formato. Conversão de Número: o parâmetro nlsparams define os seguintes caracteres, que são retornados pelos elementos de formato numérico - Caractere Decimal, Separador de Grupos, Símbolo de Moeda Nacional, Símbolo de Moeda Internacional. Se não for definido, será usado o padrão da sessão. Conversão de Data: O parâmetro nlsparams especifíca o idioma em que são retornadas as abreviações e dias, meses. Se for omitido será retornado o padrão da sessão.
• TO_NUMBER (char, [fmt], [nlsparams]): Converte strings de caractere composta de números em um número. O formato fmt pode ser específicado. O parâmetro nlsparams funciona como na função TO_CHAR, referente a conversão de números.
• TO_DATE (char, [fmt], [nlsparams]): Converte uma string que representa uma data em uma data com o formato (fmt) informado, o valor default de fmt será DD-MON-YY.

Função TO_CHAR com Datas

Você pode usar a função TO_CHAR para escrever uma data com modelo diferente do modelo (formato) default, você pode manipular o resultado de acordo com a sua necessidade.

TO_CHAR (data, 'formato')

Observações importantes:

• O modelo do formato deve ser delimitado por aspas simples;
• O formato informado pode ser qualquer elemento de formato de data válido, ex: DD, MM, MI, SS, YYYY, D, MONTH, YEAR, DAY, e assim por diante;

Exemplos de Elementos de Formato de Data Válidos

Outros formatos:

• / . , : A pontuação é reproduzida no resultado;
• "of the": String entre aspas é reproduzida no resultado.

Utilização de sufixos que alteram a exibição de números:

• TH: Número ordinal;
• SP: Números por extenso;
• SPTH ou THSP: Números decimais por extenso.

Função TO_CHAR com Números

Você pode utilizar a função do TO_CHAR com números, para transformar o número em uma string de caratere com um formato desejado. Você pode utilizar os elementos de formato abaixo:

Função TO_NUMBER e TO_DATE

Você pode converter uma string de caracteres em um número ou uma data, utilizando as funções TO_NUMBER e TO_DATE.

TO_NUMBER(char [,formato]);

TO_DATE(char [,formato]);

Para que a conversão ocorra com sucesso na conversão para números, a string não pode conter elementos inválidos, como letras, ou símbolos. Caso contrário, o erro informando que o número não é válido é retornado. Ex: SELECT TO_NUMBER('23JR456') FROM DUAL; Ocorrerá o erro ORA-01722: número inválido.

Caso utilize pontos e vírgulas, utilize o parâmetro de formatação para que o número seja convertido. Ex: SELECT TO_NUMBER('23,456.00','99,999.99') FROM DUAL;

No caso da conversão para datas, os elementos válidos de formatação são os mesmos listados na utilização de TO_CHAR com tipo data.

Elemento de Formato de Data RR

O elemento de data RR é semelhante ao elemento YY, também recupera o ano. Mas o RR se comporta diferente ao recuperar o século, considerando a data atual e os últimos 2 dígitos do ano informado. Observe o comportamento no quadro abaixo:

Espero que tenha sido útil! =)

Funções de Data

Antes de começar o assunto, é importante mencionar a função SYSDATE, que é uma função nativa do Oracle e a sua função é recuperar a data e hora atual do servidor do banco de dados. Pode ser usada em consultas e em objetos PL/SQL. Um exemplo de como utilizar:

SELECT SYSDATE FROM DUAL;

Funções de Data

As Funções de Data operam em datas Oracle, quase todas retornam datas, a única exceção é a função MONTHS_BETWEEN, que retorna um valor numérico, correspondente a quantidade de meses entre duas datas.

Algumas das mais utilizadas sãos:

• MONTHS_BETWEEN (data_1, data_2): Obtém a quantidade de meses entre a data_1 e a data_2, o resultado pode não ser um número inteiro, que representa uma parte do mês. Caso a data_1 for anterior a data_2 o resultado será negativo;
• ADD_MONTHS (data, n): Adiciona n (número de meses) meses à data, n deve ser inteiro e pode ser negativo, fazendo nesse caso uma subtração de meses;
• NEXT_DAY (data, 'char'): Obtém a data do próximo dia da semana ('char'), após a data em questão. O valor de 'char' pode ser um número que represente um dia ou uma string de caracteres;
• LAST_DAY (data): Retorna a data do último dia do mês que contém a data em questão.
• ROUND (data [, 'fmt']): Retorna a data arredondada até a unidade especificada pelo modelo de formato fmt. Se o modelo fmt for omitido, já que não é obrigatório, a data será arredondada até o dia mais próximo;
• TRUNC (data [,fmt]): Retorna a data com a parte do horário do dia truncada até a unidade especificada pelo modelo de formato fmt. Se o modelo de formato fmt não for especificado, a data será truncada até o dia mais próximo.

Abaixo uma imagem com exemplos acerca da utilização de cada função, clique na imagem para expandi-la:

Observações Importantes Sobre o Exemplo:

• A primeira coluna (MONTHS_BETWEEN) retornou a quantidade de meses desde 11/07/1986 até 30/10/2014, aproximadamente 339 meses, a parte decimal é referente aos dias entre 11 e 30 de outubro. Se a pesquisa fosse MONTHS_BETWEEN('11/10/2014','11/07/1986') o resultado seria 339, um número inteiro;
• ADD_MONTHS adicionou 28 meses à data 11/07/1986;
• A função NEXT_DAY recupera a data da próxima segunda-feira após 30/10/14, e a resposta é 03/11/14. É importante checar em que idioma está a base, pois se o idioma fosse inglês, a consulta retornaria um erro de dia inválido da semana (ORA-01846: não é um dia da semana válido) e o correto seria usar 'MONDAY', para verificar o idioma basta realizar uma consulta simples utilizando conversão da data para um caractere: SELECT TO_CHAR(SYSDATE , 'DAY/MONTH/YEAR') FROM DUAL; É possível utilizar também a numeração referente ao dia da semana, por exemplo, 1 é o número para o domingo, assim temos uma numeração de 1 à 7 para os dias da semana, essa mesma consulta do exemplo pode ser substituída por NEXT_DAY('30/10/2014', 2) AS NEXT_DAY;
• LAST_DAY: Retornou o último dia do mês de outubro;
• ROUND: São três situações no exemplo, no primeiro o parâmetro de modelo não é informado, desse modo a data é arredondada para o próximo dia, 31/10/2014. No segundo exemplo, o parâmetro de formato é informado e solicita que a data seja arredondada para o próximo ano, o resultado foi que a data de 30/10/2014 foi arredondada para 01/01/2015. No terceiro exemplo, a data foi arredondada considerando o mês, o resultado 01/11/14;
• TRUNC: São três situações no exemplo, no primeiro o parâmetro de modelo não é informado, desse modo a data é truncada para o dia 30/10/2014 (o mesmo dia, mas sem a hora/minuto/segundo). No segundo exemplo, o parâmetro de formato é informado e solicita que a data seja truncada considerando o ano, o resultado foi que a data de 30/10/2014 foi truncada para 01/01/2014. No terceiro exemplo, a data foi truncada considerando o mês, o resultado 01/10/14;
• Sobre o ROUND e TRUNC percebemos que são bastante parecidas, sendo que a função ROUND busca sempre a data posterior à data de entrada da função, enquanto que a TRUNC vai pegar sempre a data mais próxima anterior.

É isso. =)

Funções de Número

Funções de Número

As Funções de Número aceitam entrada numérica e retorna valores numéricos também. Alguns exemplos são as funções abaixo:

• ROUND (coluna | expressão, n): Arredonda o valor da coluna ou expressão de entrada para n casas decimais, se n não for informado o valor será arredondado sem casas decimais (se n for negativo, os números à esquerda da vírgula decimal serão arredondados) , também pode ser usado com tipo DATE;

• TRUNC (coluna | expressão, n): Trunca o valor da coluna ou expressão de entrada para n casas decimais, caso n não seja definido o valor default será 0 (zero), também pode ser usado com tipo DATE;

• MOD (m , n): Retorna o resto da divisão de m por n, muito utilizada na verificação de números pares ou ímpares e pode ter outras aplicações no dia-a-dia. Os valores m e n podem ser colunas, expressões, desde que o tipo de dado seja numérico.

É isso. =)

Funções de Caractere

Funções de Caractere

As Funções de Caractere são funções de uma linha que aceitam dados de caractere de entrada e podem retornar tanto caractere como dados numéricos.
São divididas nos tipos:

• Funções de manipulação de maiúsculas e minúsculas;
• Funções de manipulação de caracteres.

Funções de Manipulação de Maiúsculas e Minúsculas

Essas funções são bastante importantes, principalmente ao realizar consultas, o Oracle é considera a diferença entre maiúsculas e minúsculas, e você pode usar para resolver problemas com isso. São elas:

• LOWER (coluna|expressão): altera os caracteres de entrada retornando-os em caixa BAIXA, minúsculo;
• UPPER (coluna|expressão): altera os caracteres de entrada retornando-os em caixa ALTA, maiúsculo;
• INITCAP (coluna|expressão): altera o primeiro caractere de entrada para maiúsculo, os demais minúsculos.

Veja os exemplos abaixo:

LOWER:

UPPER:

INITCAP:

Um exemplo de utilidade no dia-a-dia segue nas imagens abaixo:

Ao buscar na tabela por 'jadsan da cunha santos', nenhuma linha é retornada. Pois o cadastro de nomes foi inserido em letras maiúsculas, veja a imagem a seguir, utilizando a função LOWER.

A consulta retorna 1 linha, pois ela compara o caractere literal com o campo NOME, mas a comparação é feita com o conteúdo desse campo em letras minúsculas. Em alguns casos, pode ser necessário validar a existência de certos dados em uma tabela antes de realizar algum processamento, se você não sabe se as informações foram armazenadas em maiúsculo ou minúsculo, você poderá usar essas funções para padronizar a busca de acordo com a caixa que você desejar e garantir o resultado correto.

Observação:

• Você pode utilizar as funções UPPER, LOWER e INITCAP com Variáveis de Substituição, por exemplo:

SELECT *

FROM FUNCIONARIOS

WHERE NOME = UPPER('&NOME_VAR');

SELECT *

FROM FUNCIONARIOS

WHERE NOME = LOWER ('&NOME_VAR');

SELECT *

FROM FUNCIONARIOS

WHERE NOME = INITCAP('&NOME_VAR');

Funções de Manipulação de Caracteres

As Funções de Manipulação de Caractere recebem dados de caractere de entrada e podem retornar caractere ou um valor numérico. São elas:

• CONCAT (param1, param2): Uni os caracteres param1 e param2 em uma nova string, você só pode usar 2 parâmetros nessa função. No entanto, existe o operador de concatenção, que são duas barras '||', utilizando esse operador você pode concatenar n strings transformando-as em uma só;

• SUBSTR: Extrai uma string de um tamanho determinado;
• LENGTH: Mostra o tamanho de uma string, retorna o valor numérico;
• INSTR: Retorna a posição numérica de um determinado caractere, ou string;
• LPAD: Preenche o valor do caractere à esquerda;
• RPAD: Preenche o valor do caractere à direita;
• TRIM: Reduz os caracteres à esquerda ou a direita ou ambos, de uma string de caracteres, bastante usado para eliminar espaços em branco, pode ser usado para eliminar outros caracteres.

Na imagem abaixo (clique para ampliar), temos um exemplo da utilização de cada função, e as várias formas de utilizar a função TRIM:

• REPLACE: Substitui todas as ocorrências de um item de pesquisa em uma string de origem com um termo de substituição e retorna a string de origem modificada. Usa três argumentos, os dois primeiros são obrigatórios, REPLACE(string_de_origem, item_de_pesquisa, [termo_da_sustituição]), se o parâmetro termo_da_sustituição não for definido, o item_de_pesquisa será eliminado da string_de_origem.  Veja exemplo, na imagem a seguir:

Caso o terceiro argumento não fosse determinado:

Observações:

1. Na função SUBSTR o primeiro parâmetro pode ser uma coluna ou caractere literal, o segundo parâmetro indica a posição de início do corte, se não for especificada o valor DEFAULT é 1, o segundo parâmetro é a posição final da string;
2. As funções LPAD e RPAD irão "completar" uma string para um tamanho desejado utilizando um caractere informado. A LPAD preenche com caracteres à esquerda (left), a RPAD preenche com os caracteres a direita (right), no exemplo, informei que o tamanho da string deve ser 10, como então ele acrescenta 9 zeros (à direita ou esquerda) para junto ao 1 completar as 10 posições;
3. Nos exemplos da função TRIM, temos o exemplo do funcionamento DEFAULT , ou seja, eliminando espaços, mas temos também exemplos da sua utilização para eliminação de caracteres específicos, à direita ou à esquerda.

É isso, aí. Espero ter ajudado!!! =)

Utilizando Funções de Uma Única Linha Para Personalizar Saídas

As funções são recursos muito utilizados no SQL, e possuem vários objetivos, os quais são:

• Executar cálculo de dados;
• Modificar itens individuais de dados;
• Manipular saídas de grupos de linhas;
• Formatar datas e números;
• Converter tipos de dados de colunas.

Os tipos de funções são: 

1. Funções de uma única linha;
2. Funções de várias linhas, conhecidas também como Funções de Grupo.

O objetivo deste tópico é esclarecer acerca das Funções de Uma Única Linha, sendo é nela que será focada as informações aqui.

As funções de uma linha trabalham alterando linhas isoladas e retornam um resultado por linha, existem muitos tipos, que são:

• Caractere: aceitam a entrada de caractere e podem retornar valores numéricos ou de caractere;
• Número: aceitam entrada numérica e retornam valores numéricos;
• Data: operam em valores do tipo DATE, todas retornam um valor do tipo DATE, com exceção da função MONTHS_BETWEEN, que retorna um número, que é a quantidade de meses dentro de um determinado período.
• Conversão: converte um tipo de dados em outro, como por exemplo: TO_DATE, TO_CHAR, TO_NUMBER;
• Geral: Exemplo desse tipo de função são as funções: NVL, NVL2, NULLIF, COALESCE, CASE, DECODE.

Para cada um tipo de função de uma única linha, existem vários exemplos, cada tipo será explorado posteriormente, em um  tópico próprio a cada um.