Banco de Dados II – Aula 04

FUNÇÕES DE CONVERSÃO

  • Existem 2 tipos de funções de conversão: implícitas e explícitas;
  • Implícitas o ORACLE converte para o tipo apropriado;
  • Explicitas o usuário faz a utilização manualmente;
  • Sempre que possível, utilizar conversões explícitas;

CONVERSÃO IMPLÍCITAS:

  • O ORACLE automaticamente faz:
    • VARCHAR2 ou CHAR para NUMBER;
    • VARCHAR2 ou CHAR para DATE;
    • NUMBER para VARCHAR2;
    • DATE para VARCHAR2;
  • EXEMPLO:… WHERE NUMERO = ‘25’;… WHERE DATANASC = ’10/23/2000’;

conversoesoracle

http://oracledbagirl.blogspot.com.br/2014/11/funcoes-de-conversao.html

TO_CHAR PARA DATE

  • Tanto TO_CHAR quanto TO_DATE, tem praticamente a mesma formatação;
  • Necessário estar limitado entre aspas simples;
  • É CASE SENSITIVE;

FORMATOS DE DADOS MAIS UTILIZADOS

Formato Significado
YYYY Ano completo com 4 dígitos
YEAR Ano por extenso (em inglês)
MM Mês com 2 dígitos em formato numérico
MONTH Nome do mês por extenso (em inglês)
MON Mês abreviado com 2 dígitos (em inglês)
DY Dia da semana abreviado por extenso (em inglês)
DAY Dia da semana por extenso (em inglês)
DD Dia com 2 dígitos por extenso (em inglês)

OUTROS FORMATOS DE DATAS

Formato Significado
SCC ou CC Acrescenta o A.C. no século
Anos em datas YYYY ou SYYYY Ano; Acrescenta o A.C.
YYY, YY ou Y Últimos 3, 2 ou 1 dígito do ano
Y.YYY Ano com formatação de ponto
IYYY, IYY ou IY Últimos 3, 2 ou 1 dígito do ano no padrão ISSO
SYEAR ou YEAR Ano por extenso, quando tem o S inclui prefixo A.C.
BC ou AD Indica o ano AC ou DC
B.C. ou A.D. Indica o ano A.C. ou D.C. (com pontos)
Q Trimestre do ano
MM Mês com 2 dígitos

OUTROS FORMATOS DE DATAS

 Formato Significado
MONTH Mês por extenso, com 9 dígitos, preenchidos com espaços em branco
MON Mês com abreviação com 3 dígitos
RM Mês em numeral romano
WW ou W Semana do ano ou mês
DDD, DD ou D Dia do ano, do mês ou da semana
DAY Dia por extenso, com 9 dígitos, preenchidos com espaços em branco
DY Dia com abreviação com 3 dígitos
J Dia juliano, desde 31/12/4713 A.C. (número de dias)

FORMATOS DE HORÁRIO

 Formato Significado
AM ou PM Indica o meridiano
A.M. ou P.M. Indica o meridiano com pontos
HH ou HH12 ou HH24 Hora do dia, hora de 1-12 ou hora de 0 a 24
MI Minutos de 0-59
SS Segundos de 0-59
SSSSS Segundos após meia-noite de 0-86399

OUTROS FORMATOS DE DATAS

 Formato Significado
/ , . Pontuação será produzida no resultado
“of the” String será produzida no resultado
TH Numero ordinal (3TH, 4TH)
SP Número por extenso (FOUR, FIVE)
SPTH ou THSP Número ordinal por extenso (FORURTH, FIVETH)

TO_CHAR PARA DATE

  • O formato de data RR é semelhante ao formato YY, porém é específico para distinguir séculos diferentes;
  • Para o século atual, o ORACLE considera os anos de 00 a 49;
  • Para o século anterior, o ORACLE considera os anos de 50 a 99;
  • Exemplos
SELECT SYSDATE FROM DUAL;

