Apostila banco sql

Prévia do material em texto

3 
 
ÍNDICE 
 
 
INTRODUÇÃO AO SQL, SQL*PLUS E PL/SQL (ORACLE) ..............................................................................4 
COMANDOS SQL ........................................................................................................................................................4 
SQL*PLUS................................................................................................................................................................6 
EXEMPLO DE UM BLOCO PL/SQL ...............................................................................................................................7 
COMANDO SELECT.................................................................................................................................................8 
COMO SELECIONAR LINHAS DE UMA ÚNICA TABELA..................................................................................................8 
COMO ORDENAR E LIMITAR AS LINHAS SELECIONADAS...........................................................................................10 
FUNÇÕES APLICADAS A LINHAS ...............................................................................................................................14 
COMO SELECIONAR DADOS DE MAIS DE UMA TABELA...............................................................................................18 
FUNÇÕES DE GRUPO .................................................................................................................................................21 
A CLÁUSULA GROUP BY...........................................................................................................................................23 
A CLÁUSULA HAVING ...............................................................................................................................................24 
A RECUPERAÇÃO DE DADOS COM SUBCONSULTAS (SUBQUERIES) ............................................................................26 
A RECUPERAÇÃO DE DADOS COM SUBCONSULTAS CORRELACIONADAS ....................................................................28 
SCRIPTS REUTILIZÁVEIS ...................................................................................................................................33 
CRIAÇÃO DE TABELAS ........................................................................................................................................35 
DICIONÁRIO DE DADOS.......................................................................................................................................40 
COMANDOS DE MANIPULAÇÃO DE DADOS..............................................................................................43 
O COMANDO INSERT..............................................................................................................................................43 
O COMANDO UPDATE ...........................................................................................................................................45 
O COMANDO DELETE............................................................................................................................................46 
CONTROLE DE TRANSAÇÕES..........................................................................................................................48 
ALTERANDO TABELAS E CONSTRAINTS ....................................................................................................51 
SEQÜÊNCIAS ...........................................................................................................................................................56 
VISÕES ......................................................................................................................................................................59 
ÍNDICES ....................................................................................................................................................................64 
CONTROLANDO O ACESSO DOS USUÁRIOS AO BANCO DE DADOS ......................................................69 
TIPOS DE PRIVILÉGIOS ..............................................................................................................................................69 
GERENCIANDO ROLES ..............................................................................................................................................73 
UTILIZAÇÃO DE ROLE COM PASSWORD ....................................................................................................................73 
ROLES QUE JÁ VEM DE FÁBRICA................................................................................................................................74 
INFORMAÇÕES SOBRE ROLES NO DICIONÁRIO DE DADOS .........................................................................................75 
ROLES ASSOCIADAS AO SISTEMA OPERACIONAL ......................................................................................................75 
LISTAS DE EXERCÍCIOS ....................................................................................................................................77 
BIBLIOGRAFIA .......................................................................................................................................................85 
[R1] Comentário: Este índice 
é do tipo Formal. Selecionar 
Inserir índice e selecionar Formal. 
 
 4 
INTRODUÇÃO AO SQL, SQL*Plus e PL/SQL (Oracle) 
 
 
Comandos SQL, SQL*Plus e PL/SQL são utilizados para acessar e manipular dados armazenados em 
um servidor de Banco de Dados Oracle. 
 
 
Linguagem ou Ferramenta Descrição 
SQL Uma linguagem não procedural para a comunicação com 
Bancos de Dados Relacionais a partir de ferramentas ou 
aplicações. 
SQL*Plus Uma ferramenta Oracle que reconhece e submete 
comandos SQL e PL/SQL para execução no servidor. 
Edita comandos SQL com um editor de linha, formata o 
resultado de queries, controla variáveis de ambiente, etc. 
Para executar estas tarefas o SQL*Plus possui seus 
próprios comandos. 
PL/SQL Uma linguagem procedural da Oracle para controlar a 
lógica de aplicações. 
 
 
Comandos SQL 
 
Comando Descrição 
SELECT Recupera dados do Banco de Dados. 
INSERT 
UPDATE 
DELETE 
Insere novas linhas, altera linhas existentes e remove linhas de 
tabelas do banco de dados, respectivamente. Estes comandos são 
conhecidos como comandos DML (Data Manipulation Language). 
CREATE 
ALTER 
DROP 
RENAME 
TRUNCATE 
Cria, altera e remove objetos do banco de dados. São conhecidos 
como comandos DDL (Data Definition Language). 
COMMIT 
ROLLBACK 
SAVEPOINT 
Gerenciam as modificações realizadas pelos comandos DML. As 
modificações efetuadas pelos comandos DML podem ser 
agrupadas em transações lógicas. 
GRANT 
REVOKE 
Atribuem e removem direitos de acesso ao banco de dados e aos 
objetos a ele pertencentes. São conhecidos como comandos DCL 
(Data Control Language). 
 
 
 
 5 
Tipos de Dados Oracle 
 
NUMBER Número ponto flutuante com precisão de 38 dígitos significativos 
NUMBER(p,s) Valor numérico com um número máximo de dígitos igual a p, e com s 
posições decimais. 
DATE Data e Hora 
CHAR(s) String de caracteres de tamanho fixo igual a s. O valor de s pode variar de 
1 a 255. 
VARCHAR2(s) String de caracteres de tamanho variável. Tamanho máximo igual a s. O 
valor de s pode variar de 1 a 2000. 
LONG String de tamanho variável até 2 gigabytes. Somente uma coluna deste 
tipo é permitida por tabela. 
RAW Utilizado para armazenar dados binários – limite: 2MB 
LONG RAW Utilizado para armazenar dados binários – limite: 2GB 
 
 
 
 6 
SQL*PLUS 
 
Connect User/Password @String de Conexão 
 
 
Comandos SQL*PLUS para a Edição deLinhas no Buffer 
 
L - Lista todas as linhas no buffer 
n - Torna corrente a linha especificada 
C/valor anterior/novo valor - Modifica um string no buffer 
R ou / - Executa o comando SQL que se encontra no buffer 
Del n - Apaga a n-ésima linha no buffer 
 
 
Comandos SQL*PLUS para a Manipulação de Arquivos 
 
SAVE Nome_do_Arquivo 
GET Nome_do_Arquivo 
START Nome_do_Arquivo 
@ Nome_do_Arquivo 
EDIT Nome_do_Arquivo 
EXIT 
 
Obs.: Só comandos SQL vão para o buffer do SQL*PLUS, isto é, comandos SQL*PLUS não vão para 
o buffer. 
 
 
Consultando a Descrição de uma Tabela 
 
Desc Nome_da_Tabela 
 
O comando (SQL*Plus) Describe exibe a estrutura de uma tabela. São exibidos os nomes de colunas, 
as colunas NOT NULL e o tipo de cada coluna. 
 
 
Criando as Tabelas a serem Utilizadas ao Longo do Curso 
 
Utilizando o SQL*PLUS conecte-se ao Banco de Dados. 
 
Na janela de logon no servidor de banco de dados digite na caixa de textos “Conta” o string 
“AlunoX”, na caixa de textos “password” digite “AlunoX” e no string de conexão digite “ORACLE”. 
 
Digite no SQL*Plus: @A:\TABELAS.SQL 
 
 
 
 7 
Exemplo de um bloco PL/SQL 
 
 
CREATE OR REPLACE REMOVE_PROFESSORES (V_Nome_Depart IN VARCHAR2) 
IS 
 V_Numero_Depart NUMBER 
 
BEGIN 
 
-- Este procedimento tem como objetivo remover, da tabela de professores, todos os 
-- professores lotados em um determinado departamento. O procedimento recebe o 
-- nome do departamento e, após descobrir o número associado a este departamento, 
-- remove todos os empregados que possuem este numero na coluna relativa ao número 
-- do departamento, na tabela de empregados. 
 
 
SELECT Numero_Depart INTO V_Numero_Depart 
 FROM Tab_Departamentos 
 WHERE Nome_Depart = V_Nome_Depart; 
 
 DELETE FROM Tab_Professores 
 WHERE Tab_Professores.Numero_Depart = V_Numero_Depart; 
 
 COMMIT; 
 
EXCEPTION 
 
 WHEN OTHERS THEN 
 ROLLBACK; 
 
END; 
 
 
 
 
 
 
 
 
 8 
COMANDO SELECT 
 
Como Selecionar Linhas de uma única Tabela 
 
 
Exemplo 1: É preciso informar as colunas desejadas da tabela. 
 
 SELECT TABLE_NAME 
 FROM USER_TABLES; 
 
 DESC EMPREGADOS 
 
 SELECT NUMERO, NOME 
 FROM EMPREGADOS; 
 
 
Exemplo 2: Utilizando a cláusula DISTINCT para suprimir linhas duplicatas. 
 
 SELECT CARGO 
 FROM EMPREGADOS; 
 
 SELECT DISTINCT CARGO 
 FROM EMPREGADOS; 
 
 
Exemplo 3: Utilizando a cláusula DISTINCT com várias colunas. 
 
 SELECT DISTINCT CARGO, NUMERO_DEPT 
 FROM EMPREGADOS; 
 
 
Exemplo 4: Utilizando a cláusula * para listar todas as colunas da tabela. 
 
 SELECT * 
 FROM EMPREGADOS; 
 
 
Exemplo 5: Utilizando operadores aritméticos ( +, -, *, / ) na cláusula Select. 
 
 SELECT NOME, SALARIO * 12, DT_ADMISSAO 
 FROM EMPREGADOS; 
 
 
Exemplo 6: Utilizando Alias. 
 
 SELECT NOME, SALARIO * 12 AS SAL_ANUAL, DT_ADMISSAO 
 FROM EMPREGADOS; 
 9 
 
 OU 
 
 SELECT NOME, SALARIO * 12 “SAL ANUAL”, DT_ADMISSAO 
 FROM EMPREGADOS; 
 
 
Exemplo 7: Utilizando operador de concatenação. 
 
 SELECT NOME||’ ‘||SOBRENOME 
 FROM EMPREGADOS; 
 
 
Exemplo 8: Como formatar relatórios simples. 
 
 COL[UMN] Nome HEADING “Nome do | Empregado” FORMAT A9 
 COL[UMN] Salario HEADING “Salário” FORMAT $99,990.00 
 
 SELECT NOME, SALARIO 
 FROM EMPREGADOS; 
 
 COL Nome CLEAR 
 COL Salario CLEAR 
 
 OU 
 
 CLEAR COL 
 
 
 10 
Como Ordenar e Limitar as Linhas Selecionadas 
 
Exemplo 1: Ordenando as linhas selecionadas com a cláusula ORDER BY 
 
 SELECT NOME, SALARIO 
 FROM EMPREGADOS 
 ORDER BY SALARIO; 
 
 OU 
 
 SELECT NOME, SALARIO 
 FROM EMPREGADOS 
 ORDER BY SALARIO DESC; 
 
Obs: Na ordenação ascendente, valores NULOS aparecem no final. 
 
 
Exemplo 2: Ordenando pela posição da coluna selecionada 
 
 SELECT NOME, SALARIO * 12 
 FROM EMPREGADOS 
 ORDER BY SALARIO * 12; 
 
 OU 
 
 SELECT NOME, SALARIO * 12 
 FROM EMPREGADOS 
 ORDER BY 2; 
 
 
Exemplo 3: Selecionando apenas os empregados lotados no departamento 20. 
 
 SELECT NOME, NUMERO_DEPT, SALARIO 
 FROM EMPREGADOS 
 WHERE NUMERO_DEPT = 20 
 ORDER BY SALARIO; 
 
 
 11 
