Funções de Grupo

Como o nome sugere, as Funções de Grupo atuam sobre conjuntos de linhas trazendo o resultado da consulta por grupo. As Funções de Linha atuavam em cada linha da consulta, enquanto que as Funções de Grupo atuam sobre um conjunto de linhas. As principais Funções de Grupos são:

Com o uso do DISTINCT a função irá considerar apenas dados não duplicados, quando não especificado o default é a forma ALL é que considera todas as linhas, inclusive as linhas duplicadas;

Todas as funções ignoram as linhas com valores NULOS, ou seja, para que essas linhas sejam consideradas no resultado, deverá ser utilizado alguma função que trate NULOS, como NVL, NVL2 ou COALESCE.

AVG e SUM

As funções AVG e SUM só podem ser utilizadas com números, visto que calculam a média e a soma de valores , respectivamente.

MIN e MAX

As funções MIN e MAX podem ser utilizadas para tipos numéricos, de caractere ou de data.

COUNT

A função COUNT pode ser utilizada de três maneiras:

1. COUNT(*): Retorna a quantidade de linhas que atendem a condição do SELECT, se não houver cláusula SELECT, retornará a quantidade de linhas existente na tabela;
2. COUNT(expr): Retorna o número de valores não nulos da expressão|coluna especificada;
3. COUNT(DISTINCT expr): Retorna o número de valores não nulos exclusivos da expressão|coluna especificada;

Cláusula GROUP BY

O uso da cláusula GROUP BY permite que seja feito agrupamento do resultado por um grupo de colunas especificado. 

Se você utilizar uma Função de Grupo e também listar uma outra coluna na consulta SELECT, será obrigatório o uso da cláusula GROUP BY.

Não é possível utilizar na cláusula GROUP BY apelidos de colunas, como ocorre na ORDER BY.

Exemplos de utilização das Funções de Grupo

Exemplo 1:

O exemplo a seguir abrange as funções AVG, MIN, MAX. Digamos que uma escola armazene a média final anual de cada aluno em uma tabela, você precisa fazer um relatório informando a menor, a maior, a média geral das notas, independente da série.

Para efeito de visualização segue uma consulta mostrando os dados existentes nas tabelas, são 19 alunos cadastrados, segue abaixo uma imagem da consulta feita sem a utilização das funções de grupo, mostrando a nota final de cada um (clique na imagem para ampliar):

Agora vamos fazer o relatório da conforme solicitado, deve exibir a média das notas, a maior nota e a menor nota, apenas filtrando pelo ano letivo 2014. Para essa consulta, não será necessário fazer o join entre tabelas, tudo o que nós precisamos exibir está na tabela NOTAS_FINAIS. A consulta seria:

É necessário atentar para a função AVG, nenhum dos alunos cadastrados possuía a NOTA_FINAL null, sendo assim o calculo está correto. Caso existisse algum aluno sem a nota final, a função AVG desconsideraria esse registro, o que traria uma inconsistência no resultado, nesse caso você precisa usar as funções NVL, NVL2 ou COALESCE para tratar os valores nulos, como já mencionado anteriormente. No exemplo, eu não usei a função SUM, mas enfim, ela soma os valores, sendo necessário ficar atentos aos valores nulos, que também seriam ignorados.

Exemplo 2:

Agora você precisará agrupar os resultados por turma, nesse caso precisaremos unir as tabelas, fazendo um JOIN, pois a tabela NOTAS_FINAIS se relaciona com a tabela ALUNO, que por sua vez possui o COD_TURMA, que é o código de cada turma. 

Primeiro, agrupando pelo código da turma, mas sem exibir o código no relatório. Você pode agrupar por uma coluna sem que esta esteja listada no SELECT. Veja:

Depois, a mesma consulta, com a diferença que eu estou exibindo o código das turmas, no primeiro exemplo a cláusula GROUP BY não é obrigatória, já neste exemplo ela é obrigatória, todas as colunas que são citadas no SELECT que NÃO estejam em uma função de GRUPO devem estar obrigatoriamente no GROUP BY, veja a imagem:

Observação: Lembrando que as funções MIN e MAX podem ser utilizadas também com tipos de dados VARCHAR2, CHAR ou DATE. Para os tipos de dados caractere, será considerada a ordem alfabética, no caso de datas será considerado o fator cronológico.

Exemplo 3:

