Upload
wanderson-senra
View
218
Download
0
Embed Size (px)
Citation preview
7/25/2019 PBD - Parte 4
1/281
Projeto de Banco de DadosInstrues DML
Prof. Wanderson Senra [email protected]
7/25/2019 PBD - Parte 4
2/281
Introduo
7/25/2019 PBD - Parte 4
3/281
Usando SQL para Consultar Seu Banco de Dados
SQL (Structured query language) :
A linguagem ANSI padro para a operao de bancosde dados relacionais
Eficiente, fcil de aprender e de usar
Funcionalmente completa (Com SQL, voc podedefinir, recuperar e manipular dados nas tabelas)
SELECT department_name
FROM departments;Oracleaplicativos
7/25/2019 PBD - Parte 4
4/281
Instrues SQL
SELECT
INSERTUPDATEDELETEMERGE
CREATE
ALTERDROPRENAMETRUNCATECOMMENT
GRANT
REVOKE
COMMITROLLBACKSAVEPOINT
Linguagem DML
Linguagem DDL
Controle de transao
Linguagem DCL
7/25/2019 PBD - Parte 4
5/281
Ambientes de Desenvolvimento para SQL
H dois ambientes de desenvolvimento:
A principal ferramenta o Oracle SQL Developer.
A interface de linha de comando SQL*Plus tambmpoder ser usada.
SQL Developer SQL*Plus
7/25/2019 PBD - Parte 4
6/281
Esquema de Human Resources (HR)
DEPARTMENTSdepartment_iddepartment_namemanager_idlocation_id
LOCATIONlocation_idstreet_addresspostal_codecidadestate_provincecountry_id
COUNTRYcountry_idcountry_nameregion_id
REGIONregion_idregion_name
EMPLOYEESemployee_idfirst_namelast_nameemailphone_numberhire_date
job_idsalriocommission_pctmanager_iddepartment_id
JOBSjob_id
job_titlemin_salarymax_salary
JOB_HISTORYemployee_id
start_dateend_datejob_iddepartment_id
7/25/2019 PBD - Parte 4
7/281
Recuperando
dados por meioda instruo SQLSELECT
7/25/2019 PBD - Parte 4
8/281
Agenda de Lies
Instruo SELECT bsica Expresses aritmticas e valores NULL na
instruo SELECT
Apelidos de colunas Uso de operador de concatenao, strings de
caracteres de literal, operador de cotaoalternativo e palavra-chave DISTINCT
Comando DESCRIBE
7/25/2019 PBD - Parte 4
9/281
Recursos das instrues SQL SELECT
SeleoProjeo
Tabela 1 Tabela 2
Tabela 1Tabela 1
Join
7/25/2019 PBD - Parte 4
10/281
Instruo SELECT bsica
SELECT identifica as colunas a serem exibidas.
FROM identifica a tabela contendo aquelascolunas.
SELECT *|{[DISTINCT] column|expression [alias],...}FROM table;
7/25/2019 PBD - Parte 4
11/281
Selecionando todas as colunas
SELECT *FROM departments;
7/25/2019 PBD - Parte 4
12/281
Selecionando colunas especficas
SELECT department_id, location_idFROM departments;
7/25/2019 PBD - Parte 4
13/281
Criando instrues SQL
As instrues SQL no fazem distino entre maisculas eminsculas.
As instrues SQL podem ser inseridas em uma ou mais linhas.
As palavras-chave no podem ser abreviadas nem divididas naslinhas.
As clusulas geralmente so colocadas em linhas separadas.
Os recuos so usados para melhorar a legibilidade.
No SQL Developer, as instrues SQL podem ser, opcionalmente,terminadas com um ponto-e-vrgula (;).
Os pontos e vrgulas so necessrios quando voc executa vrias
instrues SQL. No SQL*Plus, voc deve finalizar cada instruo SQL com um
ponto-e-vrgula (;).
7/25/2019 PBD - Parte 4
14/281
Agenda de Lies
Instruo SELECT bsica Expresses aritmticas e valores NULL na
instruo SELECT
Apelidos de colunas Uso de operador de concatenao, strings de
caracteres de literal, operador de cotaoalternativo e palavra-chave DISTINCT
Comando DESCRIBE
7/25/2019 PBD - Parte 4
15/281
Expresses aritmticas
Crie expresses com dados numricos e data
usando operadores aritmticos.
Multiplicao*
Diviso/
Subtrao-Adio+
DescrioOperador
7/25/2019 PBD - Parte 4
16/281
SELECT last_name, salary, salary + 300FROM employees;
Usando operadores aritmticos
7/25/2019 PBD - Parte 4
17/281
?
?
SELECT last_name, salary, 12*salary+100FROM employees;
Precedncia de operadores
SELECT last_name, salary, 12*(salary+100)FROM employees;
1
2
7/25/2019 PBD - Parte 4
18/281
Definindo um valor nulo
Nulo um valor indisponvel, no atribudo,
desconhecido ou no aplicvel. Nulo no o mesmo que zero ou um espao em branco.
SELECT last_name, job_id, salary, commission_pctFROM employees;
7/25/2019 PBD - Parte 4
19/281
SELECT last_name, 12*salary*commission_pct
FROM employees;
Valores nulos em expresses aritmticas
Expresses aritmticas contendo uma
avaliao de valor nulo para nulo.
7/25/2019 PBD - Parte 4
20/281
Agenda de Lies
Instruo SELECT bsica Expresses aritmticas e valores NULL na
instruo SELECT
Apelidos de colunas Uso de operador de concatenao, strings de
caracteres de literal, operador de cotaoalternativo e palavra-chave DISTINCT
Comando DESCRIBE
7/25/2019 PBD - Parte 4
21/281
Definindo um apelido de coluna
Um apelido de coluna:
Renomeia um cabealho de coluna
til com clculos
Vem imediatamente aps o nome da coluna
(tambm pode existir uma palavra-chave opcionalAS entre o nome da coluna e o apelido).
Exige aspas duplas se contiver espaos ou
caracteres especiais, ou se fizer distino entremaisculas e minsculas
7/25/2019 PBD - Parte 4
22/281
Usando apelidos de coluna
SELECT last_name "Name" , salary*12 "Annual Salary"FROM employees;
SELECT last_name AS name, commission_pct commFROM employees;
7/25/2019 PBD - Parte 4
23/281
Agenda de Lies
Instruo SELECT bsica Expresses Aritmticas e valores NULL na
instruo SELECT
Apelidos de colunas Uso de operador de concatenao, strings de
caracteres de literal, operador de cotaoalternativo e palavra-chave DISTINCT
Comando DESCRIBE
7/25/2019 PBD - Parte 4
24/281
Operador de concatenao
Um operador de concatenao:
Vincula colunas ou strings de caracteres a outrascolunas
representado por duas barras verticais (||)
Cria uma coluna resultante que uma expresso decaractere
SELECT last_name||job_id AS "Employees"FROM employees;
7/25/2019 PBD - Parte 4
25/281
Strings de caracteres de literal
Um literal um caractere, um nmero ouuma data que est includa na instruoSELECT.
Os valores de data e literal de caracteredevem ser colocados entre aspas simples.
Cada string de caracteres emitida uma
vez para cada linha retornada.
d d d l l
7/25/2019 PBD - Parte 4
26/281
Usando strings de caracteres de literal
SELECT last_name ||' is a '||job_idAS "Employee Details"
FROM employees;
Li h d li d
7/25/2019 PBD - Parte 4
27/281
Linhas duplicadas
A exibio padro de consultas de todas as linhas,
incluindo as linhas duplicadas.
SELECT department_id
FROM employees;
SELECT DISTINCT department_id
FROM employees;
1 2
A d d Li
7/25/2019 PBD - Parte 4
28/281
Agenda de Lies
Instruo SELECT bsica Expresses aritmticas e valores NULL na
instruo SELECT
Apelidos de colunas Uso de operador de concatenao, strings de
caracteres de literal, operador de cotaoalternativo e palavra-chave DISTINCT
Comando DESCRIBE
E ibi d E t t d T b l
7/25/2019 PBD - Parte 4
29/281
Exibindo a Estrutura da Tabela
Use o comando DESCRIBE para exibir a estrutura de
uma tabela. Ou selecione a tabela na rvore Connections e use a
guia Columns para visualizar a estrutura da tabela.
DESC[RIBE] tablename
Usando o comando DESCRIBE
7/25/2019 PBD - Parte 4
30/281
Usando o comando DESCRIBE
DESCRIBE employees
7/25/2019 PBD - Parte 4
31/281
Restringindo e
classificando
dados
Agenda de Lies
7/25/2019 PBD - Parte 4
32/281
Agenda de Lies
Limitando linhas com: A clusulaWHERE
Condies de comparao usando as condies =,
7/25/2019 PBD - Parte 4
33/281
Limitando linhas com uma seleo
recuperar todos osfuncionrios dodepartamento 90
EMPLOYEES
Limitando as linhas selecionadas
7/25/2019 PBD - Parte 4
34/281
Limitando as linhas selecionadas
Restrinja as linhas retornadas usando a clusulaWHERE:
A clusula WHERE vem aps a clusula FROM.
SELECT *|{[DISTINCT] column|expression [alias],...}FROM table[WHERE condition(s)];
7/25/2019 PBD - Parte 4
35/281
SELECT employee_id, last_name, job_id, department_idFROM employees
WHERE department_id =90;
Usando a clusula WHERE
St i d t d t
7/25/2019 PBD - Parte 4
36/281
SELECT last_name, job_id, department_id
FROM employeesWHERE last_name = 'Whalen' ;
Strings de caracteres e datas
As strings de caracteres e os valores de datas so
inseridos entre aspas simples. Os valores de caracteres fazem distino entre
maisculas e minsculas, e os valores de datasfazem distino de formato.
O formato padro de exibio de data DD-MON-RR.
SELECT last_nameFROM employees
WHERE hire_date = '17-FEB-96' ;
Operadores de comparao
7/25/2019 PBD - Parte 4
37/281
Operadores de comparao
Diferente de
Entre dois valores (inclusive)BETWEEN...AND...
Corresponde a qualquer lista de
valores
IN(set)
Corresponde a um padro de
caracteres
LIKE
um valor nuloIS NULL
Menor que
Igual a=
SignificadoOperador
U d d d
7/25/2019 PBD - Parte 4
38/281
SELECT last_name, salaryFROM employees
WHERE salary
7/25/2019 PBD - Parte 4
39/281
SELECT last_name, salaryFROM employees
WHERE salary BETWEEN 2500 AND 3500 ;
Condies de intervalo usando o operador BETWEEN
Use o operador BETWEEN para exibir as linhas com
base em um intervalo de valores:
Limite inferior Limite superior
Condio de associao usando o operador IN
7/25/2019 PBD - Parte 4
40/281
SELECT employee_id, last_name, salary, manager_idFROM employees
WHERE manager_id IN (100, 101, 201) ;
Condio de associao usando o operador IN
Use o operador IN para testar os valores de uma
lista:
Correspondncia de padres usando o operador LIKE
7/25/2019 PBD - Parte 4
41/281
SELECT first_nameFROM employees
WHERE first_name LIKE 'S%' ;
Correspondncia de padres usando o operador LIKE
Use o operador LIKE para executar pesquisas
com caracteres curinga de valores vlidos de stringde pesquisa.
As condies de pesquisa podem conter
caracteres literais ou nmeros:% denota zero ou muitos caracteres.
_ denota um caractere.
Combinando caracteres curinga
7/25/2019 PBD - Parte 4
42/281
Combinando caracteres curinga
possvel combinar dois caracteres curingas (%, _)
com caracteres literais para correspondncia depadres:
SELECT last_name
FROM employeesWHERE last_name LIKE '_o%' ;
Usando as condies NULL
7/25/2019 PBD - Parte 4
43/281
SELECT last_name, manager_idFROM employees
WHERE manager_id IS NULL ;
Usando as condies NULL
Teste os nulos com o operador IS NULL.
Definindo condies usando os operadores lgicos
7/25/2019 PBD - Parte 4
44/281
Definindo condies usando os operadores lgicos
Retornar TRUE se a condio for falsaNOT
Retornar TRUE se uma das condies
componentes for verdadeira
OR
Retornar TRUE se ambas as condiescomponentes forem verdadeiras
AND
SignificadoOperador
Usando o operador AND
7/25/2019 PBD - Parte 4
45/281
SELECT employee_id, last_name, job_id, salaryFROM employees
WHERE salary >= 10000AND job_id LIKE '%MAN%' ;
p
AND requer que ambas as condies
componentes sejam verdadeiras:
Usando o operador OR
7/25/2019 PBD - Parte 4
46/281
SELECT employee_id, last_name, job_id, salaryFROM employees
WHERE salary >= 10000
OR job_id LIKE '%MAN%' ;
p
OR requer que uma condio componente seja
verdadeira:
Usando o operador NOT
7/25/2019 PBD - Parte 4
47/281
SELECT last_name, job_idFROM employees
WHERE job_idNOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ;
Usando o operador NOT
Agenda de Lies
7/25/2019 PBD - Parte 4
48/281
Agenda de Lies
Limitando linhas com: A clusula WHERE
Condies de comparao usando os operadores =,
7/25/2019 PBD - Parte 4
49/281
Regras de precedncia
possvel usar parnteses para sobreporas regras de precedncia.
Diferente de6
Condio lgica NOT7
Condio lgica AND8
Condio lgica OR9
IS [NOT] NULL, LIKE, [NOT] IN4
[NOT] BETWEEN5
Condies de comparao3
Operador de concatenao2
Operadores aritmticos1
SignificadoOperador
Regras de precedncia
7/25/2019 PBD - Parte 4
50/281
SELECT last_name, job_id, salaryFROM employees
WHERE job_id = 'SA_REP'OR job_id = 'AD_PRES'
AND salary > 15000;
Regras de precedncia
SELECT last_name, job_id, salaryFROM employees
WHERE (job_id = 'SA_REP'OR job_id = 'AD_PRES')
AND salary > 15000;
1
2
Agenda de Lies
7/25/2019 PBD - Parte 4
51/281
Agenda de Lies
Limitando linhas com: A clusula WHERE
Condies de comparao usando os operadores =,
7/25/2019 PBD - Parte 4
52/281
Classificar as linhas recuperadas com a clusula
ORDER BY:ASC: ordem ascendente, padro
DESC: ordem descendente
A clusula ORDER BY vem por ltimo na instruoSELECT:SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY hire_date ;
Classificando
7/25/2019 PBD - Parte 4
53/281
Classificando
Classificando na ordem descendente:
Classificando por apelidos de coluna:
SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY hire_date DESC ; 1
SELECT employee_id, last_name, salary*12 annsalFROM employees
ORDER BY annsal ;
2
Classificando
7/25/2019 PBD - Parte 4
54/281
Classificando ao usar a posio numrica da
coluna:
Classificando por vrias colunas:
SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY 3; 3
SELECT last_name, department_id, salaryFROM employeesORDER BY department_id, salary DESC;
4
Agenda de Lies
7/25/2019 PBD - Parte 4
55/281
g
Limitando linhas com: A clusula WHERE
Condies de comparao usando os operadores =,
7/25/2019 PBD - Parte 4
56/281
Usando funesde uma nica
linha para
personalizar asada
Agenda de Lies
7/25/2019 PBD - Parte 4
57/281
Funes SQL de uma nica linha Funes de caracteres
Funes de nmeros
Trabalhando com datas Funes de datas
Funes SQL
7/25/2019 PBD - Parte 4
58/281
Funo
Entrada
arg 1
arg 2
arg n
A funo executa a ao
Sada
Resultado
Dois tipos de funes SQL
7/25/2019 PBD - Parte 4
59/281
Funes defunes
Funes defunes
Retornam um resultadopor linha
Retornam um resultadopor um conjunto de linhas
Funes
Funes de uma nica linha
7/25/2019 PBD - Parte 4
60/281
Funes de uma nica linha:
Manipulam itens de dados Aceitam argumentos e retornam um valor
Atuam em cada linha retornada
Retornam um resultado por linha Podem modificar o tipo de dados
Podem ser aninhadas
Aceitam argumentos que podem ser uma colunaou uma expresso
function_name [(arg1, arg2,...)]
Funes de uma nica linha
7/25/2019 PBD - Parte 4
61/281
Converso
Caracteres
Nmero
Data
Geral Funes
Agenda de Lies
7/25/2019 PBD - Parte 4
62/281
Funes SQL de uma nica linha Funes de caracteres
Funes de nmeros
Trabalhando com datas Funes de datas
Funes de caracteres
7/25/2019 PBD - Parte 4
63/281
Funes
LOWER
UPPER
INITCAP
CONCAT
SUBSTR
LENGTHINSTR
LPAD | RPAD
TRIM
REPLACE
Funes deConverso
Funes deManipulao
Funes de converso de capitalizao
7/25/2019 PBD - Parte 4
64/281
Essas funes convertem a capitalizao dasstrings de caracteres:
sql courseLOWER('SQL Course')
Sql CourseINITCAP('SQL Course')
SQL COURSEUPPER('SQL Course')
ResultadoFuno
Usando funes de converso de capitalizao
7/25/2019 PBD - Parte 4
65/281
SELECT employee_id, last_name, department_idFROM employees
WHERE LOWER(last_name) = 'higgins';
Exibe o nmero de funcionrio, o nome e o nmero
de departamento do funcionrio Higgins:
SELECT employee_id, last_name, department_idFROM employees
WHERE last_name = 'higgins';
Funes de manipulao de caracteres
7/25/2019 PBD - Parte 4
66/281
Essas funes manipulam as strings de caracteres:
BLACK and BLUEREPLACE('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)
ResultadoFuno
Usando as funes de manipulao de caracteres
7/25/2019 PBD - Parte 4
67/281
SELECT employee_id, CONCAT(first_name, last_name) NAME,
job_id, LENGTH (last_name),
INSTR(last_name, 'a') "Contains 'a'?"
FROM employees
WHERE SUBSTR(job_id, 4) = 'REP';
2
31 2
1
3
Agenda de Lies
7/25/2019 PBD - Parte 4
68/281
Funes SQL de uma nica linha Funes de caracteres
Funes de nmeros
Trabalhando com datas Funes de datas
Funes de nmeros
7/25/2019 PBD - Parte 4
69/281
ROUND: arredonda o valor para um decimal especfico
TRUNC: trunca o valor para um decimal especficoMOD: retorna o residual da diviso
100MOD(1600, 300)
45.93ROUND(45.926, 2)
45.92TRUNC(45.926, 2)
ResultadoFuno
Usando a funo ROUND
7/25/2019 PBD - Parte 4
70/281
SELECT ROUND(45.923,2), ROUND(45.923,0),ROUND(45.923,-1)
FROM DUAL;
DUAL uma tabela fictcia que pode ser usada
para exibir resultados de funes e clculos.
3
31 2
1 2
Usando a funo TRUNC
7/25/2019 PBD - Parte 4
71/281
SELECT TRUNC(45.923,2), TRUNC(45.923),TRUNC(45.923,-1)
FROM DUAL;3
31 2
1 2
Usando a funo MOD
7/25/2019 PBD - Parte 4
72/281
Para todos os funcionrios com o ttulo decargo de Representante de Vendas, calcule oresidual do salrio aps ele ser dividido por5.000.
SELECT last_name, salary, MOD(salary, 5000)FROM employees
WHERE job_id = 'SA_REP';
Agenda de Lies
7/25/2019 PBD - Parte 4
73/281
Funes SQL de uma nica linha Funes de caracteres
Funes de nmeros
Trabalhando com datas Funes de data
Trabalhando com datas
7/25/2019 PBD - Parte 4
74/281
SELECT last_name, hire_dateFROM employees
WHERE hire_date < '01-FEB-88';
O Oracle Database armazena datas em um formatonumrico interno: sculo, ano, ms, dia, horas, minutos
e segundos. O formato de exibio de datas padro DD-MON-RR.
Permite que voc armazene datas do sculo 21 no sculo 20especificando somente os ltimos dois dgitos do ano
Permite que voc armazene datas do sculo 20 nosculo 21 da mesma maneira
Formato de datas RR
7/25/2019 PBD - Parte 4
75/281
Ano atual1995199520012001
27.10.9527.10.1727.10.1727.10.95
1995201720171995
1995191720172095
Se dois
dgitos doano atualforem:
049
049 5099
5099
A data de retorno
ser do sculo atual
A data de retornoser do sculoposterior ao atual
A data de retorno
ser do sculoanterior ao atualA data de retornoser do sculo atual
Se o ano de dois dgitos especificado for:
Formato YYFormato RRData especificadaAno atual
Usando a funo SYSDATE
7/25/2019 PBD - Parte 4
76/281
SYSDATE uma funo que retorna:
Data Horrios
SELECT sysdate
FROM dual;
Aritmtica com datas
7/25/2019 PBD - Parte 4
77/281
Adicione um nmero data ou subtraia umnmero da data de um valor de data resultante.
Subtraia duas datas para localizar o nmero dedias entre essas datas.
Adicione horas a uma data dividindo o nmero dehoras por 24.
Usando operadores aritmticos com datas
7/25/2019 PBD - Parte 4
78/281
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKSFROM employees
WHERE department_id =90;
Agenda de Lies
7/25/2019 PBD - Parte 4
79/281
Funes SQL de uma nica linha Funes de caracteres
Funes de nmeros
Trabalhando com datas Funes de data
Funes de manipulao de datas
7/25/2019 PBD - Parte 4
80/281
Prximo dia da data especificadaNEXT_DAY
ltimo dia do msLAST_DAY
Arredonda dataROUND
Trunca dataTRUNC
Nmero de meses entre duas datasMONTHS_BETWEEN
Adiciona meses do calendrio dataADD_MONTHS
ResultadoFuno
Usando funes de datas
7/25/2019 PBD - Parte 4
81/281
'08-SET-95'NEXT_DAY ('01-SET-95','SEXTA-FEIRA')'28-FEV-95'LAST_DAY ('01-FEV-95')
19.6774194MONTHS_BETWEEN('01-SET-95','11-JAN-94')
'29-FEB-96'ADD_MONTHS (31-JAN-96',1)
ResultadoFuno
Usando as funes ROUND e TRUNC com datas
7/25/2019 PBD - Parte 4
82/281
Considere SYSDATE = '25-JUL-03':
?TRUNC(SYSDATE ,'MS')?TRUNC(SYSDATE ,'ANO')
?ROUND(SYSDATE,'MS')
?ROUND(SYSDATE ,'ANO')
ResultadoFuno
Usando as funes ROUND e TRUNC com datas
7/25/2019 PBD - Parte 4
83/281
Considere SYSDATE = '25-JUL-03':
01-JUL-03TRUNC(SYSDATE ,'MS')01-JAN-03TRUNC(SYSDATE ,'ANO')
01-AGO-03ROUND(SYSDATE,'MS')
01-JAN-04ROUND(SYSDATE ,'ANO')
ResultadoFuno
7/25/2019 PBD - Parte 4
84/281
Usando asfunes de
converso e
expressescondicionais
Agenda de Lies
7/25/2019 PBD - Parte 4
85/281
Converso implcita e explcita de tipo de dados
Funes TO_CHAR, TO_DATE, TO_NUMBER Aninhando funes
Funes gerais:
NVLNVL2
NULLIF
COALESCE
Expresses condicionais:CASE
DECODE
Funes de converso
7/25/2019 PBD - Parte 4
86/281
Conversoexplcita de tipos de dados
Conversoexplcita de tipos de dados
Tipo de dadosexplcita de tipos de dados
Converso implcita de tipo de dados
7/25/2019 PBD - Parte 4
87/281
Nas expresses, o Oracle Server pode converter
automaticamente o seguinte:
NUMBERVARCHAR2 ou CHAR
DATEVARCHAR2 ou CHAR
ParaDe
Converso implcita de tipo de dados
7/25/2019 PBD - Parte 4
88/281
Para a avaliao da expresso, o Oracle Server pode
converter automaticamente o seguinte:
VARCHAR2 ou CHARNUMBER
VARCHAR2 ou CHARDATE
ParaDe
Converso explcita de tipo de dados
7/25/2019 PBD - Parte 4
89/281
NUMBER CHARACTER
TO_CHAR
TO_NUMBER
DATE
TO_CHAR
TO_DATE
Agenda de Lies
7/25/2019 PBD - Parte 4
90/281
Converso implcita e explcita de tipo de dados
Funes TO_CHAR, TO_DATE, TO_NUMBER Aninhando funes
Funes gerais:NVL
NVL2
NULLIF
COALESCE
Expresses condicionais:CASE
DECODE
Usando a funo TO_CHAR com datas
7/25/2019 PBD - Parte 4
91/281
O modelo de formato:
Deve ser inserido entre aspas simples
Faz distino entre maisculas e minsculas
Pode incluir qualquer elemento de formato dedata vlido
Tem um elemento fm para remover espaos embranco ou suprimir zeros esquerda
separado do valor da data por uma vrgula
TO_CHAR(date, 'format_model')
Elementos do modelo de formato de data
7/25/2019 PBD - Parte 4
92/281
Abreviao de trs letras do dia da semanaDY
Nome inteiro do dia da semanaDIA
Valor de dois dgitos para o msMM
Nome inteiro do msMS
Abreviao de trs letras do msMON
Dia numrico do msDD
Ano inteiro em nmerosYYYY
Ano por extenso (em ingls)ANO
ResultadoElemento
Elementos do modelo de formato de data
7/25/2019 PBD - Parte 4
93/281
Os elementos de hora formatam a parte da horada data:
Adicione strings de caracteres, inserindo-as entre
aspas duplas:
Sufixos de nmero mostram os nmeros porextenso:
DD "of" MONTH 12 of OCTOBER
ddspth fourteenth
HH24:MI:SS AM 15:45:32 PM
Usando a funo TO_CHAR com datas
7/25/2019 PBD - Parte 4
94/281
SELECT last_name,TO_CHAR(hire_date, 'fmDD Month YYYY')
AS HIREDATEFROM employees;
Usando a funo TO_CHAR com nmeros
7/25/2019 PBD - Parte 4
95/281
Esses so alguns dos elementos de formato quevoc pode usar com a funo TO_CHAR para exibirum valor de nmero como um caractere:
Imprime um ponto decimal.
Imprime uma vrgula como um indicador de milhares,
Insere um smbolo de dlar flutuante$
Usa o smbolo da moeda local flutuanteL
Representa um nmero9
Fora a exibio de um zero0
ResultadoElemento
TO_CHAR(number, 'format_model')
Usando a funo TO_CHAR com nmeros
7/25/2019 PBD - Parte 4
96/281
SELECT TO_CHAR(salary, '$99,999.00') SALARYFROM employees
WHERE last_name = 'Ernst';
Usando as funes TO_NUMBER e TO_DATE
7/25/2019 PBD - Parte 4
97/281
Converta uma string de caracteres para um formatode nmero usando a funo TO_NUMBER:
Converta uma string de caracteres para um formato
de data usando a funo TO_DATE:
Essas funes possuem um modificador fx. Essemodificador especifica a correspondncia exata parao argumento de caractere e modelo de formato dedata de uma funo TO_DATE.
TO_NUMBER(char[, 'format_model'])
TO_DATE(char[, 'format_model'])
Usando a funo TO_CHAR e TO_DATEcom o formato de data RR
7/25/2019 PBD - Parte 4
98/281
Para localizar funcionrios admitidos antes de
1990, use o formato de data RR, que gera osmesmos resultados, independente de o comandoter sido executado em 1999 ou agora:
SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY')FROM employees
WHERE hire_date < TO_DATE('01-Jan-90','DD-Mon-RR');
Agenda de Lies
7/25/2019 PBD - Parte 4
99/281
Converso implcita e explcita de tipo de dados
Funes TO_CHAR, TO_DATE, TO_NUMBER Aninhando funes
Funes gerais:
NVLNVL2
NULLIF
COALESCE
Expresses condicionais:CASE
DECODE
Aninhando funes
7/25/2019 PBD - Parte 4
100/281
As funes de uma nica linha podem ser
aninhadas em qualquer nvel. As funes aninhadas so avaliadas desde o nvel
mais profundo at o nvel menos profundo.
F3(F2(F1(col,arg1),arg2),arg3)
Etapa 1 = Resultado 1
Etapa 2 = Resultado 2Etapa 3 = Resultado 3
Aninhando Funes: Exemplo 1
7/25/2019 PBD - Parte 4
101/281
SELECT last_name,UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US'))
FROM employeesWHERE department_id =60;
Aninhando Funes: Exemplo 2
7/25/2019 PBD - Parte 4
102/281
SELECT TO_CHAR(ROUND((salary/7), 2),'99G999D99','NLS_NUMERIC_CHARACTERS = '',.'' ')"Formatted Salary"
FROM employees;
Agenda de Lies
7/25/2019 PBD - Parte 4
103/281
Converso implcita e explcita de tipo de dados
Funes TO_CHAR, TO_DATE, TO_NUMBER Aninhando funes
Funes gerais:
NVLNVL2
NULLIF
COALESCE
Expresses condicionais:CASE
DECODE
Funes gerais
7/25/2019 PBD - Parte 4
104/281
As seguintes funes operam com qualquertipo de dados e esto relacionadas ao uso denulos:NVL (expr1, expr2)
NVL2 (expr1, expr2, expr3)
NULLIF (expr1, expr2)
COALESCE (expr1, expr2, ..., exprn)
Funo NVL
7/25/2019 PBD - Parte 4
105/281
Converte um valor nulo para um valor real: Os tipos de dados que podem ser usados so
datas, caracteres e nmeros.
Os tipos de dados devem corresponder:NVL(commission_pct,0)
NVL(hire_date,'01-JAN-97')
NVL(job_id,'No Job Yet')
Usando a funo NVL
7/25/2019 PBD - Parte 4
106/281
SELECT last_name, salary, NVL(commission_pct, 0),(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SALFROM employees;
1
1 2
2
Usando a funo NVL2
7/25/2019 PBD - Parte 4
107/281
SELECT last_name, salary, commission_pct,NVL2(commission_pct,
'SAL+COMM', 'SAL') incomeFROM employees WHERE department_id IN (50, 80);
1 2
21
Usando a funo NULLIF
7/25/2019 PBD - Parte 4
108/281
SELECT first_name, LENGTH(first_name) "expr1",last_name, LENGTH(last_name) "expr2",NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM employees;
1
23
1 2 3
Usando a funo COALESCE
7/25/2019 PBD - Parte 4
109/281
A vantagem da funo COALESCE em relao
NVL que a funo COALESCE pode aceitarvrios valores alternativos.
Se a primeira expresso no for nula, a funoCOALESCE retornar aquela expresso; casocontrrio, ela far um COALESCE das expressesremanescentes.
Usando a funo COALESCE
7/25/2019 PBD - Parte 4
110/281
SELECT last_name, employee_id,
COALESCE(TO_CHAR(commission_pct),TO_CHAR(manager_id),'No commission and no manager')
FROM employees;
Agenda de Lies
7/25/2019 PBD - Parte 4
111/281
Converso implcita e explcita de tipo de dados
Funes TO_CHAR, TO_DATE, TO_NUMBER Aninhando funes
Funes gerais:NVL
NVL2
NULLIF
COALESCE
Expresses condicionais:CASE
DECODE
Expresses condicionais
7/25/2019 PBD - Parte 4
112/281
Fornecem o uso da lgica IF-THEN-ELSE em
uma instruo SQL Usam dois mtodos:
Expresso CASE
FunoDECODE
Expresso CASE
l l d f d
7/25/2019 PBD - Parte 4
113/281
Facilita as consultas condicionais, fazendo otrabalho de uma instruo IF-THEN-ELSE:
CASE exprWHEN comparison_expr1 THEN return_expr1[WHENcomparison_expr2 THENreturn_expr2WHENcomparison_exprn THENreturn_exprn
ELSE else_expr]END
Usando a expresso CASE
7/25/2019 PBD - Parte 4
114/281
SELECT last_name, job_id, salary,CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salaryWHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"FROM employees;
Facilita as consultas condicionais, fazendo o
trabalho de uma instruo IF-THEN-ELSE:
Funo DECODE
F ili l di i i f d b lh
7/25/2019 PBD - Parte 4
115/281
Facilita as consultas condicionais fazendo o trabalhode uma expresso CASE ou uma instruo IF-THEN-ELSE:
DECODE(col|expression, search1, result1
[, search2, result2,...,][, default])
Usando a funoDECODE
7/25/2019 PBD - Parte 4
116/281
SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,'ST_CLERK', 1.15*salary,'SA_REP', 1.20*salary,
salary)REVISED_SALARY
FROM employees;
Usando a funoDECODE
E ib l d i li l d
7/25/2019 PBD - Parte 4
117/281
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_RATEFROM employees
WHERE department_id =80;
Exiba a alquota de imposto aplicvel para cadafuncionrio do departamento 80:
7/25/2019 PBD - Parte 4
118/281
Usando as
funes de
grupo
Agenda de Lies
7/25/2019 PBD - Parte 4
119/281
Funes de grupo:
Tipos e sintaxe Use AVG, SUM, MIN, MAX, COUNT
Use a palavra-chave DISTINCT nas funes degrupo
Valores NULL em uma funo de grupo
Agrupando linhas:
ClusulaGROUP BY
Clusula HAVING
Aninhando funes de grupo
O que so funes de grupo?
A f d j t d
7/25/2019 PBD - Parte 4
120/281
As funes de grupo operam em conjuntos delinhas para fornecer um resultado por grupo.
EMPLOYEES
Salrio mximo databela EMPLOYEES
Tipos de funes de grupo
7/25/2019 PBD - Parte 4
121/281
AVGCOUNT
MAX
MIN
STDDEV
SUM
VARIANCE
Groupfunes
Funes de grupo: Sintaxe
7/25/2019 PBD - Parte 4
122/281
SELECT group_function(column), ...FROM table[WHERE condition][ORDER BY column];
Usando as funes AVG a e SUM
Voc pode usar AVG e SUM para dados numricos
7/25/2019 PBD - Parte 4
123/281
SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employeesWHERE job_id LIKE '%REP%';
Voc pode usar AVG e SUM para dados numricos.
Usando as funes MIN e MAX
Voc pode usar MIN e MAX para tipos de dados
7/25/2019 PBD - Parte 4
124/281
SELECT MIN(hire_date), MAX(hire_date)FROM employees;
Voc pode usar MIN e MAX para tipos de dadosnumricos, de caracteres e de datas.
Usando a funo COUNT
COUNT(*) retorna o nmero de linhas em
7/25/2019 PBD - Parte 4
125/281
COUNT(*) retorna o nmero de linhas emuma tabela:
COUNT(expr) retorna o nmero de linhascom valores que no so nulos para expr:
SELECT COUNT(commission_pct)
FROM employeesWHERE department_id =80;
SELECT COUNT(*)FROM employees
WHERE department_id =50;1
2
Usando a palavra-chave DISTINCT
COUNT(DISTINCT expr) retorna o nmero de
7/25/2019 PBD - Parte 4
126/281
SELECT COUNT(DISTINCT department_id)FROM employees;
COUNT(DISTINCT expr) retorna o nmero devalores no nulos distintos de expr.
Para exibir o nmero de valores distintos dedepartamento na tabela EMPLOYEES:
Funes de grupo e valores nulos
A f d i l l
7/25/2019 PBD - Parte 4
127/281
As funes de grupo ignoram os valores nulos
da coluna:
A funo NVL fora as funes de grupo ainclurem os valores nulos:
SELECT AVG(commission_pct)FROM employees;
SELECT AVG(NVL(commission_pct, 0))
FROM employees;
1
2
Agenda de Lies
7/25/2019 PBD - Parte 4
128/281
Funes de grupo:
Tipos e sintaxe Use AVG, SUM, MIN, MAX, COUNT
Use a palavra-chave DISTINCT nas funes de
grupo Valores NULL em uma funo de grupo
Agrupando linhas:
Clusula GROUP BY Clusula HAVING
Aninhando funes de grupo
Criando grupos de dados
7/25/2019 PBD - Parte 4
129/281
EMPLOYEES
4400
9500
3500
6400
10033
Salrio mdio na tabelaEMPLOYEES decada departamento
Criando grupos de dados: Sintaxe da clusula GROUP BY
d d d l h d b l
7/25/2019 PBD - Parte 4
130/281
Voc pode dividir as linhas de uma tabela em
grupos menores usando a clusula GROUP BY.SELECT column, group_function(column)FROM table[WHERE condition][GROUP BY group_by_expression][ORDER BY column];
Usando a clusula GROUP BY
Todas as colunas da lista SELECT que no
7/25/2019 PBD - Parte 4
131/281
SELECT department_id, AVG(salary)FROM employees
GROUP BY department_id;
Todas as colunas da lista SELECT que noestiverem nas funes de grupo devero estar naclusula GROUP BY.
Usando a clusula GROUP BY
A coluna GROUP BY no precisa estar na lista
7/25/2019 PBD - Parte 4
132/281
A coluna GROUP BY no precisa estar na listaSELECT.
SELECT AVG(salary)FROM employeesGROUP BY department_id;
Agrupando por Mais de Uma Coluna
7/25/2019 PBD - Parte 4
133/281
EMPLOYEES Adicione os salrios na tabela
EMPLOYEES para cada cargoagrupado por departamento.
Usando a clusula GROUP BY em vrias colunas
7/25/2019 PBD - Parte 4
134/281
SELECT department_id, job_id, SUM(salary)FROM employees
WHERE department_id > 40GROUP BY department_id, job_idORDER BY department_id;
Consultas ilegais usando funes de grupo
Qualquer coluna ou expresso da lista SELECT que
7/25/2019 PBD - Parte 4
135/281
Qualquer coluna ou expresso da lista SELECT queno for uma funo de agregao dever estar naclusula GROUP BY:
SELECT department_id, COUNT(last_name)FROM employees;
SELECT department_id, job_id, COUNT(last_name)
FROM employeesGROUP BY department_id;
Uma clusula GROUP BY dever seradicionada contagem dossobrenomes de cadadepartment_id.
Adicione job_idem GROUP BY ouremova a coluna job_idda listaSELECT.
Consultas ilegais usando funes de grupo
Voc no pode usar a clusula WHERE para restringir
7/25/2019 PBD - Parte 4
136/281
Voc no pode usar a clusula WHERE para restringiros grupos.
Use a clusula HAVING para restringir os grupos. Voc no pode usar as funes de grupo na clusula
WHERE.
SELECT department_id, AVG(salary)FROM employeesWHERE AVG(salary) > 8000GROUP BY department_id;
No possvelusar a clusulaWHERE para
restringir grupos
Restringindo resultados de grupo
7/25/2019 PBD - Parte 4
137/281
EMPLOYEES
O salrio mximo pordepartamento quando ele formaior que US$ 10.000
Restringindo os resultados de grupo com a clusula HAVING
Quando voc usa a clusula HAVING o Oracle
7/25/2019 PBD - Parte 4
138/281
SELECT column, group_functionFROM table[WHERE condition][GROUP BY group_by_expression][HAVING group_condition][ORDER BY column];
Quando voc usa a clusula HAVING, o Oracle
Server restringe os grupos da seguinte forma:1. As linhas so agrupadas.
2. A funo de grupo aplicada.
3. Os grupos correspondentes clusulaHAVING
soexibidos.
Usando a clusula HAVING
7/25/2019 PBD - Parte 4
139/281
SELECT department_id, MAX(salary)FROM employeesGROUP BY department_idHAVING MAX(salary)>10000 ;
Usando a clusula HAVING
7/25/2019 PBD - Parte 4
140/281
SELECT job_id, SUM(salary) PAYROLLFROM employees
WHERE job_id NOT LIKE '%REP%'GROUP BY job_idHAVING SUM(salary) > 13000
ORDER BY SUM(salary);
Agenda de Lies
7/25/2019 PBD - Parte 4
141/281
Funes de grupo:
Tipos e sintaxe Use AVG, SUM, MIN, MAX, COUNT
Use a palavra-chave DISTINCT nas funes de
grupo Valores NULL em uma funo de grupo
Agrupando linhas:
Clusula GROUP BY Clusula HAVING
Aninhando funes de grupo
Aninhando funes de grupo
Exibe a mdia salarial mxima:
7/25/2019 PBD - Parte 4
142/281
SELECT MAX(AVG(salary))FROM employeesGROUP BY department_id;
Exibe a mdia salarial mxima:
Prtica
Crie um relatrio para exibir o nmero do gerente e quanto ganha o funcionrio com
7/25/2019 PBD - Parte 4
143/281
o menor salrio que trabalha para esse gerente. Exclua todos os funcionrios cujogerente desconhecido. Exclua quaisquer grupos onde o salrio mnimo seja US$
6.000 ou menos. Organize os resultados por ordem decrescente de salrios.
Prtica
Crie um relatrio para exibir o nmero do gerente e quanto ganha o funcionrio com
7/25/2019 PBD - Parte 4
144/281
p g q go menor salrio que trabalha para esse gerente. Exclua todos os funcionrios cujogerente desconhecido. Exclua quaisquer grupos onde o salrio mnimo seja US$
6.000 ou menos. Organize os resultados por ordem decrescente de salrios.
SELECT manager_id, MIN(salary)
FROM employeesWHERE manager_id IS NOT NULLGROUP BY manager_idHAVING MIN(salary) > 6000ORDER BY MIN(salary) DESC;
7/25/2019 PBD - Parte 4
145/281
Usando join
Agenda de Lies
Tipos de JOINS e sua sintaxe
7/25/2019 PBD - Parte 4
146/281
p JO S
Join natural:
Clusula USING
Clusula ON
Self-join
No-equijoinsOUTERjoin:
LEFT OUTERjoin
RIGHT OUTERjoinFULL OUTERjoin
Produto cartesiano
Join cruzada
Obtendo Dados de Vrias Tabelas
7/25/2019 PBD - Parte 4
147/281
EMPLOYEES DEPARTMENTS
Tipos de Joins
j i f id d
7/25/2019 PBD - Parte 4
148/281
As joins que esto em conformidade com o
padro SQL:1999 so as seguintes: Joins Natural:
Clusula NATURAL JOIN
ClusulaUSING
Clusula ON
OUTERjoins:LEFT OUTER JOIN
RIGHT OUTER JOINFULL OUTER JOIN
Joins cruzadas
Unindo tabelas com a sintaxe SQL:1999
Use uma condio de join para consultar dados de
7/25/2019 PBD - Parte 4
149/281
j pmais de uma tabela:
SELECT table1.column, table2.columnFROM table1[NATURAL JOIN table2] |[JOIN table2USING (column_name)] |
[JOIN table2ON (table1.column_name = table2.column_name)]|[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)]|[CROSS JOIN table2];
Qualificando Nomes de Coluna Ambguos
Use prefixos de tabela para qualificar nomes de
7/25/2019 PBD - Parte 4
150/281
Use prefixos de tabela para qualificar nomes decolunas que estejam presentes em vrias tabelas.
Use prefixos de tabelas para melhorar odesempenho.
Em vez de usar o nome completo da tabela nos
prefixos, use apelidos. Os apelidos de tabela fornecem a uma tabela um
nome mais curto:
Mantm o cdigo SQL menor, usa menos memria
Use apelidos de colunas para distinguir colunasque tm nomes idnticos, mas residem em tabelasdiferentes.
Agenda de Lies
Tipos de JOINS e sua sintaxe
7/25/2019 PBD - Parte 4
151/281
p
Join natural:
Clusula USING
Clusula ON
Self-join
No-equijoinsOUTERjoin:
LEFT OUTERjoin
RIGHT OUTERjoinFULL OUTERjoin
Produto cartesiano
Join cruzada
Criando joins natural
A l l NATURAL JOIN t b
7/25/2019 PBD - Parte 4
152/281
A clusula NATURAL JOIN tem como base
todas as colunas das duas tabelas que tm omesmo nome.
Ela seleciona as linhas a partir de duas tabelas
que tm valores iguais em todas as colunascorrespondentes.
Se as colunas que tm os mesmos nomes
tiverem tipos de dados diferentes, serretornado um erro.
Recuperando registros com joins natural
7/25/2019 PBD - Parte 4
153/281
SELECT department_id, department_name,location_id, city
FROM departmentsNATURAL JOIN locations ;
Criando joins com a clusula USING
S i l ti
7/25/2019 PBD - Parte 4
154/281
Se vrias colunas tiverem os mesmos nomes,
mas tipos de dados que no correspondem, usea clusula USING para especificar as colunaspara a equijoin.
Use a clusula USING para correspondersomente uma coluna quando houver mais deuma coluna correspondente.
As clusulas NATURAL JOIN e USING somutuamente exclusivas.
Unindo nomes de colunas
7/25/2019 PBD - Parte 4
155/281
EMPLOYEES DEPARTMENTS
Chave estrangeira
Chave primria
Recuperando registros com a clusula USING
7/25/2019 PBD - Parte 4
156/281
SELECT employee_id, last_name,location_id, department_id
FROM employees JOIN departmentsUSING (department_id) ;
Usando apelidos de tabela com a clusula USING
No qualifique uma coluna que usada na clusula
7/25/2019 PBD - Parte 4
157/281
SELECT l.city, d.department_name
FROM locations l JOIN departments dUSING (location_id)
WHERE d.location_id = 1400;
q q qUSING.
Se a mesma coluna for usada em qualquer outraparte da instruo SQL, no lhe d um apelido.
Criando Joins com a Clusula ON
A condio de join para a join natural
7/25/2019 PBD - Parte 4
158/281
A condio de join para a join natural
basicamente uma equijoin de todas as colunascom o mesmo nome.
Use a clusula ON para especificar condies
arbitrrias ou especificar colunas a seremunidas.
A condio de join separada de outrascondies de pesquisa.
A clusula ON facilita a compreenso do cdigo.
7/25/2019 PBD - Parte 4
159/281
l id i d
Criando three-way joins com a clusula ON
7/25/2019 PBD - Parte 4
160/281
SELECT employee_id, city, department_nameFROM employees e
JOIN departments dON d.department_id = e.department_idJOIN locations lON d.location_id = l.location_id;
Aplicando condies adicionais em uma join
Use a clusula AND ou a clusula WHERE para
7/25/2019 PBD - Parte 4
161/281
SELECT e.employee_id, e.last_name, e.department_id,d.department_id, d.location_id
FROM employees e JOIN departments dON (e.department_id = d.department_id)
AND e.manager_id = 149 ;
aplicar condies adicionais:
SELECT e.employee_id, e.last_name, e.department_id,d.department_id, d.location_id
FROM employees e JOIN departments dON (e.department_id = d.department_id)
WHERE e.manager_id = 149 ;
Ou
Agenda de Lies
Tipos de JOINS e sua sintaxe
7/25/2019 PBD - Parte 4
162/281
Join natural:
Clusula USING
Clusula ON
Self-join
No-equijoinsOUTERjoin:
LEFT OUTERjoin
RIGHT OUTERjoinFULL OUTERjoin
Produto cartesiano
Join cruzada
7/25/2019 PBD - Parte 4
163/281
Self-Joins usando a clusula ON
7/25/2019 PBD - Parte 4
164/281
SELECT worker.last_name emp, manager.last_name mgrFROM employees worker JOIN employees managerON (worker.manager_id = manager.employee_id);
Agenda de Lies
Tipos de JOINS e sua sintaxe
7/25/2019 PBD - Parte 4
165/281
Join natural:
Clusula USING
Clusula ON
Self-join
No-equijoinsOUTERjoin:
LEFT OUTERjoin
RIGHT OUTERjoinFULL OUTERjoin
Produto cartesiano
Join cruzada
No-equijoins
7/25/2019 PBD - Parte 4
166/281
EMPLOYEES JOB_GRADES
A tabela JOB_GRADES define ointervalo de valores LOWEST_SAL e
HIGHEST_SAL para cadaGRADE_LEVEL. Por isso, a colunaGRADE_LEVEL pode ser usada paraatribuir graus a cada funcionrio.
Recuperando Registros com No-equijoins
7/25/2019 PBD - Parte 4
167/281
SELECT e.last_name, e.salary, j.grade_levelFROM employees e JOIN job_grades jON e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
Agenda de Lies
Tipos de JOINS e sua sintaxe
7/25/2019 PBD - Parte 4
168/281
Join natural:
Clusula USING
Clusula ON
Self-join
No-equijoinsOUTERjoin:
LEFT OUTERjoin
RIGHT OUTERjoin
FULL OUTERjoin
Produto cartesiano
Join cruzada
Retornando registros sem correspondncia diretausando OUTERjoins
7/25/2019 PBD - Parte 4
169/281
Equijoin com EMPLOYEESDEPARTMENTS
No h funcionrios nodepartamento 190.
O funcionrio Grant
no recebeu uma IDde departamento.
Joins INNER comparadas comOUTER
No SQL:1999, a unio de duas tabelas que
7/25/2019 PBD - Parte 4
170/281
retornam somente linhas correspondentes chamada de INNERjoin.
Uma unio entre duas tabelas que retorna osresultados da INNERjoin e de linhas semcorrespondncia da tabela esquerda (ou direita) chamada de left (ou right) OUTERjoin.
Uma unio entre duas tabelas que retorna osresultados de uma INNERjoin e os resultadosde uma join esquerda e direita uma fullOUTERjoin.
JOINLEFT OUTER
7/25/2019 PBD - Parte 4
171/281
SELECT e.last_name, e.department_id, d.department_nameFROM employees e LEFT OUTER JOIN departments dON (e.department_id = d.department_id) ;
RIGHT OUTER JOIN
7/25/2019 PBD - Parte 4
172/281
SELECT e.last_name, d.department_id, d.department_nameFROM employees e RIGHT OUTER JOIN departments dON (e.department_id = d.department_id) ;
FULLOUTER JOIN
7/25/2019 PBD - Parte 4
173/281
SELECT e.last_name, d.department_id, d.department_nameFROM employees e FULL OUTER JOIN departments dON (e.department_id = d.department_id) ;
Agenda de Lies
Tipos de JOINS e sua sintaxe
7/25/2019 PBD - Parte 4
174/281
Join natural:
Clusula USING
Clusula ON
Self-join
No-equiijoinOUTERjoin:
LEFT OUTERjoin
RIGHT OUTERjoinFULL OUTERjoin
Produto cartesiano
Join cruzada
Produtos Cartesianos
Um Produto Cartesiano formado quando:
7/25/2019 PBD - Parte 4
175/281
Um Produto Cartesiano formado quando:
Uma condio de join omitida Uma condio de join invlida
Todas as linhas da primeira tabela so unidas
a todas as linhas na segunda tabela
Sempre inclua uma condio de join vlidase quiser evitar um produto Cartesiano.
Gerando um Produto Cartesiano
7/25/2019 PBD - Parte 4
176/281
ProdutoCartesiano:
20 x 8 = 160 linhas
EMPLOYEES (20 linhas) DEPARTMENTS (8 linhas)
Criando joins cruzadas
A clusula CROSS JOIN gera o produto cruzado deduas tabelas
7/25/2019 PBD - Parte 4
177/281
SELECT last_name, department_name
FROM employeesCROSS JOIN departments ;
duas tabelas.
Isso tambm chamado de produto cartesiano entre asduas tabelas.
Prtica
O departamento de RH precisa de um relatrio dosf i i d T t E ib b
7/25/2019 PBD - Parte 4
178/281
funcionrios de Toronto. Exiba o sobrenome, o
cargo, o nmero e o nome do departamento detodos os funcionrios que trabalham em Toronto.
Prtica
O departamento de RH precisa de um relatrio dosfuncionrios de Toronto Exiba o sobrenome o
7/25/2019 PBD - Parte 4
179/281
funcionrios de Toronto. Exiba o sobrenome, o
cargo, o nmero e o nome do departamento detodos os funcionrios que trabalham em Toronto.
SELECT e.last_name, e.job_id, e.department_id,d.department_nameFROM employees eJOIN departments d ON(e.department_id = d.department_id)
JOIN locations l ON(d.location_id = l.location_id)
WHERE LOWER(l.city) = 'toronto';
7/25/2019 PBD - Parte 4
180/281
Usando sub-consulta
Agenda de Lies
7/25/2019 PBD - Parte 4
181/281
Subconsulta: Tipos, sintaxe e diretrizes
Subconsultas de uma nica linha:
Functions de grupo em uma subconsulta
Clusula HAVING com subconsultas
Subconsultas de vrias linhas Use o operador ALL ou ANY.
Usando o Operador EXISTS
Valores nulos em uma subconsulta
Usando uma subconsulta para solucionar um problema
Quem tem um salrio maior que o salrio de Abel?
7/25/2019 PBD - Parte 4
182/281
Quais funcionrios tm salrio maior que Abel?
Consulta principal:
Qual o salrio de Abel?
Subconsulta:
Sintaxe de subconsulta
7/25/2019 PBD - Parte 4
183/281
A subconsulta (consulta interna) executadaantes da consulta principal (consulta externa).
O resultado da subconsulta usado pela consultaprincipal.
SELECT select_listFROM table
WHERE expr operator(SELECT select_listFROM table);
Usando uma subconsulta
7/25/2019 PBD - Parte 4
184/281
SELECT last_name, salaryFROM employees
WHERE salary >(SELECT salaryFROM employees
WHERE last_name = 'Abel');
11000
Diretrizes para usar subconsultas
I i b l
7/25/2019 PBD - Parte 4
185/281
Insira as subconsultas entre parnteses.
Insira subconsultas do lado direito da condio decomparao por questo de legibilidade.(Entretanto, a subconsulta pode ser exibida nos
dois lados do operador de comparao.) Use operadores de uma nica linha com
subconsultas de uma nica linha e operadores devrias linhas com subconsultas de vrias linhas.
Tipos de subconsultas
S b lt d i li h
7/25/2019 PBD - Parte 4
186/281
Subconsulta de uma nica linha
Subconsulta de vrias linhas
Consulta principal
Subconsultaretorno
ST_CLERK
ST_CLERK
SA_MAN
Consulta principal
Subconsulta
retornos
Agenda de Lies
S b lt Ti i t di t i
7/25/2019 PBD - Parte 4
187/281
Subconsulta: Tipos, sintaxe e diretrizes
Subconsultas de uma nica linha:
Functions de grupo em uma subconsulta
Clusula HAVING com subconsultas
Subconsultas de vrias linhas Use o operador ALL ou ANY.
Usando o Operador EXISTS
Valores nulos em uma subconsulta
Subconsultas de uma nica linha
Retorna somente uma linha
Use operadores de comparao de uma nica linha
7/25/2019 PBD - Parte 4
188/281
Use operadores de comparao de uma nica linha
Maior que ou igual a>=
Menor que
(SELECT salaryFROM employees
WHERE last_name = 'Abel');
SA_REP
8600
Usando funes de grupo em uma subconsulta
7/25/2019 PBD - Parte 4
190/281
SELECT last_name, job_id, salaryFROM employees
WHERE salary =(SELECT MIN(salary)FROM employees);
2500
Clusula HAVING com subconsultas
O servidor Oracle executa as subconsultas primeiro.
O servidor Oracle retorna resultados para a clusula
7/25/2019 PBD - Parte 4
191/281
SELECT department_id, MIN(salary)FROM employeesGROUP BY department_id
HAVING MIN(salary) >(SELECT MIN(salary)FROM employees
WHERE department_id = 50);
O servidor Oracle retorna resultados para a clusula
HAVING da consulta principal.
2500
O que est errado com esta instruo?
7/25/2019 PBD - Parte 4
192/281
SELECT employee_id, last_nameFROM employees
WHERE salary =(SELECT MIN(salary)FROM employeesGROUP BY department_id);
Operador de umanica linha comsubconsulta de
vrias linhas
7/25/2019 PBD - Parte 4
193/281
Agenda de Lies
Subconsulta: Tipos sintaxe e diretrizes
7/25/2019 PBD - Parte 4
194/281
Subconsulta: Tipos, sintaxe e diretrizes
Subconsultas de uma nica linha:
Functions de grupo em uma subconsulta
Clusula HAVING com subconsultas
Subconsultas de vrias linhas Use o operador IN, ALL ou ANY
Usando o Operador EXISTS
Valores nulos em uma subconsulta
Subconsultas de vrias linhas
Retornam mais de uma linha
Usam operadores de comparao de vrias linhas
7/25/2019 PBD - Parte 4
195/281
Usam operadores de comparao de vrias linhas
Deve ser precedido por =, !=, >, ,
7/25/2019 PBD - Parte 4
196/281
SELECT employee_id, last_name, job_id, salaryFROM employees
WHERE salary < ANY(SELECT salaryFROM employees
WHERE job_id = 'IT_PROG')
AND job_id 'IT_PROG';
9000, 6000, 4200
ANY significa mais que omnimo.=ANY equivalente a IN.
Usando o operador ALL em subconsultas de vrias linhas
7/25/2019 PBD - Parte 4
197/281
SELECT employee_id, last_name, job_id, salaryFROM employees
WHERE salary < ALL(SELECT salaryFROM employees
WHERE job_id = 'IT_PROG')
AND job_id 'IT_PROG';
9000, 6000, 4200
>ALL significa mais que o mximo e
7/25/2019 PBD - Parte 4
198/281
SELECT * FROM departmentsWHERE NOT EXISTS(SELECT * FROM employeesWHERE employees.department_id=departments.department_id);
Agenda de Lies
Subconsulta: Tipos sintaxe e diretrizes
7/25/2019 PBD - Parte 4
199/281
Subconsulta: Tipos, sintaxe e diretrizes
Subconsultas de uma nica linha:
Functions de grupo em uma subconsulta
Clusula HAVING com subconsultas
Subconsultas de vrias linhas Use o operador ALL ou ANY.
Usando o Operador EXISTS
Valores nulos em uma subconsulta
Valores nulos em uma subconsulta
7/25/2019 PBD - Parte 4
200/281
SELECT emp.last_nameFROM employees emp
WHERE emp.employee_id NOT IN(SELECT mgr.manager_idFROM employees mgr);
7/25/2019 PBD - Parte 4
201/281
Usando os
operadores de
conjunto
Agenda de Lies
Operadores de conjunto: dicas e diretrizes
7/25/2019 PBD - Parte 4
202/281
Operadores de conjunto: dicas e diretrizes
Tabelas usadas nesta lio
Operadores UNION and UNION ALL
Operador INTERSECT
Operador MINUS
Correspondncia s instrues SELECT
Uso da clusula ORDER BY nas operaes de
conjunto
Operadores de conjunto
A B A B
7/25/2019 PBD - Parte 4
203/281
UNION/UNION ALL
A B
INTERSECT
A B
MINUS
Diretrizes do operador de conjunto
As expresses nas listas SELECT devem serd t
7/25/2019 PBD - Parte 4
204/281
correspondentes em nmero.
O tipo de dados de cada coluna na segundaconsulta deve corresponder ao tipo de dados darespectiva coluna na primeira consulta.
Os parnteses podem ser usados para alterar asequncia de execuo.
A clusula ORDER BY pode aparecer apenas perto
do final da instruo.
O servidor Oracle e os Operadores Set
As linhas duplicadas so automaticamente
7/25/2019 PBD - Parte 4
205/281
eliminadas, exceto em UNION ALL. Os nomes das colunas da primeira consulta
aparecem no resultado.
A sada classificada em ordem crescente,por padro, exceto em UNION ALL.
Operador UNION
7/25/2019 PBD - Parte 4
206/281
A B
O operador UNION retornar linhas de ambas as consultas depois daeliminao das duplicaes.
Uso do operador UNION
Exibir detalhes dos cargos anterior e atual
7/25/2019 PBD - Parte 4
207/281
de todos os funcionrios. Exibir cadafuncionrio apenas uma vez.
SELECT employee_id, job_id
FROM employeesUNIONSELECT employee_id, job_idFROM job_history;
Operador UNION ALL
7/25/2019 PBD - Parte 4
208/281
O operador UNION ALL retornar linhas de ambas as consultas, incluindotodas as duplicaes.
A B
Uso do operador UNION ALL
Exibir detalhes dos departamentos anterior e atualde todos os funcionrios.
7/25/2019 PBD - Parte 4
209/281
SELECT employee_id, job_id, department_idFROM employeesUNION ALLSELECT employee_id, job_id, department_idFROM job_history
ORDER BY employee_id;
Agenda de Lies
Operadores de conjunto: dicas e diretrizes
7/25/2019 PBD - Parte 4
210/281
Operadores de conjunto: dicas e diretrizes
Tabelas usadas nesta lio
Operadores UNION and UNION ALL
Operador INTERSECT
Operador MINUS
Correspondncia s instruesSELECT
Uso da clusula ORDER BY nas operaes de
conjunto
Operador INTERSECT
7/25/2019 PBD - Parte 4
211/281
A B
O operador INTERSECT retorna linhas comuns a ambas as consultas.
Uso do operador INTERSECT
Exibe os IDs dos funcionrios e dos cargosdaqueles que no momento esto no mesmo
7/25/2019 PBD - Parte 4
212/281
daqueles que, no momento, esto no mesmocargo que j estiveram no passado (ou seja, elesmudaram de funo, mas agora retornaram afazer o mesmo que faziam anteriormente).
SELECT employee_id, job_idFROM employeesINTERSECTSELECT employee_id, job_id
FROM job_history;
Agenda de Lies
Operadores de conjunto: dicas e diretrizes
7/25/2019 PBD - Parte 4
213/281
Operadores de conjunto: dicas e diretrizes
Tabelas usadas nesta lio
Operadores UNION and UNION ALL
Operador INTERSECT
Operador MINUS Correspondncia s instrues SELECT
Uso da clusula ORDER BY nas operaes de
conjunto
Operador MINUS
7/25/2019 PBD - Parte 4
214/281
A B
O operadorMINUS retorna todas as linhas distintas selecionadas pelaprimeira linha, mas no presentes no conjunto de resultados da segundaconsulta.
Uso do operador MINUS
Exibe os IDs do funcionrios que nomudaram de cargo nenhuma vez
7/25/2019 PBD - Parte 4
215/281
mudaram de cargo nenhuma vez.
SELECT employee_idFROM employees
MINUSSELECT employee_idFROM job_history;
Agenda de Lies
Operadores de conjunto: dicas e diretrizes
7/25/2019 PBD - Parte 4
216/281
p j
Tabelas usadas nesta lio
Operadores UNION and UNION ALL
Operador INTERSECT
Operador MINUS Correspondncia s instrues SELECT
Uso da clusula ORDER BY nas operaes de
conjunto
Correspondncia s instrues SELECT
Com o operador UNION, exiba o ID do local, o nomedo departamento e o estado em que ele est
7/25/2019 PBD - Parte 4
217/281
localizado. Corresponda o tipo de dados (usando a funoTO_CHAR ou quaisquer outras funes de converso)quando as colunas no existirem em uma das tabelas.
SELECT location_id, department_name "Department",TO_CHAR(NULL) "Warehouse location"
FROM departments
UNIONSELECT location_id, TO_CHAR(NULL) "Department",state_province
FROM locations;
Correspondncia instruo SELECT: Exemplo
Com o operador UNION, exiba o ID dofuncionrio o ID do cargo e o salrio de todos os
7/25/2019 PBD - Parte 4
218/281
funcionrio, o ID do cargo e o salrio de todos osfuncionrios.
SELECT employee_id, job_id,salaryFROM employees
UNIONSELECT employee_id, job_id,0FROM job_history;
Agenda de Lies
Operadores de conjunto: dicas e diretrizes
7/25/2019 PBD - Parte 4
219/281
p j
Tabelas usadas nesta lio
Operadores UNION and UNION ALL
Operador INTERSECT
Operador MINUS Correspondncia s instrues SELECT
Uso da clusula ORDER BY nas operaes de
conjunto
Uso da clusula ORDER BY nas operaes de conjunto
A clusula ORDER BY pode aparecer apenas
7/25/2019 PBD - Parte 4
220/281
uma vez no final da consulta composta. As consultas dos componentes no podem ter
clusulas ORDER BY individuais.
A clusula ORDER BY reconhece apenas ascolunas da primeira coluna SELECT.
Por padro, a primeira coluna da primeiraconsulta SELECT usada para classificar a
sada em uma ordem crescente.
7/25/2019 PBD - Parte 4
221/281
ManipulandoDados
Agenda de Lies
Adicionando novas linhas a uma tabela InstruoINSERT
7/25/2019 PBD - Parte 4
222/281
Alterando dados em uma tabela InstruoUPDATE
Removendo linhas de uma tabela:
InstruoDELETE InstruoTRUNCATE
Controle de transaes de banco de dados usandoCOMMIT, ROLLBACK e SAVEPOINT
Consistncia na leitura
Clusula FOR UPDATE em uma instruo SELECT
Data Manipulation Language
Uma instruo DML executada quando voc:
7/25/2019 PBD - Parte 4
223/281
Adiciona novas linhas a uma tabela Modifica linhas existentes em uma tabela
Remove linhas existentes de uma tabela
Uma transao composta por uma coleode instrues DML que formam uma unidadelgica de trabalho.
Adicionando uma nova linha a uma tabela
DEPARTMENTSNew
7/25/2019 PBD - Parte 4
224/281
Insero de uma novalinha na tabelaDEPARTMENTS
Sintaxe da instruo INSERT
Adicione novas linhas a uma tabela usando a
7/25/2019 PBD - Parte 4
225/281
instruo INSERT:
Com essa sintaxe, inserida apenas uma linha porvez.
INSERT INTO table [(column [, column...])]VALUES (value [, value...]);
Inserindo novas linhas
Insira uma nova linha contendo os valores de cadacoluna.
7/25/2019 PBD - Parte 4
226/281
Liste os valores na ordem padro das colunas natabela.
Opcionalmente, liste as colunas na clusula
INSERT.
Encerre os caracteres e valores de dados entreaspas simples.
INSERT INTO departments(department_id,department_name, manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);
Mtodo implcito: omitir a coluna da lista de colunas.
Inserindo linhas com valores nulos
7/25/2019 PBD - Parte 4
227/281
Mtodo explcito: especificar a palavra-chave NULL na
clusula VALUES.
INSERT INTO departmentsVALUES (100, 'Finance', NULL, NULL);
INSERT INTO departments (department_id,department_name)
VALUES (30, 'Purchasing');
Inserindo valores especiais
A funo SYSDATE registra a data e o horrioatuais.
7/25/2019 PBD - Parte 4
228/281
INSERT INTO employees (employee_id,first_name, last_name,email, phone_number,hire_date, job_id, salary,
commission_pct, manager_id,department_id)VALUES (113,
'Louis', 'Popp','LPOPP', '515.124.4567',SYSDATE, 'AC_ACCOUNT', 6900,
NULL, 205, 110);
Inserindo valores de data e horrio especficos
Adicione um novo funcionrio.
7/25/2019 PBD - Parte 4
229/281
Verifique sua incluso.
INSERT INTO employeesVALUES (114,
'Den', 'Raphealy','DRAPHEAL', '515.127.4561',TO_DATE('FEB 3, 1999', 'MON DD, YYYY'),
'SA_REP', 11000, 0.2, 100, 60);
Criando um script
Use a substituio & em uma instruo SQL para solicitarvalores.
7/25/2019 PBD - Parte 4
230/281
INSERT INTO departments
(department_id, department_name, location_id)
VALUES (&department_id, '&department_name',&location);
& um marcador de espao para o valor da varivel.
Copiando linhas de outra tabela
Escreva a instruo INSERT com uma subconsulta:
7/25/2019 PBD - Parte 4
231/281
No use a clusula VALUES.
O nmero de colunas na clusula INSERT deve sercorrespondente s colunas na subconsulta.
Insira todas as linhas retornadas pela subconsulta natabela sales_reps.
INSERT INTO sales_reps(id, name, salary, commission_pct)SELECT employee_id, last_name, salary, commission_pctFROM employeesWHERE job_id LIKE '%REP%';
Agenda de Lies
Adicionando novas linhas a uma tabela InstruoINSERT
7/25/2019 PBD - Parte 4
232/281
Alterando dados em uma tabela InstruoUPDATE
Removendo linhas de uma tabela:
InstruoDELETE InstruoTRUNCATE
Controle de transaes de banco de dados usandoCOMMIT, ROLLBACK e SAVEPOINT
Consistncia na leitura
Clusula FOR UPDATE em uma instruo SELECT
Alterando dados em uma tabela
EMPLOYEES
7/25/2019 PBD - Parte 4
233/281
Atualize as linhas na tabela EMPLOYEES:
Sintaxe da instruoUPDATE
Modifique os valores existentes em uma tabela com ainstruo UPDATE:
7/25/2019 PBD - Parte 4
234/281
Atualize mais de uma linha por vez (se necessrio).
UPDATE tableSET column = value [, column = value, ...][WHERE condition];
Atualizando linhas em uma tabela
Os valores de uma ou vrias linhas especficas seromodificados se voc especificar a clusula WHERE:
7/25/2019 PBD - Parte 4
235/281
Os valores de todas as linhas na tabela seromodificados se voc omitir a clusula WHERE:
Especifique SET column_name= NULL para atualizarum valor de coluna como NULL.
UPDATE employeesSET department_id = 50
WHERE employee_id = 113;
UPDATE copy_empSET department_id = 110;
Atualizando duas colunas com uma subconsulta
Atualize o cargo e o salrio do funcionrio 113 demodo a corresponder aos dados do funcionrio 205.
7/25/2019 PBD - Parte 4
236/281
UPDATE employeesSET job_id = (SELECT job_id
FROM employeesWHERE employee_id = 205),
salary = (SELECT salaryFROM employees
WHERE employee_id = 205)WHERE employee_id = 113;
Atualizando linhas baseadas em outra tabela
Use as subconsultas nas instrues UPDATE paraatualizar os valores das linhas em uma tabela
7/25/2019 PBD - Parte 4
237/281
UPDATE copy_empSET department_id = (SELECT department_id
FROM employees
WHERE employee_id = 100)WHERE job_id = (SELECT job_id
FROM employeesWHERE employee_id = 200);
baseada nos valores de outra tabela:
Agenda de Lies
Adicionando novas linhas a uma tabela InstruoINSERT
7/25/2019 PBD - Parte 4
238/281
Alterando dados em uma tabela InstruoUPDATE
Removendo linhas de uma tabela: InstruoDELETE
InstruoTRUNCATE
Controle de transaes de banco de dados usandoCOMMIT, ROLLBACK e SAVEPOINT
Consistncia na leitura Clusula FOR UPDATE em uma instruo SELECT
Removendo uma linha de uma tabela
DEPARTMENTS
7/25/2019 PBD - Parte 4
239/281
Exclua uma linha da tabela DEPARTMENTS:
InstruoDELETE
possvel remover linhas existentes de uma tabelausando a instruo DELETE:
7/25/2019 PBD - Parte 4
240/281
DELETE [FROM] table[WHERE condition];
Excluindo linhas de uma tabela
Linhas especficas sero excludas se voc especificara clusula WHERE:
7/25/2019 PBD - Parte 4
241/281
Todas as linhas na tabela sero excludas se voc omitira clusula WHERE:
DELETE FROM departmentsWHERE department_name = 'Finance';
DELETE FROM copy_emp;
Excluindo linhas baseadas em outra tabela
Use as subconsultas nas instrues DELETE pararemover linhas de uma tabela baseada nos valores
7/25/2019 PBD - Parte 4
242/281
de outra tabela:
DELETE FROM employeesWHERE department_id =
(SELECT department_idFROM departments
WHERE department_nameLIKE '%Public%');
Instruo TRUNCATE
Remove todas as linhas de uma tabela, deixando-avazia, mas com a estrutura intacta
7/25/2019 PBD - Parte 4
243/281
uma instruo DDL (Data Definition Language) e noDML; no possvel ser desfeita facilmente
Sintaxe:
Exemplo:
TRUNCATE TABLE table_name;
TRUNCATE TABLE copy_emp;
Agenda de Lies
Adicionando novas linhas a uma tabela InstruoINSERT
7/25/2019 PBD - Parte 4
244/281
Alterando dados em uma tabela InstruoUPDATE
Removendo linhas de uma tabela: InstruoDELETE
InstruoTRUNCATE
Controle de transaes de banco de dados usandoCOMMIT, ROLLBACK, e SAVEPOINT
Consistncia na leitura Clusula FOR UPDATE em uma instruo SELECT
Transaes do banco de dados
Uma transao de banco de dados
7/25/2019 PBD - Parte 4
245/281
composta por uma das seguintes instrues: Instrues DML que compem uma alterao
consistente aos dados
Uma instruo DDL Uma instruo DCL (Data Control Language)
Transaes do banco de dados: incio e fim
Comea quando a primeira instruo DML SQL executada.
7/25/2019 PBD - Parte 4
246/281
Termina com um dos seguintes eventos: Uma instruo COMMIT ou ROLLBACK emitida.
Uma instruo DDL ou DCL executada (commitautomtico).
O usurio sai do SQL Developer ou do SQL*Plus.
O sistema gera uma falha.
Vantagens das instrues COMMIT e ROLLBACK
Com as instrues COMMIT e ROLLBACK, possvel:
7/25/2019 PBD - Parte 4
247/281
Assegurar a consistncia do dados
Visualizar as alteraes nos dados antes de torn-las permanentes
Agrupar operaes logicamente relacionadas
Instrues explcitas do controle de transaes
COMMITHora
7/25/2019 PBD - Parte 4
248/281
SAVEPOINT B
SAVEPOINT A
DELETE
INSERT
UPDATE
INSERT
Transao
ROLLBACKto SAVEPOINT B
ROLLBACKto SAVEPOINT A
ROLLBACK
Efetuando rollback das alteraes para um marcador
Crie um marcador na transao atual usando a instruoSAVEPOINT.
7/25/2019 PBD - Parte 4
249/281
UPDATE...SAVEPOINT update_done;
INSERT...ROLLBACK TO update_done;
Efetue rollback para o marcador usando a instruoROLLBACK TO SAVEPOINT.
Processamento implcito de transaes
Ocorre um commit automtico nas seguintescircunstncias:
7/25/2019 PBD - Parte 4
250/281
Uma instruo DDL emitida
Uma instruo DCL emitida
Sada normal do SQL Developer ou SQL*Plus sem a
emisso explcita das instrues COMMIT ouROLLBACK
Um rollback automtico ocorre devido a um
encerramento anormal do SQL Developer ouSQL*Plus, de uma falha do sistema.
Estado dos dados antes do COMMIT ou ROLLBACK
O estado prvio dos dados pode serrecuperado.
7/25/2019 PBD - Parte 4
251/281
O usurio atual pode examinar os resultadosdas operaes DML usando a instruoSELECT.
Outros usurios no podem exibir os resultadosdas instrues DML emitidas pelo usurio atual.
As linhas afetadas esto bloqueadas; elas nopodem ser alteradas por outros usurios.
Estado dos dados depois do COMMIT
As alteraes dos dados so salvas no banco dedados.
7/25/2019 PBD - Parte 4
252/281
O estado anterior dos dados sobrescrito.
Todos os usurios podem exibir os resultados.
Os bloqueios nas linhas afetadas so liberados;elas ficam disponveis para serem manipuladaspor outros usurios.
Todos os pontos de salvamento so apagados.
Efetuando commit de dados
Faa as alteraes:
DELETE FROM emplo ees
7/25/2019 PBD - Parte 4
253/281
COMMIT;
Efetue commit nas alteraes:
DELETE FROM employeesWHERE employee_id = 99999;
INSERT INTO departmentsVALUES (290, 'Corporate Tax', NULL, 1700);
Estado dos dados depois do ROLLBACK
Descarte todas as alteraes pendentes usando ainstruo ROLLBACK:
l d d d f i
7/25/2019 PBD - Parte 4
254/281
DELETE FROM copy_emp;ROLLBACK;
As alteraes nos dados esto desfeitas.
O estado anterior dos dados restaurado.
Os bloqueios nas linhas afetadas so liberados.
Estado dos dados depois do ROLLBACK: Exemplo
DELETE FROM test;25,000 rows deleted.
7/25/2019 PBD - Parte 4
255/281
ROLLBACK;Rollback complete.
DELETE FROM test WHERE id = 100;
1 row deleted.
SELECT * FROM test WHERE id = 100;No rows selected.
COMMIT;
Commit complete.
Rollback no nvel da instruo
Se uma nica instruo DML falhar durante aexecuo, o rollback ser efetuado somente
i
7/25/2019 PBD - Parte 4
256/281
nessa instruo. O Oracle Server implementa um ponto de
savepoint implcito.
Todas as outras alteraes so retidas.
O usurio deve terminar as transaesexplicitamente, executando uma instruo
COMMIT ou ROLLBACK.
Agenda de Lies
Adicionando novas linhas a uma tabela InstruoINSERT
Al d d d b l
7/25/2019 PBD - Parte 4
257/281
Alterando dados em uma tabela InstruoUPDATE
Removendo linhas de uma tabela: Instruo
DELETE InstruoTRUNCATE
Controle de transaes de banco de dados usandoCOMMIT, ROLLBACK, e SAVEPOINT
Consistncia na leitura Clusula FOR UPDATE em uma instruo SELECT
Consistncia na leitura
A consistncia na leitura garante uma viewconsistente dos dados em todos os momentos.
A l f i i
7/25/2019 PBD - Parte 4
258/281
As alteraes feitas por um usurio no entraem conflito com as realizadas por outro usurio.
A consistncia na leitura assegura que, nos
mesmos dados: Os leitores no aguardem os gravadores
Os gravadores no aguardem os leitores
Os gravadores aguardem gravadores
Implementando consistncia na leitura
UPDATE employees
SET salary = 7000
Blocos de
dados
Usurio A
7/25/2019 PBD - Parte 4
259/281
SELECT *FROM userA.employees;
SET salary = 7000WHERE last_name = 'Grant';
dados
Segmentosde undo
Dadosalteradose inalterados
Antes daalterao(dadosantigos)
Usurio B
Imagemconsistente
na leitura
Agenda de Lies
Adicionando novas linhas a uma tabela InstruoINSERT
Alt d d d t b l
7/25/2019 PBD - Parte 4
260/281
Alterando dados em uma tabela InstruoUPDATE
Removendo linhas de uma tabela: Instruo
DELETE InstruoTRUNCATE
Controle de transaes de banco de dados usandoCOMMIT, ROLLBACK e SAVEPOINT
Consistncia na leitura Clusula FOR UPDATE em uma instruo SELECT
Clusula FOR UPDATE em uma instruo SELECT
Bloqueia as linhas na tabela EMPLOYEES ondejob_id SA_REP.
SELECT employee id salary commission pct job id
7/25/2019 PBD - Parte 4
261/281
O bloqueio ser liberado apenas depois de voc emitiruma instruo ROLLBACK ou COMMIT.
Se a instruo SELECT tentar bloquear uma linha j
bloqueada por outro usurio, o banco de dadosaguardar at que ela se torne disponvel novamente e,ento, retornar os resultados da instruo SELECT.
SELECT employee_id, salary, commission_pct, job_idFROM employees
WHERE job_id = 'SA_REP'FOR UPDATEORDER BY employee_id;
Clusula FOR UPDATE: Exemplos
possvel usar a clusula FOR UPDATE em umainstruo SELECT em relao a vrias tabelas.
7/25/2019 PBD - Parte 4
262/281
As linhas das tabelas EMPLOYEES e DEPARTMENTSso bloqueadas.
Use FOR UPDATE OF column_name para qualificar acoluna que se destina alterao, apenas as linhasdessa tabela especfica sero bloqueadas.
SELECT e.employee_id, e.salary, e.commission_pctFROM employees e JOIN departments dUSING (department_id)WHERE job_id = 'ST_CLERK
AND location_id = 1500
FOR UPDATEORDER BY e.employee_id;
Questionrio
As seguintes instrues produzem os mesmos
lt d ?
7/25/2019 PBD - Parte 4
263/281
resultados?
1. Verdadeiro
2. Falso
DELETE FROM copy_emp;
TRUNCATE TABLE copy_emp;
7/25/2019 PBD - Parte 4
264/281
Criando
Outros
Objetos
Agenda de Lies
Viso geral sobre views:
C i d difi d d d d
7/25/2019 PBD - Parte 4
265/281
Criando, modificando e recuperando dadosde uma view
Operaes DML (Data Manipulation
Language) em uma view Eliminando uma view
O que uma view?
Tabela EMPLOYEES
7/25/2019 PBD - Parte 4
266/281
Vantagens das views
Para restringiracesso de dados
Para simplificarconsultas complexas
7/25/2019 PBD - Parte 4
267/281
acesso de dados consultas complexas
Para proverindependncia
de dados
Para apresentarvises diferentes dos
mesmos dados
Views simples e views complexas
Views simples Views complexasRecurso
7/25/2019 PBD - Parte 4
268/281
Sim
No
No
Uma
SimContm funes
SimContm grupos de dados
Uma ou maisNmero de tabelas
Nem sempreOperaes DML usandouma view
Criando uma view
Voc incorporou uma subconsulta na instruoCREATE VIEW:
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
7/25/2019 PBD - Parte 4
269/281
A subconsulta pode conter a sintaxe complexa deSELECT.
[ ] [ | ][(alias[, alias]...)]
AS subquery[WITH CHECK OPTION [CONSTRAINT constraint]][WITH READ ONLY [CONSTRAINT constraint]];
Criando uma view
Crie a view EMPVU80 contendo os detalhes dosfuncionrios no departamento 80:
CREATE VIEW empvu80
7/25/2019 PBD - Parte 4
270/281
Descreva a estrutura da view usando o comandoDESCRIBE do SQL*Plus
DESCRIBE empvu80
CREATE VIEW empvu80AS SELECT employee_id, last_name, salary
FROM employeesWHERE department_id =80;
Criando uma view
Crie uma view usando aliases de colunas nasubconsulta:
CREATE VIEW salvu50
7/25/2019 PBD - Parte 4
271/281
Selecione as colunas dessa view pelos nomes dealias atribudos.
CREATE VIEW salvu50AS SELECT employee_id ID_NUMBER, last_name NAME,
salary*12 ANN_SALARYFROM employees
WHERE department_id =50;
SELECT *FROM salvu50;
Recuperando dados de uma view
7/25/2019 PBD - Parte 4
272/281
FROM salvu50;
Modificando uma view
Modifique a view EMPVU80 usando uma clusulaCREATE OR REPLACE VIEW. Adicione um alias para
cada nome de coluna:
7/25/2019 PBD - Parte 4
273/281
cada nome de coluna:
Os aliases de coluna na clusula CREATE OR REPLACEVIEW so listados na mesma ordem que as colunas nasubconsulta.
CREATE OR REPLACE VIEW empvu80(id_number, name, sal, department_id)
AS SELECT employee_id, first_name || ' '|| last_name, salary, department_id
FROM employeesWHERE department_id =80;
Criando uma view complexa
Crie uma view complexa que contenha funes dogrupo para exibir os valores de duas tabelas:
7/25/2019 PBD - Parte 4
274/281
CREATE OR REPLACE VIEW dept_sum_vu(name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary),MAX(e.salary),AVG(e.salary)
FROM employees e JOIN departments dON (e.department_id = d.department_id)GROUP BY d.department_name;
Regras para execuo das operaes DML em uma view
possvel executar operaes DML normalmentenas views simples.
No possvel remover uma linha se a view
7/25/2019 PBD - Parte 4
275/281
No possvel remover uma linha se a viewcontiver:
Funes de grupo
Uma clusula GROUP BY
A palavra-chave DISTINCT
A palavra-chave ROWNUM da pseudocoluna
Regras para execuo das operaes DML em uma view
No ser possvel modificar os dados em
uma view se ela contiver:
7/25/2019 PBD - Parte 4
276/281
uma view se ela contiver: Funes de grupo
Uma clusula GROUP BY
A palavra-chave DISTINCT A palavra-chave ROWNUM da pseudocoluna
Colunas definidas por expresses
Regras para execuo das operaes DML em uma view
No ser possvel adicionar dados usando umaview se ela incluir:
Funes de grupo
7/25/2019 PBD - Parte 4
277/281
Funes de grupo Uma clusula GROUP BY
A palavra-chave DISTINCT
A palavra-chave ROWNUM da pseudocoluna Colunas definidas por expresses
Colunas NOT NULL em tabelas base que no sejam
selecionadas pela view
Usando a clusula WITH CHECK OPTION
possvel assegurar que operaes DML executadas naview permaneam no domnio da view usando aclusula
WITH CHECK OPTION:
7/25/2019 PBD - Parte 4
278/281
WITH CHECK OPTION
Qualquer tentativa para inserir (INSERT) uma linhacom um ID de departamento (department_id)
diferente de 20 ou atualizar (UPDATE) o nmero dodepartamento de qualquer linha na view falhar porqueviola a restrio WITH CHECK OPTION.
CREATE OR REPLACE VIEW empvu20AS SELECT *
FROM employeesWHERE department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck ;
Negando operaes DML
possvel impedir que alguma operao DML ocorra,adicionando a opo WITH READ ONLY definio daview.
Qualquer tentativa para executar uma operao DML
7/25/2019 PBD - Parte 4
279/281
Qualquer tentativa para executar uma operao DMLem linhas da view resultar em um erro do OracleServer.
CREATE OR REPLACE VIEW empvu10
Negando operaes DML
7/25/2019 PBD - Parte 4
280/281
p(employee_number, employee_name, job_title)
AS SELECT employee_id, last_name, job_idFROM employees
WHERE department_id = 10
WITH READ ONLY ;
Removendo uma view
possvel remover uma view sem perda de dadosporque a view se baseia em tabelas subjacentes no
banco de dados
7/25/2019 PBD - Parte 4
281/281
banco de dados.DROP VIEW view;
DROP VIEW empvu80;