Exemplo 4: Selecionando apenas o empregado denominado SMITH. 
 
 SELECT NOME, NUMERO_DEPT, SALARIO 
 FROM EMPREGADOS 
 WHERE NOME = ‘CELIO’; 
 
 
Observações: 
 
1. Usar aspas simples quando a comparação for com um literal. 
2. literal é “Case Sensitive”. 
3. default para datas é DD-MON-YY 
 
 
Exemplo 5: Selecionando com operadores lógicos. 
 
 SELECT NOME, NUMERO_DEPT, SALARIO 
 FROM EMPREGADOS 
 WHERE SALARIO > 1000; 
 
Observações: 
 
1. Operadores para comparações lógicas: = > >= <= <> 
2. Outros comparadores: 
- BETWEEN ... AND ... ou NOT BETWEEN 
- IN (Lista) ou NOT IN 
- LIKE ou NOT LIKE 
- IS NULL ou IS NOT NULL 
 
3. Operadores lógicos: 
- AND 
- OR 
- NOT 
 
 
Exemplo 6: Selecionando linhas com BETWEEN ... AND ... 
 
 SELECT NOME, DT_ADMISSAO 
 FROM EMPREGADOS 
 WHERE DT_ADMISSAO BETWEEN ‘28-SEP-90’ AND ‘30-JAN-91’; 
 
 
 12 
Exemplo 7: Selecionando linhas com a cláusula IN. 
 
 SELECT NOME, NUMERO_DEPT, SALARIO 
 FROM EMPREGADOS 
 WHERE NUMERO_DEPT IN (10, 20) 
 ORDER BY NUMERO_DEPT, SALARIO; 
 
 
Exemplo 8: Selecionando linhas com a cláusula LIKE. 
 
 SELECT NOME, SALARIO 
 FROM EMPREGADOS 
 WHERE NOME LIKE ‘S%’ 
 ORDER BY NOME; 
 
 OU 
 
 SELECT NOME, SALARIO 
 FROM EMPREGADOS 
 WHERE NOME NOT LIKE ‘%I%’ 
 ORDER BY NOME; 
 
 OU 
 
 SELECT NOME, SALARIO 
 FROM EMPREGADOS 
 WHERE NOME LIKE ‘_A%’; 
 
Observações: 
 
- “%” representa nenhum ou muitos caracteres. 
- “_” representa um único caracter. 
 
 
 13 
Exemplo 9: Selecionando linhas com a cláusula IS NULL. 
 
1. A coluna Num_supervisor contém o número do empregado que supervisiona o empregado 
corrente. 
 
SELECT NUMERO, NOME, CARGO, NUM_SUPERVISOR 
FROM EMPREGADOS; 
 
2. Com esta Query recuperamos o único empregado da empresa que não é gerenciado por ninguém, 
isto é, o Presidente da empresa. 
 
SELECT NOME, CARGO, NUM_SUPERVISOR 
FROM EMPREGADOS 
WHERE NUM_SUPERVISOR IS NULL; 
 
Observação: O resultado da cláusula Where abaixo é sempre falso pois um valor nulo não pode ser 
igual ou diferente de outro valor, mesmo que este outro valor seja nulo. Se valores nulos forem 
comparados por operadores que não o “IS NULL” o resultado será sempre falso. 
 
SELECT NOME, CARGO, NUM_SUPERVISOR 
FROM EMPREGADOS 
WHERE NUM_SUPERVISOR = NULL; 
 
 
Exemplo 10: Selecionando linhas com operadores lógicos. 
 
SELECT NOME, SALARIO, NUMERO_DEPT 
FROM EMPREGADOS 
WHERE SALARIO >= 3000 
AND (NUMERO_DEPT = 10 
OR NUMERO_DEPT = 30); 
 
 
 
[R2] Comentário: 
 
AQUI ENTRA A LISTA DE 
EXERCÍCIOS NÚMERO 1. 
 
 
 14 
Funções Aplicadas a Linhas 
 
 
Há dois tipos de funções: 
 
- Funções de linhas 
- Funções de grupos 
 
 
Um função de linha retorna um resultado por linha da tabela acessada. Já uma função de grupo 
retorna um resultado por grupo de registros.Exemplos de Funções de Linha: 
 
- LOWER (‘UFF’) � uff 
- UPPER (‘uff’) � UFF 
- INITCAP (‘UNIVERSIDADE FEDERAL’) � Universidade Federal 
- CONCAT (‘String1’, ‘String2’) � String1String2 
- SUBSTR (‘String’, 1, 3) � Str 
- LENGTH (‘UFF’) � 3 
- NVL (SAL, 0) � Se SAL for NULO seu valor será convertido para zero. 
- ROUND (78.731, 2) � 78.73 (Até 4 p/ baixo, Acima de 4 p/ cima) 
- ROUND (78.731, 0) � 79 
- TRUNC (78.731, 2) � 78.73 
- TRUNC (78.731) � 78 
- MOD (100, 30) � 10 
 
 
Exemplo 1: Utilização da função UPPER em uma sentença SQL. 
 
SELECT NOME, SALARIO, NUMERO_DEPT 
FROM EMPREGADOS 
WHERE UPPER (NOME) = ‘CELIO’; 
 
 
Exemplo 2: Utilização da função CONCAT em uma sentença SQL. 
 
SELECT CONCAT (CONCAT(NOME, ‘ ‘), SOBRENOME) NOME 
FROM EMPREGADOS; 
 
 
[R3] Comentário: Por 
enquanto vamos ver apenas as 
funções de linhas. Funções de 
grupo serão vistas mais adiante 
quando estivermos estudando 
comandos SQL que lidam com 
grupos. 
 
[R4] Comentário: Com 
relação à função NVL, se o 
primeiro argumento é numérico o 
segundo também deverá ser 
numérico. Isto é, não posso 
escrever: NVL(SAL, “NADA”). 
Por outro lado se o primeiro 
argumento é um string então devo 
colocar como segundo argumento 
outro string. 
 
 15 
Exemplo 3: Utilização das funções SUBSTR e LENGTH em uma sentença SQL. 
 
SELECT NOME, LENGTH (NOME) 
FROM EMPREGADOS 
WHERE SUBSTR (NOME, 1, 3) = ‘CEL’; 
 
 
Exemplo 4: Utilização das funções ROUND e TRUNC em uma sentença SQL. 
 
SELECT ROUND (78.731, 2), TRUNC (78.731) 
FROM EMPREGADOS; 
 
SELECT ROUND (78.731, 2), TRUNC (78.731) 
FROM SYS.DUAL; 
 
DESC SYS.DUAL 
 
SELECT DUMMY 
FROM SYS.DUAL; 
 
Observação: Dual é uma tabela (dummy) do usuário SYS que pode ser utilizada quando se deseja, por 
exemplo, buscar a data do sistema. 
 
 
Formato de Data 
 
� As datas no Oracle são armazenadas em um formato numérico interno que representa o século, o 
ano, o mês, o dia, a hora, o minuto e os segundos. 
� O formato de exibição default é DD-MON-YY 
 
 
Exemplos de Funções de Linha que manipulam Datas: 
 
- SYSDATE � Retorna a data do sistema no formato DD-MMM-YY 
- MONTHS_BETWEEN (‘10-JAN-97’, ‘10-JAN-98’) � 12 
- ADD_MONTHS (‘03-MAR-98’, -3) � 03-DEC-97 
- NEXT_DAY (‘03-MAR-98’, ‘SATURDAY’) � 07-MAR-98 
 
 
Exemplo 1: Utilização da função SYSDATE em uma sentença SQL. 
 
SELECT SYSDATE 
FROM SYS.DUAL; 
 
 
 16 
Para mudar o formato de uma data na sessão corrente 
 
 ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YY'; 
 
 
Exemplos de Funções de Linha que Realizam Conversões de Dados: 
 
- TO_CHAR � Converte um número ou uma data para VARCHAR2. Um formato pode ser 
especificado. 
- TO_NUMBER � Converte um string contendo dígitos para NUMBER 
- TO_DATE � Converte um string representando uma data para DATE de acordo com o 
formato especificado. O formato default é DD-MON-YY. 
 
 
Exemplo 1: Utilização da função TO_CHAR. Como exibir uma data no formato DD/MM/AA. 
 
SELECT NOME, TO_CHAR (DT_ADMISSAO, ‘DD/MM/YY’) ADMISSÃO 
FROM EMPREGADOS; 
 
Observações: 
 
- Por default, a largura de uma coluna que resulta de uma expressão é de 80 posições. 
- YYYY ou YYY ou YY ou Y � Os últimos 4, 3, 2 ou 1 dígitos do ano. 
- MM � O mês representado em 2 dígitos. 
- MON � O nome do mês abreviado em 3 letras. 
- DDD ou DD ou D � Dia do ano, mês ou semana. 
- DAY � O nome do dia por extenso, completado com brancos até 9 caracteres. 
- MM/AA � Apenas o mês e o ano são exibidos. 
 
 
Exemplo 2: Utilização da função TO_CHAR para exibir o dia da semana. 
 
SELECT NOME, TO_CHAR (DT_ADMISSAO, ‘DAY’) ADMISSÃO 
FROM EMPREGADOS; 
 
 
Exemplo 3: Utilização da função TO_CHAR para exibir a data no formato completo. 
 
SELECT NOME, TO_CHAR (DT_ADMISSAO, ‘DD-MM-YY HH:MI:SS’) ADMISSÃO 
FROM EMPREGADOS; 
 
 
[R5] Comentário: Para se 
mudar permanentemente a data na 
máquina do usuário é preciso 
acessar o registry do Windows e 
alterar ou incluir em 
HKEY_LOCAL_MACHINE\SOF
TWARE\ORACLE a entrada 
NLS_DATE_FORMAT (acho) 
com o valor DD/MM/YY. 
 
 17 
Exemplo 4: Utilização da função TO_CHAR para formatar um valor numérico. 
 
SELECT NOME, TO_CHAR (SALARIO, ‘$99,990.00’) SALÁRIO 
FROM EMPREGADOS; 
 
 Obs: Através do formato na função TO_CHAR não há como trocar o separador decimal para a 
vírgula. Geralmente este tipo de coisa é configurada no próprio software que será utilizado para exibir 
os dados. Ex. Delphi. 
 
 
Exemplo 5: Utilização da função TO_CHAR 
 
SELECT ‘O funcionário ‘||NOME||’ recebeu ‘||TO_CHAR(SALARIO*0.2, ‘fm$999,990.00’) As Gratificação 
FROM EMPREGADOS; 
 
Obs: O fm no formato da coluna SALARIO é utilizado para retirar espaços em branco. 
 
 
Exemplo 6: Utilização da função TO_CHAR 
 
SELECT NOME, TO_CHAR(DT_ADMISSAO, ‘fmDD “de” Month “de” YYYY”.”’) Admissão 
FROM EMPREGADOS; 
 
Observações: 
 
- fm (full mode) no formato utilizado para retirar espaços em branco. 
- Aspas ponto e aspas seguido de um plic fecham o formato da data. 
 
 
Exemplo 6: Utilização da função TO_NUMBER. Somando um a DT_ADMISSAO. 
 
SELECT NOME, TO_NUMBER(TO_CHAR(DT_ADMISSAO, ‘YYYY’)) + 1 “Admissão + 1” 
FROM EMPREGADOS; 
 
 
Exemplo 7: Outra maneira de somar um a DT_ADMISSAO. 
 
SELECT NOME, TO_CHAR(ADD_MONTHS(DT_ADMISSAO, 12), ‘YYYY’) “Admissão + 1” 
FROM EMPREGADOS; 
 
 
Exemplo 8: Utilizando a função TO_DATE. 
 
SELECT NOME, DT_ADMISSAO 
FROM EMPREGADOS 
WHERE DT_ADMISSAO = TO_DATE(‘February 20, 1991’, ‘Month dd, YYYY’); 
[R6] Comentário: 
 
