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;
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;
EXEMPLODE 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;
Guia de estudos para certificação ORACLE SQL(1Z0-047, 1Z0-051, 1Z0-061 e 1Z0-071), PL/SQL(1Z0-144, 1Z0-146 e 1Z0-148) e Administração OCA e OCP(1Z0-062,1Z0-172,1Z0-063,1Z0-083)