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’;
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
Utilizando CASE com IN no SQL Server
No SQL Server, é possível utilizar o recurso IN dentro de um Case.
Para ilustrar o exemplo, utilizamos o banco de dados Adventure Works 2008, e primeiro podemos observar o uso típico do case:
SELECT ProductID, Name, Color, case Color when 'Red' then 'Cor Primaria' when 'Blue' then 'Cor Primaria' when 'Yellow' then 'Cor Primaria' else 'Cor Secundaria' end as TesteCase FROM Production.Product WHERE Color is not null
Temos o seguinte resultado:
Observamos que para o caso das cores serem Red, Blue e Yellow, os resultados seriam os mesmos.
Podemos fazer a alteração utilizando o comando IN dentro do Case, como o código seguinte:
SELECT ProductID, Name, Color, case WHEN Color in ('Red','Blue','Yellow') then 'Cor Primaria' else 'Cor Secundaria' end as TesteCase FROM Production.Product WHERE Color is not null
Observamos que conseguimos otimizar a escrita do código, não ficando tão repetitivo, tendo o mesmo resultado:
Concatenação de uma única linha com origem de várias linhas retornadas em T-SQL
É possível utilizar o recurso em T-SQL para que várias linhas retornadas em uma select sejam concatenadas em uma única.
O cenário utilizado foi por exemplo, em impressão de etiquetas, que um produto produzido foi consumido x produtos com y quantidades.
Utilizamos no exemplo o Banco de Dados AdventureWorks2008 do SQL Server, a qual pode ser adaptado facilmente para qualquer versão que voce esteja utilizando.
Select P.Name +' - Tkg '+ SOD.CarrierTrackingNumber +' - Qty '+ CONVERT(VARCHAR, SOD.OrderQty) +' - Prc '+ CONVERT(VARCHAR, SOD.UnitPrice) From Sales.SalesOrderDetail SOD inner join Production.Product P on P.ProductID = SOD.ProductID Where SalesOrderID = 43664
O seguinte resultado irá aparecer, sendo um resiltado simples que já estamos acostumados.
O problema é que o gerador de relatório estava interpretando que cada linha do SQL estava sendo colocado um uma folha. Para isso, precisávamos colocar todas as linhas retornadas em uma única (como se fosse uma única string). Para isso, executamos o seguinte comando:
Select STUFF((SELECT DISTINCT ' ' + P.Name +' - Tkg '+ SOD.CarrierTrackingNumber +' - Qty '+ CONVERT(VARCHAR, SOD.OrderQty) +' - Prc '+ CONVERT(VARCHAR, SOD.UnitPrice) + CHAR(10) From Sales.SalesOrderDetail SOD inner join Production.Product P on P.ProductID = SOD.ProductID Where SalesOrderID = 43664 FOR XML PATH('')),1,1,'')
O seguinte resultado foi apresentado:
Precisamos colocar o CHAR(10) para que o gerador de relatório interpresse esse código como <Enter>, colocando assim cada uma das linhas em uma linha separada no relatório (diferente do retorno de várias linhas do SQL).
Dessa forma, fizemos que esse campo retornasse um MEMO para o gerador de relatório.