SELECT TO_CHAR(SYSDATE,'mm/dd/yyyy') FROM DUAL;

SELECT TO_CHAR(SYSDATE,'dd/mm/yyyy') FROM DUAL;

SELECT TO_CHAR(SYSDATE,'DAY-MONTH-YEAR') FROM DUAL;

SELECT TO_CHAR(SYSDATE,'mm/dd/yyyy HH24:MI:SS') FROM DUAL;

 

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

SELECT TO_DATE('01/01/97','DD/MM/YY') FROM DUAL;

SELECT TO_DATE('01/01/97','DD/MM/RR') FROM DUAL;

SELECT TO_DATE('01/01/49','DD/MM/RR') FROM DUAL;

SELECT TO_DATE('01/01/50','DD/MM/RR') FROM DUAL;

select trunc(to_date('27-Jul-1987'),'YYYY') FROM dual;

select trunc(to_date('27-Jul-1987'),'RRRR') FROM dual;

TO_CHAR PARA NÚMEROS

  • O tipo de dados é de NUMBER para VARCHAR2;
  • Muito utilizado para concatenação de dados;
  • O ORACLE também faz conversão implícita de VARCHAR2 para NUMBER, quando possível;
  • Exemplos
SELECT FIRST_NAME, SALARY FROM   EMPLOYEES;

SELECT FIRST_NAME, TO_CHAR(SALARY,'L99999D99') AS SALARY FROM   EMPLOYEES;

SELECT TO_CHAR(1234,'099999') FROM DUAL;

SELECT TO_CHAR(-1234,'9999MI') FROM DUAL;

SELECT TO_CHAR(1234,'S9999') FROM DUAL;

SELECT TO_CHAR(-1234,'S9999') FROM DUAL;

TO_CHAR PARA DATE

  • PRINCIPAIS ELEMENTOS
Caracter Significado
9 Representa um número
0 Impõe a exibição de um zero
$ Insere um sinal de dólar
L Usa o símbolo da moeda local
. Imprime uma casa decimal
, Imprime uma virgula como indicador de milhar
D Retorna caracter especial na posição especificada (por padrão é ,)
MI Sinal de – à direita (valores negativos)

ORDEM DE EXECUÇÃO DAS FUNÇÕES

  • A ordem de execução é feita de dentro para fora, quando existe mais de uma função na mesma expressão;
  • Exemplo
