Banco de Dados II – Aula 03

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

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

w

Conectando a %s

learningdatabase.com.br

Tecnologias em Banco de Dados Relacionais, Modelagem de dados dimencionais, tecnologias SQL Servere e Oracle

Aprendendo Programação

Algorítmos, C, C++,Pascal, Python, R

WikiDBA

by Virendra Yaduvanshi - Microsoft SQL Server Database Architect | Consultant | Blogger | Specialist | DBA | Speaker

Blog - Fabiano Neves Amorim

SELECT * FROM [Coisas Da Minha Cabeça] WHERE dbo.fn_TempoParaPost() < dbo.fn_TempoLivre()

ROMANO DBA

Administração de Bancos de Dados

Tércio Costa, Oracle Developer, OCE SQL, ACE Associate

Guia de estudos para certificação ORACLE SQL(1Z0-047, 1Z0-051, 1Z0-061 e 1Z0-071) e PL/SQL(1Z0-144, 1Z0-146 e 1Z0-148)

Strate SQL

Data Adventures with an Architect

%d blogueiros gostam disto: