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’;

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