Agora um exemplo utilizando a função COUNT. Como vimos anteriormente, esta função pode ser utilizada em três formas, será feito um exemplo para cada forma:

• COUNT(*)

• COUNT(expr)

Observação: Caso algum dos registros estivesse com o campo ANO_LETIVO nulo, essa linha não seria contabilizada nesse resultado, pois só conta os registros não nulos.

• COUNT(DISTINCT expr)

Observação: Nós sabemos que existem 19 registros nessa tabela, no entanto todos os registros de ANO_LETIVO são preenchidos com o valor 2014, nesse exemplo utilizamos a palavra-chave DISTINCT, o resultado é referente aos valores não nulos e EXCLUSIVOS na coluna ANO_LETIVO, sendo assim temos apenas 1 linha com valor exclusivo.

Cláusula HAVING

A cláusula HAVING é utilizada para restringir a consulta a partir de uma Função de Grupo, não é permitido usar a cláusula WHERE para essas restrições. No Exemplo 2 acima, exibimos a média, a menor e a maior nota final agrupadas por turmas. Digamos que só interesse saber a turma que teve resultados médios ruins, abaixo da média, ou seja < 7,0. Nesse caso, seria necessário filtrar a consulta pela Função de Grupo AVG(NOTA_FINAL) < 7.0, esse filtro é feito utilizando a cláusula HAVING. Seguem a seguir dois exemplos, um com erro e outro com a sintaxe correta:

Com erro:

Observação: Como podemos ver, o erro ORA-00934 diz que a função de grupo está sendo usada de forma não permitida.

Sintaxe correta:

Observação: Como podemos ver, a cláusula HAVING está sendo usada para restringir os resultados onde a média foi abaixo de 7.0.

Por hoje, é só! Obrigada pela visita. =)

Expressões Condicionais

Várias vezes você precisa utilizar a lógica IF-THEN-ELSE em uma consulta, por questões de regra de negócio por exemplo. A expressão CASE e a função DECODE resolvem essa necessidade, sendo a função DECODE exclusiva da sintaxe Oracle,  a expressão CASE está de acordo com o padrão ANSI SQL.

CASE

Sintaxe:

Quando você utiliza a expressão CASE o servidor verifica as condições, na ordem, a primeira expressão verdadeira é a que terá o valor da cláusula THEN retornado, caso nenhuma condição seja verdade e existir um valor default definido na cláusula ELSE esse valor será retornado, caso nenhuma condição seja verdadeira e não exista valor default definido o valor null é retornado.

Veja o exemplo abaixo, digamos que o departamento pessoal solicita uma consulta onde você deverá exibir em que nível de carreira um funcionário se enquadra de acordo com o salário dele, a regra é:

Nível de carreira Júnior para salários até 3.000,00R$, Pleno para salários entre 3.000,01R$ à 5.000,00R$, Sênior para salários entre 5.000,01R$ à 7.000,00R$, Gerente para salários entre 7.000,01R$ à 10.000,00R$, caso nenhuma das condições fossem verdadeiras o nível de carreira em questão é o de Diretor. A regra foi aplicada na consulta da seguinte forma:

DECODE

Sintaxe:

A função DECODE também implementa a lógica IF-THEN-ELSE, o valor da coluna ou expressão passada no primeiro parâmetro será comparada ao search1, se for igual o result1 será retornado, assim será para todos os outros pares de parâmetros listados na função, você também pode definir um valor default, que será retornado se a comparação não satisfazer nenhum critério de busca listado nos parâmetros, se não existir valor default definido será retornado null. A diferença com a expressão CASE é que no DECODE você não consegue fazer comparações usando testes lógicos diferente do da igualdade, como no exemplo dado no CASE, onde um teste lógico diferente do teste de igualdade é feito na cláusula WHEN (WHEN SALARIO BETWEEN 3000.01 AND 5000 THEN...), é feito um teste considerando um conjunto de valores.

No exemplo abaixo, o caso seria nomear o departamento de acordo com o ID, existe a tabela DEPARTAMENTOS que contém a descrição de cada departamento, mas suponha que você queira uma descrição diferente da "oficial":

Para DEPARTAMENTO_ID igual a 3, teremos o a descrição "MANDA-CHUVA", para DEPARTAMENTO_ID igual a 5, teremos a descrição "PEÃO" e os demais casos serão listados como "NOT FOUND".