AQUI ENTRA A LISTA DE 
EXERCÍCIOS NÚMERO 2. 
 
 
 
 18 
Como selecionar dados de mais de uma tabela 
 
� Para se exibir dados de mais de uma tabela, através de um comando SQL, é preciso definir 
condições de junção. (Joins) 
� Os joins geralmente ocorrem entre valores de chave primária e de chave estrangeira. 
� Tipos de Joins: 
� Equijoin 
� Non-equijoin 
� Outer join 
� Self Join 
� Set operators 
� Um produto cartesiano geralmente ocorre quando a condição de junção é omitida ou inválida. 
� Para evitar produtos cartesianos é preciso incluir, na cláusula Where, condições de junção válidas. 
 
 
Exemplo 1: Uma junção simples entre a Tabela de Empregados (Emp) e a tabela de Departamentos 
(Dept). 
 
 SELECT EMPREGADOS.NOME, NUMERO_DEPT, DEPARTAMENTOS.NOME 
 FROM EMPREGADOS, DEPARTAMENTOS 
 WHERE EMPREGADOS.NUMERO_DEPT = DEPARTAMENTOS.NUMERO; 
 
Obs: Haverá um ganho de desempenho e de clareza se você sempre qualificar as colunas com o nome 
das tabelas às quais elas pertencem. 
 
 
Exemplo 2: Uma junção simples entre a Tabela de Empregados e a tabela de Departamentos 
considerando apenas aqueles empregados que ganham mais de 2500,00. 
 
SELECT EMPREGADOS.NOME, EMPREGADOS.NUMERO_DEPT, 
 DEPARTAMENTOS.NOME 
FROM EMPREGADOS, DEPARTAMENTOS 
WHERE EMPREGADOS.NUMERO_DEPT = DEPARTAMENTOS.NUMERO 
AND EMPREGADOS.SALARIO > 2500; 
 
OU 
 
SELECT E.NOME, E.NUMERO_DEPT, D.NOME 
FROM EMPREGADOS E, DEPARTAMENTOS D 
WHERE E.NUMERO_DEPT = D.NUMERO 
AND E.SALARIO > 2500; 
[R7] Comentário: Ocorrerá tb 
um produto cartesiano quando 
todas as linhas da primeira tabela 
se relacionarem com todas as 
linhas da Segunda tabela. 
 
[R8] Comentário: É preciso 
informar de que tabela vem 
DEPTNO uma vez que esta coluna 
existe nas duas tabelas com o 
mesmo nome. Caso contrário 
ocorrerá um erro. 
 
[R9] Comentário: Aliases. 
 
 19 
Exemplo 3: Uma junção entre a tabela de Empregados, a tabela de Departamentos e a tabela de 
Dependentes. 
 
SELECT E.NOME, E.NUMERO_DEPT,DPT.NOME, DEP.NOME 
FROM EMPREGADOS E, DEPARTAMENTOS DPT, DEPENDENTES DEP 
WHERE E.NUMERO_DEPT = DPT.NUMERO 
AND E.NUMERO = DEP.NUMERO_EMP; 
 
 
Exemplo 4: Uma junção com maior que e menor que (Between). 
 
SELECT E.NOME, E.CARGO, E.SALARIO, F.NIVEL 
FROM EMPREGADOS E, FAIXA_SALARIAL F 
WHERE E.SALARIO BETWEEN F.MENOR_SAL AND F.MAIOR_SAL; 
 
 
Exemplo 5: Um exemplo de junção do tipo Outer Join. 
 
Recuperar o Nome de todos os empregados seguido do nome do departamento onde o empregado 
se encontra lotado. Caso o empregado não esteja lotado em nenhum departamento o nome do 
empregado deve aparecer seguido de um espaço em branco. 
 
SELECT E.NOME “Nome”, D.NOME “Nome do Depto” 
FROM EMPREGADOS E, DEPARTAMENTOS D 
WHERE E.NUMERO_DEPT = D.NUMERO(+); 
 
 
Nome Nome do Depto 
---------- -------------- 
INGO COMPRAS 
TERESA COMPRAS 
CHICO COMPRAS 
CELIO VENDAS 
JOSE VENDAS 
LUIZA MARKETING 
ARMANDO MARKETING 
WALTER VENDAS 
MARTA COMPRAS 
TUTTI 
JAMES PESQUISA 
JOHN 
SEBASTIAN COMPRAS 
ABELARDO VENDAS 
SILVIO VENDAS 
 
15 rows selected. 
 20 
Observações: 
- Observe que o empregado Tutti não está lotado em nenhum departamento. 
- O sinal de + deve ficar do lado do valor nulo. 
- O sinal de + não pode ser colocado em ambos os lados da cláusula Where. 
 
O que aconteceria se o comando fosse escrito conforme vem abaixo? 
 
SELECT E.NOME “Nome”, D.NOME “Nome do Depto” 
FROM EMPREGADOS E, DEPARTAMENTOS D 
WHERE E.NUMERO_DEPT(+) = D.NUMERO; 
 
 
Exemplo 6: Junção de uma tabela com ela própria. 
 
Recuperar o Nome de todos os empregados seguido do nome de seu respectivo supervisor. 
 
SELECT E1.NOME “Supervisor”, E2.NOME “Empregado” 
FROM EMPREGADOS E1, EMPREGADOS E2 
WHERE E1.NUMERO = E2.NUM_SUPERVISOR; 
 
Supervisor Empregado 
---------- ---------- 
INGO TERESA 
INGO CHICO 
INGO CELIO 
TERESA JOSE 
TERESA LUIZA 
CHICO ARMANDO 
CHICO WALTER 
CHICO MARTA 
CHICO TUTTI 
CHICO JAMES 
CELIO JOHN 
JOSE SEBASTIAN 
LUIZA ABELARDO 
LUIZA SILVIO 
 
14 rows selected. 
 
O que precisaria ser feito para que o empregado Ingo (que é o presidente) apareça na coluna de 
empregados, porem sem Supervisor? 
 
[R10] Comentário: Na 
verdade o valor nulo está na 
coluna Numero_dept na tabela de 
empregados. 
 
[R11] Comentário: Caso haja 
um departamento sem empregado, 
ficará em branco o nome do 
empregado e ao lado o nome do 
departamento. 
 
[R12] Comentário: 
 
SELECT E1.NOME 
“Supervisor”, E2.NOME 
“Empregado” 
FROM EMPREGADOS E1, 
EMPREGADOS E2 
WHERE E1.NUMERO(+) = 
E2.NUM_SUPERVISOR; 
 
 21 
Funções de Grupo 
 
� Funções de grupo operam com um conjunto de linhas para dar um resultado por grupo de linhas. 
Um conjunto de linhas pode ser uma tabela inteira ou linhas desta tabela divididas em grupos. 
� Funções de grupo podem aparecer tanto na cláusula Select quanto na cláusula Having. 
� A cláusula Group By divide as linhas de uma ou mais tabelas em grupos de linhas. 
� A cláusula Having seleciona os grupos que serão aceitos. 
 
 
Funções de Grupo Existentes: 
 
� AVG 
� COUNT 
� MAX 
� MIN 
� STDDEV 
� SUM 
� VARIANCE 
 
Observações: 
 
� A cláusula Distinct pode ser utilizada para que sejam considerados apenas valores não duplicatas. 
� Todas as funções de grupo ignoram valores nulos. Para substituir um valor nulo por outro valor 
utilize a função NVL. 
 
 
Exemplo 1: Utilização de funções de grupo, considerando todas as linhas de uma tabela um único 
grupo. 
 
SELECT AVG(SALARIO), MAX(SALARIO), MIN(SALARIO), SUM(SALARIO) 
FROM EMPREGADOS; 
 
OU 
 
SELECT MIN(NOME), MAX(NOME) 
FROM EMPREGADOS; 
 
 
Exemplo 2: Um único grupo definido através da cláusula Where. 
 
SELECT AVG(SALARIO), MAX(SALARIO), MIN(SALARIO), SUM(SALARIO) 
FROM EMPREGADOS 
WHERE CARGO LIKE ‘VEND%’; 
 
Exemplo 3: Utilização da função COUNT para contar o número de empregados lotados no 
departamento número 10. 
 
 22 
SELECT COUNT(*) 
FROM EMPREGADOS 
WHERE NUMERO_DEPT = 10; 
 
 
Exemplo 4: Utilização da função COUNT para contar o número de empregados que possuem 
percentual de comissão diferente de nulo. 
 
SELECT COUNT(PERC_COMISSAO) 
FROM EMPREGADOS; 
 
 
Exemplo 5: Utilização da função COUNT para contar o número de empregados na tabela. 
 
SELECT COUNT(NVL(PERC_COMISSAO, 0)) 
FROM EMPREGADOS; 
 
 
Observações: 
 
� COUNT(*) conta o número de linhas na tabela. 
� COUNT(PERC_COMISSAO) conta o número de linhas com percentual de comissão diferente de 
nulo. 
� COUNT(NUMERO) conta o número de linhas na tabela uma vez que a coluna NUMERO é a 
chave primária da tabela e toda chave primária não pode conter valores nulos. 
 
 
 
 
 23 
 A cláusula Group By 
 
Exemplo 6: Utilização da cláusula GROUP BY e da função COUNT para se contar quantos 
empregados estão lotados em cada departamento. 
 
SELECT NUMERO_DEPT, COUNT(*) 
FROM EMPREGADOS 
GROUP BY NUMERO_DEPT; 
 
Observações: 
 
� Qualquer coluna incluída na cláusula SELECT, se não estiver em uma função de grupo, deverá 
constar da cláusula GROUP BY. 
� Com a cláusula WHERE é possível excluir determinadas linhas dos grupos. 
� Por default as linhas são ordenadas ascendentemente conforme a lista de colunas especificada na 
cláusula GROUP BY. Para modificar este comportamento é preciso utilizar a cláusula ORDER 
BY. 
 
 
Exemplo 7: Utilização da cláusula Group By, da função COUNT e de um JOIN para se contar 
quantos empregados estão lotados em cada departamento. 
 
SELECT D.NOME “DEPARTAMENTO”, COUNT(*) “QTD” 
FROM EMPREGADOS E, DEPARTAMENTOS D 
WHERE E.NUMERO_DEPT = D.NUMERO 
GROUP BY D.NOME; 
 
 
Exemplo 8: Utilização da cláusula Group By e da função COUNT para se contar quantos empregados 
estão lotados em cada departamento. 
 
SELECT D.NOME “DEPARTAMENTO”, COUNT(*) “QTD” 
FROM EMPREGADOS E, DEPARTAMENTOS D 
WHERE E.NUMERO_DEPT = D.NUMERO 
GROUP BY D.NOME 
ORDER BY 2; 
 
 
[R13] Comentário: Não é 
possível fazer o Group By por 
Numero_Dept e na cláusula Select 
colocar o Nome do Departamento. 
 
[R14] Comentário: Não é 
possível fazer o Group By por 
Numero_Dept e na cláusula Select 
colocar o Nome do Departamento. 
 
[R15] Comentário: Normalme
nte esta query estaria ordenada 
ascendentemente por D.Nome. 
 
 24 
Exemplo 9: A query abaixo está correta? A intenção é listar o número dos departamentos seguido da 
média salarial. No entanto, deseja-se listar apenas aqueles departamentos cuja média salarial é superior 
a 2000. 
 
SELECT NUMERO_DEPT, AVG(SALARIO) 
FROM EMPREGADOS 
WHERE AVG(SALARIO) > 2000 
GROUP BY NUMERO_DEPT; 
 
 
A cláusula Having 
 
