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