SELECT INITCAP( CONCAT( SUBSTR( UPPER(FIRST_NAME),1,3),'_ORACLE') 
FROM EMPLOYEES

FUNÇÕES COM VALORES NULOS

  • Existem funções que podem fazer o tratamento de campos com valores nulos;
  • O valor NULO não é espaço em branco ou zeros, portanto, precisam ter funções especiais para que sem manipulados corretamente;

Comando NVL

  • Faz a conversão de um valor nulo em outro valor;
  • Sintaxe:
    • NVL(expr1,expr2)
  • Exemplo
SELECT first_name, 
       NVL(commission_pct,0), 
       commission_pct + 12 as "COMISSAO ANUAL" 
FROM   EMPLOYEES;

Comando NVL2

  • Compara se a expressão 1 não for nula, retorna a expressão 2; se a expressão 1 for nula, retorna a expressão 3;
  • Sintaxe:
    • NVL2(expr1,expr2, expr3)
  • Exemplo
SELECT first_name, 
       salary, 
       commission_pct, 
       NVL2(commission_pct, 'SALARIO / COMISSAO','SALARIO') 
FROM   employees;

Comando NULLIF

  • Compara se as duas expressões tem valores iguais. Caso sim retorna o valor NULL, e caso não, retorna o valor da expressão 1;
  • Sintaxe:
    • NULLIF(expr1,expr2)
  • Exemplo
SELECT first_name,
       last_name,
       LENGTH(first_name),   
       LENGTH(last_name),   
       NULLIF(LENGTH(first_name),        
       LENGTH(last_name))
FROM   employees;

Comando COALESCE

  • Retorna o primeiro valor NÃO NULO de uma lista de expressões;
  • Sintaxe:
    • COALESCE(expr1,expr2,expr3,… exprN);
  • Exemplo
SELECT last_name,
       manager_id,
       commission_pct,
       COALESCE(manager_id,
                commission_pct,
                -1)
FROM   employees
ORDER BY commission_pct DESC;

EXPRESSÕES CONDICIONAIS

  • Permitem utilizar IF.. THEN.. ELSE nas expressões SQL;
  • Podemos utilizar o CASE (padrão ANSI) e o DECODE (exclusivo do ORACLE)

Comando CASE

  • Praticamente tem a mesma função de expressões condicionais nas demais linguagens de programação (IF … THEN…ELSE)
  • Sintaxe do Comando CASECASE expr1WHEN comp_expr1 THEN retorno_expr1
    WHEN comp_expr2 THEN retorno_expr2
    WHEN comp_expr3 THEN retorno_expr3
    ELSE retorno_else
    END;
    
  • Exemplo de CASE
SELECT last_name, job_id, salary,
       CASE job_id
         WHEN 'IT_PROG' THEN salary * 1.20
         WHEN 'ST_CLERCK' THEN salary * 1.30
         WHEN 'SA_REP' THEN salary * 1.50
         ELSE salary
       END "NOVO SALARIO"
FROM   employees;

Comando DECODE

  • É uma versão simplificada do CASE (em termos de parâmetros), tendo a mesma finalidade;
  • Sintaxe: DECODE (expr1, busca1, resultado1, busca2, resultado2, …, retorno_default);
  • Exemplo
SELECT last_name, job_id, salary,
       DECODE (job_id, 'IT_PROG', salary * 1.20,
       'ST_CLERCK', salary * 1.30,
       'SA_REP', salary * 1.50,
       salary) "NOVO SALARIO"
FROM   employees;

FUNÇÕES DE GRUPO

  • Geralmente são utilizados para obter resultados sumarizados (resultados agrupados);
  • Pode ser aplicado na tabela inteira, ou em um determinado grupo;
  • Utiliza-se a função de grupo na sentença SELECT;
  • Na cláusula GROUP BY é onde será a determinação de qual dado será agrupado;

COMANDO AVG

  • Retorna a média aritmética de um determinado campo dividido pelo número de linhas de retorno da tabela;
  • Sintaxe: AVG([DISTINCT | ALL] n)
  • EXEMPLO DE COMANDO AVG
SELECT  AVG(SALARY)
FROM    EMPLOYEES;

SELECT  AVG(COMMISSION_PCT)
FROM    EMPLOYEES;

SELECT  AVG(NVL(COMMISSION_PCT,0))
FROM    EMPLOYEES;

COMANDO COUNT

  • Retorna o número de linhas ou quantidade de campos não nulos de uma tabela;
  • Sintaxe: COUNT(* | DISTINCT | ALL expr)
  • EXEMPLO DE COMANDO COUNT
SELECT  COUNT(*)
FROM    EMPLOYEES;

SELECT  COUNT(COMMISSION_PCT)
FROM    EMPLOYEES;

SELECT COUNT(DISTINCT JOB_ID)
FROM   EMPLOYEES;

COMANDO MAX

  • Retorna o valor máximo da expressão, ignorando os valores nulos;
  • Sintaxe: MAX([DISTINCT | ALL] expr)
  • EXEMPLO COMANDO MAX
SELECT    MIN(START_DATE),   MAX(START_DATE)
FROM      JOB_HISTORY;

COMANDO MIN

  • Retorna o valor mínimo da expressão, ignorando os valores nulos;
  • Sintaxe: MIN([DISTINCT | ALL] expr)
  • EXEMPLO COMANDO MIN E MAX
SELECT    MIN(FIRST_NAME),   MAX(FIRST_NAME)
FROM      EMPLOYEES;

COMANDO SUM

  • Retorna a soma de valores de uma expressão, ignorando os valores nulos;
  • Sintaxe: SUM([DISTINCT | ALL] expr)
  • EXEMPLO DE COMANDO SUM
SELECT  SUM(SALARY)
FROM      EMPLOYEES;

COMANDO STDDEV

  • Retorna o desvio padrão, ignorando os valores nulos;
  • Sintaxe: STDDEV([DISTINCT | ALL] expr)

COMANDO VARIANCE

  • Retorna a variação, ignorando os valores nulos;
  • Sintaxe: VARIANCE([DISTINCT | ALL] expr)

FUNÇÕES DE GRUPO COM AGRUPAMENTO

  • O agrupamento irá fazer com que dados sejam agrupados e geralmente terá uma função de grupo associada;
  • A cláusula GROUP BY precisa estar na sentença do comando SQL, contendo todos os campos que estão no SELECT, menos as próprias funções de grupo;
  • O agrupamento deve ser aplicado a dados que tenham repetição, ou que se consiga fazer operações matemáticas;
  • Não há muito sentido em fazer agrupamentos, por exemplo em campos que são chave primária, pois o conteúdo nunca se repete;
  • Os resultados retornarão uma ou mais linhas, com resultados sumarizados;
  • Não é possível utilizar apelidos das colunas na cláusula GROUP BY.
  • EXEMPLOS DE GROUP BY
SELECT    JOB_ID, COUNT(*)
FROM      EMPLOYEES
GROUP BY  JOB_ID
ORDER BY  COUNT(*) DESC;

SELECT    DEPARTMENT_ID, SUM(SALARY)
FROM      EMPLOYEES
GROUP BY  DEPARTMENT_ID
ORDER BY  DEPARTMENT_ID NULLS FIRST;

SELECT    DEPARTMENT_ID, AVG(SALARY)
FROM      EMPLOYEES
GROUP BY  DEPARTMENT_ID

SELECT    DEPARTMENT_ID, MAX(SALARY)
FROM      EMPLOYEES
GROUP BY  DEPARTMENT_ID;

SELECT    DEPARTMENT_ID, MIN(SALARY)
FROM      EMPLOYEES
GROUP BY  DEPARTMENT_ID;

FUNÇÕES DE GRUPO COM HAVING

  • A cláusula HAVING sempre será utilizada com GROUP BY;
  • Ela serve para fazer limitação de linhas, a qual é aplicada no GROUP BY;
  • Dê preferencia para fazer limitações de linhas sempre que possível na cláusula WHERE;
  • A sequencia que o ORACLE executa um comando SQL quando se tem um HAVING:
    • 1 – As linhas agrupadas;
    • 2 – A função de grupo é aplicada;
    • 3 – os grupos filtradas na cláusula HAVING são exibidos;
  • A ordem de função de grupo tem a mesma ordem das outras funções, ou seja, funções mais internas são executadas primeiro;
  • É possível ter no máximo 2 funções de grupos na mesma sentença;
  • EXEMPLO DE HAVING
SELECT    DEPARTMENT_ID, MAX(SALARY)
FROM      EMPLOYEES
GROUP BY  DEPARTMENT_ID
HAVING    MAX(SALARY) > 10000;

SELECT    DEPARTMENT_ID, AVG(SALARY)
FROM      EMPLOYEES
GROUP BY  DEPARTMENT_ID
HAVING    MAX(SALARY) > 10000;

SELECT    JOB_ID, SUM(SALARY)
FROM      EMPLOYEES
GROUP BY  JOB_ID
HAVING    SUM(SALARY) > 10000
ORDER BY  SUM(SALARY);

SELECT  MAX(AVG(SALARY))
FROM   EMPLOYEES
GROUP BY DEPARTMENT_ID;

SELECT  SUM(AVG(SALARY))
FROM   EMPLOYEES
GROUP BY JOB_ID;

 

Vídeo 01

Vídeo 02

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s