Prévia do material em texto
www.fabioprado.netwww.fabioprado.net
Fábio Prado
Aprendendo SQL
Introdução
do
Treinamento
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
3
www.fabioprado.net
Objetivo
• Ensinar o aluno a escrever instruções SQL
para acessar e atualizar dados em SGBDs
Oracle, e criar/gerenciar objetos, tais como:
tabelas, índices, visões e outros;
• Apesar do treinamento ser orientado para
Bancos de Dados Oracle 10G, 11G e 12C, a
maior parte do aprendizado se aplicará em
qualquer SGBD que implemente o padrão
ANSI (Ex.: Mysql, SQL Server, DB2).
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
4
www.fabioprado.net
janeiro de 15 4
Objetivo
• O treinamento abordará o conteúdo dos
exames 1Z0-051 (OCA/OCP 10G e 11G) e
1Z0-061 (OCA/OCP 12c);
• Ao longo do treinamento, o aluno terá
exercícios preparatórios para os exames
mencionados acima.
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
5
www.fabioprado.net
Material
• Totalmente elaborado pelo instrutor do
treinamento de acordo com a sua
experiência, conhecimentos e pesquisas;
• Parte téorica resumida para facilitar e
agilizar o aprendizado;
• Composto adicionalmente por scripts que
aplicam todos os conceitos, técnicas e dicas
aprendidas durante o treinamento e que
servirão para referências futuras.
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
6
www.fabioprado.net
Conteúdo programático
1. Introdução
2. Recuperando dados com a instrução SELECT
3. Utilizando funções em instruções SQL
4. Utilizando agrupamentos e funções de grupo
5. Recuperando dados de múltiplas tabelas
6. Utilizando operadores de conjunto
7. Atualizando dados
8. Utilizando instruções DDL
9. Utilizando instruções DCL
10. Consultando visões no Dicionário de Dados
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
7
www.fabioprado.net
Exame 1Z0-051
Múltipla escolhaFormato:
60%Pontuação aprovação:
66Qtde. questões:
120 minutosDuração:
US$ 125Preço:
-Oracle Database 10g Administrator Certified Associate
-Oracle Database 11g Administrator Certified Associate
-Oracle PL/SQL Developer Certified Associate
-OPN Certified Specialist
Certificações
associadas:
http://education.oracle.com/pls/web_prod-plq-
dad/db_pages.getpage?page_id=5001&get_params=p_exam_id:1Z0-051Link oficial:
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
8
www.fabioprado.net
1Z0-051 x Aprendendo SQL
8. Utilizando instruções DDLCreating Other Schema Objects
8. Utilizando instruções DDLUsing DDL Statements to Create and Manage Tables
7. Atualizando dadosManipulating Data
6. Utilizando operadores de conjuntoUsing the Set Operators
5. Recuperando dados de múltiplas tabelas e subqueriesUsing Subqueries to Solve Queries
5. Recuperando dados de múltiplas tabelas e subqueriesDisplaying Data from Multiple Tables
4. Utilizando agrupamentos e funções de grupoReporting Aggregated Data Using the Group Functions
3. Utilizando funções em instruções SQLUsing Conversion Functions and Conditional Expressions
3. Utilizando funções em instruções SQLUsing Single-Row Functions to Customize Output
2. Recuperando dados com a instrução SELECTRestricting and Sorting Data
2. Recuperando dados com a instrução SELECTRetrieving Data Using the SQL SELECT Statement
Aprendendo SQLExame 1Z0-051
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
9
www.fabioprado.net
Exame 1Z0-061
Múltipla escolhaFormato:
65%Pontuação aprovação:
75Qtde. questões:
120 minutosDuração:
US$ 125Preço:
-OPN Certified Specialist;
-Oracle Database 12c Administrator Certified
Associate;
-ORACLE MASTER Bronze Oracle Database 12c (Japan).
Certificações
associadas:
http://education.oracle.com/pls/web_prod-plq-
dad/db_pages.getpage?page_id=5001&get_params=p_exam_id:1Z0-061Link oficial:
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
10
www.fabioprado.net
1Z0-061 x Aprendendo SQL
8. Utilizando instruções DDLIntroduction to Data Definition Language
7. Atualizando dadosManipulating Tables using DML statements
5. Recuperando dados de múltiplas tabelas e subqueries
6. Utilizando operadores de conjunto
Using Subqueries to Solve Queries
5. Recuperando dados de múltiplas tabelas e subqueriesDisplaying Data from Multiple Tables Using Joins
4. Utilizando agrupamentos e funções de grupoReporting Aggregated Data Using the Group Functions
3. Utilizando funções em instruções SQLUsing Conversion Functions and Conditional Expressions
3. Utilizando funções em instruções SQLUsing Single-Row Functions to Customize Output
2. Recuperando dados com a instrução SELECTRestricting and Sorting Data
2. Recuperando dados com a instrução SELECTRetrieving Data Using the SQL SELECT Statement
1. IntroduçãoIntroduction
Aprendendo SQL1Z0-061
FIM
www.fabioprado.netwww.fabioprado.net
Fábio Prado
Aprendendo SQL
Capítulo 1
Introdução
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
3
www.fabioprado.net
Introdução
• Neste capítulo veremos:
— Conceitos de Bancos de Dados Relacionais e Bancos de Dados
Objeto-Relacionais;
— Conceitos de Modelo Entidade-Relacionamento;
— Conceitos de instruções SQL;
— Classificação das instruções SQL;
— Novos recursos do Oracle Database 12c;
— Conceitos do Oracle Cloud 12c.
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
4
www.fabioprado.net
Banco de Dados Relacional
• Edgar F. Codd, pesquisador da IBM, propôs o
modelo para sistemas de BD relacional em
1970 e este modelo tornou-se a base dos
SGBDRs;
• Em 1979, foi lançado o Oracle 2, o primeiro BD
relacional (com SQL) comercializado no
mundo;
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
5
www.fabioprado.net
Banco de Dados Relacional
• A estrutura básica de um SGBDR contém:
— Coleção de objetos ou relacionamentos;
— Conjunto de operadores para atuar nos relacionamentos;
— Mecanismos para garantir a integridade dos dados com precisão e
consistência.
Fonte: Oracle Corporation
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
6
www.fabioprado.net
Modelo Entidade-Relacionamento
• É uma representação da realidade composta
basicamente por entidades, relacionamentos
e atributos;
• Pode ser representado graficamente pelo
Diagrama de Entidades e Relacionamentos
(DER) de Peter Chen, criado em 1976.
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
7
www.fabioprado.net
Modelo Entidade-Relacionamento
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
8
www.fabioprado.net
Banco de Dados Objeto Relacional
• Um sistema de gerenciamento de BD Objeto-
relacional (SGBDOR) é similar a um SGBDR;
• Suporta objetos, classes e herança nos
esquemas do BD e nas instruções SQL.
• Para mais informações, consulte o link:
— http://en.wikipedia.org/wiki/Object-relational_database.
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
9
www.fabioprado.net
O que é SQL?
SQL = Structured Query Language
• Conjunto de instruções ou comandos para
manipular dados em bancos de dados
relacionais;
• Foi desenvolvida em 1970 pela IBM e
padronizada pelo Instituto Americano Nacional
de Padrões (ANSI) em 1986;
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
10
www.fabioprado.net
O que é SQL?
• Exemplos de versões SQL do padrão ANSI:
— SQL-86:
� 1ª versão da linguagem, consiste basicamente na linguagem inicial criada
pela IBM.
— SQL-99:
� Versão que teve a implementação de mais recursos novos significativos,
entre eles: expressões regulares, queries recursivas, triggers, LOB;
— SQL-2003:
� Inclusão de suporte básico ao padrão XML, instrução MERGE, colunas
com valores auto-incrementaisetc.
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
11
www.fabioprado.net
Padrão ANSI x dialetos
• Todo SGBDR possui comandos SQL específicos que não
pertencem ao padrão ANSI. Estes comandos são extensões
conhecidas como dialetos;
• Evite os dialetos, utilize sempre o padrão ANSI para obter
os seguintes benefícios:
— Permitir portabilidade entre SGBDs distintos;
— Minimizar riscos de escrever SQL ruim;
— Possibilitar melhor performance em instruções SQL complexas.
• O Oracle Database 11G é aderente ao padrão ANSI
SQL:2008. O 12c é aderente ao padrão ANSI SQL:2011.
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
12
www.fabioprado.net
Padrão ANSI x dialetos
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
13
www.fabioprado.net
Grupos de instruções SQL
• A linguagem SQL é subdividida em 4
grupos:
—DML (Data Manipulation Language):
� SELECT, INSERT, UPDATE, DELETE e MERGE;
—DDL (Data Definition Language):
� CREATE, ALTER, DROP, RENAME, TRUNCATE e COMMENT;
—DCL (Data Control Language):
� GRANT e REVOKE;
—Transaction Control:
� COMMIT, ROLLBACK e SAVEPOINT.
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
14
www.fabioprado.net
Grupos de instruções SQL
Fonte: Oracle Corporation
Capítulo 1
Parte Final
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
16
www.fabioprado.net
Novos recursos do Oracle 12c
• Melhorias em instruções SQL, tais como:
— Queries do tipo Top N;
— Funções PL/SQL na cláusula WITH;
— Execução concorrente de UNION e UNION ALL;
— Parallel DML em colunas LOB SecureFiles;
— DDL online ao apagar, por exemplo um índice ou constraint;
— Estatísticas dinâmicas, otimização de query adaptável, novos tipos de
histogramas;
— Truncate Table Cascade.
• Melhorias em operações de backup via RMAN, tais como:
— Restaurar uma ou mais tabelas;
— Cross-Plataform Backup and Restore;
• Arquitetura Multitenant;
• Para mais detalhes, leia no blog FABIOPRADO.NET o artigo
Novidades do Oracle Database 12c (Parte 1).
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
17
www.fabioprado.net
EM Cloud Control 12c
• Evolução do EM Grid Control
• Para mais detalhes, consulte os links:
— http://www.oracle.com/technetwork/oem/pdf/512028.pdf
— http://www.oracle.com/technetwork/oem/cloud-mgmt/cloudmgmt12c-wp-516612.pdf
Fonte: Oracle Corporation
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
18
www.fabioprado.net
Schema HR
• Para mais informações, leia no blog FABIOPRADO.NET o artigo:
— Instalando o schema de exemplo HR.
FIM
www.fabioprado.netwww.fabioprado.net
Fábio Prado
Aprendendo SQL
Capítulo 2
Recuperando dados
com a instrução SELECT
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
3
www.fabioprado.net
Introdução
• Neste capítulo veremos:
— Conceitos de projeção e seleção;
— Sintaxe básica das instruções SELECT;
— Expressões aritméticas e tratamento de valores nulos;
— Operadores e literais;
— Restrição e ordenação de dados.
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
4
www.fabioprado.net
Projeção X Seleção
• Para executar uma instrução SQL é importante
entender 2 conceitos básicos:
— Projeção:
� Colunas de uma ou mais tabelas a serem recuperadas;
� PROJEÇÃO = Lista de colunas.
— Seleção:
� Linhas de uma ou mais tabelas a serem recuperadas;
� SELEÇÃO = Filtro de linhas.
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
5
www.fabioprado.net
Sintaxe básica
SELECT * | {[DISTINCT] coluna |
expressão [alias], ...}
FROM tabela [alias]
WHERE coluna | expressão =
coluna | expressão
Observações:
- Após a cláusula SELECT, deve ser especificada a lista de colunas a ser recuperada;
- Após a cláusula FROM, deve(m) ser especificada(s) a(s) tabela(s).
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
6
www.fabioprado.net
Características gerais
• As instruções SQL não são case-sensitive;
• As cláusulas podem ser separadas em linhas
diferentes e identadas para facilitar o entendimento;
• O terminador ; é requerido somente no SQL Plus ou
quando são executadas N instruções;
• Aliases ou apelidos são opcionais e não requerem os
delimitadores “” quando a palavra não contém:
— Caracteres especiais, tais como o espaço em branco;
— Palavras reservadas.
• A palavra reservada AS que precede um alias é de uso
opcional.
Operadores aritméticos,
valores nulos, literais e
comandos básicos
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
8
www.fabioprado.net
Expressões aritméticas
Divisão/
Multiplicação*
Subtração-
Adição+
DescriçãoOperador
• É possível criar expressões com valores numéricos
e datas, usando operadores aritméticos;
• Assim como na aritmética, é possível utilizar
parênteses para indicar precedência de cálculo;
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
9
www.fabioprado.net
Valores nulos
NULL = valor desconhecido ou inexistente
• Valores nulos não são a mesma coisa que zero
ou espaços vazios (‘’);
• Quando você vê em alguma ferramenta (Ex.:
SQL Developer) a palavra (null) representando
o valor de uma coluna, entenda que a
ferramenta possui uma configuração que
substitui o valor nulo pela string (null) para
facilitar a visualização e entendimento.
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
10
www.fabioprado.net
Valores nulos
• Cuidado ao efetuar cálculos com valores nulos;
• Qualquer cálculo efetuado com um valor nulo
retornará sempre nulo.
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
11
www.fabioprado.net
Operadores e literais
• Use cuidadosamente os operadores de precedência ()
para retornar o resultado esperado;
• Utilize os caracteres || para concatenar valores ou
colunas (somente string), retornando-os como se fosse
uma única coluna;
• Literais podem ser concatenados nos valores ou
colunas de uma instrução. Ao concatenar datas ou
caracteres, delimite-os utilizando aspas simples (‘’);
• Quando a string possui aspas simples em seu conteúdo,
utilize um delimitador customizado através do
operador de notações q.
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
12
www.fabioprado.net
Recursos diversos
• Utilize o comando DISTINCT para eliminar valores
repetidos;
• Utilize o comando DESC ou DESCRIBE para ver a
estrutura (colunas, tipos de dados e constraint
NOT NULL) de uma tabela;
• Utilize variáveis de substituição através do
operador & ou && para substituir valores, em
tempo de execução, dentro de uma instrução
SQL.
Restringindo dados
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
14
www.fabioprado.net
Restringindo dados
• Para restringir dados é necessário utilizar a
cláusula WHERE com um ou mais
operadores de comparação e, se
necessário, um ou mais operadores lógicos.
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
15
www.fabioprado.net
Operadores de comparação
Valor nuloIS NULL
Combinação de valores incluindo coringas (% ou _)LIKE
Lista de valoresIN
Entre (inclusive) 2 valores
BETWEEN ...
AND ...
Diferente<> ou !=
Menor ou igual<=
Menor<
Maior ou igual>=
Maior>
Igualdade=
DescriçãoOperador
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
16
www.fabioprado.net
Operadores lógicos
Retorma TRUE se a condição for falsaNOT
Retorna TRUE se qualquer condição for verdadeiraOR
Retorna TRUE se ambas as condições são verdadeirasAND
DescriçãoOperador
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
17
www.fabioprado.net
Regras de precedência
Condição lógica OR9
Condição lógica AND8
Condiçãológica NOT7
Desigualdade6
[NOT] BETWEEN5
IS [NOT] NULL, LIKE, [NOT] IN4
Condições de comparação3
Operador de concatenação2
Operadores aritméticos1
OperadorPrioridade
Obs.: Para sobrescrever as regras de precedência, utilize parênteses.
Ordenando dados
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
19
www.fabioprado.net
Ordenando dados
• É possível ordenar os dados retornados utilizando
a cláusula ORDER BY, no final da instrução
SELECT;
• A ordenação pode ser realizada em modo
crescente (padrão) ou decrescente, através dos
complementos ASC ou DESC;
• A ordenação pode ser realizada através dos
seguintes itens:
— Nome da(s) coluna(s);
— Apelido da(s) coluna(s);
— Ordem da(s) coluna(s);
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
20
www.fabioprado.net
Ordenando dados
• A ordenação pode ser realizada em uma ou
mais colunas, utilizando modos crescente e
decrescente, em cada coluna desejada;
• A ordenação consome muitos recursos de
processamento do BD. Evite-a sempre que
possível (principalmente em consultas que são
executadas frequentemente).
FIM
www.fabioprado.netwww.fabioprado.net
Fábio Prado
Aprendendo SQL
Capítulo 3
Utilizando funções Utilizando funções Utilizando funções Utilizando funções
em instruções SQLem instruções SQLem instruções SQLem instruções SQL
www.fabioprado.net
janeiro de 17 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
3
Introdução
• Neste capítulo veremos:
— Visão geral e tipos de funções;
— Single-row functions;
— Formatação de datas;
— Funções aninhadas;
— Conversões explícitas e implícitas;
— Expressões condicionais.
www.fabioprado.net
janeiro de 17 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
4
Visão geral
• As funções podem ser utilizadas, em geral,
para as seguintes finalidades:
— Converter valores;
— Transformar valores;
— Realizar cálculos.
• Características gerais:
— Podem receber 0 ou N argumentos (valor de uma coluna ou
expressão);
— Retornam obrigatóriamente um valor;
— Podem transformar tipos de dados;
— Podem ser aninhadas.
www.fabioprado.net
janeiro de 17 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
5
Visão geral
Fonte: SQL Functions – Oracle – Site: www.dotnetspider.com
www.fabioprado.net
janeiro de 17 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
6
Tipos de funções
• Existem 2 tipos de funções SQL:
— Single-row functions:
� Funções que retornam um valor por linha.
— Multiple-row functions:
� Funções que retornam um valor para N linhas;
� São utilizadas com agrupamentos.
www.fabioprado.net
janeiro de 17 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
7
Tipos de funções
Fonte: SQL Functions – Oracle – Site: www.dotnetspider.com
www.fabioprado.net
janeiro de 17 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
8
Single-row functions
• Retornam um resultado por linha;
• São subdivididas em funções para trabalhar com:
— Caracteres:
� LOWER, UPPER, INITCAP, CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD,
TRIM e REPLACE;
— Números:
� ROUND, TRUNC e MOD;
— Datas:
� SYSDATE, MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND
e TRUNC;
www.fabioprado.net
janeiro de 17 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
9
Single-row functions
— Conversões:
� TO_CHAR, TO_NUMBER e TO_DATE;
— Gerais:
� NVL, NVL2, NULLIF, COALESCE e DECODE.
Funções aninhadas, Funções aninhadas, Funções aninhadas, Funções aninhadas,
trabalhando com datas, trabalhando com datas, trabalhando com datas, trabalhando com datas,
conversões e conversões e conversões e conversões e
expressões condicionaisexpressões condicionaisexpressões condicionaisexpressões condicionais
www.fabioprado.net
janeiro de 17 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
11
Funções aninhadas
• Funções single-row podem ser aninhadas em
qualquer nível;
• As funções mais internas são executadas
primeiramente.
www.fabioprado.net
janeiro de 17 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
12
Trabalhando com datas
• O Oracle armazena datas em um formato interno
numérico que contém: século, ano, mês, dia,
horas, minutos e segundos;
• O formato de visualização padrão é DD-MON-RR,
onde RR corresponde ao século, conforme regras
abaixo:
a) Se ano atual é entre 0 e 49:
- Retorna o século atual se a data informada é entre 0 e 49;
- Retorna o século anterior se a data informada é entre 50 e 99.
b) Se ano atual é entre 50 e 99:
- Retorna o próximo século se a data informada é entre 0 e 49;
- Retorna o século atual se a data informada é entre 50 e 99.
www.fabioprado.net
janeiro de 17 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
13
Trabalhando com datas
• É possível utilizar operadores aritméticos com datas;
• Elementos de formatação de datas:
Elemento Descrição
YY ou YYYY Ano em 2 ou 4 dígitos (valor numérico)
YEAR Ano por extenso
MM Mês em 2 dígitos (valor numérico)
MONTH Mês por extenso
MON Mês por extenso em apenas 3 caracteres
DD Dia do mês (valor numérico)
DAY Dia por extenso
DY Dia por extenso em apenas 3 caracteres
www.fabioprado.net
janeiro de 17 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
14
Conversões explícitas X implícitas
• Não existe comparação entre 2 tipos de dados
diferentes;
• Se uma função de conversão não for utilizada
explicitamente, o Oracle tentará fazer uma
conversão implícita, quando for necessário;
• Para evitar erros e otimizar performance,
faça conversões explícitas;
www.fabioprado.net
janeiro de 17 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
15
Conversões explícitas X implícitas
• O Oracle só consegue fazer conversão implícita
nas seguintes situações:
— De NUMBER para VARCHAR2 ou CHAR;
— De DATE para VARCHAR2 ou CHAR;
— De VARCHAR2 ou CHAR para NUMBER;
— De VARCHAR2 ou CHAR para DATE;
• Regras gerais de conversão:
— Se entre 2 valores 1 for NUMBER, o Oracle converte o outro para
NUMBER também;
— Se entre 2 valores 1 for DATE, o Oracle converte o outro para
DATE também.
www.fabioprado.net
janeiro de 17 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
16
Expressões condicionais
• É possível utilizar a expressão condicional CASE
como alternativa à função DECODE para
implementar a lógica condicional de um IF-
THEN-ELSE;
• A vantagem do CASE é que ele permite
verificar qualquer condição, e não somente de
igualdade.
FIMFIMFIMFIM
www.fabioprado.netwww.fabioprado.net
Fábio Prado
Aprendendo SQL
Capítulo 4
Utilizando agrupamentos
e
funções de grupo
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
3
Introdução
• Neste capítulo veremos:
— Visão geral de agrupamentos;
— Funções de grupo e funções agregadas;
— Considerações gerais sobre agrupamentos e funções de grupo;
— Funções analíticas.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
4
Visão geral de Agrupamentos
• Pode ser entendido como um recurso que possui a
capacidade de processar múltiplos valores e retornar
um resultado único e é muito útil para realizar
cálculos;
• Permite agrupar o resultado de múltiplas linhas,
baseando-se nos valores da tabela inteira ou de uma
ou mais colunas;
• O agrupamento normalmente é utilizado com as
chamadas funções de grupo ou funções agregadas,
que operam em conjuntos de linhas para fornecer um
resultado por grupo;
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
5
Sintaxe
SELECT [coluna, …]
funcao_grupo(coluna), …
FROM tabela
[WHERE condicao]
[GROUP BY coluna, …]
[ORDER BY coluna, …]
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
6
Funções de grupo
• As funções degrupo mais utilizadas são:
— COUNT: retorna o total de linhas;
— SUM: retorna a soma de valores;
— AVG: retorna a média de valores;
— MAX: retorna o valor máximo;
— MIN: retorna o valor mínimo;
— VARIANCE: retorna a variância (soma dos quadrados dos desvios /
ocorrências);
— STDDEV: retorna o desvio padrão (raiz quadrada da variância).
Obs.: Para compreender melhor desvio padrão e variância é necessário ter conhecimentos prévios de
estatística (ver link http://educacao.uol.com.br/matematica/media-desvio-padrao-e-variancia-nocoes-de-estatistica.jhtm)
Considerações e
Funções Analíticas
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
8
Considerações gerais
• Considerações gerais sobre o uso de
agrupamentos e funções de grupo:
— As funções de grupo ignoram valores nulos, portanto, é uma boa
prática usar NVL;
— Todas as colunas da lista SELECT que não estão inclusas nas
funções de grupo devem estar incluídas na cláusula GROUP BY;
— Apelido da coluna não pode ser usado na cláusula GROUP BY;
— Use a cláusula HAVING para restringir grupos e WHERE apenas
para restringir linhas;
— É possível aninhar as funções de grupo.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
9
Funções analíticas
• Pesquise sobre funções analíticas para
desenvolver queries com agrupamentos mais
complexos;
• Elas podem ser utilizadas em conjuntos de
linhas independentes do agrupamento;
• Melhoram a produtividade e são mais
performáticas do que instruções SQL com
funções similares customizadas.
FIM
www.fabioprado.netwww.fabioprado.net
Fábio Prado
Aprendendo SQL
Capítulo 5
Recuperando dados
de múltiplas tabelas e
subqueries
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
3
Introdução
• Neste capítulo veremos:
— Visão geral de relacionamentos;
— Conceitos e considerações gerais ao efetuar ligações;
— Conceitos e utilização de equi-join, self-join, theta join, outer
join e produto cartesiano;
— Visão geral e utilização de subqueries;
— Suqueries escalares e correlacionadas.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
4
Visão geral
• Para recuperar dados de múltiplas tabelas
precisamos entender os conceitos
relacionados às ligações de tabelas (JOINs);
• Tipos de ligações (padrão ANSI SQL 1999) :
— Equi-joins (Natural join)
— Self-joins
— Theta Joins (Nonequijoins)
— Outer joins
— Produtos cartesianos
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
5
Sintaxe
SELECT tabela1.coluna,
tabela2.coluna FROM
tabela1
[NATURAL JOIN tabela2] |
[JOIN tabela2 USING (coluna)] |
[JOIN tabela2
ON (tabela1.coluna = tabela2.coluna)] |
[LEFT | RIGHT | FULL OUTER JOIN tabela2]
ON (tabela1.coluna = tabela2.coluna)] |
[CROSS JOIN tabela2]
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
6
Considerações gerais
• Use apelidos curtos para qualificar as
tabelas, ao invés do nome delas;
• Use os apelidos das tabelas para referenciar
as colunas (obrigatório a partir do 11G
quando houver colunas com nomes ambíguos);
• Ao recuperar na lista de colunas, 2 ou mais
colunas que possuem o mesmo nome, atribua
um apelido diferente para cada uma delas.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
7
Equi-join
• Recupera somente as linhas que possuem valores
iguais na(s) coluna(s) de ligação;
• Permite efetuar ligações através das seguintes
cláusulas:
— NATURAL JOIN:
� Ligação automática através de colunas com mesmo nome e tipo de dado;
— USING:
� Ligação automática através de colunas com somente mesmo nome;
— ON:
� Ligação efetuada através de colunas especificadas (não precisa ter mesmo
nome e nem mesmo tipo de dado).
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
8
Self-join
• Ocorre quando uma tabela é ligada a ela
mesma, ou seja, quando uma tabela possui
auto-relacionamento;
• Pode ser substituído por queries hierárquicas.
Para mais informações leia no blog
FABIOPRADO.NET o artigo Consultando dados
com queries hierárquicas.
Theta join, outer join e
produto cartesiano
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
10
Theta join
• Ocorre quando uma tabela é ligada a outra
através de uma faixa de valores;
• Nonequijoins significa que a ligação vai
retornar os registros de uma tabela baseando-
se na faixa de valores de uma segunda tabela.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
11
Outer join
• Difere-se de um Equi-Join porque retorna
além dos registros que combinam entre as 2
tabelas, os registros que não combinam,
conforme cláusulas abaixo:
— LEFT [OUTER] JOIN:
� Registros que não combinam da tabela à esquerda da ligação;
� Antes do 12c, somente o padrão ANSI permitia LEFT JOIN de uma tabela com 2 ou
mais (outras) tabelas. No 11G ou versões anteriores, ao tentar fazer isso no
dialeto Oracle, ocorrerá o erro ORA-01417;
— RIGHT [OUTER] JOIN:
� Registros que não combinam da tabela à direita da ligação;
— FULL [OUTER] JOIN:
� Registros que não combinam de ambas as tabelas da ligação.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
12
Outer join
• Valores nulos (NULL) retornarão nas colunas
das tabelas que contém as linhas que não
combinam;
• Utilize sempre a sintaxe do padrão ANSI. No
dialeto Oracle o caminho para fazer um FULL
OUTER JOIN é maior e menos performático.
• Cuidado! Por questões de performance, evite
quando for possível, Outer Joins!
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
13
Produto cartesiano
• Ocorre quando não há uma ligação entre as
tabelas;
• Com a clásula CROSS JOIN, retorna qtde. de
linha da 1ª tabela X qtde. de linhas da 2ª;
• Muitas vezes é consequência de:
— Ligações omitidas (por esquecimento ou falta de
conhecimento);
— Ligação inválida.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
14
Considerações finais
• Existem ainda 2 outros tipos de ligações (não
especificadas no padrão ANSI SQL 99):
— Semi-joins:
� Retorna somente linhas da 1º tabela que combinam com linhas de
uma 2ª tabela ou conjunto;
� Pode ser implementado através das cláusulas IN ou EXISTS.
— Anti-joins:
� Retorna somente linhas da 1º tabela NÃO que combinam com linhas
de uma 2ª tabela ou conjunto;
� Pode ser implementado através das cláusulas NOT IN ou NOT
EXISTS.
Utilizando subqueries
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
16
Visão geral
• Subqueries são representadas como uma query
contida em outra query;
• A query principal, mais à esquerda é conhecida como
Outer query e a query à direita (mais interna), a
subquery, é conhecida como Inner query;
• A Inner query é sempre processada primeiro;
• São conhecidas no padrão ANSI como Semi-joins e
Anti-joins.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
17
Visão geral
• Subqueries devem estar contidas entre
parênteses;
• Se a subquery retornar apenas uma linha,
utilize um operador single-row (Exs.: =, <>)
antes dos parênteses, caso contrário, utilize
um operador multiple-row (Ex.: IN);
• Valores nulos devem ser tratados para não
retornar resultado vazio.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
18
Visão geral
• Operadores single-row:
— Igualdade: =
— Maior que: >
— Maior ou igual que: >=
— Menor que: <
— Menor ou igual que: <=
—Diferente (não igual que): <> ou !=
• Operadores multiple-row:
— Igual a um membro da lista: IN
— Compara um valor a cada valor da lista: ANY
— Compara um valor a todos os valores da lista: ALL
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
19
Subqueries escalares
• Subqueries que retornam apenas um valor (1
linha e 1 coluna) são conhecidas como
subqueries escalares;
• Subqueries escalares podem ser usadas:
— Como parte de instruções DECODE e CASE;
— Todas as cláusulas da instrução SELECT, exceto GROUP BY;
— Na cláusula SET e WHERE de uma instrução UPDATE ou DELETE.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
20
Subqueries correlacionadas
• Subqueries correlacionadas possuem
relacionamento com a query principal;
• São processadas linha-a-linha;
• Permitem escrever consultas complexas
envolvendo valores agrupados.
FIM
www.fabioprado.netwww.fabioprado.net
Fábio Prado
Aprendendo SQL
Capítulo 6
Utilizando operadores
de conjunto
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
3
Introdução
• Neste capítulo veremos:
— Visão geral dos operadores de conjunto;
— Operadores de conjunto UNION, UNION ALL, INTERSECT e
MINUS;
— Ordenação de conjuntos.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
4
Visão geral
• Os operadores de conjuntos permitem unir ou
subtrair dados de instruções SQL distintas, unindo o
resultado delas;
• As instruções SQL se integram e passam a compor
uma só instrução;
• Todas as instruções SQL devem ter a mesma qtde. e
tipo de colunas da 1ª instrução;
• Os operadores de conjunto são: UNION ALL, UNION,
INTERSECT e MINUS.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
5
UNION ALL
• Permite unir 2 ou mais conjuntos de dados;
• Todos os dados (mesmo os repetidos) são
unidos;
• A partir do 12c, os conjuntos podem ser
processados paralelamente.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
6
UNION
• Permite unir 2 ou mais conjuntos de dados;
• Somente os dados distintos (elimina os dados
repetidos) são unidos;
• Para eliminar os dados repetidos, um
DISTINCT implícito é executado na instrução
SQL;
• A partir do 12c, os conjuntos podem ser
processados paralelamente.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
7
INTERSECT
• Permite unir 2 ou mais conjuntos de dados;
• Somente os dados comuns (elimina os dados
diferentes) são unidos.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
8
MINUS
• Permite subtrair os dados de 2 ou mais
conjuntos;
• Permanece os dados do 1º conjunto que não
estão contidos no 2º conjunto.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
9
Ordenação
• Por padrão, a ordenação é sempre realizada
na primeira coluna (exceto UNION ALL);
• Se for necessário efetuar outra ordenação
qualquer, a cláusula ORDER BY deve ser
inclusa depois do último conjunto;
• Se for utilizar apelidos de colunas para
efetuar a ordenação, utilize os apelidos do 1º
conjunto.
FIM
www.fabioprado.netwww.fabioprado.net
Fábio Prado
Aprendendo SQL
Capítulo 7
Atualizando dados
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
3
Introdução
• Neste capítulo veremos:
— Conceitos de instruções DML;
— Instruções INSERT, UPDATE e MERGE;
— INSERT Multitable;
— Instruções DELETE e TRUNCATE;
— Instrução SELECT FOR UPDATE;
— Conceitos e utilização de transações.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
4
Visão geral
• Conceitualmente DML abrange as instruções
SELECT, INSERT, UPDATE e DELETE, porém
na documentação da Oracle, normalmente
DML se refere somente às instruções que
atualizam dados: INSERT, UPDATE e DELETE;
• Uma ou N instruções DML constituem uma
transação, que é finalizada sempre através
de um COMMIT ou ROLLBACK.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
5
INSERT
• Para inserir dados em uma tabela, utilizamos
a instrução INSERT;
• Sintaxe:
INSERT INTO tabela [(coluna [,coluna …] ) ] -- lista de colunas
VALUES (valor [,valor …] );
• A lista de colunas é opcional, mas é uma boa
prática sempre listar todas as colunas da
tabela para facilitar o entendimento da
instrução e evitar possíveis erros futuros.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
6
INSERT
• Valores nulos são inseridos nas colunas não
referenciadas na instrução;
• É possível inserir múltiplas linhas substituindo a
cláusula VALUES por uma subquery contendo uma
instrução SELECT;
• Para inserir o valor DEFAULT de colunas, quando ele
existir, basta omitir a(s) coluna(s) desejada(s) na
instrução SQL (INSERT ou UPDATE) ou incluir a
palavra-chave DEFAULT na cláusula VALUES.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
7
INSERT Multitable
• INSERTs Multitable permitem inserir dados de uma
determinada origem, em múltiplas tabelas, em uma
única instrução SQL, ou seja, um único INSERT
(Multitable) substitui múltiplas instruções INSERT
convencionais;
• Por executar tudo em uma só instrução, é mais rápido
do que múltiplos INSERTS separados;
• Principais características:
— Não funciona através de dblinks;
— Sequências não podem ser usadas;
— Não pode exceder 999 colunas na(s) cláusula(s) INTO.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
8
UPDATE
• Para alterar dados em uma tabela, utilizamos
a instrução UPDATE;
• Sintaxe:
UPDATE tabela
SET coluna = valor [, coluna = valor, …]
[WHERE condição];
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
9
UPDATE
• Podemos alterar 1 ou N linhas por vez;
• Não esqueça a clásula WHERE quando quiser
restringir o conjunto de linhas a serem
alteradas;
• É possível utilizar subqueries como valores ou
filtros.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
10
MERGE
• Utilize a instrução MERGE para realizar
processamento UPSERTs: INSERT ou UPDATE;
• Sintaxe:
MERGE INTO tabela_destino apelido_origem
USING (tabela_origem | visão | subquery) apelido_destino
ON (condição_de_ligação)
WHEN MATCHED THEN
UPDATE SET
apelido_destino.col1 = apelido_origem.col1 …
WHEN NOT MATCHED THEN
INSERT (lista_colunas_tabela_destino)
VALUES (lista_colunas_tabela_origem)
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
11
MERGE
• Substitui múltiplas instruções SQL (SELECT e INSERT +
SELECT e UPDATE) por uma única instrução SQL, que
é mais enxuta e mais rápida;
• Características do MERGE:
— Pode combinar múltiplas operações: INSERT, UPDATE e DELETE;
— É uma instrução determinística, ou seja, não permite atualizar a
mesma linha de uma tabela múltiplas vezes na mesma instrução;
— Na maior parte dos casos é mais flexível e eficiente que as soluções
tradicionais de “UPSERT”.
— Cuidado ao utilizar sequences! Mesmo se uma condição (Ex.: NOT
MATCHED) não for alcançada, o número sequencial dela será perdido!
DELETE, TRUNCATE e
SELECT FOR UPDATE
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
13
DELETE
• Para apagar dados de uma tabela, utilizamos
a instrução DELETE;
• Sintaxe:
DELETE [FROM] tabela
[WHERE condição];
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.14
DELETE
• Podemos apagar 1 ou N linhas por vez;
• Não esqueça a clásula WHERE quando quiser restringir
o conjunto de linhas a serem apagadas;
• É possível possível utilizar subqueries como filtros;
• Quando for necessário apagar todas as linhas de uma
tabela, se você tem certeza de que naõ precisará
mais recuperá-las, prefira a instrução TRUNCATE.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
15
TRUNCATE
• É uma instrução DDL que possui as seguintes
características:
— Limpa (zera) a tabela;
— Não é transacional, ou seja, não permite desfazer a operação;
— É muito mais rápida que um DELETE sem cláusula WHERE;
— Até o 11G não se aplica em tabelas que possuem FKs. No 12c, é
possível executar TRUNCATE CASCADE se a FK for tipo ON
DELETE CASCADE.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
16
SELECT FOR UPDATE
• Permite bloquear as linhas durante a leitura;
• Libera as linhas somente após um COMMIT ou
ROLLBACK.
Controle transacional
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
18
Controle transacional
• Transação é uma sequência de operações que
são tratadas como um bloco único e indivisível
(atômico);
• As instruções de controle transacional
permitem finalizar uma transação,
efetivando-a ou desfazendo-a;
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
19
Controle transacional
• Uma transação é finalizada após uma das seguintes situações:
— Ao executar um comando COMMIT ou ROLLBACK
� COMMIT efetiva a transação;
� ROLLBACK desfaz a transação
— Ao executar uma instrução DDL ou DCL:
� Ao executá-las, ocorre um commit automático na transação existente;
— Quando o usuário finaliza a sessão:
� Finalização normal realiza COMMIT;
� Finalização anormal realiza ROLLBACK.
— Quando o sistema falha:
� Realiza ROLLBACK.
Obs.: O comando SAVEPOINT permite marcar pontos dentro de uma transação.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
20
Controle transacional
• Considerações gerais sobre transações:
— Somente o usuário atual pode ver os dados da transação em
andamento;
— As linhas afetadas pela transação são bloqueadas para escrita
até a sua finalização, portanto, outros usuários só poderão
consultá-las;
— Quando um COMMIT é executado, todos os usuários podem ver
as alterações produzidas pela transação e as linhas bloqueadas
são liberadas;
— Savepoints existentes são liberados ao final da transação.
FIM
www.fabioprado.netwww.fabioprado.net
Fábio Prado
Aprendendo SQL
Capítulo 8
Utilizando instruções DDL
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
3
Introdução
• Neste capítulo veremos:
— Regrais gerais para criar objetos;
— Criação, deleção e alteração de tabelas;
— Principais tipos de dados;
— Criação e manutenção de constraints, visões, sequências,
índices e sinônimos.
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
4
Visão geral
• Ao executar instruções DDL as transações existentes são
commitadas automaticamente. TRUNCATE é uma instrução
DDL;
• Instruções DDL não são transacionais;
• Evite executar instruções DDL nos BDs de produção no horário
comercial, pois elas invalidam cursores e podem causar locks
nas tabelas;
• No 12c, muitas operações de manutenção realizadas através de
instruções DDL não causam mais locks na tabela, portanto, não
causam impacto nas aplicações. Exemplos: DROP INDEX
ONLINE, DROP CONSTRAINT ONLINE, ALTER INDEX [VISIBLE |
INVISIBLE].
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
5
Visão geral
• Regras gerais para compor o nome de
tabelas, colunas, índices, constraints, visões,
sequências e sinônimos:
— Deve conter até 30 caracteres;
— Deve começar com uma letra;
— Deve conter somente os seguintes caracteres: A-Z, a-z, 0-9, _,
$ e #;
— Não pode duplicar o nome de outro objeto dentro do mesmo
schema;
— Pode ser case-sensitive, conter outros caracteres especiais ou
palavras reservadas do Oracle, somente quando o nome for
delimitado por aspas duplas (não recomendado);
Tabelas
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
7
Criando Tabelas
• Para criar uma tabela é necessário:
— Ter o privilégio CREATE TABLE;
— Possuir permissão para gravar dados em um determinado tablespace;
— Especificar no mínimo, o nome da tabela e das colunas, tipo de dado e
tamanho da coluna (quando o tipo de dado exigir);
— Especificar um valor default (com o mesmo tipo de dado) para as colunas, se
for necessário:
� Valores permitidos: literais, expressão ou funções SQL.
� Valores não permitidos: nomes de colunas ou pseudocolunas
• Sintaxe:
CREATE TABLE [schema].tabela
(coluna tipo_dado [DEFAULT expressão] [, …]);
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
8
Principais tipos de dados
Dados binários armazenados em arquivos externos (até 4 GB)BFILE
Dados binários de tamanho variável (entre 8 TB e 128 TB).BLOB
Dados binários.RAW e LONG RAW
Caracteres de tamanho variável (entre 8 TB e 128 TB).CLOB
Caracteres de tamanho variável até 2 GB. Não utilizar mais, pois foi substituído
pelo CLOB. Oracle permite somente 1 coluna deste tipo por tabela.
LONG
Data contendo ano, mês, dia, horas, minutos e segundos.DATE
Números de tamanho variável com até 38 dígitos de precisão, onde p representa a
precisão (tamanho total) e s a escala (qtde. de decimais). P e S são opcionais.
NUMBER (p, s)
Caracteres de tamanho fixo com tamanho até 2000. Tamanho não utilizado é
preenchido com espaços em branco.
CHAR (tamanho)
Caracteres de tamanho variável com tamanho até 4000 (até 11G) ou 32767 (somente
12c).
VARCHAR2
(tamanho)
DescriçãoTipo de dado
Referência: (http://docs.oracle.com/cd/B28359_01/server.111/b28320/limits001.htm)
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
9
Constraints
• Crie constraints nas tabelas para:
— Aplicar regras de negócio e impor restrições ao inserir/atualizar dados;
— Prevenir a deleção de dados se existirem dependências.
• Tipos de constraints válidas:
— NOT NULL: Não permite valores nulos;
— UNIQUE: Permite somente valores únicos;
— PRIMARY KEY: Valor identificador de cada linha da tabela. Permite
somente valores únicos e não permite valores nulos;
— FOREIGN KEY: Valor identificador de um relacionamento com a PK da
tabela pai;
— CHECK: Expressão para validar/restringir valor de entrada.
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
10
Constraints
• Considerações gerais:
— Podem ser nomeadas explicitamente ou Oracle gera
implicitamente um nome contendo SYS_ + string aleatória;
— Podem ser criadas junto com a tabela ou depois de sua criação;
— Podem ser criadas no nível de coluna ou da tabela.
• Sintaxe:
CREATE TABLE [schema].tabela
(coluna tipo_dado [DEFAULT expressão]
[coluna_constraint],
…
[tabela_constraint] [,…]);
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
11
Constraints
• Criando constraint no nível da tabela:
CREATE TABLE [schema].tabela
(coluna tipo_dado [DEFAULT expressão]
[coluna_constraint],
…
[tabela_constraint] [,…]);
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
12
Alterando tabelas
• Podemos utilizar o comando ALTER TABLE
para:
— Colocar a tabela em modo somente leitura ou leitura/escrita;
— Adicionar/modificar/remover/renomear colunas;
— Adicionar/remover constraints;
— Adicionar colunas invisíveis (somente 12c);
— Renomear a tabela.
www.fabioprado.net
fevereirode 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
13
Apagando tabelas
• Uma tabela pode ser apagada em 2 modos (a
partir do 10G):
— Tabela sendo movida para a lixeira (recycle bin);
— Tabela apagada definitivamente (sem ir para a lixeira).
Visões
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
15
Visões
• Uma visão é um objeto que cria um novo ponto de
acesso a uma ou mais colunas de uma ou mais
tabelas;
• São classificadas em 2 tipos:
— Simples:
� Acessa 1 tabela;
� Não contém funções e nem agrupamentos de dados;
� Permite atualizações.
— Complexa:
� Acessa 1 ou mais tabelas;
� Pode conter funções e agrupamentos de dados;
� Nem sempre permite atualizações. Uma técnica muito pouco utilizada (não
recomendada), é utilizar triggers INSTEAD OF para permitir atualizações em
visões complexas.
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
16
Visões
• Vantagens:
— Encapsulamento de dados;
— Restringe acesso a dados não autorizados;
— Facilita o gerenciamento de acesso aos dados;
— Permite simplificar a escrita de queries complexas.
• Sintaxe:
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW nome
[(alias [, alias] …)]
AS
subquery
[ WITH CHECK OPTION [CONSTRAINT constraint] ]
[ WITH READ ONLY [CONSTRAINT constraint] ];
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
17
Visões
• Utilize a cláusula WITH CHECK OPTION para
restringir DML ao escopo de dados da visão;
• Utilize a cláusula WITH READ ONLY para
impedir DML na visão;
• Para apagar uma visão, execute a instrução
SQL abaixo:
DROP VIEW nome_visao;
Sequências
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
19
Sequências
• Uma sequência é um objeto que permite gerar
números únicos que normalmente são utilizados como
valores de PKs;
• No 12c, sequências podem ser implementadas como
valor default de colunas. As colunas do tipo IDENTITY
(novas no 12c) implementam internamente sequências
com valor default;
• Características principais de uma sequência:
— Automaticamente geram números únicos;
— Valor inicial e final são pré-definidos;
— Valor incremental pode ser customizado;
— Valores podem ser reciclados e/ou colocados em cache para otimizar o
acesso.
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
20
Sequências
• Sintaxe:
CREATE SEQUENCE nome_sequencia
[INCREMENT BY n]
[START WITH n]
[ {MAXVALUE n | NOMAXVALUE} ]
[ {MINVALUE n | NOMINVALUE} ]
[ {CYCLE | NOCYCLE} ]
[ {CACHE n | NOCACHE} ];
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
21
Sequências
• Pseudocolunas NEXTVAL e CURRVAL:
— NEXTVAL:
� Retorna o próximo valor de uma sequência;
� Retorna valores únicos mesmo se ela for chamada por usuários
diferentes;
— CURRVAL:
� Retorna o valor atual de uma sequência;
� O usuário tem que chamar NEXTVAL previamente.
• Para verificar o último valor de uma sequência,
recomenda-se consultar a coluna LAST_NUMBER
da visão do DD ALL_SEQUENCES.
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
22
Sequências
• Sequências com valor em cache são acessadas
muito rapidamente. Para mais detalhes,
consulte no blog FABIOPRADO.NET o artigo
Cache em Oracle Sequences;
• GAPs podem ocorrer ao utilizar sequences, nas
seguintes situações:
— Ao executar ROLLBACK;
— Quando o sistema falhar;
— Se a sequência for chamada indevidamente;
— Se o BD for reiniciado (este item pode ser contornado).
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
23
Sequências
• Para modificar uma sequência:
— Execute o comando ALTER SEQUENCE ….;
— O valor atual é o único parâmetro que não pode ser modificado;
— Usuário deve ser o dono da sequência ou ter o privilégio ALTER
SEQUENCE.
• Para apagar uma sequência, execute o
comando:
DROP SEQUENCE nome_sequencia;
ÍNDICES
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
25
Índices
O índice de um livro
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
26
Índices
• Podem ser de 4 tipos:
— B-tree (normal):
� Utilizado para otimizar alta cardinalidade.
— Bitmap:
� Utilizado para otimizar baixa cardinalidade.
— Function Based:
� Utilizado para otimizar consultas que utilizam funções.
— Domain Indexes:
� Índices mais complexos para atender situações especiais, tais
como: grandes pesquisas textuais ou pesquisas de dados
geográficos.
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
27
Índices
• Índices são objetos que servem para otimizar o acesso
aos dados de uma tabela;
• A indexação é feita no nível de coluna(s) e sua
manutenção é realizada automaticamente pelo
Oracle Database;
• Otimizam consultas, mas degradam as atualizações.
Cada índice criado em uma tabela degrada em média
3X cada atualização (INSERT, UPDATE, DELETE ou
MERGE);
• No 12c, podemos criar mais de 1 índice na mesma
coluna, mas somente 1 deles deve estar visível.
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
28
Índices
• Podem ser criados de 2 formas:
— Automaticamente:
� O Oracle cria índices automaticamente para toda PK e UK.
— Manualmente:
� Usuário pode criar índices nas colunas desejadas.
• Devem ser criados principalmente:
— Nas colunas utilizadas em filtros ou ligações de consultas
frequentes;
— Tabelas grandes (em tabelas pequenas normalmente eles são ignorados);
— Quando a consulta retorna poucas linhas ou quando poucas
linhas são ligadas.
SINÔNIMOS
www.fabioprado.net
fevereiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
30
Sinônimos
• Sinônimos são objetos que simplificam o acesso a
outros objetos do Banco de Dados, tais como:
— Tabelas, sequências, visões, procedures, funções e packages;
• Permitem criar referências locais a objetos de outros
usuários;
• Podem ser públicos ou privados;
• O usuário que está criando o sinônimo deve ter
privilégios de acesso aos objetos que estão sendo
referenciados.
FIM
www.fabioprado.netwww.fabioprado.net
Fábio Prado
Aprendendo SQL
Capítulo 9
Utilizando instruções DCL
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
3
Introdução
• Neste capítulo veremos:
— Visão geral das instruções DCL;
— Conceitos e gerenciamento de privilégios de sistema e de
objetos;
— Conceitos e gerenciamento de roles;
— Como consultar privilégios de sistema, objetos e roles.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
4
Visão geral
• O controle de acesso é uma das tarefas do
DBA e pode ser realizado por meio da
concessão (GRANT) ou revogação (REVOKE)
de:
—Privilégios de sistema;
—Privilégios de objetos;
—Roles.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
5
Privilégios de sistema
• Permitem realizar determinadas ações dentro do
Banco de Dados, tais como: criar, alterar e apagar
usuários e objetos;
• São concedidos ou revogados por usuários com
privilégios administrativos (DBAs);
• Existem mais de 100 privilégios de sistema
disponíveis;
• Privilégios de sistema mais comuns que devem ser
concedidos a um desenvolvedor: CREATE SESSION,
CREATE TABLE, CREATE SEQUENCE, CREATE VIEW e
CREATE PROCEDURE.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
6
Privilégios de objeto
• Atuam sobre os objetos do Banco de Dados,
concedendo ou revogando privilégios que
permitem acessar/atualizar dados;
• São concedidos por usuárioscom privilégios
administrativos ou pelos donos dos objetos;
• Privilégios de objeto existentes: INSERT,
SELECT, UPDATE, DELETE, ALTER , INDEX e
REFERENCES.
Roles e consultas de
privilégios
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
8
Roles
• Roles contém grupos de privilégios que podem
ser atribuídos aos usuários para facilitar o
gerenciamento de privilégios;
• Existem muitas roles pré-definidas no Oracle
Database que podem facilitar a atribuição de
privilégios para usuários que necessitam de
privilégios para executar tarefas específicas,
como por exemplo, a role DBA;
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
9
Roles
• Roles podem conter, além de privilégios,
outras roles;
• Roles podem conter senhas, que exigem que o
usuário forneça-a antes de receber os seus
privilégios. Somente a partir do 11G, é
obrigatório o fornecimento desta senha (através
de uma instrução SQL).
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
10
Roles
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
11
Consultando privilégios
Privilégios de sistema concedidos ao usuário logado.USER_SYS_PRIVS
Roles acessíveis pelo usuário logadoUSER_ROLE_PRIVS
Privilégios de tabelas concedidos às rolesROLE_TAB_PRIVS
Privilégios de sistema concedidos às rolesROLE_SYS_PRIVS
DescriçãoVisão do DD
FIM
www.fabioprado.netwww.fabioprado.net
Fábio Prado
Aprendendo SQL
Capítulo 10
Consultando visões do
Dicionário de Dados
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
3
Visão geral
• Neste capítulo veremos:
— O que é o Dicionário de Dados;
— Como pesquisar visões do Dicionário de Dados para obter
informações sobre objetos do BD;
— Como adicionar comentários em tabelas e como consultar estes
comentários.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
4
O que é o Dicionário de Dados?
• O Dicionário de Dados (DD) é um conjunto de
objetos que possui os metadados de todo o
BD;
• Seus objetos residem no schema SYS e
tablespace SYSTEM;
• É composto basicamente de tabelas base e
visões acessíveis pelos usuários.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
5
Visões do DD
Dados dinâmicos relacionados à performanceV$
Permite visualizar todos os objetos do BDDBA
Permite visualizar objetos que são de
propriedade do usuário logado + objetos de
outros usuários que ele pode acessar
ALL
Permite visualizar objetos que são de
propriedade do usuário logado
USER
DescriçãoPrefixo
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
6
Consultando as visões do DD
• A visão DICTIONARY contém o nome e
descrição de todas as tabelas e visões do DD;
• Se você quer visualizar objetos do seu
próprio schema consulte as visões USER_ ou
consulte ALL_, filtrando dados pela coluna
OWNER;
• Somente se você tiver privilégios de DBA,
consulte as visões DBA_.
www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
7
Adicionando comentários
• Adicione comentários nas tabelas e colunas
para permitir um entendimento mais fácil do
modelo de dados.
FIM
www.fabioprado.netwww.fabioprado.net
Fábio Prado
Aprendendo SQL
Final
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
3
www.fabioprado.net
Dicas finais
• Aqueles que desejam aprender mais sobre SQL,
recomendo a leitura dos seguintes itens:
— Guia Oracle ® Database SQL Language Reference 11g Release 2 (11.2) E41084-02
— Guia Oracle® Database SQL Language Reference 12c Release 1 (12.1) E17209-14
— Artigos sobre SQL e SQL/Tuning do Blog FABIOPRADO.NET
• Para obter mais informações sobre as certificações da
Oracle, acesse o site Oracle Certification;
• Recomendo fortemente estudar algum simulado ou livro
voltado para a certificação, das empresas abaixo, antes
de fazer o exame desejado:
— Kaplan SelfTest
— Sybex
— Oracle Press
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
4
www.fabioprado.net
Livros indicados
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
5
www.fabioprado.net
Dump X Simulado
• Dumps ou Braindumps são ilegais e de baixa
qualidade;
• Simulados são preparados por empresas
sérias e contém explicações precisas sobre as
questões.
• Para descobrir se um determinado material é
dump ou simulado, faça uma pesquisa no
site:
— http://www.certguard.com/Search.asp
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
6
www.fabioprado.net
Dump X Simulado
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
7
www.fabioprado.net
Dump X Simulado
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
8
www.fabioprado.net
Blog
Acompanhe os artigos do blog FABIOPRADO.NET em:
—http://www.fabioprado.net
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
9
www.fabioprado.net
Facebook
CURTA a página do blog FABIOPRADO.NET no
FACEBOOK para acompanhar:
— Novidades e notícias do blog;
— Novidades e notícias sobre Bancos de Dados e TI, em geral;
— Oportunidades de emprego.
http://www.facebook.com/DBAFabioPrado
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
10
www.fabioprado.net
Twitter
SIGA o twitter do DBA FABIOPRADO.NET para
acompanhar:
— Novidades e notícias do blog;
— Novidades e notícias sobre Bancos de Dados e TI, em geral;
— Oportunidades de emprego.
http://twitter.com/dbafabioprado
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
11
www.fabioprado.net
YouTube
Inscreva-se no canal do YouTube do DBA/Instrutor Fábio
Prado para ver dicas gerais sobre Bancos de Dados, TI e
carreira:
https://www.youtube.com/user/fbifabio10
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
12
www.fabioprado.net
LinkedIn
Se você gostou do treinamento, POR FAVOR, deixe uma recomendação no perfil
do Fábio Prado no LinkedIn:
janeiro de 15 Aprendendo SQL
© 2014 Fábio Prado. Todos os direitos reservados.
13
www.fabioprado.net
Referências
- Blog do DBA/Instrutor FABIOPRADO
- Oracle Certification
- Oracle ® Database SQL Language Reference 11g Release 2 (11.2)
E41084-02
- Oracle® Database SQL Language Reference 12c Release 1 (12.1)
E17209-14