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