� Para se restringir a inclusão de grupos não se pode utilizar a cláusula WHERE. 
� A cláusula WHERE deve ser utilizada para se restringir a inclusão de linhas em um grupo. 
� Para se omitir a inclusão de grupos inteiros do resultado de uma query deve-se utilizar a cláusula 
HAVING. 
 
 
Exemplo 10: A utilização da cláusula HAVING para listar o número dos departamentos seguido da 
média salarial de seus empregados. No entanto, deseja-se listar apenas aqueles departamentos cuja 
média salarial é superior a 2000. 
 
SELECT NUMERO_DEPT, AVG(SALARIO) 
FROM EMPREGADOS 
GROUP BY NUMERO_DEPT 
HAVING AVG(SALARIO) > 2000; 
 
 
Exemplo 11: A utilização da cláusula GROUP BY para listar a quantidade de empregados por 
departamento/cargo. Isto é, grupos dentro de grupos. Não deve ser exibidoNúmero de Departamento 
NULO. 
 
SELECT NUMERO_DEPT, CARGO, COUNT(*) 
FROM EMPREGADOS 
GROUP BY NUMERO_DEPT, CARGO 
HAVING NUMERO_DEPT IS NOT NULL; 
 
 
 25 
Exemplo 12: A utilização da cláusula GROUP BY para listar a quantidade de empregados por 
departamento/cargo. Só devem ser exibidos os grupos de departamentos/cargo com mais de 1 
empregado. 
 
SELECT NUMERO_DEPT, CARGO, COUNT(*) 
FROM EMPREGADOS 
GROUP BY NUMERO_DEPT, CARGO 
HAVING COUNT(*) > 1; 
 
 
As cláusulas do comando Select são avaliadas na seguinte ordem: 
 
1. Se o comando SQL contem a cláusula WHERE, o SGBD seleciona as linhas candidatas. 
2. O SGBD identifica os grupos especificados pela cláusula GROUP BY. 
3. A cláusula HAVING restringe os grupos resultantes que não estão de acordo com os critérios 
especificados nesta cláusula. 
 
 26 
A recuperação de dados com subconsultas (Subqueries) 
 
 
Uma subconsulta é um comando SELECT embutido em uma cláusula de outro comando SQL. 
 
Quando e como utilizar: 
 
� Escreva subconsultas para recuperar dados baseados em critérios desconhecidos. 
� Pode ser muito útil quando se necessita selecionar linhas de uma tabela com uma condição que 
depende dos dados que estão na própria ou em outra tabela. 
� Subconsultas não podem conter a cláusula ORDER BY. 
� Duas classes de operadores de comparações são utilizadas em subconsultas: 
� Operadores de uma única linha: >, =, >=, <, <=, <> 
� Operadores de multiplas linhas: IN e NOT IN. 
� Como uma subconsulta é processada? 
� Primeiramente é executado o comando SELECT aninhado. 
� Em seguida o resultado é utilizado em uma condição da consulta principal. 
 
 
Exemplo 1: A utilização de uma subconsulta aninhada para recuperar o nome e salário de todos os 
empregados que trabalham no mesmo departamento que o JOSE trabalha. 
 
SELECT NOME, SALARIO 
FROM EMPREGADOS 
WHERE NUMERO_DEPT = (SELECT NUMERO_DEPT 
 FROM EMPREGADOS 
 WHERE NOME = ‘JOSE’); 
 
 
Exemplo 2: A utilização de uma subconsulta aninhada para recuperar o nome e salário de todos os 
empregados que ganham mais do que a média salarial da empresa. 
 
SELECT NOME, SALARIO 
FROM EMPREGADOS 
WHERE SALARIO > (SELECT AVG(SALARIO) 
 FROM EMPREGADOS); 
 
 
[R16] Comentário: Primeiro é 
executada esta query para em 
seguida executar a query principal. 
 
 27 
Exemplo 3: A utilização de uma subconsulta aninhada para recuperar o nome, número do 
departamento e salário de todos os empregados que trabalham no departamento situado no RIO ou no 
departamento denominado VENDAS. 
 
SELECT NOME, NUMERO_DEPT, SALARIO 
FROM EMPREGADOS 
WHERE NUMERO_DEPT = (SELECT NUMERO 
 FROM DEPARTAMENTOS 
 WHERE LOCAL = ‘RIO’ OR 
 NOME = ‘VENDAS’); 
 
 
Exemplo 4: A utilização de uma subconsulta aninhada para recuperar o nome, número do 
departamento e salário de todos os empregados que trabalham no departamento situado no RIO ou no 
departamento denominado VENDAS. 
 
Correção da query anterior com a utilização da cláusula IN uma vez que esta subconsulta retorna mais 
de uma linha. 
 
SELECT NOME, NUMERO_DEPT, SALARIO 
FROM EMPREGADOS 
WHERE NUMERO_DEPT IN (SELECT NUMERO 
 FROM DEPARTAMENTOS 
 WHERE LOCAL = ‘RIO’ OR 
 NOME = ‘VENDAS’); 
 
 
Exemplo 5: A utilização de uma subconsulta aninhada para recuperar o número do departamento e 
sua média salarial. Devem ser recuperados apenas os departamentos cuja média salarial é maior do 
que a média salarial do departamento número 30. 
 
SELECT NUMERO_DEPT, AVG(SALARIO) 
FROM EMPREGADOS 
GROUP BY NUMERO_DEPT 
HAVING AVG(SALARIO) >= (SELECT AVG(SALARIO) 
 FROM EMPREGADOS 
 WHERE NUMERO_DEPT = 30); 
 
 
Exemplo 6: A utilização de uma subconsulta aninhada para recuperar, por departamento, o nome e o 
salário do empregado mais bem pago. 
 
SELECT NOME, SALARIO 
FROM EMPREGADOS 
WHERE (SALARIO, NUMERO_DEPT) IN (SELECT MAX(SALARIO), NUMERO_DEPT 
 FROM EMPREGADOS 
 GROUP BY NUMERO_DEPT); 
[R17] Comentário: Isso não 
funciona se a subquery recuperar 
mais de um número de 
departamento. 
 
 28 
A recuperação de dados com subconsultas correlacionadas 
 
O exemplo abaixo difere do anterior (Exemplo 6 da pág. anterior) uma vez que neste caso a 
subconsulta é executada uma vez para cada linha da tabela de empregados na consulta mais externa. 
 
Exemplo 1: A utilização de uma subconsulta correlacionada para recuperar, por departamento, o 
nome e o salário do empregado mais bem pago. 
 
SELECT NOME, SALARIO 
FROM EMPREGADOS E1 
WHERE SALARIO = (SELECT MAX(SALARIO) 
 FROM EMPREGADOS E2 
 WHERE E1.NUMERO_DEPT = E2.NUMERO_DEPT); 
 
 
Exemplo 2: A utilização de uma subconsulta correlacionada para recuperar o nome dos empregados 
que trabalham no projeto numero 2. 
 
SELECT NOME 
FROM EMPREGADOS E 
WHERE 2 IN ( SELECT NUMERO_PROJ 
 FROM TRABALHAM 
 WHERE NUMERO_EMP = E.NUMERO); 
 
 
Exemplo 3: A mesma query acima sem a utilização de subconsulta correlacionada. 
 
SELECT NOME 
FROM EMPREGADOS E, TRABALHAM T 
WHERE E.NUMERO = T.NUMERO_EMP 
AND T.NUMERO_PROJ = 2; 
 
 
 29 
Quantificador Existencial 
 
� Exists representa o quantificador existencial, uma noção emprestada da lógica formal. 
� Em SQL, um predicado existencialmente quantificado é representado pela expressão da forma 
EXISTS (SELECT * FROM ... ). 
� Essa expressão será verdadeira se o resultado do cálculo da subconsulta representado por 
“SELECT * FROM ...” não estiver vazio, isto é, se existir pelo menos um registro na tabela 
FROM da subconsulta que satisfaz a condição WHERE dessa mesma subconsulta. 
� Quanquer consulta que utilize IN pode alternativamente ser formulada com EXISTS, porém o 
inverso não é verdadeiro. 
 
 
Exemplo 1: Obter o nome dos empregados que trabalham no projeto nº 2. 
 
SELECT NOME 
FROM EMPREGADOS E 
WHERE EXISTS (SELECT * 
 FROM TRABALHAM 
 WHERE NUMERO_EMP = E.NUMERO 
 AND NUMERO_PROJ = 2); 
 
OU 
 
SELECT NOME 
FROM EMPREGADOS E, TRABALHAM T 
WHERE E.NUMERO = T.NUMERO_EMP 
AND T.NUMERO_PROJ = 2; 
 
 
Exemplo 2: Obter o nome dos empregados que não trabalham no projeto nº 2. 
 
SELECT NOME 
FROM EMPREGADOS E 
WHERE NOT EXISTS 
(SELECT * 
FROM TRABALHAM 
WHERE NUMERO_EMP = E.NUMERO 
 AND NUMERO_PROJ = 2); 
 
OU 
 
 30 
SELECT NOME 
FROM EMPREGADOS 
MINUS 
SELECT NOME 
FROM EMPREGADOS E, TRABALHAM T 
WHERE T.NUMERO_EMP = E.NUMERO 
AND T.NUMERO_PROJ = 2; 
 
 
Exemplo 3: Obter o nome dos empregados que trabalham em todos os projetos. 
 
SELECT NOME 
FROM EMPREGADOS E 
WHERE NOT EXISTS 
(SELECT * 
FROM PROJETOS P 
WHERE NOT EXISTS 
 (SELECT * 
 FROM TRABALHAM T 
 WHERE T.NUMERO_EMP = E.NUMERO 
 AND T.NUMERO_PROJ = P.NUMERO)); 
 
 
Observações: 
 
- Deve ser selecionado um nome de empregado quando não existir um projeto no qual ele não 
trabalhe. 
 
 
Exemplo 4: Obter o nome dos empregados que trabalham em todos os projetos nos quais o 
empregado 7521 trabalha. 
 
SELECT NOME 
FROM EMPREGADOS E 
WHERE NOT EXISTS 
(SELECT * 
FROM TRABALHAM T1 
WHERE NUMERO_EMP = 7521 
AND NOT EXISTS 
 (SELECT * 
 FROM TRABALHAM T2 
 WHERE T2.NUMERO_EMP = E.NUMERO 
 AND T2.NUMERO_PROJ = T1.NUMERO_PROJ)); 
 
 31 
União (Union e Union All) 
 
� Linhas duplicatas são eliminadas do resultado de uma união a não ser que o operador UNIONinclua explicitamente o quantificador ALL. Assim, no exemplo nº 1, o projeto nº 3 é selecionado 
em ambos os SELECTS, mas só aparece uma vez no resultado final. 
� Já o exemplo nº 2 retornará os números de projeto 2, 3 e 3. 
� Qualquer número de SELECTS pode ser unido pelo UNION. 
� Quando sabemos que não haverá elementos duplicados no resultado é conveniente utilizarmos 
UNION ALL para que o sistema não seja forçado a eliminar duplicidades, desnecessariamente. 
 
 
Exemplo 1: Obter o número dos projetos que, ou se iniciaram após 31-JUL-97, ou possuem o 
empregado 7566 nele trabalhando. União sem repetição. 
 
SELECT NUMERO 
FROM PROJETOS 
WHERE DT_INICIO > ‘31-JUL-97’ 
UNION 
SELECT NUMERO_PROJ 
FROM TRABALHAM 
WHERE NUMERO_EMP = 7566; 
 
 
Exemplo 2: Obter o número dos projetos que, ou se iniciaram após 31-JUL-97, ou possuem o 
empregado 7566 nele trabalhando. União com repetição. 
 
