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! =)