Ana Cristina de Figueiredo Dornelas SQL E ORACLE 2ª. PARTE

(1)

Ana Cristina de Figueiredo Dornelas

SQL E ORACLE 2ª. PARTE

Ana Cristina de Figueiredo Dornelas

E-mail: anacristina.dornelas@gmail.com

(2)

DML

(3)

DML

• Recursos das instruções SELECT

Seleção Projeção

Junção

(4)

Criando uma seleção básica

• Sintaxe de uma instrução SELECT básica:

SELECT [DISTINCT] select_list FROM table_list

– Onde

• select_list: uma lista separada por vírgula de colunas de tabelas (ou expressões).

tabelas (ou expressões).

Sintaxe:

| *

|[schema.] [table.|view.] *

|[schema.] [table.|view.] column1 [c_alias] ,

[schema.] [table.|view.]column2 [c_alias] [,...]

| expr1, expr2 [,...]

O uso da expressão * seleciona todas as colunas das tabelas ou visões especificadas em FROM.

c_alias cria uma apelido (alias) para a coluna, que irá aparecer como cabeçalho da coluna no resultado da seleção.

(5)

Criando uma seleção básica (cont.)

• Sintaxe de uma instrução SELECT básica (cont.)

• DISTINCT

Remove as linhas duplicadas (que possuam o mesmo conteúdo) do resultado final

• table_list : uma lista separada por vírgula das tabelas das

• table_list : uma lista separada por vírgula das tabelas das quais as linhas do resultado (result set) devem ser

recuperadas.

Sintaxe:

|[schema.] {table1|view1} [t_alias], [schema.]

{table2|view2} [,...]

t_alias cria uma apelido (alias) para a tabela que pode ser usado para referenciar a tabela em outros cláusulas do comando

select.

(6)

Modelo físico das tabelas utilizadas

nos exemplos a seguir

(7)

Criando uma seleção básica (cont.)

• Selecionando todas as colunas:

SELECT * FROM emp;

• Selecionando uma coluna específica:

SELECT job FROM emp;

• Eliminando linhas duplicadas:

SELECT DISTINCT job FROM emp;

• Selecionando algumas colunas.

SELECT empno, ename, job FROM emp;

(8)

Cabeçalhos de colunas em resultados

• Defaults de cabeçalho de colunas – nomes dos atributos/colunas nas tabelas

• Alinhamento default.

– Esquerda: dados caracteres e data.

– Direita: dados numéricos.

• Exibição default: letra maiúscula.

(9)

Definindo apelido de coluna

• Renomeia um cabeçalho de coluna.

• É útil para cálculos.

• É colocado imediatamente ao nome da coluna.

Pode ser colocado a palavra AS opcionalmente entre o nome da coluna e o apelido.

SELECT ename AS nome, sal salário FROM emp;

• Necessita de aspas duplas caso contenha espaços ou caracteres especiais ou faça distinção entre

maiúsculas e minúsculas.

SELECT ename "Nome", sal*12 "Salário Anual"

FROM emp;

(10)

Restringindo dados

• Sintaxe:

[WHERE conditions]

– Onde, – Onde,

• WHERE é uma cláusula que define a(s) condição(ões) que cada linha na(s) tabela(s) listada(s) em table_list deve atender para ser qualificada para a seleção.

• A cláusula WHERE pode conter especificações de junções

internas e externas cuja sintaxe e exemplos de execução serão vistos mais adiante.

• Exemplo:

SELECT ename, job FROM emp WHERE job = ‘MANAGER’

(11)

Strings de caracteres e datas

• As strings de caracteres e valores de data aparecem entre aspas simples.

• Os valores de caracteres fazem distinção entre maiúsculas e minúsculas. E os valores de datas diferenciam formatos.

• O formato padrão para a instalação Oracle em

• O formato padrão para a instalação Oracle em linguagem Português – Brasil é DD/MM/RR (Dia/Mês/Ano em 2 ou 4 posições).

• Exemplos:

SELECT ename, job, deptno FROM emp WHERE ename = ‘JONES’;

SELECT * FROM emp WHERE hiredate = '17/11/1981'

(12)

Expressões e Operadores

• O Oracle é compatível com a maioria dos

operadores existentes nas linguagens. Estes podem ser dos seguintes tipos:

– Aritméticos – operam sobre operandos numéricos.

– Caractere – manipulam strings.

– Comparação.

– Lógico – manipulam expressões de comparação.

(13)

Operadores aritméticos

