Funções em sql

Funções de Caracter

• Aceitam caracteres como entrada.
• Retornam caracteres ou números.
• Mais usadas:
  • lower
  • upper
  • initcap
  • lpad
  • rpad
  • substr
  • instr
  • ltrim
  • rtrim
  • length
  • translate
  • replace

LOWER

Transforma os caracteres alfabéticos que estão em maiúsculas para minúsculas.

lower(col | valor)

Exe.:

select   lower(campo) from tabela /

UPPER

Transforma os caracteres alfabéticos que estão em minúsculas para maiúsculas.

upper(col | valor)

Exe.:

select   upper(campo) from tabela /

INITCAP

Transforma a primeira letra da string em maiúscula.

initcap(col | valor)

Exe.:

select   initcap(campo) from tabela /

LPAD

Preenche a coluna ou literal da esquerda até o tamanho total de n posições. O espaço restante é preenchido com a string informada, se for omitida o valor é preenchido com espaço em branco.

lpad(col | valor, n, 'string')

Exe.:

select   lpad(nome,20,'*'),   lpad(sobrenome,20),   lpad(campo,20) from tabela /

RPAD

Mesma utilização da função lpad, porém o preenchimento se dá da direita até o tamanho total de n posições.

SUBSTR

Retorna uma substring de n extraída da coluna ou literal, começando na posição pos. Se n é omitido, a string é extraída da posição pos até o final. Numeração de caracteres em uma string: esquerda para a direita, começando em 1.

substr(col | valor, pos, n)

Exe.:

select   substr('SQL ORACLE',2,4),   name,   substr(name,2),   substr(name,3,5) from tabela /

INSTR

Localiza a posição do caracter da enésima ocorrência de string na coluna ou literal começando na posição pos.

instr(col | valor, 'string', pos, n)

Exe.:

select   name,   instr(name,'A'),   instr(name,'ES'),   instr(name,'C',1,2) from tabela /

LTRIM

Remove da esquerda da coluna ou literal as ocorrências de chars, se não especificada serão removidos os espaços em branco.

ltrim(col | valor, 'chars')

Exe.:

select   name,   ltrim(name,'A'),   ltrim(name,'AS'),   ltrim(name,'ASOP') from tabela /

RTRIM

Mesma utilização da função rtrim, remove da direita da coluna.

LENGTH

Retorna o número de caracteres, ou dígitos, da coluna ou literal.

length(col | valor)

Exe.:

select   name,   length(name) from tabela /

TRANSLATE

Transforma o caracter de para o caracter para. Mais de um caracter podem ser combinados. Todas as ocorrências do caracter de serão traduzidas pelo correspondente caracter para. Se o correspondente caracter para não é fornecido, o caracter de é removido.

translate( col | valor, de, para)

Exe.:

select   ename,   translate(ename,'C','P'),   job,   translate(job, 'ARN', 'IT') from emp /

REPLACE

Transforma a coluna ou literal com cada ocorrência de string1 substituída por string2. Se string2 for omitida, todas as ocorrências de string1 serão removidas.

replace(col | valor, 'string1', 'string2')

Exe.:

select   ename,   replace(ename,'CO', 'KA'),   job,   replace(job,'MANAGER','BOSS') from emp /

Funçoes Numéricas

Aceitam números como entrada e retornam valores numéricos.
Mais usadas:

• round
• ceil
• power
• sign
• mod
• trunc
• floor
• sqrt
• abs

ROUND

Arredonda a coluna expressão ou valor para n casa decimais. Se n é omitido, o arredondamento elimina as casa decimais, se n é negativo, os números à esquerda do ponto decimal são arrendondados para zero.

round(col | valor, n)

Exe.:

SELECT   ROUND (45.923,1),   ROUND (45.923),   ROUND (45.323,-1),   ROUND (42.323,-2),   SAL,   ROUND (SAL/32,2) FROM EMP WHERE DEPTNO = 10 /

TRUNC

Trunca a coluna expressão ou valor para n casas decimais. Se n omitido, o truncamento elimina as casas decimais, se n negativo, os números à esquerda do ponto decimal são truncados para zero.

trunc(col | valor, n)

Exe.:

SELECT   TRUNC (45.923,1),   TRUNC (45.923),   TRUNC (45.323,-1),   TRUNC (42.323,-2),   SAL,   TRUNC (SAL/32,2) FROM EMP WHERE DEPTNO = 10 /

CEIL

Encontra o menor inteiro, maior ou igual à coluna, expressão ou valor .

ceil(col | valor)

Exe.:

SELECT   CEIL (SAL),   CEIL(99.9),   CEIL(101.76),   CEIL(-11.1) FROM EMP WHERE SAL BETWEEN 3000 AND 5000 /

FLOOR

Encontra o maior inteiro, menor ou igual a coluna, expressão ou valor.

floor(col | valor)

Exe.:

SELECT   FLOOR(SAL),   FLOOR(99.9),   FLOOR(101.76),   FLOOR(-11.1) FROM EMP WHERE SAL BETWEEN 3000 AND 5000 /

POWER

Encontra a coluna, expressão ou valor à enésima potência. n pode ser negativo, mas deve ser inteiro.

power(col | valor, n)

Exe.:

SELECT   SAL,   POWER(SAL,2),   POWER(SAL,3),   POWER(50,5) FROM EMP WHERE DEPTNO = 10 /

SQRT

Calcula  a raiz quadrada da coluna, expressão ou valor, que não podem ser negativos.

sqrt(col | valor)

Exe.:

SELECT   SAL,   SQRT(SAL),   SQRT(40),   SQRT(COMM) FROM EMP WHERE COMM > 0 /

SIGN

Retorna - 1, 0, 1 se a coluna, expressão ou valor é um número negativo, zero ou positivo

sign(col | valor)

Exe.:

SELECT   SAL - COMM,   SIGN(SAL-COMM),   COMM - SAL,   SIGN(COMM-SAL) FROM EMP WHERE COMM > 0 /

ABS 

Retorna o valor absoluto da coluna, expressão ou valor.

abs(col | valor)

Exe.:

SELECT   SAL,   COMM,   COMM-SAL,   ABS(COMM-SAL),   ABS(-35) FROM EMP WHERE DEPTNO = 30 /

MOD

Encontra o resto da divisão de valor1 por valor2.

mod(valor1, valor2)

Exe.:

SELECT   SAL,   COMM,   MOD(SAL,COMM),   MOD(100,40) FROM EMP ORDER BY COMM /

Funções de Manipulação de Datas

O Oracle armazena as datas em um formato numérico interno, que pode variar de 1-jan-4712 AC até 31-dez-4712 DC.
O formato interno representa:

• Século
• Ano
• Mês
• Dia
• Horas
• Minutos
• Segundos

SYSDATE

É uma pseudo-coluna que retorna o valor corrente da data e hora. É comum selecionar SYSDATE a partir da tabela interna e vazia chamada DUAL.

select sysdate from dual;

OPERAÇÕES ARITMÉTICAS

É possível realizar cálculos com datas, utilizando operadores aritméticos.
Operações possíveis:

• date + number
  • Adiciona um número de dias à data, retornando uma data.
• date - number
  • Subtrai um número de dias da data, retornando uma data.
• date - date
  • Subtrai um número de dias da data, retornando uma data.
• date + number/24
  • Adiciona um número de horas à data, retornando uma data.

Exe.:

SELECT   HIREDATE,   HIREDATE-7,   SYSDATE-HIREDATE FROM EMP WHERE HIREDATE LIKE '%06%' /

MONTHS_BETWEEN

Retorna o número de meses dentre dentre data1 e data2, positivo ou negativo.

months_between(data1, data2)

Exe.:

SELECT   MONTHS_BETWEEN(SYSDATE,HIREDATE),   MONTHS_BETWEEN('01-JAN-98','05-NOV-99') FROM EMP WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE) > 59 /

ADD_MONTHS

Adiciona n meses à data, sendo que n deve ser inteiro e não negativo.

add_months(data, n)

Exe.:

SELECT   HIREDATE,   ADD_MONTHS(HIREDATE,3),   ADD_MONTHS(HIREDATE,-3) FROM EMP WHERE DEPTNO=20 /

NEXT_DAY

Retorna a data do próximo dia da semana especificado, seguinte à data1. Char pode ser um número representando um dia, ou uma string.

next_day(data1, char)

Exe.:

