Fun‡äes SQL do Oracle.ppt

Usando funções “Single-Row” para customizar a saída

Objetivos

Depois de completar esta lição, você deve estar apto a fazer o seguinte:• Descrever vários tipos de funções disponíveis no SQL• Usar funções de caractér, número e data em comandos SELECT

Agenda

• Funções SQL Single-row• Funções de caractér• Funções de número• Trabalhando com datas• Funções de Data

Funções SQL

Função

Entrada

arg 1

arg 2

arg n

Função faz a ação

Saída

Valor de resultado

Dois tipos de funções SQL

Single-row Multiple-row

Retorna um resultado por linha Retora um resultado porConjunto de linhas

Funções

Funções Single-Row

Funções Single-row:• Manipula itens de dados• Aceita argumentos e retorna um valor• Age em cada linha que é retornada• Retorna um resultado por linha• Pode modificar o tipo de dados• Pode ser aninhada• Aceita argumentos que podem ser colunas ou uma

expressãofunction_name [(arg1, arg2,...)]

Funções Single-Row

Conversão

Caractér

Número

Data

GeralFunções

Single-row

Agenda


Funções de número

• ROUND: Arredonda o valor para um decimal específico• TRUNC: Reduz o valor para um decimal específicoMOD: Retorna o resto da divisão

100MOD(1600, 300)

45.93ROUND(45.926, 2)

45.92TRUNC(45.926, 2)

ResultFunction

SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)FROM DUAL;

Usando a função ROUND

DUAL é uma tabela manequm que voce pode usar para visualizaros resultados de funções e cálculos

3

31 2

1 2

Usando a função TRUNC

SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1)FROM DUAL;

3

31 2

1 2

SELECT last_name, salary, MOD(salary, 5000)FROM employeesWHERE job_id = 'SA_REP';

Usando a função MOD

Para todos os empregados que o a função “Sales Representative”, calcule o resto do salário depois da divisão por 5,000.

Agenda


SELECT last_name, hire_dateFROM employeesWHERE hire_date < ''01-FEB-88';';

Trabalhando com datas

• O banco de dados Oracle armazena datas em um formato numérico: século, ano, mês, dia, horas e segundos.

• O padrão de data padrão é DD-MM-RR.– Permite você armazenar datas do século 21 no século 20

especificando os últimos dias do ano– Permite você armazenar datas do século 20 no século 21 da

mesma forma

RR Formato de data

Current Year1995199520012001

27-OCT-9527-OCT-1727-OCT-1727-OCT-95

1995201720171995

1995191720172095

Se os dois digitos do ano atual forem:

0–49

0–49 50–99

50–99

Retorna a data com o século atual

Retorna a data com um século posterior

Retorna a data no século anterior

Retorna a data com o século atual

Se os dois digitos especifcados forem:

Formato YYFormatoRRData específicaAno atual

Usando a função SYSDATE

SYSDATE é uma função que retorna:• Data• Tempo

SELECT sysdateFROM dual;;

Cálculos com datas

• Adicione ou subtraía um número de ou para uma data para um valor resultante.

• Substrair duas datas para encontrar o número de dias entre elas.

• Adicionar horas a uma data dividindo o número de horas por 24.

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKSFROM employeesWHERE department_id = 90;

Usando operadores aritiméticos com datas

Agenda


Funções de manipulação de data

Next day of the date specifiedNEXT_DAY

Last day of the monthLAST_DAY

Round dateROUND

Truncate dateTRUNC

Number of months between two datesMONTHS_BETWEEN

Add calendar months to dateADD_MONTHS

ResultadoFunção

Usando funções de data

'08-SEP-95'NEXT_DAY ('01-SEP-95','FRIDAY')

'28-FEB-95'LAST_DAY ('01-FEB-95')

19.6774194MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')

‘29-FEB-96'ADD_MONTHS (‘31-JAN-96',1)

ResultFunction

Usando funções ROUND e TRUNC com datas

Assumir SYSDATE = '25-JUL-03':

01-JUL-03TRUNC(SYSDATE ,'MONTH')

01-JAN-03TRUNC(SYSDATE ,'YEAR')

01-AUG-03ROUND(SYSDATE,'MONTH')

01-JAN-04ROUND(SYSDATE ,'YEAR')

ResultadoFunção

Agenda


Functions de caractér

Functions de caractér

LOWERUPPERINITCAP

CONCATSUBSTRLENGTHINSTRLPAD | RPADTRIMREPLACE

FunçõesCase-conversion Functions de manipulação

De caractér

Funções Case-Conversion

Estas funções convertem “case” de strings:

sql courseLOWER('SQL Course')

Sql CourseINITCAP('SQL Course')

SQL COURSEUPPER('SQL Course')

ResultadoFunção

SELECT employee_id, last_name, department_idFROM employeesWHERE LOWER(last_name) = 'higgins';

Usando funções Case-Conversion

Mostrar o número,nome e número do departamento do empregado cujo nome é Higgins:

SELECT employee_id, last_name, department_idFROM employeesWHERE last_name = 'higgins';

Funções de manipulação de caractér

Estas funções manipulam strings:

BLACK and BLUE REPLACE('JACK and JUE','J','BL')

10LENGTH('HelloWorld')

6INSTR('HelloWorld', 'W')

*****24000LPAD(salary,10,'*')

24000*****RPAD(salary, 10, '*')

HelloWorldCONCAT('Hello', 'World')

elloWorldTRIM('H' FROM 'HelloWorld')

HelloSUBSTR('HelloWorld',1,5)

ResultadoFunçãp

SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR(last_name, 'a') "Contains 'a'?"FROM employeesWHERE SUBSTR(job_id, 4) = 'REP';

Usando funções de mainuplação de caractér

2

31 2

1

3

Resumo

Nesta lição, você deve ter aprendido como:• Realizar cálculos em dados usando funções• Modificar dados individuais usando funções

Prática 3: Overview

Esta prática abrange os seguintes tópicos:• Escrevendo uma consulta que mostra a data atual• Criando consultas que requerem o uso de funções

numeráis, de caracter e data• Realizando cálculos de anos e mêses de serviço par um

empregado

Documents

Fun‡äes SQL do Oracle.ppt