SELECT NUMERO 
FROM PROJETOS 
WHERE DT_INICIO > ‘31-JUL-97’ 
UNION ALL 
SELECT NUMERO_PROJ 
FROM TRABALHAM 
WHERE NUMERO_EMP = 7566; 
 
 
 32 
Exemplo 3: A possibilidade de incluirmos constantes numa cláusula SELECT é frequentemente útil 
quando a cláusula UNION é utilizada. Por exemplo, para indicar qual das duas condições WHERE foi 
atendida para a inclusão do elemento no resultado final. 
 
SELECT NUMERO, ‘DT_INICIO > 07-JAN-90’ CONDIÇÃO 
FROM PROJETOS 
WHERE DT_INICIO > ‘07-JAN-90’ 
UNION ALL 
SELECT NUMERO_PROJ, ‘NUMERO_EMP = 7566’ 
FROM TRABALHAM 
WHERE NUMERO_EMP = 7566; 
 
Resultado: 
 
NUMERO CONDIÇÃO 
------------ --------------------- 
 2 DT_INICIO > 07-JAN-90 
 3 DT_INICIO > 07-JAN-90 
 4 DT_INICIO > 07-JAN-90 
 3 NUMERO_EMP = 7566 
 
 
 
 33 
SCRIPTS REUTILIZÁVEIS 
 
 
O objetivo aqui é escrever scripts que, em tempo de execução, indaguem o usuário sobre o valor de 
variáveis utilizadas na cláusula WHERE de comandos SQL embutidos no script. 
 
Exemplo 1: Usando Variáveis de Substituição em Instruções SQL. 
 
SELECT NOME, SALARIO 
FROM EMPREGADOS 
WHERE CARGO = '&CARGO'; 
 
Enter value for cargo: VENDEDOR 
 
 
Como Gerar um Script para esta Instrução 
 
Chame o EDIT e após editar o comando abaixo, salve este arquivo com o nome EXERC01.SQL no 
diretório Z: 
 
SELECT NOME, SALARIO 
FROM EMPREGADOS 
WHERE CARGO = '&CARGO'; 
 
Para executar, dê ALT+TAB para o SQL*PLUS e digite: 
 
 START Z:\EXERC01.SQL (com ou sem o .SQL) 
 ou 
 @Z:\EXERC01.SQL (com ou sem o .SQL) 
 
A Execução Aparecerá Assim: 
 
 SQL> @Z:\Exerc01 
 
 Enter value for cargo: VENDEDOR 
 
 old 3: WHERE CARGO = '&CARGO' 
 new 3: WHERE CARGO = 'VENDEDOR' 
 
 NOME SALARIO 
 --------------- ------------- 
 ARMANDO 1600.45 
 WALTER 1250 
 MARTA 1250 
 TUTTI 1100 
 34 
Definindo Variáveis do Usuário 
 
Exemplo 2: Chamar o EDIT e após editar o comando abaixo, salvar este arquivo com o nome 
EXERC02.SQL no diretório Z: 
 
 SET ECHO OFF 
 SET VERIFY OFF 
 
 ACCEPT DATA_ADM DATE FORMAT DD-MON-YY PROMPT "Entre com a data 
 da contratação: " 
 SELECT NOME, DT_ADMISSAO ADMISSAO 
 FROM EMPREGADOS 
 WHERE DT_ADMISSAO > '&DATA_ADM' 
 / 
 UNDEFINE DATA_ADM 
 SET VERIFY ON 
 SET ECHO ON 
 
Para executar, dê ALT+TAB para o SQL*PLUS e digite: 
 
 START EXERC02 
 
Observações: 
 
- SET ECHO OFF evita que os comandos ecoem na tela do SQL*PLUS. (Assim como ocorre no 
DOS). 
- SET VERIFY OFF faz desaparecer da tela os valores OLD e NEW da variável de substituição. 
- Note que &DATA_ADM na cláusula WHERE acima encontra-se entre plics. Isto evita que o 
usuário seja obrigado a digitar os PLICS. 
- Sem os plics o usuário teria de digitá-los para tudo que necessitasse ser tratado como caracter 
ou data. 
- O comando ACCEPT cria a variável (caso ela já não tenha sido criada anteriormente), lê o 
valor digitado pelo usuário e o atribui à variável. 
- Sintaxe do comando: ACCEPT variável [datatype] [FORMAT] [PROMPT texto] [HIDE] 
- O comando UNDEFINE libera a área de memória ocupada pela variável. 
- Assim como no DOS, se digito a cláusula WHERE assim: WHERE CARGO = ‘&1’ então 
será possível executar este script assim: @Z:\Exerc02.SQL VENDEDOR 
- Só comandos SQL vão para o buffer. Todos os demais comandos não vão para o buffer do 
SQL*Plus. 
 35 
CRIAÇÃO DE TABELAS 
 
 
Estruturas de dados que podem ser criadas com um SGBD Oracle 7 
 
Estruturas de Dados Descrição 
Tabela Armazena dados. 
Visão Representa logicamente subconjuntos de dados 
de uma ou mais tabelas. 
Sequência Gera valores de chaves primárias. 
Índice Melhora o desempenho de algumas consultas. 
 
 
Tabelas 
 
� Uma tabela pode ser criada a qualquer momento. 
� Não é necessário especificar seu tamanho, no momento da sua criação, embora seja possível. 
� A estrutura de uma tabela pode ser modificada a qualquer momento, sem a necessidade de se tirar 
o banco do ar. 
� Quando uma tabela é criada sua definição é armazenada no dicionário de dados. 
� Para se poder criar tabelas é preciso ter o privilégio de CREATE TABLE e o direito de utilizar 
algum espaço em disco, alocado para o banco de dados. 
� Quem concede estes direitos para os usuários do banco é o Administrador de Banco de Dados. 
(DBA) 
 
 
Comando Create Table 
 
Exemplo: 
 
CREATE TABLE FORNECEDORES 
(NUMERO NUMBER(2) PRIMARY KEY, 
NOME VARCHAR2(25) NOT NULL, 
TELEFONE CHAR(7), 
ENDERECO VARCHAR2(20), 
VALOR_FORNEC NUMBER (8,2)); 
 
 
Observações: 
 
- O nome de uma tabela deve começar por uma letra. 
- Pode ter até 30 caracteres. 
- Deve conter apenas: A-Z, a-z, 0-9, _, $ e #. 
- Não pode ter o mesmo nome de qualquer outro objeto existente no esquema do usuário. 
[R18] Comentário: Os alunos 
deverão criar esta tabela 
exatamente como está aqui. 
 
 36 
Tipos de Dados Oracle 
 
NUMBER Número ponto flutuante com precisão de 38 dígitos significativos 
NUMBER(p,s) Valor numérico com um número máximo de dígitos igual a p, e com s 
posições decimais. 
DATE Data e Hora 
CHAR(s) String de caracteres de tamanho fixo igual a s. O valor de s pode variar de 
1 a 255. 
VARCHAR2(s) String de caracteres de tamanho variável. Tamanho máximo igual a s. 
O valor de s pode variar de 1 a 2000. 
LONG String de tamanho variável até 2 gigabytes. Somente uma coluna do tipo 
Long é permitida por tabela. 
RAW e 
LONG RAW 
Equivalem a VARCHAR2 e LONG, respectivamente. São utilizados para 
armazenar dados binários, que não devem ser interpretados pelo servidor 
Oracle. 
 
 
Tipos de Constraints 
 
 PRIMARY KEY 
 FOREIGN KEY 
 NOT NULL 
 UNIQUE 
 CHECK 
 
Observações: 
 
- É possível criar uma constraint após a criação da tabela. 
- Uma constraint pode ser definida a nível de coluna ou a nível de tabela. 
- Constraints são armazenadas no Dicionário de Dados e podem ser facilmente recuperadas se 
possuírem nomes razoáveis. 
 
 
Como dar Nome às Constraints 
 
Exemplo 1: Constraints Primary Key e Not Null. 
 
CREATE TABLE FORNECEDORES 
(NUMERO NUMBER(2) 
 CONSTRAINT FORNECEDORES_NUMERO_PK PRIMARY KEY, 
NOME VARCHAR2(25) 
 CONSTRAINT FORNECEDORES_NOME_NN NOT NULL, 
TELEFONE CHAR(7) 
 CONSTRAINT FORNECEDORES_TELEFONE_NN NOT NULL, 
ENDERECO VARCHAR2(20), 
VALOR_FORNEC NUMBER (8,2)); 
 
[R19] Comentário: Automatic
amente será criado um índice 
único para a(a) coluna(s) 
especificada(s), se ele já não 
existir. 
 
[R20] Comentário: Para teste 
em cima de faixas de valores. 
 
[R21] Comentário: Os alunosdeverão Dropar a tabela de 
Fornecedores existente (foi criada 
pelos próprios alunos) e, em 
seguida, deverão criá-la 
novamente, agora com as 
constraints. 
 
 37 
 
Verificando o Comportamento da Constraint Primary Key da Tabela de Fornecedores 
 
 INSERT INTO FORNECEDORES 
 VALUES (10, 'NAYA CONSTRUCOES LTDA', '5555555', NULL, 0); 
 
 INSERT INTO FORNECEDORES 
 VALUES (10, 'NAYA CONSTRUCOES LTDA', '5555555', NULL, 0); 
 
 INSERT INTO FORNECEDORES 
 * 
 ERROR at line 1: 
 ORA-00001: unique constraint (CARLOS.SYS_C00660) violated 
 
 
Exemplo 2: Constraints Primary Key e Not Null. 
 
CREATE TABLE DEPARTAMENTOS 
(NUMERO NUMBER(2) 
 CONSTRAINT DEPARTAMENTOS_NUMBER_PK PRIMARY KEY, 
NOME VARCHAR2(14) 
 CONSTRAINT DEPARTAMENTOS_NOME_NN NOT NULL, 
LOCAL VARCHAR2(13)); 
 
 
Exemplo 3: Constraint Check e Integridade Referecial com a própria tabela de Empregados e 
 com a tabela de Departamentos. 
 
 CREATE TABLE EMPREGADOS 
 (NUMERO NUMBER(4) 
 CONSTRAINT EMPREGADOS_NUMBER_PK PRIMARY KEY, 
 NOME VARCHAR2(10), 
 SOBRENOME VARCHAR2(10), 
 CPF CHAR(11) 
 CONSTRAINT EMPREGADOS_CPF_UN UNIQUE, 
 CARGO VARCHAR2(9), 
 NUM_SUPERVISOR NUMBER(4) 
 CONSTRAINT EMP_EMP_NUM_SUPERVISOR_FK 
 REFERENCES EMPREGADOS (NUMERO), 
 DT_ADMISSAO DATE, 
 SALARIO NUMBER(7,2), 
 PERC_COMISSAO NUMBER(4,2) 
 CONSTRAINT EMPREGADOS_PERC_COMISSAO_CK 
 CHECK (PERC_COMISSAO IN (10, 12.5, 15, 17.5, 20)), 
 NUMERO_DEPT NUMBER(2) 
 CONSTRAINT EMPR_DEPARTAMENTOS_NUMERO_DEPT_FK 
 REFERENCES DEPARTAMENTOS (NUMERO) 
 ON DELETE CASCADE); 
[R22] Comentário: Ver 
observações na pág 35. Para que 
valores nulos não sejam aceitos 
seria preciso acrescentar uma 
constraint Not Null. 
 
[R23] Comentário: Quando se 
usa a Constraint Check não quer 
dizer que um valor nulo não possa 
ser informado. 
 
[R24] Comentário: Nome da 
tabela filho – Nome da tabela pai – 
Nome da coluna na tabela filho – 
Tipo da constraint. A tabela filho 
é a de Empregados. A tabela pai é 
a de Departamentos. 
 
