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

Deixe um comentário Cancelar resposta

Digite seu comentário aqui...

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

Email (obrigatório) (Nunca tornar endereço público)

Nome (obrigatório)

Site

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

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

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

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

Cancelar

Conectando a %s

learningdatabase.com.br

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

Banco de Dados II – Aula 03

VARIÁVEIS DE SUBSTITUIÇÃO

FUNÇÕES DE LINHAS

FUNÇÕES DE LINHAS – CARACTERES

FUNÇÕES DE NÚMEROS

ROUND

TRUNC

MOD

FUNÇÕES DE DATA

SYSDATE

CALCULOS DE DIAS COM DATA

OUTRAS FUNÇÕES DE DATA

MONTHS_BETWEEN(data1,data2)

ADD_MONTHS(data1,n)

NEXT_DAY (data1,numeric)

LAST_DAY (data1)

ROUND (data1[,valor])

TRUNC (data1[,valor])

Deixe um comentário Cancelar resposta

Banco de Dados II – Aula 03

VARIÁVEIS DE SUBSTITUIÇÃO

FUNÇÕES DE LINHAS

FUNÇÕES DE LINHAS – CARACTERES

FUNÇÕES DE NÚMEROS

ROUND

TRUNC

MOD

FUNÇÕES DE DATA

SYSDATE

CALCULOS DE DIAS COM DATA

OUTRAS FUNÇÕES DE DATA

MONTHS_BETWEEN(data1,data2)

ADD_MONTHS(data1,n)

NEXT_DAY (data1,numeric)

LAST_DAY (data1)

ROUND (data1[,valor])

TRUNC (data1[,valor])

Compartilhe isso:

Curtir isso:

Relacionado

Deixe um comentário Cancelar resposta