SELECT   HIREDATE,   NEXT_DAY(HIREDATE,'SEXTA-FEIRA'),   NEXT_DAY(HIREDATE,6) FROM EMP WHERE DEPTNO=10 /

LAST_DAY

Retorna o último dia do mês da data1. Char pode ser um número representando um dia, ou uma string.

last_day(data1)

Exe.:

SELECT   SYSDATE,   LAST_DAY(SYSDATE),   HIREDATE,   LAST_DAY(HIREDATE),   LAST_DAY('15-FEV-98') FROM EMP WHERE DEPTNO=10 /

ROUND

Formatos:

• round(data1)
  • Retorna data1 com a hora definida como 12:00AM (meia noite).
• round(data1, 'MONTH')
  • Retorna o primeiro dia do mês contido em data1 se ela estiver na primeira metade do mês, caso contrário, o primeiro dia do próximo mês.
• round(data1, 'YEAR')
  • Retorna o primeiro dia do ano contido em data1 se ela estiver na primeira metade do ano, caso contrário, o primeiro dia do próximo ano.

Exe.:

SELECT   SYSDATE,   ROUND(SYSDATE,'MONTH'),   ROUND(SYSDATE,'YEAR') FROM DUAL /

TRUNC

Formatos:

• trunc(data1, 'MONTH')
  • Retorna o primeiro dia do mês contido em data1.
• trunc(data1, 'YEAR')
  • Retorna o primeiro dia do ano contido em data1.

Exe.:

SELECT   SYSDATE,   TRUNC(SYSDATE,'MONTH'),   TRUNC(SYSDATE,'YEAR') FROM DUAL /

TO_CHAR

Converte o número ou data para o formato fmt. Se fmt for omitido será usado o formato default DD-MM-YY para data ou char para números. Os formatos somente afetam a forma como o dado é mostrado, não alterando o seu valor interno.

to_char(num | data, ['fmt'])

Exe.:

SELECT   TO_CHAR(SYSDATE,'DAY,DDTH MONTH YYYY') FROM DUAL / SELECT   TO_CHAR(SAL,'$9,999') FROM EMP /

TO_NUMBER

Converte char que contém um número para number.

to_number(char)

Exe.:

SELECT   EMPNO,   ENAME,   JOB,   SAL FROM EMP WHERE SAL > TO_NUMBER('1500') /

TO_DATE

Converte o valor char representando uma data, em uma data de acordo com o formato fmt especificado. Se ftm for omitido, será usado DD-MM-YY.

to_date(char, 'fmt')

Exe.:

SELECT   EMPNO,   ENAME,   HIREDATE FROM EMP WHERE HIREDATE = TO_DATE ('Dezembro 3,1981','Month dd,yyyy') /

DECODE

Realiza buscas condicionais, funcionando como case ou if. Col | expressão são comparados com cada argumento busca e retorna o correspondente resultado quando a combinação for verdadeira. Se o valor default for omitido, será retornado umnull.

decode(col | expressão, busca1, resultado1, [busca2, resultado2, ... ] default)

Exe.:

SELECT   ENAME,   JOB,   DECODE(      JOB,      'CLERK','WORKER',      'MANAGER','BOSS',      'INDEFINIDO'   ) CARGO FROM EMP / SELECT   JOB,   SAL,   DECODE(     JOB,     'ANALYST',SAL*1.1,     'CLERK',SAL*1.15,     'MANAGER',SAL*0.95,     SAL   ) CORRIGIDO FROM EMP /

NVL

Converte um valor null para val.

nvl(col | valor, val)

Exe.:

SELECT   SAL,   COMM,   SAL*12+NVL(COMM,0),   NVL(COMM,1000),   SAL*12+NVL(COMM,1000) FROM EMP WHERE DEPTNO =10 /

GREATEST

Retorna o maior valor da lista de valores.

greatest(col | valor1, col | valor2, ...)

Exe.:

SELECT   GREATEST(1000,2000),   GREATEST(SAL,COMM) FROM EMP WHERE DEPTNO =30 /

LEAST

Retorna o menor valor da lista.

least(col | valor1, col | valor2, ...)

Exe.:

SELECT   LEAST(1000,2000),   LEAST(SAL,COMM) FROM EMP WHERE DEPTNO =30 /