[R25] Comentário: Permite 
deleção na tabela pai e a deleção 
das linhas dependentes na tabela 
filho. Pode não haver a cláusula 
ON DELETE CASCADE. 
 
 38 
Exemplo 4: 
 
 CREATE TABLE DEPENDENTES 
 (NUMERO_EMP NUMBER(4) 
 CONSTRAINT DEPENDENTES_EMP_NUMERO_EMP_FK 
 REFERENCES EMPREGADOS (NUMERO), 
 NUM_ORDEM NUMBER(2), 
 NOME VARCHAR2(10), 
 CONSTRAINT DEPENDENTES_NUM_EMP_NUM_ORD_PK 
PRIMARY KEY(NUMERO_EMP, NUM_ORDEM)); 
 
 
Observações sobre a Constraint Primary Key: 
 
- A constraint Primary Key é uma combinação das constraints Unique e Not Null. 
- Um índice único é automaticamente criado. 
 
 
Observações sobre a Constraint Unique: 
 
- Designa uma coluna ou uma combinação de colunas de tal forma que duas linhas não possam 
ter o mesmo valor. 
- Valores nulos são aceitos. 
- Automaticamente é criado um índice único para a(s) coluna(s) especificada(s). 
 
 
Observações sobre a Constraint Foreign Key: 
 
- Estabelece um relacionamento com a chave primária ou única da mesma ou de outra tabela. 
- Deve referenciar um valor existente na tabela pai ou ser nulo. 
- Chaves estrangeiras são baseadas em dados e são puramente lógicas, isto é, não são ponteiros 
físicos. 
- Uma chave estrangeira, parte de uma chave primária, não pode ser nula pois uma chave 
primária não pode ser nula, nem parcialmente nula. 
- Havendo a cláusula ON DELETE CASCADE, uma deleção na tabela pai causa a deleção das 
linhas relacionadas na tabela filho. 
 
 
Observações sobre a Constraint Check: 
 
- A constraint Check define uma condição que cada linha deve satisfazer. 
- Não pode fazer referência a CURRVAL, NEXTVAL, LEVEL e ROWNUM ou chamadas às 
funções SYSDATE, UID, USER. 
- Subconsultas não são permitidas na definição de uma constraint check. 
 39 
Outras Formas de se Validar uma Restrição de Integridade 
 
- Triggers 
- Procedimentos ou funções armazenados no servidor de banco de dados 
- Através do código na própria aplicação. 
 
 
Como Consultar uma Constraint no Dicionário de Dados 
 
 SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, 
 SEARCH_CONDITION 
 FROM USER_CONSTRAINTS 
 WHERE CONSTRAINT_NAME = 'EMPREGADOS_PERC_COMISSAO_CK'; 
 
 
Como Criar uma Tabela Através de uma Subconsulta 
 
 
 CREATE TABLE EMPREGADOS_VENDAS 
 AS SELECT * 
 FROM EMPREGADOS 
 WHERE CARGO = ‘VENDEDOR’; 
 
 
Observação: 
 
- A tabela Empregados_Vendas é criada contendo todos os empregados no cargo de vendedores. 
- Apenas a constraint NOT NULL é copiada. 
- Ao término da execução do comando, confirme a sua criação executando um DESCRIBE. 
 
 
 
[R26] Comentário: Na 
verdade Search_Condition é a 
única informação que não consta 
do próprio nome da constraint 
'EMPREGADOS_PERC_COMIS
SAO_CK'. 
 
[R27] Comentário: Poderia 
especificar aqui quais colunas 
desejo copiar. 
 
 40 
DICIONÁRIO DE DADOS 
 
 
 É um dos mais importantes componentes do servidor Oracle7. Os componentes do dicionário são 
criados quando um banco de dados é criado. Sempre que um banco de dados está em operação, o 
dicionário de dados é atualizado e mantido pelo servidor Oracle7. O Owner de todas as tabelas do 
dicionário de dados é o usuário SYS. As tabelas que compõem o dicionário de dados são raramente 
acessadas diretamente pois são difíceis de entender. Logo, os usuários costumam acessar visões das 
tabelas do dicionário que possuem as informações num formato mais fácil de entender. 
 
 
Exemplo de informações no Dicionário de Dados: 
 
� Informações sobre os usuários; 
� Privilégios concedidos a usuários; 
� Informações sobre os objetos existentes no banco: tabelas, visões, índices, etc; 
� Constraints associadas a tabelas do banco, etc. 
 
 
Existem 4 classes de visões no dicionário de dados: 
 
PREFIXO DESCRIÇÃO 
USER_ Contém objetos cujo owner é o próprio usuário. Ex. USER_TABLES 
ALL_ Acessa objetos aos quais o usuário recebeu algum direito em adição aos 
objetos possuidos pelo usuário. 
DBA_ Permite que um usuário com o privilégio de DBA acesse qualquer objeto no 
banco de dados. 
V$ Apresenta informações de performance do Servidor e informações de lock. 
Disponível apenas para o DBA. 
 
 
Outras Visões: Várias visões do dicionário de dados não usam os prefixos listados acima. Por 
exemplo: 
 
 
Nome da Visão Descrição 
DICTIONARY Descreve todas as tabelas, visões e sinônimos que compõem o 
dicionário de dados. 
DICT_COLUMNS Descreve as colunas das tabelas, visões e sinônimos que 
compõem o dicionário de dados. 
IND É sinônimo para USER_INDEXES 
[R28] Comentário: Muito 
Importantes. 
 
 41 
Exemplo 1: Como saber que visões existem no dicionário de dados e para que servem. 
 
 DESC DICTIONARY 
 
 SELECT TABLE_NAME, COMMENTS 
 FROM DICTIONARY; 
 
 
Exemplo 2: Para conhecer a descrição das colunas de qualquer tabela ou visão do dicionário de 
dados, veja o conteúdo de DICT_COLUMNS. Na cláusula WHERE você deverá informar o nome da 
tabela ou visão do dicionário que deseja conhecer. 
 
 SELECT COLUMN_NAME, COMMENTS 
 FROM DICT_COLUMNS 
 WHERE TABLE_NAME = ‘USER_OBJECTS’; 
 
 
Exemplo 3: Como saber que objetos (tabelas, no caso) um usuário possui. 
 
Que tipos de objetos um usuário pode possuir? Resposta: Tables, Views, Sequences, Indexes e 
Clusters. 
 
 DESC USER_OBJECTSSELECT OBJECT_NAME 
 FROM USER_OBJECTS 
 WHERE OBJECT_TYPE = ‘TABLE’; 
 
 
Exemplo 4: Como saber que tipos de objetos um usuário possui. 
 
 SELECT DISTINCT OBJECT_TYPE 
 FROM USER_OBJECTS; 
 
 
Exemplo 5: Como saber a que tabela e a que coluna uma constraint pertence? 
 
 SELECT TABLE_NAME, COLUMN_NAME 
 FROM USER_CONS_COLUMNS 
 WHERE CONSTRAINT_NAME = ‘EMPR_DEPARTAMENTOS_NUMERO_DEPT_FK’; 
 42 
Exemplo 6: Como recuperar o nome de todas as constraints da tabela de Empregados acompanhado 
do nome das colunas associadas às contraints. 
 
 SELECT CONSTRAINT_NAME, COLUMN_NAME 
 FROM USER_CONS_COLUMNS 
 WHERE TABLE_NAME = ‘EMPREGADOS’; 
 
Resultado: 
 
CONSTRAINT_NAME COLUMN_NAME 
----------------------------------------------------------- ------------------------------ 
EMPREGADOS_CPF_UK CPF 
EMPREGADOS_NUMERO_PK NUMERO 
EMPREGADOS_PERC_COMISSAO_CK PERC_COMISSAO 
EMPREG_DEPARTAMENTOS_NUMERO_FK NUMERO_DEPT 
EMP_EMP_NUM_SUPERVISOR_FK NUM_SUPERVISOR 
 
 
 
 
 43 
COMANDOS DE MANIPULAÇÃO DE DADOS 
 
 
Comandos Descrição 
INSERT Adiciona novas linhas a uma tabela. 
UPDATE Modifica linhas existentes em uma tabela. 
DELETE Remove linhas existentes de uma tabela. 
COMMIT Torna permanente todas as modificações pendentes. 
SAVEPOINT Permite um Rollback até a marca de savepoint. 
ROLLBACK Desfaz todas as manipulações efetuadas. 
 
 
O Comando INSERT 
 
Exemplo 1: Quando são informados os dados de todas as colunas e na ordem default, não é 
necessário informar o nome das colunas. 
 
 
 INSERT INTO DEPARTAMENTOS 
 VALUES (10, 'COMPRAS', 'RIO'); 
 
 
Exemplo 2: Os nomes das colunas foram informados desnecessariamente. 
 
 INSERT INTO DEPARTAMENTOS (NUMERO, NOME, LOCAL) 
 VALUES (50, 'PROJETOS', 'RIO'); 
 
 
Exemplo 3: Método implícito de inserção de nulos. 
 
 INSERT INTO DEPARTAMENTOS (NUMERO, NOME) 
 VALUES (50, 'PROJETOS'); 
 
 
Exemplo 4: Método explícito de inserção de nulos. Além da palavra-chave NULL, também pode ser 
utilizado o string vazio ‘’ 
 
 INSERT INTO DEPARTAMENTOS (NUMERO, NOME, LOCAL) 
 VALUES (50, 'PROJETOS', NULL); 
 
 
[R29] Comentário: Numero 
� Primary Key 
 Nome � Not Null 
 Local � Pode ser nulo. 
 
 44 
Exemplo 5: Os nomes das colunas precisaram ser informados uma vez que os dados não estão na 
ordem default. 
 
 INSERT INTO DEPARTAMENTOS (NOME, NUMERO, LOCAL) 
 VALUES ('PROJETOS', 50, ‘RIO’); 
 
 
Exemplo 6: A cláusula NULL foi utilizada. 
 
 INSERT INTO EMPREGADOS 
 VALUES (7788, 'JOSE', 'NONATO', '55555555555', NULL, 7566, '17-DEC-91', 
 3000, 12.5, 20); 
 
Para mudar o separador decimal e o separador de grupos 
 
ALTER SESSION SET NLS_NUMERIC_CHARACTERS=',.' 
 
Quando o caracter decimal não é o ponto (.) ou quando o separador de grupos é utilizado, os números 
que aparecem em instruções SQL devem aparecer entre plics, pois a vírgula costuma ser utilizada 
como separador de itens em instruções SQL. 
 
Exemplo: 
 
 INSERT INTO EMPREGADOS 
 VALUES (7788, 'JOSE', 'NONATO', '55555555555', 'ANALISTA', 7566, '17-DEC-91', 
 3000, ’12,5’, 20); 
 
 
Exemplo 7: Inserção de valores especiais: USER e SYSDATE. 
 
 INSERT INTO EMPREGADOS 
 VALUES (7788, USER, '55555555555', NULL, 7566, TO_CHAR (SYSDATE, 
 ‘DD/MON/YY’), 3000, 12.5, 20); 
 
 
Exemplo 8: Utilizando variáveis de substituição. 
 
 INSERT INTO DEPARTAMENTOS (NUMERO, NOME, LOCAL) 
 VALUES (&Numero_dep, ‘&Nome_dep’, ‘&Local_dep’); 
 
[R30] Comentário: SYSDAT
E � DATA e HORA. 
 
 45 
Exemplo 9: Como copiar linhas de outra tabela. 
 
 CREATE TABLE DEPT 
 (NUMERO NUMBER(2) 
 CONSTRAINT DEPT_NUMBER_PK PRIMARY KEY, 
 NOME VARCHAR2(14) 
 CONSTRAINT DEPT_NOME_NN NOT NULL, 
 LOCAL VARCHAR2(13)); 
 
 
 INSERT INTO DEPT (NUMERO, NOME, LOCAL) 
 SELECT NUMERO, NOME, LOCAL 
 FROM DEPARTAMENTOS 
 WHERE LOCAL = ‘RIO’; 
 
 