• Cria expressões com dados numéricos e date.

Operador Descrição + Adicionar

- Subtrair

SELECT ename, sal, sal+300 FROM emp;

SELECT ename, 12*sal+comm FROM emp WHERE ename = 'ALLEN‘;

- Subtrair

* Multiplicar / Dividir

(14)

Valores nulos

• Definindo um valor nulo

– Um valor nulo não

está disponível, não é atribuído, é

atribuído, é

desconhecido ou não é aplicável.

– Um valor nulo não é o mesmo que um zero ou um espaço em branco.

(15)

Valores nulos (cont.)

• Nas expressões aritméticas

– Expressões

aritméticas contendo um valor nulo é

um valor nulo é

avaliada como nula.

(16)

Operador de concatenação

• Concatena colunas ou strings de caractere a outras colunas.

• É representado por duas barras verticais (||).

• Cria uma coluna resultante que é uma

• Cria uma coluna resultante que é uma expressão de caracteres.

• Exemplo:

SELECT ename || ' trabalha como ' || job FROM emp;

SELECT ename || ' trabalha como ' || job

(17)

Exercícios

1. A instrução SELECT será executada corretamente?

SELECT ename, job, sal Salary FROM emp;

2. Há quatro erros de codificação na instrução abaixo. Você pode identificá-los?

SELECT empno, ename SELECT empno, ename

Salary X 12 Salario Anual

FROM emp;

3. Crie uma consulta para exibir o nome, o cargo, a data de admissão e o número do funcionário. Mostre o número do funcionário como sendo a primeira coluna. Salve a consulta em um arquivo chamado slide17.sql. Execute a consulta

4. Crie uma consulta para exibir os cargos exclusivos a partir da tabela EMP.

(18)

Operadores de comparação

Operador Significado

= Igual a

> Maior do que

>= Maior do que ou igual a

< Menor do que

<= Menor ou igual a

<> Diferente de

SELECT ename, sal, comm FROM emp WHERE sal <= comm;

SELECT ename, sal, comm FROM emp

(19)

Outros operadores de comparação

Operador Significado BETWEEN

...AND...

Entre dois valores(inclusive)

IN(list) Vincula qualquer um de uma lista IN(list) Vincula qualquer um de uma lista

de valores

LIKE Vincula um padrão de caractere IS NULL É um valor nulo

(20)

Exemplos de uso de outros operadores de comparação

SELECT empno, ename, sal, mgr FROM emp WHERE mgr IN (7902, 7566, 7788);

SELECT ename, sal FROM emp

WHERE sal BETWEEN 1000 AND 1500;

SELECT ename FROM emp WHERE mgr IS NULL;

(21)

Usando o operador LIKE

• Use o operador LIKE para executar pesquisas curinga de valores de string de pesquisa válidos.

• As condições de pesquisa podem conter caracteres literais ou números.

– % denota zero ou muitos caracteres.

– _ denota um caractere.

SELECT ename FROM EMP

WHERE ename LIKE 'S%';

SELECT ename FROM emp

WHERE ename LIKE '_A%‘;

(22)

Operadores Lógicos

Operador Significado

AND Retorna TRUE se as

condições de componentes forem TRUE.

OR Retorna se cada condição OR Retorna se cada condição de componente for TRUE.

NOT Retorna TRUE se a condição seguinte for FALSE.

(23)

Exemplos de uso de operadores lógicos

SELECT * FROM emp

WHERE sal >= 1100 AND job = 'CLERK';

SELECT * FROM emp WHERE sal >= 1100 OR job = 'CLERK';

SELECT * FROM emp WHERE sal < 1000 OR sal >

3000;

SELECT * FROM emp WHERE deptno=10 OR deptno=30 AND sal > 2500;

SELECT * FROM emp WHERE (deptno=10 OR deptno=30) AND sal > 2500;

(24)

Usando o operador NOT

SELECT ename, job FROM emp WHERE job NOT IN

('CLERK', 'MANAGER','ANALYST');

• O operador NOT pode ser utilizado também com outros operadores SQL, como BETWEEN, LIKE e NULL.

...WHERE sal NOT BETWEEN 1000 AND 1500 ...WHERE ename NOT LIKE '%A%';

...WHERE comm IS NOT NULL

(25)

Regras de precedência

1. Divisão e multiplicação: / e * 2. Adição e subtração: + e – 3. Operadores de comparação 4. Operador NOT

4. Operador NOT 5. Operador AND 6. Operador OR

Sobreponha regras de precedências usando parênteses.

