VARIÁVEIS DE SUBSTITUIÇÃO
- Geralmente utilizadas para executar uma instrução SELECT mais de uma vez;
- Servirá como se fosse parâmetro;
- Somente utilizadas na cláusula WHERE, com o símbolo : (para apex);
- O símbolo & para sqlplus, pode ser utilizado em mais cláusulas;
- As variáveis de substituição servem para guardar temporariamente valores;
- EXEMPLOS
Exemplo 01 SELECT * FROM employees WHERE employee_id = :employee_id; Exemplo 02 SELECT * FROM employees WHERE first_name = :first_name AND salary >= :salary; Exemplo 03 SELECT * FROM employees WHERE last_name like '&last_name'; SELECT * FROM &tabela (no SQLDeveloper, pressione + , e ele pedirá o valor a ser digitado)
FUNÇÕES DE LINHAS
- Podem ser utilizadas para tipos de dados character, data, números em uma instrução SELECT;
- Funcionam como conversão, formatação de dados (datas e números), manipulam strings, podem ter ou não dados de entrada (ou parâmetros), mas sempre possuem dados de saída;
- As maioria das funções apresentadas aqui são do Oracle;
- Possuem 2 tipos:
- por Linha: só retornam um resultado por linha (funções de character, número, data e conversão);
- de Grupo: manipulam grupos de linhas para retornar apenas um resultado;
- Caracteristicas das functions de linha;
- Podem receber argumentos (podendo ser uma constant, variável, nome de coluna, expressão);
- Podem ser usados na cláusulas SELECT, WHERE, ORDER BY;
- Podem ser encadeadas;
- Funções de data: entradas do tipo DATE e retornam um tipo DATE, exceto MONTHS_BETWEEN, que retorna um tipo número;
- Funções numéricas: entradas do tipo NUMERIC e retornam um tipo NUMERIC;
- Funções de character: entrada do tipo caracteres e retornam caracteres ou numeros;
- Funções de conversão: convertem um tipo de dado em outro;
- Outras funções: NVL, NVL2, NULLIF, COALESCE, CASE, DECODE;
FUNÇÕES DE LINHAS – CARACTERES
- Manipulação de caixa alta e baixa (maiúsculo e minúsculo):
- UPPER: retornar todos os valores em letras maiúsculas;
- LOWER: retornar todos os valores em letras minúsculas;
- INITCAP: a primeira letra de cada palavra em maiúscula e o restante minúsculas;
- Manipulação de caracter:
- CONCAT: Une dois valores;
- SUBSTR: Extrai uma string de determinado tamanho;
- LENGTH: Retorna o tamanho de uma string em um valor numérico;
- INSTR: Localiza uma posição numérica de um character nomeado;
- LPAD: Preenche com valor do character à esquerda;
- RPAD: Preenche com valor do character à direita;
- TRIM: Retira caracteres em branco à direita e à esquerda de uma string;
- Exemplos
SELECT UPPER('rodrigo saito') FROM DUAL; SELECT LOWER('RODRIGO SAITO') FROM DUAL; SELECT INITCAP('rodrigo saito') FROM DUAL; SELECT CONCAT('Rodrigo','Saito') FROM DUAL; SELECT SUBSTR('Teste de caracteres no Oracle',1,5) FROM DUAL; SELECT SUBSTR('Teste de caracteres no Oracle',6) FROM DUAL; SELECT LENGTH('Teste de caracteres no Oracle') FROM DUAL; SELECT INSTR('Teste de caracteres no Oracle','s') FROM DUAL; SELECT LPAD('Teste de caracteres no Oracle',40, '0') FROM DUAL; SELECT RPAD('Teste de caracteres no Oracle',40, '0') FROM DUAL; SELECT REPLACE('rodrigo anchieta', 'anchieta', 'saito') FROM DUAL; SELECT TRIM('R' FROM 'RODRIGO') FROM DUAL;
FUNÇÕES DE NÚMEROS
- Tem como entrada dados do tipo numérico e sempre retornam dados do tipo numérico;
- ROUND: arredonda o valor para cima (caso o decimal seja maior que 5);
- TRUNC: retorna o valor a ser truncado;
- MOD: retorna o resto da divisão;
ROUND
- SINTAXE: ROUND(VALOR,N): N é opcional, sendo o número de casas decimais a ser definido;
- Exemplos
SELECT ROUND(10.5) FROM DUAL; SELECT ROUND(10.5,1) FROM DUAL; SELECT ROUND(10.51,1) FROM DUAL; SELECT ROUND(10.55,1) FROM DUAL; SELECT ROUND(10.60,1) FROM DUAL; SELECT ROUND(10.554,2) FROM DUAL; SELECT ROUND(10.555,2) FROM DUAL;
TRUNC
- SINTAXE: TRUNC(VALOR,N): N é opcional, sendo o número de casas decimais a ser definido. O comando basicamente irá cortar o número definido;
- Exemplos
SELECT TRUNC(10.1) FROM DUAL; SELECT TRUNC(10.1,1) FROM DUAL; SELECT TRUNC(10.15,1) FROM DUAL; SELECT TRUNC(10.155,2) FROM DUAL; SELECT TRUNC(10.155,-1) FROM DUAL; SELECT TRUNC(10.155,-2) FROM DUAL; SELECT TRUNC(100.155,-2) FROM DUAL;
MOD
- SINTAXE: MOD(M,N): M PRIMEIRO VALOR, E N é o segundo valor. O comando retornar o resto de M por N.
- Exemplos
SELECT MOD(10,2) FROM DUAL; SELECT MOD(10,3) FROM DUAL; SELECT MOD(11,3) FROM DUAL; SELECT MOD(19,5) FROM DUAL;
FUNÇÕES DE DATA
- Datas são armazenadas internamente em formato numérico, tendo a representação de século, ano, mês, dia, hora, minuto e segundos;
- Padrão do Oracle é MM/DD/YYYY;
- Data válidas são 01/01/4712 A.C. a 31/12/9999 D.C.;
- Na inserção do dado do tipo data em um campo, internamente o Oracle armazena o século da função SYSDATE, nas para exibição esse dado é ocultado por padrão;
- Date armazena internamente 4 dígitos para o ano (dois dígitos para século e dois dígitos para ano);
- Apesar da exibição ser de 2 dígitos para o ano, internamente são 4 dígitos;
- De 1951 a 2050 mostra os 2 últimos dígitos do século atual;
SYSDATE
- Retorna a data e horário do século atual, podendo ser usado em qualquer coluna.
- É possível utilizar na tabela de sistema DUAL;\
- Exemplo
select SYSDATE from DUAL (será exibido por padrão somente a data) SELECT SYSDATE + 5 FROM DUAL; SELECT SYSDATE - 5 FROM DUAL; SELECT SYSDATE - (SYSDATE - 10) FROM DUAL; SELECT SYSDATE - TO_DATE('01/01/2015','mm/dd/yyyy') FROM DUAL; select TO_CHAR(sysdate,'mm/dd/yyyy hh:mi') from dual; SELECT TO_CHAR(SYSDATE,'dd/mm/yyyy hh24:mi') FROM DUAL; SELECT TO_CHAR(TO_DATE('01/01/2016 09:01','dd/mm/yyyy hh24:mi'),'dd/mm/yyyy hh24:mi') FROM DUAL; SELECT TO_CHAR(TO_DATE('01/01/2016 09:01','dd/mm/yyyy hh24:mi') + 1/24,'dd/mm/yyyy hh24:mi') FROM DUAL; SELECT TO_CHAR(TO_DATE('01/01/2016 09:01','dd/mm/yyyy hh24:mi') + 3/24,'dd/mm/yyyy hh24:mi') FROM DUAL; SELECT TO_CHAR(TO_DATE('01/01/2016 09:01','dd/mm/yyyy hh24:mi') + 1/(24*60),'dd/mm/yyyy hh24:mi') FROM DUAL; SELECT TO_CHAR(TO_DATE('01/01/2016 09:00:00','dd/mm/yyyy hh24:mi:ss') + 20/(24*60*60),'dd/mm/yyyy hh24:mi:ss') FROM DUAL; SELECT TO_CHAR(SYSDATE + 20/(24*60*60),'dd/mm/yyyy hh24:mi:ss') FROM DUAL; SELECT TO_CHAR(SYSDATE,'dd/mm/yyyy hh24:mi') FROM DUAL;
CALCULOS DE DIAS COM DATA
- É possível fazer adição e subtração de dias em datas, além de fazer a diferença de dias entre datas, considerando os dias, meses e anos;
- Para se fazer operações com horas, é necessário dividir um dia pela quantidade de horas (ou seja, 24);
- Para se fazer operações com minutos, é necessário saber o quanto 1 minuto representa em 1 dia (1440);
OUTRAS FUNÇÕES DE DATA
- Tem como entrada de parâmetros do tipo data e retornam valores do tipo data ou numérico;
MONTHS_BETWEEN(data1,data2)
- Retorna o número de meses entre as datas do parâmetro;
- Exemplos
SELECT MONTHS_BETWEEN( TO_DATE('01/01/2016'), TO_DATE('01/01/2015')) FROM DUAL; SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('01/01/2015')) FROM DUAL;
ADD_MONTHS(data1,n)
- Retorna uma data, adicionando a quantidade de meses especificadas no parâmetro em data1;
- Exemplos
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL; SELECT SYSDATE + 90 FROM DUAL;
NEXT_DAY (data1,numeric)
- Retorna o próximo dia da semana, sendo representando pelos números de 1 a 7 (1 é domingo a 7 que é sábado);
- Exemplos
SELECT NEXT_DAY(SYSDATE,1) FROM DUAL; SELECT NEXT_DAY(SYSDATE,4) FROM DUAL; SELECT NEXT_DAY(SYSDATE,-3) FROM DUAL;
LAST_DAY (data1)
- Retorna o último dia do mês especificado;
SELECT LAST_DAY(SYSDATE) FROM DUAL; SELECT LAST_DAY(TO_DATE('05/04/2015')) FROM DUAL;
ROUND (data1[,valor])
- Retorna uma data arredondada, sendo o parâmetro valor opcional, e o padrão equivalente ao parâmetro ‘day’;
- Exemplos
SELECT ROUND(SYSDATE) FROM DUAL; SELECT ROUND(SYSDATE,'DAY') FROM DUAL; SELECT ROUND(SYSDATE,'MONTH') FROM DUAL; SELECT ROUND(SYSDATE,'YEAR') FROM DUAL; SELECT ROUND(TO_DATE('01/15/2016 11:59:00','mm/dd/yyyy hh24:mi:ss')) FROM DUAL; SELECT ROUND(TO_DATE('01/15/2016 12:01:00','mm/dd/yyyy hh24:mi:ss')) FROM DUAL; SELECT ROUND( TO_DATE('01/15/2016','mm/dd/yyyy'),'MONTH') FROM DUAL; SELECT ROUND( TO_DATE('01/16/2016','mm/dd/yyyy'),'MONTH') FROM DUAL; SELECT ROUND(TO_DATE('06/30/2016','mm/dd/yyyy'),'YEAR') FROM DUAL; SELECT ROUND(TO_DATE('07/01/2016','mm/dd/yyyy'),'YEAR') FROM DUAL;
TRUNC (data1[,valor])
- Retorna uma data truncada (cortada), sendo o parâmetro valor opcional;
- Exemplos
SELECT TRUNC(SYSDATE) FROM DUAL; SELECT TRUNC(SYSDATE,'DAY') FROM DUAL; SELECT TRUNC(SYSDATE,'MONTH') FROM DUAL; SELECT TRUNC(SYSDATE,'YEAR') FROM DUAL; SELECT TRUNC( TO_DATE('07/15/2016', 'mm/dd/yyyy')) FROM DUAL; SELECT TRUNC( TO_DATE('07/15/2016', 'mm/dd/yyyy'),'MONTH') FROM DUAL; SELECT TRUNC(TO_DATE('07/15/2016', 'mm/dd/yyyy'),'YEAR') FROM DUAL;
Vídeo 1
Vídeo 2