O Comando UPDATE 
 
Exemplo 1: Atualização da coluna LOCAL. 
 
 UPDATE DEPARTAMENTOS 
 SET LOCAL = ‘ORLANDO’ 
 WHERE NUMERO = 40; 
 
 
Exemplo 2: Atualização da coluna NOME e da coluna LOCAL. 
 
 UPDATE DEPARTAMENTOS 
 SET NOME = ‘MARKETING’, LOCAL = ‘ORLANDO’ 
 WHERE NUMERO = 40; 
 
 
Exemplo 3: Atualização com subconsulta. 
 
 UPDATE DEPARTAMENTOS 
 SET LOCAL = ‘FLORIANOPOLIS’ 
 WHERE NUMERO IN (SELECT NUMERO 
 FROM DEPARTAMENTOS 
 WHERE LOCAL = ‘PORTO ALEGRE’); 
 
 
Exemplo 4: Todas as linhas são atualizadas quando não há a cláusula WHERE. 
 
 UPDATE DEPARTAMENTOS 
 SET LOCAL = ‘ORLANDO’; 
[R31] Comentário: Uma outra 
alternativa seria: 
 
CREATE TABLE DEPT 
AS SELECT NUMERO, NOME, 
LOCAL 
 FROM DEPARTAMENTOS 
 WHERE LOCAL = ‘RIO’; 
 
 46 
Exemplo 5: Atualização de chave primária. 
 
 UPDATE DEPARTAMENTOS 
 SET NUMERO = 70 
 WHERE NUMERO = 10; 
 
UPDATE DEPARTAMENTOS 
 * 
ERROR at line 1: 
ORA-02292: integrity constraint (CARLOS.EMPR_DEPARTAMENTOS_NUMERO_DEPT_FK) violated - 
child record found 
 
Observação: 
 
- Não se pode atualizar a chave primária de uma tabela pai se houver filhos na tabela filho. 
 
 
O Comando DELETE 
 
Exemplo 1: Apagar o empregado 7788 da tabela de empregados. 
 
 DELETE FROM EMPREGADOS 
 WHERE NUMERO = 7788; 
 
 
Exemplo 2: Apagar todos os empregados admitido em dezembro de 1980. 
 
 DELETE FROM EMPREGADOS 
 WHERE TO_CHAR (DT_ADMISSAO, ‘MM-YY’) = ‘12-90’; 
 
 
Exemplo 3: Apagar todos as linhas da tabela TRABALHAM. 
 
 SELECT * FROM TRABALHAM; 
 
 DELETE FROM TRABALHAM; 
 
 SELECT * FROM TRABALHAM; 
 
 ROLLBACK; 
 
 SELECT * FROM TRABALHAM; 
 
 
[R32] Comentário: Como 
fazer? 
1.Recuperar os empregados 
lotados no departamento 10. 
2.Armazenar estes numeros de 
empregados em uma tabela. 
3.Alterar para nulo o número do 
departamento onde estes 
empregados estão lotados. 
4.Alterar o número do 
departamento de 10 para 70. 
5.Alterar para 70 o número do 
departamentos onde os 
empregados (slavos) estão lotados. 
 
O professor disse que havendo um 
trigger para fazer a atualização em 
cascata está atualização é possível. 
 
 47 
Exemplo 4: Apagar todos as linhas da tabela TRABALHAM. 
 
 TRUNCATE TABLE TRABALHAM; 
 
 ROLLBACK; 
 
 SELECT * FROM TRABALHAM; 
 
 
 48 
CONTROLE DE TRANSAÇÕES 
 
Uma transação contém: 
 
� um ou mais comandos DML 
� um comando DDL 
� um comando DCL 
 
Uma transação começa quando o primeiro comando SQL é executado e termina quando ocorre um dos 
seguintes eventos: 
 
� execução de um comando COMMIT ou ROLLBACK 
� execução de um comando DDL ou DCL (ocorre um COMMIT automático Antes e Depois da 
execução destes comandos. Logo, mesmo que o comando DDL ou DCL falhe, não será possível 
dar ROLLBACK nos comandos que antecederam este comando.) 
� ocorrência de certos tipos de erro como, por exemplo, um deadlock (ocorre um ROLLBACK) 
� término de uma sessão (se o usuário sai normalmente do SQL*PLUS ocorre um COMMIT) 
� falha na estação (por exemplo, quando se desliga a máquina estando logado no SQL*PLUS) ou no 
servidor 
 
Quando uma transação termina, o próximo comando SQLinicia automaticamente a próxima 
transação. 
 
 
Vantagens do COMMIT ou ROLLBACK 
 
� Garante a consistência dos dados agrupando operações logicamente relacionadas. 
� Agrupa os comandos de manipulação logicamente relacionados. 
� Permite que se veja o resultado de manipulações antes que elas sejam confirmadas. 
 
 
 49 
Controlando Transações Através de SavePoints 
 
 INSERT INTO EMPREGADOS 
 VALUES (7844, 'TUTTI', 'VASQUES', '12222222222', 'VENDEDOR', 7698, '08-SEP-93', 
 1100, NULL, NULL); 
 
 INSERT INTO EMPREGADOS 
 VALUES (7900, 'JAMES', 'BOND', '13333333333', 'PORTEIRO', 7698, '03-DEC-93', 950, 
 NULL, 40); 
 
 SAVEPOINT ABC; 
 
 UPDATE EMPREGADOS 
 SET SALARIO = SALARIO * 1.5; 
 
 IF (CONDIÇÃO) THEN 
 ROLLBACK TO ABC; 
 ELSE 
 COMMIT 
 END IF 
 
Obs: Se um único comando DML falha durante a execução de uma transação apenas este comando 
sofre ROLLBACK. Quanto às demais modificações caberá ao usuário dar COMMIT ou ROLLBACK 
explicitamente. 
 
 
O Estado dos Dados antes do Commit ou Rollback 
 
� Antes do commit os dados são vistos por outros usuários como se não tivessem sofrido nenhuma 
manipulação. 
� As linhas afetadas são bloqueadas (locked). Outros usuários não podem modificar os dados 
manipulados uma vez que estes dados encontram-se bloqueados. 
 
 
O que Acontece após o Commit 
 
� As manipulações efetuadas nos dados são escritas no banco. 
� Os dados anteriores são permanentemente perdidos. 
� Todos os usuários autorizados podem ver os resultados. 
� Todas as linhas bloqueadas são liberadas. 
� Todos os Savepoints são apagados. 
 
 
O que Acontece após o Rollback 
 
� As manipulações efetuadas nos dados são desfeitas. 
� As linhas bloqueadas são liberadas. 
 50 
Rollbak a Nível de Comando 
 
� Se um único comando DML falha durante sua execução, somente este comando sofre Rollback. 
� Todas as demais modificações são provisoriamente mantidas até que um comando Commit ou 
Rollback seja executado. 
 
 
 
 
 
 
 
 51 
ALTERANDO TABELAS E CONSTRAINTS 
 
 
O comando Alter Table lhe permite: 
 
� Adicionar e modificar colunas de uma tabela. 
� Adicionar e remover constraints. 
� Habilitar e desabilitar constraints. 
 
 
O comando Drop Table: 
 
� Remove as linhas e a estrutura de uma tabela. 
 
 
Outros comandos que afetam uma tabela são: 
 
� Rename - Para trocar o nome de uma tabela. 
� Truncate - Para remover todas as linhas de uma tabela. 
� Comment - Para adicionar um comentário sobre um objeto do dicionário de dados. 
 
 
Todos os três comandos acima são comandos DDL. Quando executados causam um COMMIT 
automático. Cuidado... 
 
 
 52 
Exemplo 1: Adicionando três colunas à tabela de Empregados. 
 
Como a tabela de Empregados foi criada: 
 
 CREATE TABLE EMPREGADOS 
 (NUMERO NUMBER(4) 
 CONSTRAINT EMPREGADOS_NUMBER_PK PRIMARY KEY, 
 NOME VARCHAR2(10), 
 SOBRENOME VARCHAR2(10), 
 CPF CHAR(11) 
 CONSTRAINT EMPREGADOS_CPF_UK UNIQUE, 
 CARGO VARCHAR2(9), 
 NUM_SUPERVISOR NUMBER(4) 
 CONSTRAINT EMP_EMP_NUM_SUPERVISOR_FK 
 REFERENCES EMPREGADOS (NUMERO), 
 DT_ADMISSAO DATE, 
 SALARIO NUMBER(7,2), 
 PERC_COMISSAO NUMBER(4,2) 
 CONSTRAINT EMPREGADOS_PERC_COMISSAO_CK CHECK 
 (PERC_COMISSAO IN (10, 12.5, 15, 17.5, 20)), 
 NUMERO_DEPT NUMBER(2) 
 CONSTRAINT EMPREG_DEPARTAMENTOS_NUMERO_FK 
 REFERENCES DEPARTAMENTOS (NUMERO)); 
 
 
Comando Alter Table: 
 
 ALTER TABLE EMPREGADOS 
 ADD (RUA VARCHAR2(50), 
 NUM NUMBER(6), 
 COMPLEMENTO VARCHAR2(30)); 
 
 
Regras a serem observadas na utilização do Alter Table: 
 
- É possível adicionar e modificar colunas de uma tabela mas não se pode remover uma coluna 
de uma tabela. A tabela deve ser criada novamente sem a coluna que se deseja remover. 
- Não se pode especificar em que posição da tabela você deseja que a coluna apareça. Ela 
aparecerá no final da tabela, isto é, será a última coluna. 
- Ao se criar uma nova coluna em uma tabela só se pode especificar a cláusula NOT NULL se a 
tabela estiver vazia. 
- A largura (precisão) de uma coluna só deve ser diminuída se a coluna só possui valores nulos 
ou se a tabela se encontra vazia. 
- A modificação de um tipo de dado de uma coluna só deve ser efetuada se a coluna só contém 
valores nulos. 
- Para converter uma coluna CHAR em VARCHAR2 ou de VARCHAR2 para CHAR a coluna 
deverá estar vazia ou o tamanho não deverá ser modificado. 
[R33] Comentário: Numero já 
existe. 
 
[R34] Comentário: Os valores 
não serão truncados. Ocorrerá um 
erro. 
 
 53 
- A modificação do valor Default de uma coluna só afeta as linhas inseridas posteriormente a 
esta modificação. 
 
 
Exemplo 2: Modificando o tamanho de uma coluna na tabela de Empregados. 
 
 ALTER TABLE EMPREGADOS 
 MODIFY (RUA VARCHAR2 (40)); 
 
A modificação abaixo será concluída com sucesso? _____ Porque? _______________________ 
_____________________________________________________________________________ 
 
 
Exemplo 3: Dropando uma constraint da tabela Trabalham. 
 
Passos a serem executados: 
 
1. Descobra o nome da constraint que se deseja dropar acessando a USER_CONSTRAINTS. 
2. Digamos que após você descobrir os nomes das constraints associadas à tabela Trabalham você 
ainda estivesse na dúvida sobre qual constraint deveria ser dropada. Para você descobrir a que 
coluna da tabela Trabalham cada uma das constraints recuperadas acima se referem seria preciso 
pesquisar a visão do dicionário de dados USER_CONS_COLUMNS 
3. Execute o comando abaixo para dropar a constraint desejada: 
 
 ALTER TABLE TRABALHAM 
 DROP CONSTRAINT NOME_DA_CONSTRAINT; 
 
 