(26)

Cláusula ORDER BY

• Sintaxe:

[WHERE conditions]

[ORDER BY order_list [ASC | DESC] ]

– Onde,

• A cláusula ORDER BY define a ordem em que as linhas do resultado são classificadas.

• order_list: especifica as colunas que serão utilizadas para a classificação.

• As palavras ASC e DESC são usadas para especificar se as linhas serão classificadas em ordem ascendente (1...9 a...z) ou descendente (9...1 z...a).

SELECT ename, job, deptno, hiredate FROM emp ORDER BY hiredate;

SELECT ename, deptno, sal FROM emp

(27)

Cláusula ORDER BY (cont.)

• Classificação por apelido de coluna.

SELECT empno, ename, sal*12 salanual FROM emp

ORDER BY salanual;

• Classificação por várias colunas.

SELECT ename, deptno, sal FROM emp

ORDER BY deptno, sal DESC;

• Pode-se classificar por uma coluna que não esteja na lista do SELECT.

(28)

Exercícios

1. Crie uma consulta para exibir o nome e o salário dos funcionários que recebem mais de 2.850.

2. Crie uma consulta para exibir o nome do funcionário e o número do departamento para o funcionário de

número 7566.

3. Modifique o exercício 1 para exibir o nome e o salário 3. Modifique o exercício 1 para exibir o nome e o salário

de todos os funcionários cujos salários não estejam na faixa entre 1.500 e 2.850.

4. Exiba o nome do funcionário, o cargo e a data de admissão dos funcionários admitidos entre 20 de fevereiro de 1981 e 1 de maio de 1981. Ordene a

consulta de modo crescente pela data inicial. Salve a consulta no arquivo slide28.sql . Execute a consulta.

(29)

Exercícios - continuação

5. Exiba o nome do funcionário e o número do departamento de todos os funcionários entre os departamentos 10 e 30 por ordem alfabética de nome.

6. Modifique o exercício 3 para listar o nome e o salário dos funcionários que recebem mais de 1.500 e que estão nos departamentos 10 ou 30. Nomeie as colunas para

Empregado e Salário Mensal, respectivamente.

7. Exiba o nome e o cargo de todos os funcionários que não possuem um gerente.

8. Exiba os nomes de todos os funcionários que possuem uma

‘A’ na terceira letra de seus nomes.

9. Exiba todos os funcionários que possuem duas letras ‘L’ e estão no departamento 30 ou seu gerente seja o 7782.

Salve a consulta no arquivo slide29.sql . Execute a consulta.

(30)

Funções de uma única linha

• Manipulam itens de dados.

• Aceitam argumentos e retornam um valor.

• Agem em cada linha retornada.

• Retornam um resultado por linha.

• Podem modificar o tipo de dados.

• Podem ser aninhadas.

function_name (coluna|expressão, [arg1, arg2, ...])

(31)

Funções de caractere

Funções de

conversão de Funções de manipulação de

conversão de maiúsculas e

minúsculas

LOWER UPPER INITCAP

manipulação de caractere

CONCAT SUBSTR LENGT

INSTR LPAD TRIM

(32)

Objetivos das funções

Função Objetivo

LOWER(coluna|expressão) converte valores alfanuméricos para letras minúsculas.

UPPER(coluna|expressão) converte caractere alfanuméricos para UPPER(coluna|expressão) converte caractere alfanuméricos para

letras maiúsculas.

INITCAP(coluna|expressão) converte a primeira letra de cada palavra para maiúscula e mantém as outras letras minúscula.

CONCAT(coluna|expressão) Concatena o primeiro valor do caractere ao segundo valor do

caractere, equivalente ao operador de concatenação(||).

(33)

Objetivos das funções

SUBSTR(coluna|expressão,m[,n]) Extrai uma string de determinado

