Oracle Funções

Funções de Manipulação de Caracteres

CONCAT(‘Hello’,'World’);
Resultado: HelloWorld

SUBSTR(‘HelloWorld’,1,5)
RESULTADO Hello

LENGTH(‘HelloWorld’)
RESULTADO 10

INSTR(‘HelloWorld’, ‘W’)
RESULTADO 6

LPAD(salary, 10, ‘*’)
RESULTADO *****24000

RPAD(salary, 10, ‘*’)
RESULTADO 24000*****

REPLACE(‘Jack and JUE’,'J’,'BL’)
RESULTADO BLACK and BLUE

TRIM(‘H’ FROM ‘HelloWorld’)
RESULTADO elloWorld

Limpar a direita
RTRIM(‘H’ FROM ‘HelloWorld’)
RESULTADO HelloWorld

Limpar a esquerda
LTRIM(‘H’ FROM ‘HelloWorld’)
RESULTADO elloWorld

Associar 1 ao 4, 2 ao 5 e 3 ao 6
TRANSLATE (’1tech231′,’123′,’456′)
RESULTADO 4tech564

Funções Númericas

Arredonda
ROUND (45.926, 2)
RESULTADO 45.93

ELIMINA a terceira casa decimal
TRUNC(45.926, 2)
RESULTADO 45.92

Obter o resto da divisão
MOD(1600,300)
RESULTADO 100

Interessado na dezena
ROUND(45.923, -1)
RESULTADO 50

Trabalhando com Data

Formato padrão de exibição DD-MMM-RR
exemplo: 01-FEB-88

Oracle Grava assim
CENTURY YEAR MONTH DAY HOUR MINUTE SECOND

SELECT SYSDATE FROM DUAL
RESULTADO 10-NOV-11

date+number adciona um número de dias a uma data.
RESULTADO date

date – number subtrai um número de dia a uma data
RESULTADO date

date – date subtrai uma data de outra
RESULTADO dia

date + number/24 adicionar um número de horas a uma data.
RESULTADO horas

SELECT last_name, (SYSDATE-hire_date)/7 AS semanas
FROM employees
WHERE department_id = 90

FUNÇÕES DE DATAS
Obs.: Quando não precisar de tabela use FROM DUAL (Tabela DUMMY do Oracle)

MONTHS_BETWEEN – número de meses entre duas datas
SELECT MONTHS_BETWEEN (’01-SEP-95′,’11-JAN-94′) from dual
RESULTADO 19.6774194

ADD_MONTHS – adiciona meses a uma data
SELECT ADD_MONTHS (’11-JAN-94′, 6) FROM DUAL
RESULTADO 11-JUL-94

NEXT_DAY – dia seguinte da data especificada
SELECT NEXT_DAY (’08-JUL-81′, ‘FRIDAY’) FROM DUAL
RESULTADO 10-JUL-81

LAST_DAY – último dia do mês
SELECT LAST_DAY (’08-JUL-81′) FROM DUAL
RESULTADO 31-JUL-81

ROUND – arredonda a data
SELECT ROUND (SYSDATE, ‘MONTH’) FROM DUAL
RESULTADO 01-AUG-03

ROUND – arredonda a data
SELECT ROUND (SYSDATE, ‘YEAR’) FROM DUAL
RESULTADO 01-JAN-04

TRUNC – trunca a data
SELECT TRUNC(SYSDATE, ‘MONTH’) FROM DUAL
RESULTADO 01-JUL-03

SELECT TRUNC(SYSDATE, ‘YEAR’) FROM DUAL
RESULTADO 01-JAN-03

EXEMPLOS GERAIS

Como obter aumento salarial para todos os empregados:
SELECT employee_id, last_name, salary,
ROUND(salary*1.155, 2) AS “Novo Salario”
FROM employees

SELECT employee_id, last_name, salary,
ROUND(salary*0.155,2) AS “Aumento”,
ROUND(salary*1.55,2) AS “Novo Salario”
FROM employees

Obter o sobrenome e o tamanho do sobrenome de todos os empregados cujo sobrenome comece com J, A ou M, ordene a consulta por sobrenome.
SELECT INITCAP(last_name) AS Sobrenome, LENGTH(last_name) FROM employees
WHERE SUBSTR(last_name,1,1) = ‘J’ OR SUBSTR(last_name,1,1) = ‘A’ OR SUBSTR(last_name,1,1) = ‘M’

SQL OTIMIZADA:
SELECT INITCAP(last_name) Sobrenome, LENGTH(last_name) Tamanho
FROM employees WHERE SUBSTR(last_name,1,1) IN (‘J’,'A’,'M’)
ORDER BY last_name

Tempo de trabalho em meses do empregado
SELECT last_name, hire_date,
ROUND(MONTHS_BETWEEN(SYSDATE,hire_date),0) AS “tempo de trabalho”
FROM employees

Empregados cujo nome termine com a letra n
SELECT employee_id, CONCAT(first_name, last_name) NOME, job_id, LENGTH (last_name), INSTR(last_name, ‘a’) “Tem ‘a’ ? ”
FROM employees WHERE first_name LIKE ‘%n’

OTIMIZADO
SELECT employee_id, CONCAT(first_name, last_name) NOME, job_id, LENGTH (last_name), INSTR(last_name, ‘a’) “Tem ‘a’ ? ”
FROM employees WHERE SUBSTR(first_name,-1) IN (‘n’)

Mostre o número do empregado, data de contratação, número de meses trabalhados até hoje, a data de avaliação após 6 meses de contratação, a primeira sexta-feira depois de sua contratação e o último dia do mês de contratação de cada empregado que trabalha há menos de 200 meses na empresa

SELECT employee_id, hire_date, ROUND(MONTHS_BETWEEN(SYSDATE,hire_date),0) AS “tempo de trabalho”, ADD_MONTHS (hire_date, 6) “data de avaliação”, NEXT_DAY (hire_date, ‘FRIDAY’) AS “Primeira Sexta”, LAST_DAY (hire_date) AS “Último dia do Mês” FROM employees WHERE ROUND(MONTHS_BETWEEN(SYSDATE,hire_date),0) < 200

FUNÇÕES DE CONVERSÕES

Number = TO_NUMBER
Date = TO_DATE

Usando TO_CHAR com números

ELEMENTO
9 - representa número
0 forçar zero para ser mostrado
$ colocar o sinal de dollar
L usar o simbolo da moeda local
. Usar ponto como decimal
, usar virgula como indicador de milhares.

SELECT TO_CHAR(salary, '99000.00') FROM employees
RESULTADO 380.00

SELECT TO_CHAR(salary, 'L99,000.09') FROM employees
RESULTADO US$1,850.52

Observações:
O Oracle mostra uma sequencia de # no lugar do número, se o mesmo exceder o número de dígitos do formato especificado.
O Oracle arredonda as casas decimais do números de acordo com o formato especificado.

Character = TO CHAR
TO_CHAR(date, 'format_model')

Elementos Permitidos
YYYY ano completo
YEAR ano por extenso
MM Mês com 2 números
MONTH nome completo do mês
MON 3 primeiras letras do mÊs
DY abreviação do dia da semana
DAY Nome do dia da semana
DD dia númerico do mês

Formato dos elementos de tempo:
HH24:MI:SS AM = 15:45:32 PM

Adicionar String utilizando aspas
DD "of" MONTH = 12 of OCTOBER

Sufixos numéricos
ddspth = fourteenth

SELECT last_name, TO_CHAR(hire_date, 'fmDD Month YYYY')
AS hiredate FROM employees
RESULTADO 17 June 1987

Funções Aninhadas

SELECT last_name, UPPER(CONCAT(SUBSTR (LAST_NAME,1 ,8 ), US) FROM employes

FUÇÃO NVL
Tratamento um campo nullo converte o valor NULL para um valor desejado
NVL(commission_pct,0)
RESULTADO 0

NVL(hire_date, ’01-JAN-97′)
RESULTADO 01-JAN-97

NVL(job_id, “sem trabalho”)
RESULTADO sem trabalho

FUÇÃO NVL2
Tratamento um campo nullo com condição.

NVL2(comission_pct,’SAL+COMM’, ‘SAL’)
se for nulo faça o q esta no primeiro parametro se nao for nulo faça o segundo parametro.

FUNÇÃO NULLIF
retorna nulo se for verdadeiro.

FUNÇÃO COALESCE
fazer verificação de 3 campos

Expressões Condicionais
Case
SELECT job_id,
CASE job_id
WHEN ‘IT_PROG’ THEN 1.10*salary
ELSE ‘salary’ END “Salario Revisado”
FROM EMPLOYEES
///////////////// EXEMPLO 2
SELECT job_id,
CASE job_id
WHEN ‘AD_PRES’ THEN ‘A’
WHEN ‘ST_MAN’ THEN ‘B’
WHEN ‘IT_PROG’ THEN ‘C’
WHEN ‘SA_REP’ THEN ‘D’
WHEN ‘ST_CLERK’ THEN ‘E’
ELSE ’0′ END “JOB_ID”
FROM EMPLOYEES

SELECT last_name, salary,
(CASE job_id
WHEN salary<1000 THEN “Low”
ELSE salary END) “Salario Revisado”
FROM EMPLOYEES
/////////////////////EXEMPLO 2

SELECT last_name, salary,
(CASE
WHEN salary<1999 THEN ’00%’
WHEN salary between 2000 AND 3999 THEN ’09%’
WHEN salary between 4000 AND 5999 THEN ’20%’
WHEN salary between 6000 AND 7999 THEN ’30%’
WHEN salary between 8000 AND 9999 THEN ’40%’
WHEN salary between 10000 AND 11999 THEN ’42%’
ELSE ‘salary’ END) “Salario Revisado”
FROM EMPLOYEES WHERE department_id IN (80)

/////////////////////EXEMPLO 2 – Solução com DECODE
SELECT last_name, salary,
DECODE (TRUNC(salary/2000,0),
0,0.00,
1,0.09,
2,0.20,
3,0.30,
4,0.40,
5,0.42,
6,0.44,
0.45) TAX_RATE
FROM employees
WHERE department_id = 80

LAST_NAME SALARY TAX_RATE
Zlotkey 10500.00 .42

Decode (específico Oracle)
— sem exemplo.

Relatório para cada empregado.
SELECT last_name
|| ‘ ganha ‘
|| TO_CHAR(salary, ‘L99,000.09′)
|| ‘ mensalmente mas deseja ‘
|| TO_CHAR(ROUND(salary*3,2), ‘fmL99,999.00′)
FROM employees
Obs.: o uso do fm é para eliminar o espaços em branco a esquerda.

Mostre o sobrenome, data de cotnratação e o dia da semana por extenso no qual o empregado começou. Chame a coluna de DIA e ordene os resultados pelo dia da semana começando por MONDAY e terminando em SUNDAY.
SELECT last_name, hire_date, TO_CHAR(hire_date, ‘day’) DIA FROM employees ORDER BY TO_CHAR(hire_date-1,’d')
RESULTADO Grant 24-MAY-99 monday

Consulta para exibir o sobrenome e a comissão dos empregados. Quando um empregado não tiver comissão, mostre coo “Não possui comissão”. A coluna deve chamar-se COMISSÃO

SELECT last_name, NVL(TO_CHAR(commission_pct), ‘Não possui comissão’) “COMISSÃO” FROM employees