Exemplo 4: Criando uma Constraint. 
 
 ALTER TABLE TRABALHAM 
 ADD CONSTRAINT NOME_DA_CONSTRAINT 
 FOREIGN KEY (NUMERO_PROJ) 
 REFERENCES PROJETOS (NUMERO); 
 
 
Exemplo 5: Coma dropar uma constraint de primary key forçando a remoção da respectiva constraint 
de foreign key. 
 
 ALTER TABLE PROJETOS 
 DROP CONSTRAINT PROJETOS_NUMBER_PK CASCADE; 
 
 OU 
 
 ALTER TABLE PROJETOS 
 DROP PRIMARY KEY CASCADE; 
 
[R35] Comentário: SELECT 
CONSTRAINT_NAME, 
CONSTRAINT_TYPE 
 FROM 
USER_CONSTRAINTS 
 WHERE TABLE_NAME = 
'TRABALHAM'; 
 
[R36] Comentário: SELECT 
UC.CONSTRAINT_NAME, 
UC.CONSTRAINT_TYPE, 
 
UCC.COLUMN_NAME 
 FROM 
USER_CONSTRAINTS UC, 
USER_CONS_COLUMNS UCC 
 WHERE 
UC.CONSTRAINT_NAME = 
UCC.CONSTRAINT_NAME 
 AND UC.TABLE_NAME = 
'TRABALHAM'; 
 
 54 
 
Exemplo 5: Como habilitar e desabilitar constraints. 
 
Digamos que você deseja remover a coluna DT_FIM da tabela de Projetos. Como você já sabe não há 
como fazer isto através do comando ALTER TABLE. É preciso remover a tabela e recriá-la sem esta 
coluna. Siga os passos a seguir: 
 
1. Em primeiro lugar salve os dados da tabela Projetos. 
 
 CREATE TABLE PROJETO_AUX 
 AS SELECT NUMERO, NOME, DT_INICIO 
 FROM PROJETOS; 
 
2. Agora tente Dropar a tabela de Projetos. Conseguiu? ____ A menssagem ORA-02266: 
unique/primary keys in table referenced by enabled foreign keys deverá ter aparecido. Aforeign key a que a menssagem se refere é a foreign key da tabela Trabalham. Veja, a seguir, o 
comando de criação desta tabela: 
 
 CREATE TABLE TRABALHAM 
 (NUMERO_EMP NUMBER(4) 
 CONSTRAINT TRABALHAM_EMPREGADOS_NUMERO_FK 
 REFERENCES EMPREGADOS (NUMERO), 
 NUMERO_PROJ NUMBER(2) 
 CONSTRAINT TRABALHAM_PROJETOS_NUMERO_FK 
 REFERENCES PROJETOS (NUMERO), 
 CONSTRAINT TRABALHAM_PK PRIMARY KEY(NUMERO_EMP, 
 NUMERO_PROJ)); 
 
3. Para que possamos apagar a tabela Projetos será preciso desabilitar a constraint que está 
provocando este erro, isto é, a constraint TRABALHAM_PROJETOS_NUMERO_FK. 
 
ALTER TABLE TRABALHAM 
DISABLE CONSTRAINT TRABALHAM_PROJETOS_NUMERO_FK; 
 
4. Agora podemos dropar a tabela Projetos. 
 
DROP TABLE PROJETOS; 
 
[R37] Comentário: Não 
funcionou. Ao dropar a tabela 
Projetos (passo 4) o SGBD 
reclama como se houvesse outra 
constraint habilitada. 
 
 55 
5. Recrie a tabela Projetos sem a coluna DT_FIM. 
 
 CREATE TABLE PROJETOS 
 (NUMERO NUMBER(2) 
 CONSTRAINT PROJETOS_NUMBER_PK PRIMARY KEY, 
 NOME VARCHAR2(14), 
 DT_INICIO DATE); 
 
6. Copie os dados da tabela PROJETO_AUX para a tabela PROJETO. 
 
 INSERT INTO PROJETOS 
 SELECT * FROM PROJETO_AUX; 
 
7. E, finalmente, reabilite a constraint TRABALHAM_PROJETOS_NUMERO_FK. 
 
ALTER TABLE 
ENABLE CONSTRAINT TRABALHAM_PROJETOS_NUMERO_FK; 
 
 
Exemplo 6: Como dropar uma tabela e as constraints que dela dependam. 
 
 DROP TABLE PROJETOS CASCADE CONSTRAINTS; 
 
Observação: Esta é uma alternativa para os itens 3 e 4 do exemplo 5. Neste caso seria necessário 
recriar as constraints dropadas com o cascade. 
 
 
Exemplo 7: Como renomear uma tabela, uma visão, uma seqüência ou um sinônimo. 
 
 RENAME PROJETOS TO PROJ; 
 
[R38] Comentário: Todos os 
dados são deletados. 
A transação corrente é comitada. 
Todos os índices são dropados. 
Todas as constraints relacionadas 
são dropadas. 
Não é possível rollbecar este 
comando. 
 
[R39] Comentário: É preciso 
ter direitos sobre o objeto 
renomeado e sobre os demais 
afetados. 
 
 56 
SEQÜÊNCIAS 
 
 
Para que servem as seqüências: 
 
� São utilizadas na geração de números únicos para a função de chaves primárias. 
� Uma seqüência é um objeto que pode ser compartilhado. 
� Quando mantidas na memória aumentam a performance das aplicações. 
 
 
Exemplo 1: Criação de uma seqüência para ser utilizada na geração de valores de chave primária para 
a tabela de departamentos. 
 
 CREATE SEQUENCE S_DEPARTAMENTOS_NUMERO 
 INCREMENT BY 1 
 START WITH 41 
 MAXVALUE 99 
 NOCACHE 
 NOCYCLE; 
 
 
Exemplo 2: Utilização da seqüência na inserção de uma linha na tabela de departamentos. 
 
 INSERT INTO DEPARTAMENTOS (NUMERO, NOME, LOCAL) 
 VALUES (S_DEPARTAMENTOS_NUMERO.NEXTVAL, ‘CONTABILIDADE’, 
 ‘NITEROI’); 
 
 
Observações: 
 
� A opção CYCLE não deve ser utilizada se a seqüência será utilizada para gerar valores de chave 
primária. 
� Com a opção CACHE é possível pedir para o Oracle manter na memória uma certa quantidade de 
valores da seqüência que, quando solicitados, serão buscados na memória e não no disco. 
� Intervalos na seqüência podem ocorrer quando: 
� Ocorre um Rollback 
� Ocorre um crash no sistema 
� A seqüência é utilizada em mais de uma tabela 
� Para se saber que valor da seqüência foi utilizado no mais recente Insert em Departamentos deve-
se utilizar S_DEPARTAMENTOS.CURRVAL. 
 
 
[R40] Comentário: O default 
é 1. 
 
[R41] Comentário: O default 
é 1. 
 
[R42] Comentário: Por default 
o valor máximo é 10 elevado a 27. 
 
[R43] Comentário: O default 
é CACHE 20. Posso escrever 
CACHE 100, por exemplo. Se não 
quero o cache escrevo 
NOCACHE. 
 
[R44] Comentário: O default 
é NOCYCLE. A outra opção é 
CYCLE. 
 
[R45] Comentário: O default 
é CACHE 20 
 
[R46] Comentário: Observe 
que uma sequencia não tem qq 
víncula com tabela nenhuma. 
 
[R47] Comentário: Digamos 
que insiro um departamento na 
tabela de departamentos e, em 
seguida, desejo inserir os 
empregados lotados naquele 
departamento. Como saber que 
valor informar na chave 
estrangeira NUMERO_DEPT em 
empregados? 
 
 57 
Exemplo 3: Utilização de CURRVAL na inserção de um empregado. 
 
 INSERT INTO EMPREGADOS(NUMERO, NOME, SOBRENOME, CPF, CARGO, 
NUM_SUPERVISOR, DT_ADMISSAO, SALARIO, PERC_COMISSAO, NUMERO_DEPT) 
 VALUES (7567, 'VINICIUS', 'AGUIAR', '84630817492', 'VENDEDOR', 7566, '10-JAN-92', 
 1200, 10, S_DEPARTAMENTOS_NUMERO.CURRVAL); 
 
 
Exemplo 4: Criação de uma seqüência utilizando todos os defaults. 
 
 CREATE SEQUENCE ABC; 
 
 
Como consultar os dados de uma seqüência no dicionário de dados: 
 
 Desc USER_SEQUENCES 
 
 
Name Null? Type 
 ------------------------------- --------------- -------------------- 
 SEQUENCE_NAME NOT NULL VARCHAR2(30) 
 MIN_VALUE NUMBER 
 MAX_VALUE NUMBER 
 INCREMENT_BY NOT NULL NUMBER 
 CYCLE_FLAG VARCHAR2(1) 
 ORDER_FLAG VARCHAR2(1) 
 CACHE_SIZE NOT NULL NUMBER 
 LAST_NUMBER NOT NULL NUMBER 
 
 
Regras a respeito da utilização de NEXTVAL e CURRVAL 
 
Onde é possível utilizar NEXTVAL e CURRVAL: 
 
� Em um comando SELECT que não seja parte de uma sub-query. 
� Em um comando SELECT, sub-query de um INSERT. 
� Na cláusula VALUE de um INSERT. 
� Na cláusula SET de um UPDATE. 
 
Quando não é possível utilizar NEXTVAL e CURRVAL: 
 
� Em um SELECT de uma VIEW. 
� Em um comando SELECT com a palavra-chave DISTICT 
� Em um comando SELECT com as cláusulas GROUP BY, HAVING E ORDER BY. 
� Em uma subquery em um comando SELECT, DELETE ou UPDATE. 
� Em uma expressão DEFAULT no comando CREATE TABLE ou ALTER TABLE. 
[R48] Comentário: Acho que 
isso é para saber se a sequencia é 
ascendente ou descendente. 
 
[R49] Comentário: Quando se 
cria uma sequence sem cache 
(NOCACHE), cache size é igual a 
zero. 
 
[R50] Comentário: Indica o 
próximo número a ser utilizado 
quando os números do cache 
acabarem. 
 
[R51] Comentário: 
 
SELECT 
S_DEPARTAMENTOS_NOME.C
URRVAL 
FROM SYS.DUAL; 
 
[R52] Comentário: 
 
DELETE FROM EMPREGADOS 
WHERE NUMERO_DEPT = 
(SELECT NUMERO 
 
FROM DEPARTAMENTOS 
 
WHERE LOCAL = ‘RIO’; 
 
UPDATE EMPREGADOS 
SET NUMERO = (SELECT 
S_DEPARTAMENTOS_NOME.
NEXTVAL 
 FROM 
SYS.DUAL)  INVÁLIDO. 
Válido sem subquery. 
WHERE NUMERO = (SELECT 
NUMERO 
 FROM 
EMPREGADOS 
 WHERE 
CARGO = ‘FAXINEIRO’)  
Sequencia na subquery não é 
VÁLIDO. 
 
[R53] Comentário: 
 
CREATE TABLE TESTE 
(N1 NUMBER(3), 
 N2 NUMBER(3) DEFAULT 
3); 
 
 
 58 
Como modificar uma seqüência 
 
Exemplo 4: Modificação de uma seqüência através do comando ALTER SEQUENCE. 
 
 ALTER SEQUENCE S_DEPARTAMENTOS_NUMERO 
 INCREMENT BY n 
 MAXVALUE n ou NOMAXVALUE 
 MINVALUE n ou NOMINVALUE 
 CYCLE ou NOCYCLE 
 CACHE n ou NOCACHE; 
 
 
Observação: Para se poder alterar uma seqüência é preciso ser o OWNER da seqüência ou ter o 
privilégio de ALTER para a seqüência. 
 
 
Como remover uma seqüência