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.

Variáveis de Substituição

As Variáveis de Substituição permitem que a consulta se torne bastante dinâmica, para que o Oracle reconheça a sua utilização, basta utilizar o E comercial único (&) ou duplo (&&) junto a uma variável (sem espaço), que será então substituída pelo valor de entrada solicitado ao executar a consulta.

Exemplo na imagem abaixo:

Como é possível ver na imagem, a consulta faz a projeção pelo NOME, DEPARTAMENTO_ID e DESCRICAO, para o funcionário cujo o CPF será definido na execução da consulta. Ao executar-la, o servidor verifica se a variável foi definida na seção (comando DEFINE), caso o valor para essa variável não tenha sido definido o servidor solicita que seja inserido o valor pelo usuário.

O resultado da consulta:

Se a variável em questão se tratar de um valor de data ou caractere, é necessário delimitar o valor de entrada com aspas simples, ou ocorrerá erro. Como no exemplo abaixo:

A maneira correta seria informar o valor delimitado com aspas, ou delimitar a variável com aspas simples diretamente na consulta, ex: AND F.NOME = '&NOME'; 

Abaixo um exemplo usando as aspas ao passar o valor:

O exemplo acima, fez uso do apenas do E comercial simples (&), significa que a cada execução da consulta, será solicitado ao usuário ou aplicação que informe qual o número do CPF que ele deve buscar na consulta.

Caso não seja essa a intenção, é possível usar o E comercial duplo (&&) que solicitará apenas uma vez o valor da variável durante a sessão. Como na imagem abaixo:

Caso deseje-se "limpar" o valor da variável, você pode utilizar o comando UNDEFINE nome_da_variável. No caso acima, poderíamos limpar a variável DEPARTAMENTO_NUM:

UNDEFINE DEPARTAMENTO_NUM;

As variáveis de substituição podem ser usadas para complementar:

• Condições WHERE;
• Cláusulas ORDER BY;
• Expressões de Coluna;
• Nomes de Tabelas;
• Instruções SELECTS inteiras.

IMPORTANTE: Uma variável de substituição pode ser usada em qualquer local na instrução SELECT, exceto como a primeira palavra especificada no prompt de comando.

Os primeiros exemplos do post são referentes ao uso para complementar as condições WHERE. É possível usar as variáveis na cláusula ORDER BY, deixando o relatório dinâmico quanto a ordenação, ou seja, ao executar o usuário poderá especificar por qual coluna desejará ordenar:

ORDER BY &coluna_ou_posição_numérica;

É possível também utilizar as variáveis de substituição para definir que coluna seja exibida de acordo com a necessidade do usuário, por exemplo:

SELECT F.NOME, D.DEPARTAMENTO_ID, D.DESCRICAO, &COLUNA_DINAMICA

FROM   FUNCIONARIOS F, DEPARTAMENTOS D

WHERE  F.DEPARTAMENTO_ID = D.DEPARTAMENTO_ID;

Será solicitado ao usuário qual a coluna que deverá ser exibida por último, podendo o usuário escolher quaisquer coluna que existam nas tabelas mencionadas na cláusula FROM.

Por último segue um exemplo sobre como utilizar variáreis de substituição para construir uma cláusula SELECT inteira:

SELECT &COLUNAS

FROM   &TABELAS

WHERE  &CONDICOES;

Será que funciona? Vamos ver! Abaixo seguem as imagens dos valores inseridos em casa variável:

Observações interessantes sobre a utilização das variáveis cabem aqui, perceba que ao definir as colunas eu usei o alias que eu defini depois ao listar as tabelas, outra observação é que mais de uma coluna foi listada na variável &COLUNAS, assim como na variável &TABELAS, e na condição WHERE a variável foi substituída por uma expressão completa, referente ao relacionamento entre essas tabelas D.DEPARTAMENTO_ID = F.DEPARTAMENTO_ID. Na imagem a seguir, temos o resultado da consulta:

Por hoje é só, espero que o conteúdo possa ajuda-lo. =)

Uso da Cláusula ORDER BY

Uso da Cláusula ORDER BY

A cláusula ORDER BY permite que você exiba determinado resultado de forma ordenada, o modo padrão  de ordenação é ASC (ordem crescente), mas é possível alterar o modo utilizando a palavra-chave DESC para que a ordem de exibição passe a ser decrescente.

A sintaxe do uso da cláusula:

SELECT expressão/colunas

FROM tabela(as)

[WHERE condição(es)]

[ORDER BY {coluna, expressão, posição_numérica} [ASC|DESC]] ;

A cláusula ORDER BY não é obrigatória, caso seja utilizada será sempre a última cláusula a ser inserida na instrução SELECT. Caso a cláusula de ordenação não seja utilizada, a ordem da recuperação das linhas será indefinida, podendo o servidor Oracle não extrair a mesma ordem para consultas idênticas.

É possível ordenar por várias colunas, ficando a ordenação combinada entre as colunas listadas na cláusula ORDER BY, não há limites na utilização de colunas na classificação, o limite é apenas usar colunas das tabelas envolvidas na instrução SELECT. Essa cláusula será muito útil no dia-a-dia do DBA ou desenvolvedor.