É isso aí. Espero ter ajudado! =)

Funções Gerais

As Funções Gerais são funções de linha que você pode utilizar com qualquer tipo de dados. As mais utilizadas são:

• NVL(entrada, saída_se_null);
• NVL2(entrada, saída_se_não_null, saída_se_null);
• NULLIF(entrada_1, entrada_2);
• COALESCE(entrada_1, entrada_2, ..., entrada_n).

DEFINIÇÕES

A função NVL(entrada, saída_se_null) recebe o parâmetro de entrada e caso o seu valor seja null ela retorna saída_se_null, que pode ser um valor literal ou uma expressão e até mesmo uma outra coluna. Essa função é muito importante ao realizar operações aritméticas entre colunas, pois se um número é aritmeticamente manipulado com valores nulos o resultado será um null e não um número. Caso a coluna ou variável ou expressão utilizada na operação não garanta que os valores serão todos não-nulos, você pode utilizar a função NVL para definir qual valor deverá ser considerado ao invés de null. O tipo retornado da função NVL será sempre o mesmo tipo da entrada.

A função NVL2(entrada, saída_se_não_null, saída_se_null) recebe o parâmetro entrada e realiza o tratamento, caso ele não seja null ou not null, ao contrário da NVL, que só trata os valores null. Se o valor de entrada for não-nulo, será retornado a expressão, valor ou coluna especificada em saída_se_não_null, caso contrário (sendo nulo) o valor que será retornado será a expressão, valor ou coluna especificada em saída_se_null.

A função NULLIF(entrada_1, entrada_2) compara os parâmetros entrada_1 e entrada_2 referente a sua igualdade, se esses valores forem igual a função retorna null, caso contrário entrada_1 será retornado, lembrando que avalia a igualdade também de strings de caractere, não apenas números.

A função COALESCE(entrada_1, entrada_2, ..., entrada_n) retorna o valor do primeiro parâmetro não-nulo, todas as entradas são do mesmo tipo de dados.

EXEMPLOS

O primeiro exemplo é a respeito da utilização das funções NVL e NVL2 (clique na imagem para ampliar):

Considerações:

• Você pode observar que a utilização do NVL na coluna ENDERECO fez com que ao invés da consulta exibir null nos endereços não cadastrados, exibisse a string 'FUNC SEM ENDERECO CADASTRADO', coluna que chamei de END_NVL;
• A NVL2 foi utilizada para classificar se o endereço cadastrado está ou não completo (null ou not null respectivamente);
• Coloquei um exemplo do tipo de erro que pode ocorrer ao se trabalhar com valores nulos em expressões aritméticas, na coluna que nomeei de SAL_PLUS_COMISSAO, nós temos o salário do funcionário somado ao valor real da sua comissão, a coluna VAL_COMISSAO_PERC contém o valor da porcentagem da comissão do funcionário, sendo que alguns não recebem comissão, estando esse campo null para esses colaboradores. Isso faz com que o resultado do salário do empregado que não possui comissão, somado com a comissão null, seja também null, o que não é verdade, o valor tem que ser o próprio salário sem o "plus" da comissão. A forma certa está na coluna que nomeei de SAL_PLUS_COMISSAO_NVL, que traz o resultado correto para os casos em que o funcionário possui ou não comissão, utilizando o NVL e tratando o percentual de comissão para 0(zero) caso o valor na coluna seja null. Deu para entender? =)

O segundo exemplo é a respeito da utilização das funções COALESCE e NULLIF  (clique na imagem para ampliar):

Considerações:

• A função COALESCE foi utilizada para recuperar o primeiro telefone não nulo, o que eu chamei de TEL_VALIDO;
• A função NULLIF foi usada para comparar os campos TELEFONE_1 e TELEFONE_2, como em nenhum dos casos os campos são iguais, o retorno foi o TELEFONE_1, nesse caso cabe uma observação, sabemos que se os valores forem iguais a NULLIF retornará null, você pode ver que alguns registros retornaram null (linhas 1,2 e 5), mas nesse caso o resultado é null não por TELEFONE_1 e TELEFONE_2 serem iguais, mas sim pelo fato de TELEFONE_1 ser null, nós poderiamos utilizar a função NVL em conjunto com a NULLIF, para garantir que só retornaria null se os valores das duas colunas fossem realmente iguais.

É isso. Espero ter ajudado!

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.

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.