Dentro de funções de linha temos funções para tratar caractere, números e data. Vejamos exemplos:
Funções de Caractere
LOWER / UPPER / INITCAP
LOWER – Todas as letras em minúscula.
UPPER – Todas as letras em maiúscula.
INITCAP – Primeiras letras em maiúscula, após um espaço ou aspas simples também virá em maiúsculo, o restante todas as letras virão em minúscula.
LOWER(S1)
UPPER(S1)
INITCAP(S1)
S1 é um parâmetro obrigatório. Exemplo de uso:
SELECT LOWER('TESTE') FROM DUAL;
Resultado: teste
INITCAP: Letra inicial maiúscula, depois da aspas simples irá colocar também em maiúscula, veja exemplos abaixo:
INITCAP('RED O''BRIEN') = Red O’BrienINITCAP ('McDonald''s') = Mcdonald’S
CONCAT(S1,S2)
Os dois parâmetros S1 e S2 são obrigatórios.
CONCAT só aceita dois parâmetros. Veja um exemplo:CONCAT('AB','CD') = ABCD
LPAD e RPAD
Objetivo: preencher com algum caractere os espaços vazios correspondente ao tamanho informado.
LPAD(s1, n, s2)
RPAD(s1, n, s2)
Parâmetros:
s1 (caractere string: obrigatório);
n (número: obrigatório);
s2 (caractere string: opcional; s2 se omitido terá valor de um espaço em branco).
Ex: 4 aspas simples para simular uma aspa, o objetivo é criar um tamando de 6 caracteres, o LPAD vai preencher com aspas simples, (pois foi o parâmetro informado abaixo) o tamanho de 6, o nome renan tem 5 caracteres, então o 6º é preenchido com aspas simples, veja o exemplo abaixo:
select lpad('renan',6,'''') from dual
Resultado: 'renan
Vejamos outros exemplos:
select lpad('renan',6,'-') from dual = -renan
Obs: um espaço em branco antes do r é adicionado, no ex. abaixo:
select lpad('renan',6) from dual = renan
select lpad('renan',4,'3') from dual = rena
select lpad('renan',4) from dual = rena
select rpad('renan',4) from dual = rena
TRIM / LTRIM / RTRIM
LTRIM(s1, s2)
RTRIM(s1, s2)
Parâmetros: s1, s2 ambos são caracteres.
s1 é obrigatório, e s2 é opcional se omitido, default será um espaço em branco.
LTRIM('*renan***') = *renan***
LTRIM('*renan***','*') = renan***
RTRIM('*renan***','*') = *renan
RTRIM('RENAN','N') = RENA
TRIM
Sintaxe: TRIM(trim_info trim_char FROM trim_source)
Parâmetros:
trim_info é uma dessas palavras-chaves: LEADING, TRAILING, BOTH
leading: esquerda; trailing: direita; both: ambos (apara esq e dir)
Se trim_info for omitido então o default será: BOTH.
trim_char é um caractere que será aparado, se omitido: espaço vazio
trim_source é o código da string, obrigatório, se informado NULL retornará NULL, se informado ” retornará NULL.
Funciona da mesma forma que LTRIM e RTRIM mas com sintaxe diferente.
ex:trim(trailing '-' from 'abc----') = abc
trim('*' from '*abc*') = abc
trim('*' from 'xyz*') = xyz
trim(leading '1' from '1bob') = bob
trim(both'1' from '1k1') = k
LENGTH(s)
O parâmetro s é obrigatório.
Quando usado com data ex: length(data) o retorno será o número de caracteres do formato padrão.LENGTH(123) = 3
length('abc') = 3
INSTR (s1, s2, pos, n)
s1 é a string que será usada para buscar outro parte de string nela, s1 é obrigatório.
s2 é a substring, é a string que você quer procurar, é obrigatório;
pos é a posição inicial em s1 que iniciará a busca de s2 dentro de s1 (é opcional, o default é 1);
n é a ocorrência de s2 a localizar (opcional, default é 1).
se pos for negativo, a busca em s1 para ocorrência de s2 iniciará a partir do final da string, ex: -1 começa do último caractere inclusivo.
Processo: Localizar uma string dentro de outra (daí o nome da função: INSTRing).
Retorno da função: Númeroinstr('teste','e') = 2
instr('teste','e',3) = 5
instr('teste','e',1,2) = 5
SUBSTR (s, pos, len)
Objetivo: Extrair um pedaço do caractere.
s = string, obrigatório;
pos = número, obrigatório;
len = número, opcional, se omitido considera a substituição do início do valor pos até o último caractere de s.substr('teste',1,1) = t
substr('teste',1) = teste
substr('teste',-3) = ste
substr('teste',-3,-1) = null (porque tam é -1)
substr(SYSDATE,-4) = "C-18" (Data 02-DEC-18 no padrão americano)
substr(12345,-4) = 2345
REPLACE (S1,S2,S3)
S1 e S2 são obrigatórios. S3 é opcional
S1 é a string fonte. a busca será feita a partir da S2 que será localizada em S1.
S3 é a string que será substituída casa haja ocorrência de S2 dentro de S1.
Caso não informe a ocorrência de S2 em S1 será apagada.replace('oracle@gmail.com', '@gmail.com') = oracle
replace('oracle@gmail.com', 'oracle', 'renan') = renan@gmail.com
replace(sysdate, 'DEC', 'JAN') = 02-JAN-18 (sysdate é 02-DEC-18)
SOUNDEX(s)
s = string fonte, obrigatório.
Processo: Traduz uma string para seu código SOUNDEX.
Retorno: string.
Tabela de código soundex:
1 = B, F, P, V
2 = C, G, J, K, Q, S, X, Z
3 = D, T
4 = L
5 = M, N
6 = R
O código é composto na primeira letra da string mais 3 números somente, todas as outras letras restantes são ignoradas:
Exemplo:
soundex(‘worthington’),
soundex(‘worthen’)
Ambos retornarão = W635
soundex(‘renan’) = R55
Funções de Número
CEIL (n)
n é obrigatório e é número.
CEIL arredonda sempre pra cima.CEIL(1) = 1
ceil(1.2) = 2
FLOOR (n)
n é obrigatório e um tipo numérico de data.
Objetivo: FLOOR arredonda um número sempre pra baixo.FLOOR(12.1) = 12
FLOOR(12.9) = 12
ROUND (N,I)
N é obrigatório, I não é obrigatório e se não informado assume default 0.
ROUND arredonda um dado número N em campos se positivo então arredonda referente as casas decimais e se negativo arredonda o número inteiro ex:round(12.355143,2) = 12.36
round(259.99,-1) = 260ROUND(154.99,-1) = 150ROUND(12.4) = 12ROUND(12.5) = 13 o retorno é o tipo de dados: número
se I for 0 ou não informado então irá arredondar o número para inteiro (sem pontos decimais)
TRUNC (N,J)
N é obrigatório e J não é obrigatório, default = 0
trunc tora o número.trunc(12.355143,2) = 12.35
trunc(259.99,-1) = 250
TRUNC(333,-3) = 0
REMAINDER (N1,N2)
Ambos parâmetros são obrigatórios.
Retorna a diferença entre N1 e o múltiplo de N2 mais próximo a N1remainder(9,3) = 0 (neste exemplo o múltiplo de 3 mais perto de 11 é o número 12, sendo assim 11 – 12 = -1
remainder(10,3) = 1
remainder(11,3) = -1
A ideia do remainder é usar o ROUND para aproximar de N1, ex: o múltiplo de 3 são (3,6,9,12) o mais perto de 11 é o 12 fazendo o ROUND.
MOD (N1,N2)
MOD é igual o REMAINDER só que ao invés de ROUND usa FLOOR.MOD(9,3) = 0
MOD(10,3) = 1
MOD(11,3) = 2
ABS (n)
Retorna o valor absoluto de n.
Essa função usa como argumento qualquer tipo de dado numérico ou qualquer tipo de dado não numérico que possa ser implicitamente convertido em um tipo de dado numérico. A função retorna o mesmo tipo de dados que o tipo de dados numérico do argumento.
O exemplo a seguir retorna o valor absoluto de -15:
SELECT ABS(-15) "Absoluto" FROM DUAL;
Absoluto
----------
15
SIGN (n)
SIGN retorna o sinal de n. Essa função usa como argumento qualquer tipo de dado numérico ou qualquer tipo de dado não numérico que possa ser implicitamente convertido em NUMBER e retorna NUMBER.
Para o valor do tipo NUMBER, o sinal é: -1 se n < 0
0 se n = 0
1 se n > 0
SELECT SIGN(-15) "Sinal" FROM DUAL;
Sinal
----------
-1
Para números binários de ponto flutuante (BINARY_FLOAT e BINARY_DOUBLE), essa função retorna o bit de sinal do número. O bit do sinal é:-1 se n <0
+1 se n> = 0 ou n = NaN
Funções de Data
SYSDATE
Objetivo: Mostrar a data atual de acordo com o sistema operacional em que o servidor Oracle está instalado.SYSDATE = 16-JUL-18
Observação: Esse formato acima pode ser alterado, alterando o parâmetro da sessão NLS_DATE_FORMAT.
Saída: Data
ROUND (d,i)
Parâmetros:
d é uma data (obrigatório);
i é um formato (opcional).
Se o segundo parâmetro opcional for omitido, o valor DATE será arredondado para a hora mais próxima.
SELECT to_char(sysdate, 'DD-MON-YY HH:MI:SS') AS RAW_DATE, to_char(round(sysdate),'DD-MON-YY HH:MI:SS') as ROUNDED_DATE FROM DUAL; RAW_DATE = 26-SEP-18 05:35:17 ROUNDED_DATE = 26-SEP-18 12:00:00
Obs: Se no ROUND não passar o parâmetro então irá arredondar a hora se for < 12 horas para 12:00:00 do mesmo dia e se a hora a ser arredondada for >= 12 então vai somar o dia +1 e também irá para 12 horas. Caso a data seja 01-jan com horário 12:00:00 e usar ROUND sem passar parâmetro nessa data então a data irá para 02-jan 12:00:00, veja exemplo:
select
to_char(to_date('01-JAN-19 12:00:00', 'dd-mon-yy hh:mi:ss'),'dd-mon-yy hh:mi:ss') data_hora,
to_char(round(to_date('01-JAN-19 12:00:00', 'dd-mon-yy hh:mi:ss')), 'dd-mon-yy hh:mi:ss') data_hora_round
from dual
Resultado:
data_hora = 01-jan-19 12:00:00
data_hora_round = 02-jan-19 12:00:00
W
round(to_char(sysdate,’dd-mm-yyyy’),’w‘)
Arredonda pra data próxima ou a primeira da semana, dependendo se está mais perto do fim da semana vai ir pra próxima semana no primeiro dia. Se estiver mais perto do primeiro dia da semana então vai pro primeiro dia dessa semana atual.
ex: dia 06-04-2019 está mais perto do fim dia 7, porque a semana começa dia 1 até dia 7 a semana 2 começa do dia 8 até o dia 14 e assim por dianteround(to_date('06-04-2019','dd-mm-yyyy'),'w') = 08-04-2019
SELECT ROUND (TO_DATE ('27-OCT-00'),'YEAR')
"Ano Novo" FROM DUAL;
Ano Novo
---------
01-JAN-01
Exemplos ROUND(DATE, fmt)
fmt são os formatos possíveis como por exemplo: yy para ano, YEAR, mm, etc…
select
to_char(
round(
to_date('06-04-2019 10:29:00',
'dd-mm-yyyy hh:mi:ss'),
'hh') ,
'dd-mm-yyyy hh:mi:ss am') round_hh,
Resultado: 06-04-2019 10:00:00 am
to_char(
round(
to_date('06-04-2019 10:30:00',
'dd-mm-yyyy hh:mi:ss'),
'hh'),
'dd-mm-yyyy hh:mi:ss am') round_hh_2,
Resultado: 06-04-2019 11:00:00 am
to_char(
round(
to_date('06-04-2019 10:30:29',
'dd-mm-yyyy hh:mi:ss'),
'mi') ,
'dd-mm-yyyy hh:mi:ss am') round_mi,
Resultado: 06-04-2019 10:30:00 am
to_char(
round(
to_date('06-04-2019 10:30:30',
'dd-mm-yyyy hh:mi:ss'),
'mi') ,
'dd-mm-yyyy hh:mi:ss am') round_mi_2,
Resultado: 06-04-2019 10:31:00 am
to_char(
round(
to_date('06-04-2019 10:00:30',
'dd-mm-yyyy hh:mi:ss'),
'mi') ,
'dd-mm-yyyy hh:mi:ss am') round_mi_3,
Resultado: 06-04-2019 10:01:00 am
/*
não pode usar formato ss para arredondar
gera um erro:
ORA-01899: bad precision specifier
*/
to_char(
round(
to_date('06-04-2019 10:00:30',
'dd-mm-yyyy hh:mi:ss'),
'ss') ,
'dd-mm-yyyy hh:mi:ss') round_ss
Resultado: erro fmt ss
to_char(
round(
to_date('06-04-2019 10:00:30',
'dd-mm-yyyy hh:mi:ss'),
'dd') ,
'dd-mm-yyyy hh:mi:ss am') round_dd,
Resultado: 06-04-2019 12:00:00 am
to_char(
round(
to_date('06-04-2019 12:00:00',
'dd-mm-yyyy hh:mi:ss'),
'dd') ,
'dd-mm-yyyy hh:mi:ss pm') round_dd_2
Resultado: 07-04-2019 12:00:00 am
from dual
TRUNC (d, i)
Parâmetros:
d é uma data (obrigatório).
i é o formato (opcional).
Processo: Executa a mesma tarefa que ROUND para datas, exceto que TRUNC sempre arredonda para baixo.
Saída: Data
sysdate = 27-SEP-18TRUNC(sysdate, 'MM') = 01-SEP-18TRUNC(sysdate, 'RR') = 01-JAN-18TRUNC(to_date('06-04-2019','dd-mm-yyyy'),'w') = 01-04-2019
NEXT_DAY (d, c)
Ambos parâmetros são obrigatórios
Parâmetros:
d = É a data, obrigatório;
c = É o texto de referência do dia da semana, obrigatório.
Processo: Retorna uma data válida representando a primeira ocorrência do dia c seguido da data representada por d.
Saída: Data
NEXT_DAY(
to_date('27-SEP-2018',
'DD-MON-YYYY'),
'SATURDAY')
Resultado = 29-SEP-18
LAST_DAY (d)
Parâmetros: d é uma data, obrigatório.
Passa o parâmetro de data e a função retorna o último dia do mês referente a data informada.
Saída: Date.
SYSDATE = 01-SEP-19
LAST_DAY(SYSDATE)
Resultado: 30-SEP-19
ADD_MONTHS (d, n)
Parâmetros:
d é uma data, obrigatório;
n é um número inteiro, obrigatório.
Processo: Adiciona n meses a d e retorna um valor de data válido para o resultado.
Saída: Data
Obs: se d informado último dia do mês e add n então irá retornar o último dia do mês ex.
Se informar d = 31-jan e n = 1 então irá retornar 29-fev ou 28-fev depende do ano bissexto do contrário sempre retorna o dia igual ex: data 02-jan e d=1 então retorna 02-fev.
Obs: Use n um valor negativo para subtrair a data.
select SYSDATE, ADD_MONTHS(sysdate,3) ADD_3_MESES from dual;
| sysdate | ADD_3_MESES |
|---|---|
| 01-OCT-19 | 01-JAN-20 |
MONTHS_BETWEEN (d1, d2)
Parâmetros: d1 and d2 são datas, ambos parâmetros são obrigatórios.
Para obter um número positivo, espera-se que o primeiro parâmetro seja o maior valor; espera-se que o segundo seja o menor. Qualquer uma das abordagens funciona, mas não deixe de considerar o sinal do resultado; se o segundo parâmetro for o valor maior, o resultado será um número negativo.
Resultado: número. MONTHS_BETWEEN(TO_DATE('15-04-2019','DD-MM-YYYY'),TO_DATE('01-05-2019','DD-MM-YYYY')) = -.5483
TO_TIMESTAMP (c, format_model, nls_parms)
Objetivo: Converter dados c no tipo de dados TIMESTAMP, que difere de DATE por incluir segundos fracionários. O parâmetro format_model define a máscara que obrigatoriamente deve ser uma máscara TIMESTAMP, como por exemplo, conter informações de ano, mês, dia, horas, minutos, segundos e segundos fracionários.
Parâmetros:
c é um tipo de dados caractere (obrigatório);
format_model deve definir o formato de c correspondente aos elementos do modelo de formato TIMESTAMP (opcional). O requisito padrão é que c deve estar no formato TIMESTAMP.
nls_parms (opcional) caso informado deve ser os da view V$NLS_PARAMETERS.
Saída: um valor no tipo de dados TIMESTAMP.
Exemplo: Aqui está uma string que representa uma data. O modelo de formato é incluído para definir o padrão e informar a função TIMESTAMP onde estão as informações DD, onde estão as informações MON e assim por diante. Veja um exemplo abaixo:
SELECT TO_TIMESTAMP( '2020-JAN-01 13:34:00:093423', 'RRRR-MON-DD HH24:MI:SS:FF' ) HORA_EVENTO FROM DUAL Resultado: HORA_EVENTO ---------------------------- 01-JAN-20 01.34.00.093423 PM
TO_TIMESTAMP_TZ (c, format_model, nls_parms)
Parâmetros:
c é uma string (obrigatório).
format_model value must define the format of c corresponding to TIMESTAMP WITH TIME ZONE format model elements (opcional). The default requirement is that c must be in the TIMESTAMP format.
nls_parms (opcional) caso informado deve ser os da view V$NLS_PARAMETERS.
Processo: transforma c em um valor de TIMESTAMP WITH TIME ZONE, em que format_model define o formato no qual c armazena as informações de TIMESTAMP WITH TIME ZONE. O fuso horário será o padrão definido pelo parâmetro SESSION.
Saída: Um valor do tipo de dados: TIMESTAMP WITH TIME ZONE.
Exemplo: Converta a sequência de caracteres ’17-04-2016 16:45:30 ‘em um tipo de dados TIMESTAMP WITH TIME ZONE, fornecendo uma máscara de formato:
SELECT TO_TIMESTAMP_TZ( '17-04-2019 16:45:30', 'DD-MM-RRRR HH24:MI:SS') FUSO_HORARIO FROM DUAL; Resultado: FUSO_HORARIO --------------------------------------- 17-APR-19 04.45.30.000000 PM US/PACIFIC
Observação:
Não existe uma função de conversão que converta valores para o tipo de dados TIMESTAMP WITH LOCAL TIME ZONE. Para isso, use o CAST, que é o próximo tópico:
CAST (e AS d)
Parâmetros:
e é uma expressão;
d é um tipo de dado.
Objetivo: Converte e para o formato que você especificar em d. Isso é particularmente útil para converter representações de texto de informações de data e hora em formatos de data e hora, particularmente TIMESTAMP WITH LOCAL TIME ZONE.
Saída: Um valor do tipo de dados d.
Exemplo: a seguir, convertemos um valor no formato padrão TIMESTAMP, apresentado como um valor literal:
SELECT CAST( '19-JAN-16 11:35:30' AS TIMESTAMP WITH LOCAL TIME ZONE ) CONVERTEU_PARA_LTZ FROM DUAL Resultado: CONVERTEU_PARA_LTZ ---------------------------- 19-JAN-16 11.35.30.000000 AM
Podemos fazer vários tipos de CAST, basta informar a string e o tipo de dado correspondente, veja mais exemplos:
SELECT
CAST('123.45' AS INTEGER) TEXT_TO_INT,
CAST('123.45' AS NUMBER) TEXT_TO_NUMBER,
CAST('31-DEC-2019' AS DATE) TEXT_TO_DATE
FROM DUAL;
| TEXT_TO_INT | TEXT_TO_NUMBER | TEXT_TO_DATE |
|---|---|---|
| 123 | 123.45 | 31-DEC-19 |
Aritmética com Datas
No tópico anterior vimos várias funções que podemos utilizar com datas, como por exemplo adicionar ou subtrair meses de uma data específica usando ADD_MONTH, para adicionar basta passar o parâmetro numérico positivo e para subtrair passar um valor numérico negativo.
Também vimos que para descobrir a diferença em meses entre uma data e outra usamos MONTH_BETWEEN. Podemos realizar outros cálculos como por exemplo:
Os apelidos são auto-explicativos: SELECT SYSDATE + 1 AS AMANHA, SYSDATE - 7 AS UMA_SEMANA_ATRAS, SYSDATE + (10/1440) AS DEZ_MINUTOS_A_FRENTE FROM DUAL;
Mais um exemplo: retorna o número de dias desde que cada funcionário foi contratado subtraindo a coluna HIREDATE da tabela EMP menos SYSDATE:
SELECT SYSDATE "Agora", hire_date as "Contrato em", SYSDATE - hire_date "Dias Contratado" FROM hr.employees WHERE employee_id = 100; Resultado: Agora Contratado em Dias Contratado ----------- --------------- --------------- 01-OCT-19 17-JUN-03 5950.10
A consulta acima retorna o número de dias desde que o funcionário foi contratado, usando o cálculo SYSDATE – hire_date, achando assim o valor 5950 dias.
Vejamos mais um exemplo usando cálculos de datas e números:
Cuidado com as conversões!
desenvoracle.home.blog 