tamanho na expressão começando na posição m, até n caracteres depois (n pode ser omitido, aí se pega todos os caracteres até o final da string.

LENGTH(coluna|expressão) Retorna o número de caracteres da LENGTH(coluna|expressão) Retorna o número de caracteres da

expressão

INSTR(coluna|expressão,m) Retorna a posição numérica do caractere nomeado.

LPAD(coluna|expressão,n,’string’) Preenche o campo a esquerda com o valor informado com uma largura total de n posições.

TRIM(anterior|posterior|ambos,trim_ch

aracter FROM trim_source) organiza os caracteres de fim de linha, a partir de uma string de caracteres.

(34)

Exemplos de manipulação de caractere

Função Resultado

LOWER('Curso de SQL Oracle') curso de sql oracle

UPPER('Curso de SQL Oracle') CURSO DE SQL ORACLE INITCAP('Curso de SQL Oracle') Curso De Sql Oracle

CONCAT('bom',' dia') bom dia CONCAT('bom',' dia') bom dia SUBSTR('exemplo',1,4) exem

LENGTH('exemplo') 7

INSTR('exemplo','p') 5

LPAD(sal,10,'*') ******5000

TRIM('s' FROM 'ssmith') mith

(35)

A Tabela DUAL

• Uma tabela “fictícia” utilizada no Oracle para retornar valores de resultados conhecidos.

– Por ex., valores de constantes, expressões não derivadas de uma tabela com dados do usuário.

• A tabela DUAL pertence ao usuário “SYS” e pode ser acessada por todos os usuários.

ser acessada por todos os usuários.

– Ela contém uma coluna, DUMMY, e uma linha com valor X que será populado com o valor de retorno de interesse na seleção.

• Exemplo, selecione a data e hora do servidor:

SELECT

TO_CHAR(SYSDATE, 'DD/MON/YYYY HH24:MI:SS') FROM DUAL;

(36)

Exemplos de manipulação de caractere (cont.)

SELECT LOWER('Curso de SQL Oracle')

"Colocando em Minúsculas" FROM DUAL;

SELECT UPPER('Curso de SQL Oracle') SELECT UPPER('Curso de SQL Oracle')

"Colocando em Maiúsculas" FROM DUAL;

SELECT SUBSTR('exemplo',1,4)FROM DUAL;

SELECT LENGTH('exemplo') FROM DUAL;

(37)

Exemplos de manipulação de caractere (cont.)

SELECT 'O cargo de ' || INITCAP(ename) || ' é de '

|| LOWER(job) AS "Detalhes do Empregado"

FROM emp;

SELECT empno, ename, deptno FROM emp WHERE ename = 'blake’;

SELECT empno, ename, deptno FROM emp WHERE ename = UPPER('blake');

SELECT ename "Nomes que terminam com N"

FROM emp

WHERE SUBSTR(ename, -1, 1) = 'N';

(38)

Funções numéricas

• ROUND(coluna|expressão,n): arredonda a coluna, expressão ou valor para n casas

decimais, ou se n for omitido, nenhuma casa decimal.

– Se n for negativo, os números à esquerda do ponto decimal serão arredondados.

ROUND(45.926, 2) → 45,93

SELECT ROUND(45.926, 2)

"Valor Arredondado" FROM DUAL;

(39)

Funções numéricas

• TRUNC(coluna|expressão,n): trunca a coluna, expressão ou valor para n casas decimais ou se n for omitido, nenhuma casa decimal.

– Se n for negativo, os números à esquerda do ponto decimal serão truncados.

TRUNC (45.926, 2) → 45,92

SELECT TRUNC (45.926, 2)

"Valor Truncado" FROM DUAL;

• MOD(m,n): retorna o resto da divisão de m por n.

MOD (1600, 300) → 100

SELECT MOD(1600,300) "Resto de 1600/300" FROM DUAL;

(40)

Trabalhando com Datas

• O valores de data são armazenados no tipo date. O tipo date armazena data contendo dados como

século, ano, mês, dia, hora, minutos e segundo.

– Podem representar valores entre 1 de Janeiro de 4712 AC e 31 de Dezembro de 4712 DC.

• A data é guardada internamente através de um formato de dados desconhecido para o

utilizador/programador.

– No entanto, é possível alterar o formato de apresentação conforme time zones (fusos horários).

– O formato padrão para a instalação Oracle no território BRAZIL é DD/MM/RR (Dia/Mês/Ano em 2 ou 4 posições).

(41)

Trabalhando com Datas (cont.)

• O Oracle representa as datas segundo o

formato definido em NLS_DATE_FORMAT.

– Pode alterar-se o seu valor temporariamente numa sessão SQLPlus através do comando ALTER

SESSION.

• Para verificar o formato de datas e demais formatos relativos a regionalização da

instalação Oracle, basta executar o comando abaixo na conta system no utilitário SQLPlus:

SHOW PARAMETERS NLS;

(42)

Alterando o formato de datas no SQLPlus

• Execute o utilitário SQLPlus.

• Conecte-se ao banco com o usuário “uniceub”

CONNECT uniceub

• Execute os comandos e observe os resultados.

SHOW PARAMETERS NLS;

SELECT hiredate FROM emp;

ALTER SESSION SET

NLS_DATE_FORMAT = ‘MON-DD-YYYY‘;

SELECT hiredate FROM emp;

(43)

Aritmética com Datas

Operação Resultado Descrição

data + número Data Adiciona um número de dias para uma data

data – número Data Subtrai um número de dias de uma data

Podem ser executadas as seguintes operações:

data

data – data Número de dias Subtrai uma data de outra

data + número/24 Data Adiciona um número de hora para uma data

SELECT ename, (SYSDATE-hiredate)/7 semanas FROM emp

WHERE deptno = 10;

(44)

Funções de Data

Função Descrição

MONTHS_BETWEEN(data1,data2) Retorna o número de meses entre data1 e data2.

ADD_MONTHS(data,n) Adiciona um número n de meses de calendário à data.

NEXT_DAY(data,’dia_semana’) Retorna o valor de data do próximo dia da NEXT_DAY(data,’dia_semana’) Retorna o valor de data do próximo dia da

semana especificado por

dia_semana (‘segunda’, ‘terça’,...). Também se podem usar os números de 1 (Domingo) a 7(Sábado).

LAST_DAY(data) Localiza a data do último dia do mês que contém a data.

SYSDATE Função sem argumentos que devolve a

(45)

Funções de Data (cont.)

Função Descrição

ROUND(data,

[‘DAY’|‘MONTH’|‘YEAR’|‘outro formato*’])

Retorna a data arredondada para a unidade de formato especificada no

segundo argumento. Arredonda a data para o dia ou ao dia da semana ou ao mês ou ao ano.

TRUNC(data, Retorna a data truncada para a unidade de TRUNC(data,

[‘DAY’|‘MONTH’|‘YEAR’|‘outro formato*’])

Retorna a data truncada para a unidade de formato especificada no segundo

argumento.

Trunca a data para o dia ou ao dia da semana ou ao mês ou ao ano.

* Outros formatos de datas serão apresentados mais adiante.

(46)

Usando funções de data

SELECT MONTHS_BETWEEN('31/12/08', SYSDATE)

"Meses para o final do ano" FROM DUAL;

SELECT ADD_MONTHS(SYSDATE, -3)

"Três meses atrás" FROM DUAL;

SELECT NEXT_DAY(SYSDATE,'SEXTA')

"Próxima Sexta-Feira" FROM DUAL;

SELECT LAST_DAY(SYSDATE)

"Ultimo dia deste mês" FROM DUAL;

(47)

Usando funções de data (cont.)

SELECT TRUNC(SYSDATE, 'MM')

"Início do mês" FROM DUAL;

SELECT SELECT

TRUNC(TO_DATE('15/05/2008'),'YEAR')

"Início do ano" FROM DUAL;

SELECT ROUND(TO_DATE('15/05/2008'), 'MONTH') "Início do mês" FROM DUAL;

(48)

Usando funções de data (cont.)

• Recupere as datas dos próximos dias da semana.

SELECT NEXT_DAY(sysdate,'Domingo') "Domingo", NEXT_DAY(sysdate,'Segunda') "Segunda", NEXT_DAY(sysdate,'Terça') "Terça",

NEXT_DAY(sysdate,'Terça') "Terça", NEXT_DAY(sysdate,'Quarta')"Quarta", NEXT_DAY(sysdate,'Quinta')"Quinta", NEXT_DAY(sysdate,'Sexta') "Sexta", NEXT_DAY(sysdate,'Sábado‘)"Sábado"

FROM DUAL;

(49)

Usando funções de data (cont.)

• Exemplo:

– Compare as datas de admissão de todos os

funcionários contratados em 1982. Exiba o número do funcionário, a data de admissão e o mês de início usando as funções ROUND e TRUNC.

usando as funções ROUND e TRUNC.

(50)

Funções de conversão

Conversão de

tipo de dados

Conversão implícita de tipo de dados

Conversão

explícita de

tipo de dados

(51)

Conversão implícita de tipos de dados

• Para atribuições, o servidor Oracle pode converter automaticamente o seguinte:

De Para

VARCHAR2 ou CHAR NUMBER

VARCHAR2 ou CHAR DATE

NUMBER VARCHAR2

DATE VARCHAR2

(52)

Conversão implícita de tipos de dados (cont.)

• Para avaliação da expressão, o servidor Oracle pode converter automaticamente o seguinte:

De Para

VARCHAR2 ou CHAR NUMBER

VARCHAR2 ou CHAR DATE

Conversões de CHAR para NUMBER ocorrem somente se a string de caractere representar um número válido. Conversões de CHAR para DATE ocorrem somente se a string de caractere possuir o formato default da instalação Oracle (no nosso caso, DD/MM/RR.)

(53)

Conversão explícita de tipo de dados

TO_NUMBER TO_DATE

caractere

número data

TO_CHAR TO_CHAR

(54)

Conversão explícita de tipo de dados (cont.)

TO_CHAR(número|data, [ fmt],[nlsparams])

Converte um valor de número ou data para uma string de caractere VARCHAR2 com modelo de formato fmt (que será descrito mais adiante) No caso de um número, o parâmetro nlsparams especifica o caractere decimal, separador de grupo, símbolo de moeda.

grupo, símbolo de moeda.

No caso de uma data, o parâmetro nlsparams especifica o idioma no qual os nomes de dias, meses e abreviação retornam.

TO_NUMBER(carac,[fmt], [nlsparams])

Converte uma string de caractere contendo

dígitos para um número no formato especificado pelo modelo de formato opcional fmt.

O parâmetro nlsparams tem a mesma finalidade de TO_CHAR para números.

(55)

Conversão explícita de tipo de dados (cont.)

TO_DATE(carac,[fmt], [nlsparams])

Converte uma string de caractere representando uma data para um valor de data de acordo com o fmt especificado.

fmt especificado.

O parâmetro nlsparams possui a mesma finalidade na função TO_CHAR para datas.

(56)

Função TO_CHAR com datas

TO_CHAR (data, ‘fmt’)

• O modelo de formato:

– Deve estar entre aspas e fazer distinção entre – Deve estar entre aspas e fazer distinção entre

maiúsculas e minúsculas.

– Pode incluir qualquer elemento de formato de data válido.

– Tem um elemento fm para remover espaços preenchidos ou suprimir zeros à esquerda.

– É separado do valor de data por uma vírgula.

(57)

Elementos de formatos de data

Elemento Descrição

DDD ou DD ou D Dia do ano, mês ou semana

DAY Dia da semana (extenso)

MM Número do mês

MON Nome abreviado do mês

MONTH Nome por extenso do mês

YYYY, YYY, YY, Y Representar o ano com 4, 3, 2 ou 1 dígitos respectivamente

HH ou HH12 ou HH24 Horas do dia ou hora (1 a 12) ou hora (0 a 23)

MI Minutos

SS Segundos

SSSSS Segundos depois da meia-noite

- / , . ; : "texto” Pontuação ou texto entre aspas

(58)

Exemplo de uso do fmt com datas

SELECT ename,

TO_CHAR(hiredate, 'fmDD "de" MONTH

"de" YYYY') "Data de Admissão"

FROM emp;

SELECT TO_CHAR(SYSDATE, 'DD/MM/YY "às"

HH24:MM:SS"hs"') "Hora e Data do Sistema" FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'Day, DD "de"

Month "de YYYY') "Data com Dia da Semana" FROM DUAL;

(59)

Função TO_CHAR com números

TO_CHAR (número, ‘fmt’)

• Use estes formatos com a função TO_CHAR para exibir um valor de número como um caractere:

Elemento Descrição Exemplo Resultado

9 Um símbolo 9 para cada algarismo 999999 1234 9 Um símbolo 9 para cada algarismo

significativo a representar antes ou depois da vírgula

999999 1234

0 Exibe zeros à esquerda 0999999 001234

$ Sinal de dólar flutuante $999999 $1234

L Símbolo da moeda local flutuante L999999 FF1234 . Ponto decimal na posição

especificada

999999.99 1234.00

(60)

Usando a função

TO_CHAR com números

SELECT TO_CHAR(sal,'$99,999') Salario FROM emp

WHERE ename = 'SCOTT';

SELECT TO_CHAR(-1000000,'L99999,99MI') SELECT TO_CHAR(-1000000,'L99999,99MI')

"Valor Negativo na Moeda Local"

FROM DUAL;

(61)

Usando as funções

TO_NUMBER e TO_DATE

SELECT TO_NUMBER('1210.73', '9999.99') FROM DUAL;

SELECT ename "Contratados no Mês de Maio"

FROM EMP FROM EMP WHERE

WHERE TO_NUMBER(TO_CHAR(HIREDATE,'MM'))= 05;

SELECT TO_DATE('24.09.2005', 'DD/MM/YY')

"Data"

FROM DUAL;

(62)

Formato de Data RR

• O formato de data RR é similar ao elemento YY, porém ele permite especificar séculos diferentes.

Se o ano de dois dígitos fornecido for:

0-49 50-99

Se os dois Data de retorno – século Data de retorno – século Se os dois

dígitos final do ano atual forem

0-49 Data de retorno – século atual

Data de retorno – século seguinte

50-99 Data de retorno – século anterior

Data de retorno – século atual

Ano Atual Data Fornecida Interpretada RR Interpretada YY

1994 27/OCT/95 1995 1995

1994 27/OCT/17 2017 1917

(63)

Função NVL

• Converte nulo para um valor real.

• Sintaxe:

NVL(expr1, expr2) Onde,

expr1 é o valor de origem ou expressão que pode ser nulo.

expr2 é o valor de destino para a conversão de nulo.

• Os tipos de dados que podem ser usados são data, caractere e número.

Tipo de Dados Exemplo de Conversão

NUMBER NVL(comm,0)

DATE NVL(hiredate,'01-JAN-97')

CHAR ou VARCHAR2 NVL(job,‘Sem cargo ainda')

(64)

Função DECODE

• Facilita pesquisas condicionais realizando o trabalho de uma instrução case ou if-then-else.

DECODE (col/exp, pesquisa1, resultado1

[, pesquisa2, resultado2,...,]

[, default])

– O primeiro parâmetro é comparado com pesquisa1. Se for igual é devolvido o valor de resultado1. Caso contrário é comparado com pesquisa2 e assim sucessivamente. Se não for igual a nenhum dos os valores de pesquisa então é devolvido o valor default.

(65)

Usando as funções NVL e DECODE

SELECT ename, sal, comm, (sal*12)+NVL(comm,0) FROM emp;

SELECT ename, job, sal, SELECT ename, job, sal,

DECODE (job, 'ANALYST', sal*1.1, 'CLERK', sal*1.15, 'MANAGER', sal*1.2,

sal)

"Novo Salário"

FROM emp;

(66)

Aninhando funções

• As funções de uma única linha podem ser aninhadas em qualquer nível.

• Funções aninhadas são avaliadas a partir do nível mais interno para o nível mais externo.

F3 ( F2 ( F1 (col, arg1), arg2), arg3) F3 ( F2 ( F1 (col, arg1), arg2), arg3)

Etapa1 = resultado 1 Etapa2 = resultado 2 Etapa3 = resultado 3

(67)

Exercícios

1. Crie uma consulta para exibir a data atual. Coloque um apelido na coluna Date.

2. Exiba o número do funcionário, o nome, o salário e o aumento salarial de 15% expresso com número inteiro. Coloque um apelido na coluna como Novo Salario. Salve a instrução em um arquivo nomeado slide67.sql.

Execute a consulta.

3. Modifique a consulta do arquivo slide67.sql para adicionar uma coluna que subtrairá o salário antigo do novo.Coloque um apelido nesta nova que subtrairá o salário antigo do novo.Coloque um apelido nesta nova coluna denominado aumento.

4. Para cada funcionário exiba o nome do mesmo e calcule o número de meses entre hoje e as sua datas de admissão. Coloque um apelido na coluna denominado MesesTrabalhados. Ordene os resultados por

número de meses trabalhados. Arredonde para cima o número de meses para o número inteiro mais próximo.

5. Crie uma consulta que exibirá o nome do funcionário com a primeira letra maiúscula e todas as outras minúsculas, bem como o tamanho de seus nomes. Faça isto para todos os funcionários cujo nome começa com J, A ou M. Forneça a cada coluna um rótulo apropriado.

(68)

Inserindo dados - a Instrução INSERT

• Adicione novas linhas em uma tabela usando a instrução INSERT.

• Sintaxe:

INSERT INTO INSERT INTO

[schema.] table [t_alias]

(column, column,...)

VALUES ([value|expr, value|expr,...])

• Somente uma linha é inserida por vez com esta sintaxe.

(69)

Inserindo novas linhas

• Insere uma nova linha contendo valores para cada coluna.

• A listagem dos nomes da colunas na cláusula INSERT é facultativo desde que:

– Os valores mencionados a seguir à cláusula VALUE S estejam na mesma ordem das colunas da tabela

na mesma ordem das colunas da tabela

– Todas os valores de colunas estejam sendo provisionados no INSERT

• Coloque os valores de data e caractere entres aspas simples.

• Exemplo:

INSERT INTO dept (deptno, dname, loc) VALUES (50, 'DEVELOPMENT', 'DETROIT');

(70)

Exemplos de inserção de linhas

• Inserção de um registro sem especificar os nomes das colunas (produz omesmo resultado que o

exemplo anterior):

INSERT INTO dept

VALUES (50, 'DEVELOPMENT', 'DETROIT');

• Inserção de um registro sem incluir todos os dados e alterando a ordem de algumas colunas (produz o mesmo resultado que os dois exemplos anteriores):

INSERT INTO dept (dname, loc, deptno)

(71)

Inserindo valores nulos

• Omitindo a coluna da lista de colunas:

INSERT INTO dept (deptno, dname ) VALUES (60, 'MIS');

VALUES (60, 'MIS');

• Especificando a palavra chave NULL

INSERT INTO dept

VALUES (70, 'FINANCE', NULL);

(72)

Exemplos de inserção de linhas (cont.)

• Inserindo valores especiais

INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)

VALUES (7196, 'GREEN', 'SALESMAN‘,7782, SYSDATE, 2000, NULL, 10);

SYSDATE, 2000, NULL, 10);

• Inserindo valores específicos de data:

INSERT INTO emp

VALUES (2296,'AROMANO','SALESMAN',7782, TO_DATE('3/FEV/1997','DD/MM/RR'),1300,

NULL, 10);

(73)

Inserindo dados

a partir de outra tabela

• Crie a instrução INSERT com uma subconsulta.

• Não use a cláusula VALUES.

• Faça a correspondência do número de colunas na cláusula INSERT com o número de colunas na

subconsulta.

• Exemplo:

– Crie uma nova tabela denominada Gerentes com os campos (id:number(4); nome:varchar2(10); salario(7,2);

data_admissao:date) – Execute o comando

INSERT INTO gerentes(id, nome, salario, data_admissao) SELECT empno, ename, sal, hiredate

FROM emp

WHERE job = 'MANAGER';

(74)

Alterando dados - a Instrução UPDATE

• Modifique linhas existentes com a instrução UPDATE.

• Sintaxe:

UPDATE [schema.] table [t_alias]

SET column = value|expr| (subquery)

[, column = value|expr| (subquery),...]

[WHERE conditions]

• Atualize mais de uma linha por vez, se necessário.

(75)

Exemplo de atualização de linhas.

• Aumenta o salário de todos os empregados em 10%:

UPDATE emp

SET sal = sal * 1.1;

• Aumenta o salário de todos os empregados do departamento 20 em 10%:

departamento 20 em 10%:

UPDATE emp

SET sal = sal * 1.1 WHERE deptno = 20;

• Muda o salário e a função de ‘MARTIN’:

UPDATE emp

SET sal = sal * 2, job = 'MANAGER' WHERE ename = 'MARTIN';

(76)

Exemplo de atualização de linhas (cont.)

• Atualizando com subconsulta de várias colunas

– Colocando o empregado de número 7698 no mesmo

cargo e departamento que o empregado de número 7499

UPDATE emp

SET (job, deptno) =

(SELECT job, deptno FROM emp WHERE empno = 7499)

WHERE empno = 7698;

• A subconsulta pode ser realizada com base em dados de outra tabela.

(77)

Apagando dados – a instrução DELETE

• Removendo-se linhas existentes de uma tabela usando a instrução DELETE.

• Sintaxe

DELETE [FROM] [schema.] table [t_alias]

[WHERE conditions]

• Apagando todas as linhas de uma tabela:

DELETE FROM empregados;

• Apagando todas as linhas de acordo com uma condição específica:

DELETE FROM EMPREGADOS WHERE JOB = 'MANAGER';

(78)

Exemplos de remoção de dados

• Apagando a partir de outra tabela

DELETE FROM empregados WHERE deptno =

(SELECT deptno FROM dept WHERE dname ='SALES');

WHERE dname ='SALES');

• Removendo todos os funcionários que iniciaram após 1 de janeiro de 1997.

DELETE FROM emp WHERE hiredate >

TO_DATE('01.01.1997', 'DD.MM.YYYY');

(79)

Restrições de Integridade

• Se tentar inserir, atualizar ou remover um

registro com um valor vinculado a uma restrição de integridade, o Oracle emitirá uma mensagem de erro não permitindo a execução da operação.

• Exemplo:

UPDATE emp

SET deptno = 55

WHERE deptno = 10;

(80)

Exercícios

• Serão dados em listas à parte.