Observações importantes acerca da ordenação dos valores no modo DEFAULT (ASC);

1. Valores númericos serão exibidos do menor para o maior (ex: 1, 2, 3, ... , 999);
2. Valores de data serão exibidos em ordem cronológica (ex: 1-jan-92, 2-jan-92, ... , 22-out-14);
3. Valores de caracteres serão exibidos em ordem alfabética (ex: de A a Z);
4. Os valores NULOS (null), serão exibidos por ÚLTIMO em uma sequência CRESCENTE e no INÍCIO se a sequência for DECRESCENTE; Essa ordem pode ser manipulada adicionando a palavra-chave NULLS LAST ou NULLS FIRST à cláusula ORDER BY;
5. Você pode classificar por uma coluna que não esteja incluída na lista do SELECT;
6. Você pode usar o número da posição da coluna, o alias, uma expressão ou a própria coluna para ordenar.

O último ponto listado é bastante importante, mas vale salientar que só podemos ordenar utilizando a numeração da posição para as colunas que estão na lista do SELECT, se possível faça exercícios com a utilização do ORDER BY, que é bastante útil na rotina de quem desenvolve PL/SQL. O quarto ponto é importante e será cobrado no teste da certificação. Lembrando que para alterar a ordem de crescente para decrescente basta acrescentar a palavra-chave  DESC após a coluna que se deseja alterar a ordem para decrescente.

Por hora, é só.

Recursos da Instrução SELECT

Recursos da Instrução SELECT

Boa tarde, vamos ao meu primeiro post: Introdução a instrução SELECT. Antes, quero esclarecer que o objetivo do blog é escrever sobre o que venho estudando, já que esta é a melhor forma de fixar o que aprendemos, escrevendo sobre o tema. E quem sabe, poder talvez ajudar alguém que também iniciou os estudos na área.

O SELECT é uma instrução DML (Data Manipulation Language), é muito utilizada, não apenas para criar consultas ou relatórios, como também é uma ferramenta muito utilizada na criação de objetos PL/SQL. Com essa instrução, é possível criar cursores, validar dados, realizar inserção de dados, criar tabelas com mesmas estruturas com/sem dados, criar views, entre outras utilidades. É importante compreender bem suas ferramentas.

Inicialmente, falaremos dos recursos da instrução SELECT, que são: seleção, projeção e junção.

Seleção: refere-se ao que você deseja selecionar, restringir, na sua consulta. Um exemplo seria, digamos que exista uma tabela de funcionários e você deseja filtrar, selecionar, apenas os funcionários que ganham acima de 2000,00 R$, nesse caso aplica-se uma restrição com o uso da cláusula WHERE, seria algo do tipo:

SELECT *

FROM FUNCIONARIOS

WHERE SALARIO > 2000,00R$;

Todas as colunas seriam exibidas, mas apenas os funcionários com salário acima de 2000,00R$ estariam nas linhas selecionadas.

Projeção: o recurso de projeção refere-se a quais colunas serão projetadas ao executar a consulta, ao usar o * (como no exemplo anterior) todas as colunas serão projetadas ao executarmos a consulta, ou seja, todas as colunas da tabela serão mostradas no resultado, independente do filtro de seleção. Digamos que não haja interesse em que todos os campos sejam exibidos, você pode projetar apenas os campos que interessam:

SELECT MATRICULA, NOME, ENDEREÇO

FROM FUNCIONARIOS;

Perceba que nesse caso, todos os registros serão selecionados, não há filtros na pesquisa. No entanto, apenas a matrícula, nome e endereço serão exibidos, essa é a nossa projeção.

Junção: refere-se a junção de duas ou mais tabelas, digamos que exista a tabela FUNCIONARIOS e nela existe o ID_DEPARTAMENTO de cada funcionário, e que exista a tabela DEPARTAMENTOS com o ID_DEPARTAMENTO  e DESC_DEPARTAMENTO, e você precisa exibir a descrição do departamento, o nome e a matrícula de cada funcionário. Nesse caso, um dos dados que você precisa selecionar está em outra tabela, além de FUNCIONARIOS, você precisa fazer a junção dessas tabelas para selecionar a DESCR_DEPARTAMENTO. Seria:

SELECT F.MATRICULA, F.NOME, D.DESCR_DEPARTAMENTO

FROM FUNCIONARIOS F, DEPARTAMENTOS D

WHERE F.ID_DEPARTAMENTO = D.ID_DEPARTAMENTO;

Esse tipo de junção é extremamente utilizada, visto que por questões normativas, não teremos uma única tabela com todos os campos e tipos de informações possíveis. Para que a junção ocorra corretamente, tem-se que se observar o correto relacionamento entre as entidades, que é feito pelo uso das chaves primárias e estrangeiras, garantindo a integridade das informações.

Por hoje é só, se alguém foi trazido pela força do destino a essa página, pode deixar a sua contribuição nos comentários e volte sempre.