Material completo de banco de dados Oracle by

Capítulo 1: Conceitos de Banco de Dados
1.1
Objetivos deste capítulo
Introduzir os conceitos básico de sistema gerenciador de banco de dados, banco de dados
relacional, álgebra relacional, e a arquitetura do Oracle.
1.2
Sistema de Gerenciamento de Banco de Dados
O Sistema de Gerenciamento de Banco de Dados (SGBD) é um software que controla o
armazenamento, as modificações, e os acessos às informações do banco de dados.
O SGBD atua como interface entre os usuários e as informações armazenadas. Todas as
solicitações relativas aos dados do banco de dados são interceptadas, interpretadas, e
executadas pelo SGBD. Nenhum acesso aos dados pode ser feito de outra maneira.
Desta forma, o SGBD isola o usuário de todos os detalhes particulares do hardware ou do
sistema operacional sendo utilizado, tais como método de acesso e blocagem de disco. A
forma de acesso do usuário ao banco de dados não deve mudar quando o hardware ou o
sistema operacional mudam.
Existem modelos alternativos para se implementar um SGBD, sendo que os mais comuns
são hierárquico, rede, listas invertidas e relacional.
1.3
Banco de dados relacional
O modelo predominante atualmente é o relacional. Um banco de dados relacional pode ser
definido como um banco de dados que aparece ao usuário como uma coleção de tabelas
relacionadas, e nada além de tabelas.
Os três elementos de um banco de dados relacional são:
 tabelas
 colunas
 linhas
1
Os operadores relacionais são:
Seleção/Restrição
Projeção
União
Interseção
Diferença
Produto
Junção
é uma operação que recupera e mostra dados de uma
tabela. É possível recuperar todas as linhas, ou apenas
algumas que satisfaçam uma determinada condição ou a
várias condições. Esta operação é chamada também de
subconjunto horizontal.
é uma operação que recupera e mostra dados de apenas
algumas colunas, e portanto é chamada de subconjunto
vertical.
mostra todas as linhas que aparecem em duas tabelas.
mostra todas as linhas que aparecem nas duas tabelas.
mostra todas as linhas que aparecem em apenas uma das
tabelas
é o resultado obtido pela concatenação de duas tabelas.
Todas as linhas da primeira tabela são concatenadas com
todas as linhas da segunda tabela.
é o resultado obtido pela concatenação de duas tabelas de
acordo com condições específicas. Apenas as linhas que
atendem as condições estabelecidas são concatenadas.
Seleção/Restrição
Projeção
2
União
Interseção
Diferença
3
Produto
João
José
Manoel
x
Pintor
Escritor
=
João
João
José
José
Manoel
Manoel
Pintor
Escritor
Pintor
Escritor
Pintor
Escritor
Junção
João
José
Maria
Ana
1.4
10
10
20
30
10
20
30
40
Vendas
Pesq.
Cont.
Secret.
João
José
Maria
Ana
10
10
20
30
10
10
20
30
Vendas
Vendas
Pesq.
Cont.
Arquitetura do Oracle
Background Processes
System Global Area
Banco de Dados
Servers
Redo Log Files
Users
Control Files
Data Files
O SGBD:
Estruturas de memória:
System Global Area (SGA) é uma região compartilhada de memória alocada pelo
SGBD Oracle, que contém dados e informações de controle. Quanto maior for a SGA
melhor será o desempenho do Oracle. Os dados da SGA são compartilhados por
4
todos os usuários que estão acessando o banco de dados. As informações
armazenadas na SGA estão divididas por tipo de estrutura de memória, incluindo
‘database buffers’, ‘redo log buffers’ e ‘shared poll’.
Program Global Area (PGA) é uma área de memória que contém dados e
informações de controle para um processo servidor. Uma PGA é criada pelo Oracle
quando um processo servidor é iniciado.
Processos:
Os processos são divididos em Processos Usuário e Processos do Oracle. Cada
usuário conectado possui um Processo Usuário, através do qual se comunica com o
Oracle. Entre os processos do Oracle estão os Processos Servidores, que executam as
solicitações emitidas pelo Processos Usuários, e os Processos Background.
O Banco de dados:
Data Files
Contém todos os dados do banco de dados.
Redo Log Files
Contém todas as informações relativas às
alterações efetuadas no banco de dados
para permitir a recuperação.
Control Files
Registra a estrutura física do banco de dados.
5
Capítulo 2: Introdução às Consultas
2.1
Objetivos deste capítulo
Este capítulo faz uma introdução à linguagem de consulta utilizada para acessar o Banco de
Dados Oracle. Em particular, são discutidas as declarações utilizadas para:
2.2

realizar cálculos

manusear valores nulos corretamente

nomes alternativos para títulos das colunas

concatenar colunas

ordenar linhas

fornecer critérios de pesquisa
O utilitário SQL*PLUS
O SQL*PLUS é uma interface através da qual os comandos SQL podem ser entrados e
executados. O SQL*PLUS possui outros comandos, que permitem formatar a saída dos
comandos SQL, além de fornecer facilidades para editar e salvar comandos SQL.
Para ativar o SQL*PLUS digite SQLPLUS na linha de comando do MS-DOS, e pressione a
tecla Enter. Quando o SQLPLUS solicitar para entrar com o nome do usuário digite
ALUNO1, e quando for solicitada a senha forneça ALUNO1 novamente.
As linhas de comando do SQL*PLUS são prefixadas por: SQL>. As linhas de continuação
são numeradas. Todo o curso está baseado na utilização desta ferramenta pelos participantes.
Os comandos podem ser escritos em uma ou mais linhas.
2.3
Construção básica de uma consulta
O comando SELECT recupera informações do banco de dados, implementando todos os
operadores da álgebra relacional.
Em sua forma mais simples deve incluir:
a. A cláusula SELECT, que lista as colunas a serem envolvidas (essencialmente a Projeção,
conforme definido na álgebra relacional).
b. A cláusula FROM, que especifica as tabelas envolvidas.
Para listar os números de todos os departamentos, nomes dos empregados e números dos
gerentes da tabela EMP, digita-se:
6
SQL> SELECT DEPTNO, ENAME, MGR
2 FROM EMP
DEPTNO
------20
30
30
20
30
30
10
20
10
30
20
30
20
10
ENAME
MGR
---------- ------SMITH
7902
ALLEN
7698
WARD
7698
JONES
7839
MARTIN
7698
BLAKE
7839
CLARK
7839
SCOTT
7566
KING
TURNER
7698
ADAMS
7788
JAMES
7698
FORD
7566
MILLER
7782
14 rows selected.
É possível selecionar todas as colunas da tabela colocando-se um "*" (asterisco) após a
palavra SELECT, como visto abaixo:
SQL> SELECT *
2 FROM EMP
EMPNO
------7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
ENAME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
JOB
MGR HIREDATE
SAL
COMM DEPTNO
--------- ------- --------- ------- ------- ------CLERK
7902 13-JUN-83
800
20
SALESMAN
7698 15-AUG-83
1600
300
30
SALESMAN
7698 26-MAR-84
1250
500
30
MANAGER
7839 31-OCT-83
2975
20
SALESMAN
7698 05-DEC-83
1250
1400
30
MANAGER
7839 11-JUN-84
2850
30
MANAGER
7839 14-MAY-84
2450
10
ANALYST
7566 05-MAR-84
3000
20
PRESIDENT
09-JUL-84
5000
10
SALESMAN
7698 04-JUN-84
1500
0
30
CLERK
7788 04-JUN-84
1100
20
CLERK
7698 23-JUL-84
950
30
ANALYST
7566 05-DEC-83
3000
20
CLERK
7782 21-NOV-83
1300
10
14 rows selected.
2.4
Expressões aritméticas
Uma expressão é a combinação de um ou mais valores, operadores e funções, que resultam
em um valor calculado.
As expressões aritméticas podem conter nomes de colunas, constantes numéricas, e
operadores aritméticos. Os operadores são + - * e / , para soma, subtração, multiplicação e
divisão, respectivamente.
No exemplo abaixo calcula-se o salário anual multiplicando-se o salário mensal por doze.
SQL> SELECT ENAME, SAL*12, COMM
2 FROM EMP
7
ENAME
SAL*12
COMM
---------- ------- ------SMITH
9600
ALLEN
19200
300
WARD
15000
500
JONES
35700
MARTIN
15000
1400
BLAKE
34200
CLARK
29400
SCOTT
36000
KING
60000
TURNER
18000
0
ADAMS
13200
JAMES
11400
FORD
36000
MILLER
15600
Se a expressão aritmética contiver mais de um operador, multiplicação e divisão são de
mesma precedência, porém com precedência superior a adição e subtração, que também são
de mesma precedência. No caso de operadores com a mesma precedência, o da esquerda é
processado primeiro.
No exemplo abaixo, a multiplicação (250*12) é realizada primeiro, e depois somada ao
salário.
SQL> SELECT ENAME, SAL+250*12
2 FROM EMP
ENAME
SAL+250*12
---------- ---------SMITH
3800
ALLEN
4600
WARD
4250
JONES
5975
MARTIN
4250
BLAKE
5850
CLARK
5450
SCOTT
6000
KING
8000
TURNER
4500
ADAMS
4100
JAMES
3950
FORD
6000
MILLER
4300
Parênteses podem ser utilizados para especificar a ordem na qual os operadores serão
executados. Se, por exemplo, for necessário somar 250 ao salário antes de multiplicar por
12, devemos escrever:
SQL> SELECT ENAME, (SAL+250)*12
2 FROM EMP
ENAME
(SAL+250)*12
---------- -----------SMITH
12600
ALLEN
22200
WARD
18000
JONES
38700
MARTIN
18000
8
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
2.5
37200
32400
39000
63000
21000
16200
14400
39000
18600
Nomes alternativos para títulos de colunas
Para mostrar o resultado de uma consulta, o SQL*PLUS normalmente utiliza o nome da
coluna como título da coluna. Em muitos casos, isto torna o título sem significado. Pode ser
alterado o título da coluna usando-se um Aliás.
O Aliás é especificado escrevendo-se um texto após item a ser mostrado na cláusula
SELECT. Por padrão, o nome do Aliás é convertido para letras maiúsculas, e não pode
conter espaços em branco, a menos que seja colocado entre aspas.
Para mostrar como título da coluna SALARIO ANUAL no lugar de SAL*12, podemos
escrever:
SQL> SELECT ENAME NOME, SAL*12 “SALARIO ANUAL”, COMM
2 FROM EMP
NOME
SALARIO ANUAL
COMM
---------- -------------- ------SMITH
9600
ALLEN
19200
300
WARD
15000
500
JONES
35700
MARTIN
15000
1400
BLAKE
34200
CLARK
29400
SCOTT
36000
KING
60000
TURNER
18000
0
ADAMS
13200
JAMES
11400
FORD
36000
MILLER
15600
14 rows selected.
Nota: somente na cláusula SELECT o Aliás pode ser usado, nas outras cláusulas não.
2.6
O operador de concatenação
O operador de concatenação (||) permite juntar colunas, expressões aritméticas, ou valores
constantes, para criar uma expressão do tipo caracter. Colunas dos dois lados do operador
são combinadas para formar uma só coluna.
Para combinar o número do empregado com o nome do empregado em uma coluna apenas,
utilizando EMPREGADO como título, devemos escrever:
SQL> SELECT EMPNO||ENAME EMPREGADO
2 FROM EMP
9
EMPREGADO
-------------------------------------------------7369SMITH
7499ALLEN
7521WARD
7566JONES
7654MARTIN
7698BLAKE
7782CLARK
7788SCOTT
7839KING
7844TURNER
7876ADAMS
7900JAMES
7902FORD
7934MILLER
14 rows selected.
2.7
Literais
Um literal é qualquer caracter, expressão, ou número, incluido na cláusula SELECT, que não
é um nome ou Aliás da coluna.
O literal da cláusula SELECT é listado em todas as linhas mostradas pelo comando. Literais
dos tipos data e caracter devem ser envoltos por apóstrofos ('). Literais numéricos não
precisam de apóstrofos.
O comando abaixo contém literal concatenado com colunas, e um Aliás para a primeira
coluna.
SQL>
SQL>
2
3
4
COLUMN EMPREGADO FORMAT A20
SELECT EMPNO||'-'||ENAME EMPREGADO,
'TRABALHA NO DEPARTAMENTO',
DEPTNO
FROM EMP
EMPREGADO
-------------------7369-SMITH
7499-ALLEN
7521-WARD
7566-JONES
7654-MARTIN
7698-BLAKE
7782-CLARK
7788-SCOTT
7839-KING
7844-TURNER
7876-ADAMS
7900-JAMES
7902-FORD
7934-MILLER
'TRABALHANODEPARTAMENTO' DEPTNO
------------------------ ------TRABALHA NO DEPARTAMENTO
20
TRABALHA NO DEPARTAMENTO
30
TRABALHA NO DEPARTAMENTO
30
TRABALHA NO DEPARTAMENTO
20
TRABALHA NO DEPARTAMENTO
30
TRABALHA NO DEPARTAMENTO
30
TRABALHA NO DEPARTAMENTO
10
TRABALHA NO DEPARTAMENTO
20
TRABALHA NO DEPARTAMENTO
10
TRABALHA NO DEPARTAMENTO
30
TRABALHA NO DEPARTAMENTO
20
TRABALHA NO DEPARTAMENTO
30
TRABALHA NO DEPARTAMENTO
20
TRABALHA NO DEPARTAMENTO
10
14 rows selected.
2.8
Manuseio de valores nulos
10
Se em uma linha não existir valor para uma determinada coluna, este valor é dito como
sendo nulo.
Um valor nulo é um valor que não está disponível, é desconhecido, ou não é aplicável. Um
valor nulo não é o mesmo que zero. Zero é um número.
Se o valor de qualquer coluna envolvida em uma expressão for nulo, o resultado da
expressão também será nulo. No exemplo abaixo, somente aparecem os salários anuais dos
vendedores, pois somente estes tem comissão não nula.
SQL> SELECT ENAME, SAL*12+COMM SALARIO_ANUAL
2 FROM EMP
ENAME
SALARIO_ANUAL
---------- ------------SMITH
ALLEN
19500
WARD
15500
JONES
MARTIN
16400
BLAKE
CLARK
SCOTT
KING
TURNER
18000
ADAMS
JAMES
FORD
MILLER
14 rows selected.
Para calcular o resultado corretamente, deve ser utilizada a expressão NVL, que converte um
valor nulo em um valor não nulo, conforme mostrado abaixo:
SQL> SELECT ENAME, SAL*12+NVL(COMM, 0) SALARIO_ANUAL
2 FROM EMP
ENAME
SALARIO_ANUAL
---------- ------------SMITH
9600
ALLEN
19500
WARD
15500
JONES
35700
MARTIN
16400
BLAKE
34200
CLARK
29400
SCOTT
36000
KING
60000
TURNER
18000
ADAMS
13200
JAMES
11400
FORD
36000
MILLER
15600
14 rows selected.
A função NVL espera dois argumentos:
a)
uma expressão
11
b)
um valor não nulo
Podem ser convertidos valores nulos de data, números, ou caracteres:
NVL(ColunaData, ‘01-jan-84')
NVL(ColunaNumero, 9)
NVL(ColunaCaracter, ‘alfanumérico')
2.9
Eliminação de linhas duplicadas
A não ser que seja especificado o contrário, as linhas duplicadas não são eliminadas dos
resultados das consultas.
SQL> SELECT DEPTNO
2 FROM EMP
DEPTNO
------20
30
30
20
30
30
10
20
10
30
20
30
20
10
Para eliminar as linhas duplicadas, deve ser incluída a palavra DISTINCT após o SELECT.
SQL> SELECT DISTINCT DEPTNO
2 FROM EMP
DEPTNO
------10
20
30
Múltiplas colunas podem ser especificadas após a palavra DISTINCT.
SQL> SELECT DISTINCT DEPTNO, JOB
2 FROM EMP
DEPTNO
------10
10
10
20
20
20
JOB
--------CLERK
MANAGER
PRESIDENT
ANALYST
CLERK
MANAGER
12
30 CLERK
30 MANAGER
30 SALESMAN
Acima estão mostradas todas as combinações diferentes de departamentos e cargos.
2.10
Ordenação das linhas
Normalmente, a ordem das linhas retornadas por uma consulta é indefinida. A cláusula
ORDER BY pode ser usada para ordenar linhas. Se for usada, ORDER BY deve ser sempre
a última cláusula de um comando SELECT.
Para ordenar pelo nome do empregado, usamos:
SQL> SELECT ENAME, JOB, SAL*12, DEPTNO
2 FROM EMP
3 ORDER BY ENAME
ENAME
---------ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD
JOB
SAL*12 DEPTNO
--------- ------- ------CLERK
13200
20
SALESMAN
19200
30
MANAGER
34200
30
MANAGER
29400
10
ANALYST
36000
20
CLERK
11400
30
MANAGER
35700
20
PRESIDENT
60000
10
SALESMAN
15000
30
CLERK
15600
10
ANALYST
36000
20
CLERK
9600
20
SALESMAN
18000
30
SALESMAN
15000
30
14 rows selected.
Ordem padrão de ordenação:
- Números: menores primeiro
- Data:
mais cedo primeiro
- Caracteres: ordem alfabética
Para reverter esta ordem, pode ser utilizada a palavra DESC após o nome da coluna, na
cláusula ORDER BY.
Para listarmos os funcionários mais novos antes dos mais antigos fazemos:
SQL> SELECT ENAME, JOB, HIREDATE
2 FROM EMP
3 ORDER BY HIREDATE DESC
ENAME
---------JAMES
KING
BLAKE
TURNER
ADAMS
CLARK
JOB
--------CLERK
PRESIDENT
MANAGER
SALESMAN
CLERK
MANAGER
HIREDATE
--------23-JUL-84
09-JUL-84
11-JUN-84
04-JUN-84
04-JUN-84
14-MAY-84
13
WARD
SCOTT
MARTIN
FORD
MILLER
JONES
ALLEN
SMITH
SALESMAN
ANALYST
SALESMAN
ANALYST
CLERK
MANAGER
SALESMAN
CLERK
26-MAR-84
05-MAR-84
05-DEC-83
05-DEC-83
21-NOV-83
31-OCT-83
15-AUG-83
13-JUN-83
É possível efetuar a ordenação por mais de uma coluna, sendo algumas em ordem
ascendente e outras em ordem descendente.
SQL> SELECT DEPTNO, JOB, ENAME
2 FROM EMP
3 ORDER BY DEPTNO, SAL DESC
DEPTNO
------10
10
10
20
20
20
20
20
30
30
30
30
30
30
JOB
--------PRESIDENT
MANAGER
CLERK
ANALYST
ANALYST
MANAGER
CLERK
CLERK
MANAGER
SALESMAN
SALESMAN
SALESMAN
SALESMAN
CLERK
ENAME
---------KING
CLARK
MILLER
SCOTT
FORD
JONES
ADAMS
SMITH
BLAKE
ALLEN
TURNER
WARD
MARTIN
JAMES
Exercício: verificar como ficam os valores nulos após a ordenação.
2.11
Critérios de pesquisa
Os critérios de pesquisa são informados na cláusula WHERE, que corresponde ao operador
Restrição da álgebra relacional. Nesta cláusula estão estabelecidos os critérios necessários
para uma linha poder ser selecionada.
A cláusula WHERE pode comparar valores em colunas, valores literais, expressões
aritméticas, ou funções. Três elementos são sempre necessários:
- Um nome de coluna
- Um operador de comparação
- Um nome de coluna, constante, ou lista de valores
Existem dois tipos de operadores: lógicos e SQL.
Os operadores lógicos testam as seguintes condições:
=
>
>=
<
<=
igual
maior
maior ou igual
menor
menor ou igual
14
<>
diferente (^= ou !=)
Para listar os nomes, números, cargos e departamentos de todos os funcionários com cargo
CLERK, usamos:
SQL> SELECT ENAME, EMPNO, JOB, DEPTNO
2 FROM EMP
3 WHERE JOB = ‘CLERK'
ENAME
EMPNO JOB
DEPTNO
---------- ------- --------- ------SMITH
7369 CLERK
20
ADAMS
7876 CLERK
20
JAMES
7900 CLERK
30
MILLER
7934 CLERK
10
Para listar o nome e o número dos departamentos com número maior que 20, usamos:
SQL> SELECT DNAME, DEPTNO
2 FROM DEPT
3 WHERE DEPTNO > 20
DNAME
DEPTNO
-------------- ------SALES
30
OPERATIONS
40
Para sabermos que funcionários tem comissão superior ao salário, usamos:
SQL> SELECT ENAME, SAL, COMM
2 FROM EMP
3 WHERE COMM > SAL
ENAME
SAL
COMM
---------- ------- ------MARTIN
1250
1400
Existem quatro operadores SQL, que operam sobre todos os tipos de dados.
BETWEEN...AND...
IN(lista)
LIKE
IS NULL
entre dois valores (inclusive)
idênticos a uma lista de valores
semelhante a um modelo de caracteres
valor é nulo
O operador BETWEEN testa valores no intervalo ou idênticos aos limites.
SQL> SELECT ENAME, SAL
2 FROM EMP
3 WHERE SAL BETWEEN 1000 AND 2000
ENAME
SAL
---------- ------ALLEN
1600
WARD
1250
MARTIN
1250
TURNER
1500
ADAMS
1100
MILLER
1300
15
O operador IN testa os valores especificados em uma lista.
SQL> SELECT EMPNO, ENAME, SAL, MGR
2 FROM EMP
3 WHERE MGR IN ( 7902, 7566, 7788 )
EMPNO
------7369
7788
7876
7902
ENAME
SAL
MGR
---------- ------- ------SMITH
800
7902
SCOTT
3000
7566
ADAMS
1100
7788
FORD
3000
7566
Se caracteres ou datas forem utilizados, devem ser envoltos por apóstrofos.
Usando o operador LIKE é possível selecionar linhas de acordo com modelo fornecido. O
símbolo "%" representa qualquer seqüência de zero ou mais caracteres. O símbolo "_"
(sublinhado) representa qualquer um caracter, mas apenas um, no mínimo e no máximo.
Para listarmos todos os empregados cujos nomes começam com a letra "S", usamos:
SQL> SELECT ENAME
2 FROM EMP
3 WHERE ENAME LIKE 'S%'
ENAME
---------SMITH
SCOTT
Para listar os nomes de todos os empregados que contém exatamente 4 letras, usamos:
SQL> SELECT ENAME
2 FROM EMP
3 WHERE ENAME LIKE '____'
(quatro caracteres de sublinhado)
ENAME
---------WARD
KING
FORD
O operador IS NULL serve especificamente para testar valores nulos.
Para listar os funcionários que não possuem gerente, usamos:
SQL> SELECT ENAME, MGR
2 FROM EMP
3 WHERE MGR IS NULL
ENAME
MGR
---------- ------KING
16
Os operadores mostrados abaixo podem ser utilizados para testes de negação:
!=
^=
<>
NOT NomeDaColuna =
NOT NomeDaColuna >
NOT BETWEEN
NOT IN
NOT LIKE
IS NOT NULL
não igual
não igual a
não maior do que
fora da faixa especificada
fora da lista especificada
não semelhante ao modelo
não é nulo
Para listar os empregados com salários fora da faixa de 1000 a 2000, usamos:
SQL> SELECT ENAME, SAL
2 FROM EMP
3 WHERE SAL NOT BETWEEN 1000 AND 2000
ENAME
SAL
---------- ------SMITH
800
JONES
2975
BLAKE
2850
CLARK
2450
SCOTT
3000
KING
5000
JAMES
950
FORD
3000
8 rows selected.
Para listar os empregados cujos cargos não começam com a letra M, usamos:
SQL> SELECT ENAME, JOB
2 FROM EMP
3 WHERE JOB NOT LIKE 'M%'
ENAME
---------SMITH
ALLEN
WARD
MARTIN
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
JOB
--------CLERK
SALESMAN
SALESMAN
SALESMAN
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
Para listar os empregados com gerentes, usamos:
SQL> SELECT ENAME, MGR
2 FROM EMP
3 WHERE MGR IS NOT NULL
ENAME
MGR
---------- ------SMITH
7902
17
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
TURNER
ADAMS
JAMES
FORD
MILLER
7698
7698
7839
7698
7839
7839
7566
7698
7788
7698
7566
7782
x <> NULL e x = NULL
é sempre falso. Nulo nunca é igual a nada nem diferente de nada, nem a outro nulo.
2.12
Consultas com condições múltiplas
Os operadores AND e OR podem ser utilizados para criar expressões lógicas compostas.
O predicado AND espera que todas duas condições sejam verdadeiras.
O predicado OR espera que uma (ou as duas) condições sejam verdadeiras.
Nos exemplos abaixo as condições são as mesmas, porém os predicados são diferentes.
Para listar todos os CLERKs com salário entre 1000 e 2000, usamos:
SQL>
2
3
4
SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL BETWEEN 1000 AND 2000
AND JOB = 'CLERK'
EMPNO
------7876
7934
ENAME
---------ADAMS
MILLER
JOB
SAL
--------- ------CLERK
1100
CLERK
1300
Para listar os funcionários que são CLERK, ou que recebem entre 1000 e 2000, usamos:
SQL>
2
3
4
SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL BETWEEN 1000 AND 2000
OR JOB = ‘CLERK'
EMPNO
------7369
7499
7521
7654
7844
7876
7900
7934
ENAME
---------SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER
JOB
SAL
--------- ------CLERK
800
SALESMAN
1600
SALESMAN
1250
SALESMAN
1250
SALESMAN
1500
CLERK
1100
CLERK
950
CLERK
1300
Quando aparecem tanto ANDs quanto ORs em uma expressão lógica, todos os ANDs são
processados antes que os ORs sejam processados.
18
Uma vez que AND tem precedência sobre OR, a consulta abaixo retorna todos os gerentes
com salário maior do que 1500, e todos os vendedores.
SQL>
2
3
4
5
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE SAL > 1500
AND JOB = 'MANAGER'
OR JOB = 'SALESMAN'
EMPNO
------7499
7521
7566
7654
7698
7782
7844
ENAME
---------ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
TURNER
JOB
SAL DEPTNO
--------- ------- ------SALESMAN
1600
30
SALESMAN
1250
30
MANAGER
2975
20
SALESMAN
1250
30
MANAGER
2850
30
MANAGER
2450
10
SALESMAN
1500
30
Para selecionar todos os vendedores e gerentes com salário superior a 1500, usamos:
SQL>
2
3
4
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE SAL > 1500
AND ( JOB = 'MANAGER' OR JOB = ‘SALESMAN' )
EMPNO
------7499
7566
7698
7782
ENAME
---------ALLEN
JONES
BLAKE
CLARK
JOB
SAL DEPTNO
--------- ------- ------SALESMAN
1600
30
MANAGER
2975
20
MANAGER
2850
30
MANAGER
2450
10
Os parênteses especificam a ordem em que os operadores devem ser calculados.
2.13
Precedência dos operadores
a) os operadores de comparação e os operadores SQL tem precedências iguais.
=, !=, <, >, <=, >=, BETWEEN...AND..., IN, LIKE, IS NULL
b) NOT (para reverter o resultado lógico da expressão, WHERE NOT(sal>2000))
c) A ND
d) OR
Para listar todos os gerentes, de qualquer departamento, e os CLERK do departamento 10
apenas, usamos:
SQL> SELECT *
2 FROM EMP
3 WHERE JOB = ‘MANAGER' OR (JOB = ‘CLERK' AND DEPTNO = 10)
EMPNO ENAME
JOB
MGR HIREDATE
SAL
COMM DEPTNO
------- ---------- --------- ------- --------- ------- ------- ------7566 JONES
MANAGER
7839 31-OCT-83
2975
20
19
7698 BLAKE
7782 CLARK
7934 MILLER
MANAGER
MANAGER
CLERK
7839 11-JUN-84
7839 14-MAY-84
7782 21-NOV-83
2850
2450
1300
30
10
10
Neste caso os parênteses são desnecessários, uma vez que o AND processa primeiro.
2.14
Resumo do comando SELECT
SELECT
FROM
WHERE
ORDER BY
2.15
[DISTINCT] {* | coluna [aliás],....}
tabela
condições
{coluna | expressão} [ASC|DESC]
Exercícios
a) Selecionar todas as informações da tabela SALGRADE
GRADE
LOSAL
HISAL
------- ------- ------1
700
1200
2
1201
1400
3
1401
2000
4
2001
3000
5
3001
9999
b) Selecionar todas a informações da tabela EMP
EMPNO
------7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
ENAME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
JOB
MGR HIREDATE
SAL
COMM DEPTNO
--------- ------- --------- ------- ------- ------CLERK
7902 13-JUN-83
800
20
SALESMAN
7698 15-AUG-83
1600
300
30
SALESMAN
7698 26-MAR-84
1250
500
30
MANAGER
7839 31-OCT-83
2975
20
SALESMAN
7698 05-DEC-83
1250
1400
30
MANAGER
7839 11-JUN-84
2850
30
MANAGER
7839 14-MAY-84
2450
10
ANALYST
7566 05-MAR-84
3000
20
PRESIDENT
09-JUL-84
5000
10
SALESMAN
7698 04-JUN-84
1500
0
30
CLERK
7788 04-JUN-84
1100
20
CLERK
7698 23-JUL-84
950
30
ANALYST
7566 05-DEC-83
3000
20
CLERK
7782 21-NOV-83
1300
10
14 rows selected.
c) Listar todos os funcionários com salário entre 1000 e 2000
ENAME
DEPTNO
SAL
---------- ------- ------ALLEN
30
1600
WARD
30
1250
MARTIN
30
1250
TURNER
30
1500
ADAMS
20
1100
MILLER
10
1300
6 rows selected.
d) Listar os números e os nomes dos departamentos, ordenados pelo nome do departamento
20
DEPTNO
------10
40
20
30
DNAME
-------------ACCOUNTING
OPERATIONS
RESEARCH
SALES
e) Listar os diferentes tipos de cargo
JOB
--------ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN
f) Listar os detalhes dos empregados dos departamentos 10 e 20 em ordem alfabética de
nome.
EMPNO
------7876
7782
7902
7566
7839
7934
7788
7369
ENAME
---------ADAMS
CLARK
FORD
JONES
KING
MILLER
SCOTT
SMITH
JOB
MGR HIREDATE
SAL
COMM DEPTNO
--------- ------- --------- ------- ------- ------CLERK
7788 04-JUN-84
1100
20
MANAGER
7839 14-MAY-84
2450
10
ANALYST
7566 05-DEC-83
3000
20
MANAGER
7839 31-OCT-83
2975
20
PRESIDENT
09-JUL-84
5000
10
CLERK
7782 21-NOV-83
1300
10
ANALYST
7566 05-MAR-84
3000
20
CLERK
7902 13-JUN-83
800
20
g) Listar os nomes e os cargos de todos os CLERK do departamento 20.
ENAME
---------SMITH
ADAMS
JOB
--------CLERK
CLERK
h) Listar os nomes de todos os empregados onde aparece TH ou LL no nome.
ENAME
---------SMITH
ALLEN
MILLER
i) Listar os seguintes detalhes dos funcionários que tem gerente.
ENAME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
TURNER
ADAMS
JAMES
JOB
SAL
--------- ------CLERK
800
SALESMAN
1600
SALESMAN
1250
MANAGER
2975
SALESMAN
1250
MANAGER
2850
MANAGER
2450
ANALYST
3000
SALESMAN
1500
CLERK
1100
CLERK
950
21
FORD
MILLER
ANALYST
CLERK
3000
1300
j) Mostrar o nome e a remuneração total de todos os empregados.
ENAME
REMUNERACAO
---------- ----------SMITH
9600
ALLEN
19500
WARD
15500
JONES
35700
MARTIN
16400
BLAKE
34200
CLARK
29400
SCOTT
36000
KING
60000
TURNER
18000
ADAMS
13200
JAMES
11400
FORD
36000
MILLER
15600
k) Mostrar todos os empregados que foram admitidos em 1983.
ENAME
DEPTNO HIREDATE
---------- ------- --------SMITH
20 13-JUN-83
ALLEN
30 15-AUG-83
JONES
20 31-OCT-83
MARTIN
30 05-DEC-83
FORD
20 05-DEC-83
MILLER
10 21-NOV-83
l) Mostrar o nome, o salário anual e comissão, de todos os vendedores com salário mensal
maior do que a comissão. O resultado deve ser ordenado com os maiores salários na frente.
Se dois ou mais empregados tiverem o mesmo salário, deve ser ordenado pelo nome do
funcionário.
ENAME
SALARIO_ANUAL
COMM
---------- ------------- ------ALLEN
19200
300
TURNER
18000
0
WARD
15000
500
m) Selecione os dados para reproduzir a saída mostrada abaixo.
QUEM, O QUE, QUANDO
--------------------------------------------------------------------SMITH TEM A POSICAO DE CLERK NO DEPARTAMENTO 20 DESDE 13-JUN-83
ALLEN TEM A POSICAO DE SALESMAN NO DEPARTAMENTO 30 DESDE 15-AUG-83
WARD TEM A POSICAO DE SALESMAN NO DEPARTAMENTO 30 DESDE 26-MAR-84
JONES TEM A POSICAO DE MANAGER NO DEPARTAMENTO 20 DESDE 31-OCT-83
MARTIN TEM A POSICAO DE SALESMAN NO DEPARTAMENTO 30 DESDE 05-DEC-83
BLAKE TEM A POSICAO DE MANAGER NO DEPARTAMENTO 30 DESDE 11-JUN-84
CLARK TEM A POSICAO DE MANAGER NO DEPARTAMENTO 10 DESDE 14-MAY-84
SCOTT TEM A POSICAO DE ANALYST NO DEPARTAMENTO 20 DESDE 05-MAR-84
KING TEM A POSICAO DE PRESIDENT NO DEPARTAMENTO 10 DESDE 09-JUL-84
TURNER TEM A POSICAO DE SALESMAN NO DEPARTAMENTO 30 DESDE 04-JUN-84
ADAMS TEM A POSICAO DE CLERK NO DEPARTAMENTO 20 DESDE 04-JUN-84
JAMES TEM A POSICAO DE CLERK NO DEPARTAMENTO 30 DESDE 23-JUL-84
FORD TEM A POSICAO DE ANALYST NO DEPARTAMENTO 20 DESDE 05-DEC-83
MILLER TEM A POSICAO DE CLERK NO DEPARTAMENTO 10 DESDE 21-NOV-83
22
2.16
Respostas dos exercícios
a) SQL> SELECT *
2 FROM SALGRADE
b) SQL> SELECT *
2 FROM EMP
c) SQL> SELECT ENAME, DEPTNO, SAL
2 FROM EMP
3 WHERE SAL BETWEEN 1000 AND 2000
d) SQL> SELECT DEPTNO, DNAME
2 FROM DEPT
3 ORDER BY DNAME
e) SQL> SELECT DISTINCT JOB
2 FROM EMP
f) SQL> SELECT *
2 FROM EMP
3 WHERE DEPTNO IN(10,20)
4 ORDER BY ENAME
g) SQL> SELECT ENAME, JOB
2 FROM EMP
3 WHERE JOB = ‘CLERK'
4 AND DEPTNO = 20
h) SQL> SELECT ENAME
2 FROM EMP
3 WHERE ENAME LIKE '%TH%'
4 OR ENAME LIKE '%LL%'
i) SQL> SELECT ENAME, JOB, SAL
2 FROM EMP
3 WHERE MGR IS NOT NULL
j) SQL> SELECT ENAME, SAL*12+NVL(COMM,0) REMUNERACAO
2 FROM EMP
k) SQL> SELECT ENAME, DEPTNO, HIREDATE
2 FROM EMP
3 WHERE HIREDATE LIKE '%83'
l) SQL> SELECT ENAME, SAL*12 SALARIO_ANUAL, COMM
2 FROM EMP
3 WHERE SAL > COMM
4 AND JOB = 'SALESMAN'
5 ORDER BY SAL DESC, ENAME
m) SQL> SELECT ENAME||' TEM A POSICAO DE '||JOB||
2 ' NO DEPARTAMENTO '||DEPTNO||
3 ' DESDE '||HIREDATE "QUEM, O QUE, QUANDO"
4 FROM EMP
23
Capítulo 3: Variáveis de Substituição
3.1
Objetivos deste capítulo
Descrever as variáveis de substituição e como estas são usadas nas declarações SQL.
3.2
Variáveis de substituição com um "&"
A variável de substituição pode ser utilizada em uma declaração SELECT, representando um
valor a ser fornecido quando o comando for executado.
SQL> SELECT EMPNO, ENAME, SAL
2 FROM EMP
3 WHERE DEPTNO = &NUMERO_DO_DEPARTAMENTO
Enter value for numero_do_departamento: 10
old
new
3: WHERE DEPTNO = &NUMERO_DO_DEPARTAMENTO
3: WHERE DEPTNO = 10
EMPNO
------7782
7839
7934
3.3
ENAME
SAL
---------- ------CLARK
2450
KING
5000
MILLER
1300
Variáveis de substituição com dois "&"
Quando são utilizados dois "&" na variável, o SQL*PLUS solicita o valor da variável apenas
uma vez, guardando este valor para uso subseqüente.
Exemplo: &&NUMERO_DO_DEPARTAMENTO
24
Capítulo 4: Funções Numéricas e de Caracteres
4.1
Objetivos deste capítulo
Este capítulo mostra as funções do Oracle que manipulam números e sequências de
caracteres.
4.2
Funções que manipulam caracteres
4.2.1 LOWER(coluna | literal)
Transforma letras maiúsculas em minúsculas.
SQL> SELECT LOWER(DNAME), LOWER('CURSO DE SQL')
2 FROM DEPT
LOWER(DNAME)
-------------accounting
research
sales
operations
LOWER('CURSODESQL')
-------------------curso de sql
curso de sql
curso de sql
curso de sql
4.2.2 UPPER(coluna | literal)
Transforma letras minúsculas em maiúsculas.
SQL> SELECT ENAME
2 FROM EMP
3 WHERE ENAME = UPPER('smith')
ENAME
---------SMITH
4.2.3 INITCAP(coluna | literal)
Transforma a primeira letra da palavra em maiúscula e as demais minúsculas.
SQL> SELECT INITCAP(DNAME), INITCAP(LOC)
2 FROM DEPT
INITCAP(DNAME)
-------------------Accounting
Research
Sales
Operations
INITCAP(LOC)
-------------------New York
Dallas
Chicago
Boston
4.2.4 LPAD(coluna | literal, tamanho, 'caracter')
Preenche a sequência de caracteres, à esquerda, com o caracter e o tamanho informados. Se
o caracter de preenchimento não for informado, será utilizado o caracter de espaço.
25
No exemplo abaixo, as três colunas são preenchidas à esquerda até um total de 20 caracteres.
A primeira coluna foi preenchida com asteriscos, a segunda com espaços, e a terceira com
pontos. Note que a terceira coluna é numérica.
SQL> SELECT LPAD(DNAME, 20, ‘*'), LPAD(DNAME, 20), LPAD(DEPTNO, 20,'.')
2 FROM DEPT
LPAD(DNAME,20,‘*')
LPAD(DNAME,20)
LPAD(DEPTNO,20,'.')
-------------------- -------------------- -------------------**********ACCOUNTING
ACCOUNTING ..................10
************RESEARCH
RESEARCH ..................20
***************SALES
SALES ..................30
**********OPERATIONS
OPERATIONS ..................40
4.2.5 RPAD(coluna | literal, tamanho, 'caracter')
Preenche a seqüência de caracteres, à direta, com o caracter e o tamanho informados. Se o
caracter de preenchimento não for informado, será utilizado o caracter de espaço.
SQL> SELECT RPAD(DNAME, 20, ‘*'), RPAD(DNAME, 20), RPAD(DEPTNO, 20,'.')
2 FROM DEPT
RPAD(DNAME,20,'*')
-------------------ACCOUNTING**********
RESEARCH************
SALES***************
OPERATIONS**********
RPAD(DNAME,20)
-------------------ACCOUNTING
RESEARCH
SALES
OPERATIONS
RPAD(DEPTNO,20,'.')
-------------------10..................
20..................
30..................
40..................
4.2.6 SUBSTR(coluna | literal, posição, comprimento)
Retorna uma seqüência de caracteres, a partir da posição indicada, com o comprimento
especificado.
SQL> SELECT SUBSTR('ORACLE',2,4), SUBSTR(DNAME,2), SUBSTR(DNAME,3,5)
2 FROM DEPT
SUBSTR('ORACLE',2,4)
-------------------RACL
RACL
RACL
RACL
SUBSTR(DNAME,2)
--------------CCOUNTING
ESEARCH
ALES
PERATIONS
SUBSTR(DNAME,3,5)
-------------------COUNT
SEARC
LES
ERATI
4.2.7 INSTR(coluna | literal, ‘seqüência de caracteres', posição, n)
Encontra a posição da primeira ocorrência da seqüência de caracteres na coluna ou literal, se
os dois últimos parâmetros forem omitidos.
Encontra a posição da n-ésima ocorrência a partir da posição indicada, se os quatro
parâmetros forem fornecidos.
SQL> SELECT DNAME,
2 INSTR(DNAME,'A'), INSTR(DNAME,'ES'), INSTR(DNAME,'C',1,2)
3 FROM DEPT
26
DNAME
INSTR(DNAME,'A') INSTR(DNAME,'ES') INSTR(DNAME,'C',1,2)
------------ ---------------- ----------------- -------------------ACCOUNTING
1
0
3
RESEARCH
5
2
0
SALES
2
4
0
OPERATIONS
5
0
0
4.2.8 LTRIM(coluna | literal, 'caracteres')
Remove os caracteres indicados à esquerda da coluna ou do literal. Se nenhum caracter for
especificado, serão eliminados os espaços à esquerda.
SQL> SELECT DNAME,
2 LTRIM(DNAME,'A'), LTRIM(DNAME,'AS'), LTRIM(DNAME,'ASOP')
3 FROM DEPT
DNAME
-------------ACCOUNTING
RESEARCH
SALES
OPERATIONS
LTRIM(DNAME,'A')
---------------CCOUNTING
RESEARCH
SALES
OPERATIONS
LTRIM(DNAME,'AS')
----------------CCOUNTING
RESEARCH
LES
OPERATIONS
LTRIM(DNAME,'ASOP')
------------------CCOUNTING
RESEARCH
LES
ERATIONS
No exemplo acima, na segunda coluna foram removidas as letras "A" à esquerda dos nomes
dos departamentos. Na terceira coluna foram removidas tanto a letra "A" quanto a letra "S".
Na quarta coluna foram removidas as letras "A", "S", "O" e "P" à esquerda.
4.2.9 RTRIM(coluna | literal, 'caracteres')
Remove os caracteres indicados à direita da coluna ou do literal. Se nenhum caracter for
especificado, serão eliminados os espaços à direita.
SQL> SELECT DNAME,
2 RTRIM(DNAME,'G'), RTRIM(DNAME,'GHS'), RTRIM(DNAME,'N')
3 FROM DEPT
DNAME
-------------ACCOUNTING
RESEARCH
SALES
OPERATIONS
RTRIM(DNAME,'G')
---------------ACCOUNTIN
RESEARCH
SALES
OPERATIONS
RTRIM(DNAME,'GHS')
-----------------ACCOUNTIN
RESEARC
SALE
OPERATION
RTRIM(DNAME,'N')
---------------ACCOUNTING
RESEARCH
SALES
OPERATIONS
4.2.10 SOUNDEX(coluna | literal)
Retorna uma seqüência de caracteres que representa o som das palavras contidas na coluna
ou no literal.
SQL> SELECT ENAME, SOUNDEX(ENAME)
2 FROM EMP
3 WHERE SOUNDEX(ENAME) = SOUNDEX('FRED')
ENAME
SOUNDEX(ENAME)
---------- -------------------FORD
F630
27
4.2.11 LENGTH(coluna | literal)
Retorna o número de caracteres (ou dígitos) na coluna ou literal especificados.
SQL> SELECT LENGTH('CURSO DE SQL'), LENGTH(DEPTNO), LENGTH(DNAME)
2 FROM DEPT
LENGTH('CURSODESQL')
LENGTH(DEPTNO)
LENGTH(DNAME)
--------------------- --------------------- --------------------12
2
10
12
2
8
12
2
5
12
2
10
4.2.12 TRANSLATE(coluna | literal, de, para)
Substitui os caracteres do argumento "de" pelos caracteres do argumento "para", na coluna
ou literal especificados. Todas as ocorrências dos caracteres do parâmetro "de" são
substituídas pelos caracteres no parâmetro "para". Caracteres do parâmetro "de" sem
correspondência no parâmetro "para" são removidos.
No exemplo abaixo foram trocados os caracteres "C" por "P" nos nomes dos funcionários.
SQL> SELECT ENAME, TRANSLATE(ENAME,'C','P')
2 FROM EMP
3 WHERE DEPTNO = 10
ENAME
---------CLARK
KING
MILLER
TRANSLATE(ENAME,'C','P')
------------------------PLARK
KING
MILLER
No exemplo abaixo foram trocados "A" por "I", e "R" por "T", nos cargos.
SQL> SELECT JOB, TRANSLATE(JOB,'AR','IT')
2 FROM EMP
3 WHERE DEPTNO = 10
JOB
--------MANAGER
PRESIDENT
CLERK
TRANSLATE(JOB,'AR','IT')
-----------------------------MINIGET
PTESIDENT
CLETK
4.2.13 REPLACE(coluna | literal, de, para)
Substitui a seqüência de caracteres "de" pela seqüência de caracteres "para", na coluna ou
literal especificados.
No exemplo abaixo SALESMAN é substituído por VENDEDOR:
SQL> SELECT JOB, REPLACE(JOB,'SALESMAN','VENDEDOR')
2 FROM EMP
28
JOB
--------CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
REPLACE(JOB,'SALESMAN','VENDEDOR')
---------------------------------------CLERK
VENDEDOR
VENDEDOR
MANAGER
VENDEDOR
MANAGER
MANAGER
ANALYST
PRESIDENT
VENDEDOR
CLERK
CLERK
ANALYST
CLERK
14 rows selected.
No exemplo abaixo, "CO" foi substituído por "PX":
SELECT ENAME, REPLACE(ENAME,'CO','PX')
FROM EMP
ENAME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
REPLACE(ENAME,'CO','PX')
-----------------------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SPXTT
<-------------KING
TURNER
ADAMS
JAMES
FORD
MILLER
14 rows selected.
4.2.14 Funções aninhadas
As funções que operam sobre uma única linha podem ser aninhadas, sendo que neste caso as
funções internas são executadas antes das externas.
SQL> SELECT DNAME, LENGTH(DNAME),
2 LENGTH(DNAME)-LENGTH(TRANSLATE(DNAME,'AS','A'))
3 FROM DEPT
DNAME
LENGTH(DNAME)LENGTH(DNAME)-LENGTH(TRANSLATE(DNAME,'AS','A'))
---------- -----------------------------------------------------------ACCOUNTING
10
0
RESEARCH
8
1
SALES
5
2
OPERATIONS
10
1
29
4.3
Funções numéricas
4.3.1 ROUND(coluna | literal, n)
Arredonda o valor da coluna ou do literal na n-ésima casa decimal. Se n for negativo
eqüivale às dezenas. Se n for omitido eqüivale a um número inteiro.
SQL> SELECT ROUND(45.923,1), ROUND(45.923), ROUND(42.323,-1),
ROUND(SAL/32,2)
2 FROM EMP
3 WHERE DEPTNO = 10
ROUND(45.923,1)
ROUND(45.923) ROUND(42.323,-1)
ROUND(SAL/32,2)
----------------- ----------------- ----------------- ----------------45.9
46
40
76.56
45.9
46
40
156.25
45.9
46
40
40.63
4.3.2 TRUNC(coluna | literal, n)
Trunca o valor da coluna ou do literal na n-ésima posição.
SQL> SELECT TRUNC(45.923,1), TRUNC(45.923), TRUNC(42.323,-1),
TRUNC(SAL/32,2)
2 FROM EMP
3 WHERE DEPTNO = 10
TRUNC(45.923,1)
TRUNC(45.923) TRUNC(42.323,-1)
TRUNC(SAL/32,2)
----------------- ----------------- ----------------- ----------------45.9
45
40
76.56
45.9
45
40
156.25
45.9
45
40
40.62
4.3.3 CEIL(coluna | literal)
Retorna o menor inteiro maior ou igual ao valor da coluna ou do literal.
SQL> SELECT CEIL(SAL), CEIL(99.9), CEIL(101.76), CEIL(-11.1)
2 FROM EMP
3 WHERE SAL BETWEEN 3000 AND 5000
CEIL(SAL) CEIL(99.9) CEIL(101.76) CEIL(-11.1)
--------- ---------- ------------ ----------3000
100
102
-11
5000
100
102
-11
3000
100
102
-11
4.3.4 FLOOR(coluna | literal)
Retorna o maior inteiro menor ou igual ao valor da coluna ou literal.
SQL> SELECT FLOOR(SAL), FLOOR(99.9), FLOOR(101.76), FLOOR(-11.1)
2 FROM EMP
3 WHERE FLOOR(SAL) BETWEEN 3000 AND 5000
30
FLOOR(SAL) FLOOR(99.9) FLOOR(101.76) FLOOR(-11.1)
---------- ----------- ------------- -----------3000
99
101
-12
5000
99
101
-12
3000
99
101
-12
4.3.5 POWER(coluna | literal, n)
Eleva a coluna ou literal a n-ésima potência.
SQL> SELECT SAL, POWER(SAL,2), POWER(SAL,3), POWER(50,5)
2 FROM EMP
3 WHERE DEPTNO = 10
SAL
POWER(SAL,2)
POWER(SAL,3)
POWER(50,5)
----------------- ----------------- ----------------- ----------------2450
6002500
14706125000
312500000
5000
25000000
125000000000
312500000
1300
1690000
2197000000
312500000
4.3.6 EXP(coluna | literal)
Eleva e (2.71828183...) à coluna ou literal.
SELECT EXP(4)
FROM
DUAL
EXP(4)
---------54.59815
1 row selected.
4.3.7 LOG(base, coluna | literal)
Calcula o logaritmo da coluna ou do literal na base especificada.
SELECT LOG(10,100)
FROM
DUAL
LOG(10,100)
----------2
1 row selected.
4.3.8 LN(coluna | literal)
Calcula o logaritmo neperiano da coluna ou do literal.
SELECT LN(95)
FROM
DUAL
LN(95)
---------4.55387689
4.3.9 SQRT(coluna | literal)
Retorna a raiz quadrada da coluna ou do literal.
31
SQL> SELECT SAL, SQRT(SAL), SQRT(40), SQRT(COMM)
2 FROM EMP
3 WHERE COMM > 0
SAL
SQRT(SAL)
SQRT(40)
SQRT(COMM)
---- ----------------- ----------------- ----------------1600
40 6.324555320336759 17.32050807568877
1250 35.35533905932738 6.324555320336759 22.3606797749979
1250 35.35533905932738 6.324555320336759 37.41657386773941
4.3.10 SIGN(coluna | literal)
Retorna -1 se o valor da coluna ou do literal for negativo, retorna 0 se for igual a zero, ou
retorna 1 se for maior do que zero.
SQL> SELECT SAL-COMM, SIGN(SAL-COMM), COMM-SAL, SIGN(COMM-SAL)
2 FROM EMP
3 WHERE DEPTNO = 30
SAL-COMM
SIGN(SAL-COMM)
COMM-SAL
SIGN(COMM-SAL)
----------------- ----------------- ----------------- ----------------1300
1
-1300
-1
750
1
-750
-1
-150
-1
150
1
1500
1
-1500
-1
Salários maiores do que a comissão podem ser listados conforme mostrado abaixo:
SQL> SELECT ENAME, SAL, COMM
2 FROM EMP
3 WHERE SIGN(SAL-COMM) = 1
ENAME
SAL
COMM
---------- ----------------- ----------------ALLEN
1600
300
WARD
1250
500
TURNER
1500
0
4.3.11 ABS(coluna | literal)
Retorna o valor absoluto da coluna ou do literal.
SQL> SELECT SAL, COMM, COMM-SAL, ABS(COMM-SAL)
2 FROM EMP
3 WHERE DEPTNO = 30
SAL
COMM
COMM-SAL
ABS(COMM-SAL)
----------------- ----------------- ----------------- ----------------1600
300
-1300
1300
1250
500
-750
750
1250
1400
150
150
2850
1500
0
-1500
1500
950
4.3.12 MOD(valor1, valor2)
Retorna o resto da divisão de valor1 por valor2.
32
SQL>
2
3
4
SELECT SAL, COMM, MOD(SAL,COMM), MOD(100,40)
FROM EMP
WHERE DEPTNO = 30
ORDER BY COMM
SAL
COMM
MOD(SAL,COMM)
MOD(100,40)
----------------- ----------------- ----------------- ----------------1500
0
1500
20
1600
300
100
20
1250
500
250
20
1250
1400
1250
20
2850
20
950
20
4.3.13 Funções trigonométricas
Calculam o seno (SIN), coseno (COS), e tangente (TAN) da coluna ou literal.
SELECT SIN(30*3.141592654/180),
COS(60*3.141592654/180),
TAN(45*3.141592654/180)
FROM
DUAL
SIN(30*3.1
---------.5
COS(60*3.1
---------.5
TAN(45*3.1
---------1
4.3.14 Funções hiperbólicas
Calculam o seno hiperbólico (SINH), coseno hiperbólico (COSH) e tangente hiperbólica
(TANH) da coluna ou literal.
SELECT SINH(1), COSH(0), TANH(.5)
FROM
DUAL
SINH(1)
---------1.17520119
4.4
COSH(0)
------1
TANH(.5)
---------.462117157
Exercícios
a) Listar o número do departamento, o nome do funcionário, e o salário com aumento de
15% expresso em número inteiro.
DEPTNO
----------------20
30
30
20
30
30
10
20
10
30
20
30
20
10
ENAME
PCTSAL
---------- ----------------SMITH
920
ALLEN
1840
WARD
1438
JONES
3421
MARTIN
1438
BLAKE
3278
CLARK
2818
SCOTT
3450
KING
5750
TURNER
1725
ADAMS
1265
JAMES
1093
FORD
3450
MILLER
1495
33
b) Gerar a listagem mostrada a seguir:
EMPREGADO_E_CARGO
-------------------SMITH
CLERK
ALLEN
SALESMAN
WARD
SALESMAN
JONES
MANAGER
MARTIN
SALESMAN
BLAKE
MANAGER
CLARK
MANAGER
SCOTT
ANALYST
KING
PRESIDENT
TURNER
SALESMAN
ADAMS
CLERK
JAMES
CLERK
FORD
ANALYST
MILLER
CLERK
14 rows selected.
c) Listar uma lista contendo todos os empregados com um identificador composto pelas duas
primeiras letras de seus cargos, os dois dígitos do meio de seus números de matrícula, e o
código sonoro de seus nomes.
NOME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
CODIGO
-------CL36S530
SA49A450
SA52W630
MA56J520
SA65M635
MA69B420
MA78C462
AN78S300
PR83K520
SA84T656
CL87A352
CL90J520
AN90F630
CL93M460
d) Faça uma procura não sensitiva a letras maiúsculas ou minúsculas por um cargo fornecido
pelo usuário.
EMPNO
----7369
7876
7900
7934
ENAME
---------SMITH
ADAMS
JAMES
MILLER
JOB
MGR HIREDATE
SAL COMM DEPTNO
--------- ----- --------- ----- ----- -----CLERK
7902 13-JUN-83
800
20
CLERK
7788 04-JUN-84 1100
20
CLERK
7698 23-JUL-84
950
30
CLERK
7782 21-NOV-83 1300
10
e) Imprima os nomes dos departamentos centrados em um campo com largura de 20
posições.
DEPARTAMENTO
-------------------ACCOUNTING
RESEARCH
SALES
OPERATIONS
34
f) Encontrar a primeira ocorrência da letra "L" nos nomes dos empregados e substituir pela
letra "X".
ENAME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
PRIMEIRA_OCORRENCIA_
-------------------SMITH
AXLEN
WARD
JONES
MARTIN
BXAKE
CXARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MIXLER
14 rows selected.
35
4.5
Respostas dos exercícios
a)
SQL> SELECT DEPTNO, ENAME, ROUND (SAL*1.15) PCTSAL
2 FROM EMP
b)
SQL> SELECT RPAD(ENAME,10)||LPAD(JOB,10) EMPREGADO_E_CARGO
2 FROM EMP
c)
SQL> SELECT ENAME NOME,
2 SUBSTR(JOB,1,2)||SUBSTR(EMPNO,2,2)||SOUNDEX(ENAME) CODIGO
3 FROM EMP
d)
SQL> SELECT *
2 FROM EMP
3 WHERE UPPER(JOB) = UPPER ('&JOB')
Enter value for job: clerk
old
3: WHERE UPPER(JOB) = UPPER ('&JOB')
new
3: WHERE UPPER(JOB) = UPPER ('clerk')
e)
SQL> SELECT LPAD(' ',(20-LENGTH(DNAME))/2)||DNAME DEPARTAMENTO
2 FROM DEPT
f)
SQL>
2
3
4
SELECT ENAME,
TRANSLATE(SUBSTR(ENAME,1,INSTR(ENAME,'L')),'L','X')||
SUBSTR(ENAME,INSTR(ENAME,'L')+1) PRIMEIRA_OCORRENCIA_DE_L
FROM EMP
36
Capítulo 5: Funções de Data e de Conversão
5.1
Objetivos deste capítulo
Este capítulo mostra as funções do Oracle que manipulam datas e fazem a conversão de tipo
de dados.
5.2
Funções que manipulam datas
5.2.1 SYSDATE
SYSDATE é uma pseudo-coluna que retorna a data e a hora corrente. A tabela SYS.DUAL é
pública, contém apenas uma coluna chamada DUMMY, e uma linha com o valor 'X'. Esta
tabela é útil para retornar um único valor de uma constante, de uma pseudo-coluna, ou de
uma expressão. Abaixo a tabela SYS.DUAL foi utilizada para retornar a data corrente.
SQL> SELECT SYSDATE
2 FROM SYS.DUAL
SYSDATE
--------25-OCT-94
5.2.2 Aritmética de datas
Datas podem ser somadas ou subtraídas de outras datas, ou de valores numéricos.
data+número
data-número
data-data
data+número/24
Soma o número de dias à data, produzindo outra data
Subtrai o número de dias à data, produzindo outra data
Subtrai uma data da outra, produzindo número de dias
Soma número de horas à data, produzindo outra data
SQL> SELECT HIREDATE, HIREDATE+7, HIREDATE-7, SYSDATE-HIREDATE
2 FROM EMP
3 WHERE HIREDATE LIKE '%JUN%'
HIREDATE
--------13-JUN-83
11-JUN-84
04-JUN-84
04-JUN-84
HIREDATE+7
---------20-JUN-83
18-JUN-84
11-JUN-84
11-JUN-84
HIREDATE-7
---------06-JUN-83
04-JUN-84
28-MAY-84
28-MAY-84
SYSDATE-HIREDATE
----------------4152.351851851852
3788.351851851852
3795.351851851852
3795.351851851852
A operação SYSDATE-HIREDATE retorna o número de dias entre a admissão do
empregado e a data atual.
5.2.3 MONTHS_BETWEEN(data1,data2)
Retorna o número de meses decorridos entre data1 e data2.
SQL>
2
3
4
SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE),
MONTHS_BETWEEN('01-JAN-84','05-NOV-88')
FROM EMP
WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE) > 59
37
MONTHS_BETWEEN(SYSDATE,HIREDATE) MONTHS_BETWEEN('01-JAN-84','05-NOV-88'
-------------------------------- -------------------------------------136.3985293458781
-58.1290322580645
134.3340132168459
-58.1290322580645
126.9791745071685
-58.1290322580645
131.8178841845878
-58.1290322580645
130.6565938620072
-58.1290322580645
124.4630454749104
-58.1290322580645
125.366271281362
-58.1290322580645
127.6565938620072
-58.1290322580645
123.5275616039427
-58.1290322580645
124.6888519265233
-58.1290322580645
124.6888519265233
-58.1290322580645
123.0759487007168
-58.1290322580645
130.6565938620072
-58.1290322580645
131.1404648297491
-58.1290322580645
14 rows selected.
5.2.4 ADD_MONTHS(data, meses)
Adiciona o número de meses especificados à data.
SQL> SELECT HIREDATE, ADD_MONTHS(HIREDATE,3), ADD_MONTHS(HIREDATE,-3)
2 FROM EMP
3 WHERE DEPTNO=20
HIREDATE
--------13-JUN-83
31-OCT-83
05-MAR-84
04-JUN-84
05-DEC-83
ADD_MONTHS(HIREDATE,3)
---------------------13-SEP-83
31-JAN-84
05-JUN-84
04-SEP-84
05-MAR-84
ADD_MONTHS(HIREDATE,-3)
----------------------13-MAR-83
31-JUL-83
05-DEC-83
04-MAR-84
05-SEP-83
5.2.5 NEXT_DAY(data, dia_da_semana)
Data do próximo dia da semana após data.
SQL> SELECT HIREDATE, NEXT_DAY(HIREDATE,'FRIDAY'), NEXT_DAY(HIREDATE,6)
2 FROM EMP
3 WHERE DEPTNO = 10
HIREDATE
--------14-MAY-84
09-JUL-84
21-NOV-83
NEXT_DAY(HIREDATE,'FRIDAY')
--------------------------18-MAY-84
13-JUL-84
25-NOV-83
NEXT_DAY(HIREDATE,6)
-------------------18-MAY-84
13-JUL-84
25-NOV-83
5.2.6 LAST_DAY(data)
Retorna a data do último dia do mês da data especificada.
SQL>
2
3
4
SELECT SYSDATE, LAST_DAY(SYSDATE), HIREDATE,
LAST_DAY(HIREDATE), LAST_DAY('15-FEB-88')
FROM EMP
WHERE DEPTNO = 20
38
SYSDATE
--------25-OCT-94
25-OCT-94
25-OCT-94
25-OCT-94
25-OCT-94
LAST_DAY
(SYSDATE)
---------31-OCT-94
31-OCT-94
31-OCT-94
31-OCT-94
31-OCT-94
HIREDATE
--------13-JUN-83
31-OCT-83
05-MAR-84
04-JUN-84
05-DEC-83
LAST_DAY
(HIREDATE)
---------30-JUN-83
31-OCT-83
31-MAR-84
30-JUN-84
31-DEC-83
LAST_DAY
('15-FEB-88')
------------29-FEB-88
29-FEB-88
29-FEB-88
29-FEB-88
29-FEB-88
5.2.7 ROUND(data)
ROUND(data, 'MONTH')
ROUND(data, 'YEAR')
ROUND(data) retorna a data com a indicação da hora como sendo zero horas (meia noite).
Até o meio dia retorna a mesma data, após o meio dia retorna o dia seguinte. Esta função é
útil quando são comparadas datas com horas diferentes.
ROUND(data, 'MONTH') retorna o primeiro dia do mês da data, se a data estiver na
primeira quinzena, ou retorna o primeiro dia do mês seguinte, se a data estiver na segunda
quinzena.
ROUND(data, 'YEAR') retorna o primeiro dia do ano da data, se a data estiver no primeiro
semestre, ou retorna o primeiro dia do ano seguinte, se a data estiver no segundo semestre.
SQL> SELECT SYSDATE, ROUND(SYSDATE,'MONTH'), ROUND(SYSDATE,'YEAR')
2 FROM SYS.DUAL
SYSDATE
ROUND(SYSDATE,'MONTH') ROUND(SYSDATE,'YEAR')
--------- ---------------------- --------------------25-OCT-94 01-NOV-94
01-JAN-95
5.2.7 TRUNC(data)
TRUNC(data, 'MONTH')
TRUNC(data, 'YEAR')
TRUNC(data) retorna a data com o tempo indicando zero horas.
TRUNC(data,'MONTH') retorna o primeiro dia do mês da data.
TRUNC(data,'YEAR') retorna o primeiro dia do ano da data.
SQL> SELECT SYSDATE, TRUNC(SYSDATE,'MONTH'), TRUNC(SYSDATE,'YEAR')
2 FROM SYS.DUAL
SYSDATE
TRUNC(SYSDATE,'MONTH') TRUNC(SYSDATE,'YEAR')
--------- ---------------------- --------------------25-OCT-94 01-OCT-94
01-JAN-94
5.3
Funções de conversão
5.3.1 Formatos de Data
Formato
CC ou SCC
YYYY ou SYYYY
YYY, YY, Y
Significado
Século; S prefixa datas Antes de Cristo com "-"
Ano; S prefixa datas Antes de Cristo com "-"
Últimos 3,2,1 dígitos do ano
39
Y,YYY
YEAR ou SYEAR
BC ou AD
B.C. ou A.D.
Q
MM
Month
MON
WW ou W
DDD, DD, D
DAY
DY
J
AM ou PM
A.M. ou P.M.
HH ou HH12
HH24
MI
SS
SSSS
/., etc...
"..."
Ano com vírgula separando os milhares
Ano por extenso. S prefixa datas Antes de Cristo com ""
Indicador de Antes ou Depois de Cristo
Idêntico a BC/AD com pontos
Quarto do ano (trimestre)
Mês
Mês por extenso, com 9 caracteres, espaços à direita
Nome do mês, com 3 caracteres
Semana do ano ou do mês
Dia do ano, do mês, ou da semana
Dia por extenso, com 9 caracteres, espaços à direita
Nome do dia, abreviado com 3 caracteres
Dia Juliano (dias desde 31/12/4713 AC)
Indicador de meridiano
Indicador de meridiano com pontos
Hora do dia no intervalo 1 a 12
Hora do dia no intervalo 0 a 23
Minuto
Segundo
Segundos após à meia noite (0-86399)
Pontuação reproduzida no resultado
Caracteres entre aspas reproduzido no resultado
Notas:
1 - O prefixo 'fm' (fill mode) antes de MONTH ou DAY suprime os espaços à direita
2 - O sufixo 'TH' produz números ordinais ("DDTH" para "4TH")
3 - O sufixo 'SP' produz números por extenso ("DDSP" para "FOUR")
4 - O sufixo 'SPTH' para números ordinais por extenso ("DDSPTH" para "FOURTH")
5 - Os códigos são sensitivos a caracteres maiúsculos e minúsculos
5.3.2 TO_CHAR(número|data,['formato'])
Converte número ou data em caracter no formato especificado.
SQL> SELECT TO_CHAR(SYSDATE,'DAY, DDTH MONTH YYYY')
2 FROM SYS.DUAL
TO_CHAR(SYSDATE,'DAYDDTHMONTHYYYY')
----------------------------------TUESDAY
,25TH OCTOBER
1994
Adicionando-se o prefixo fm (fill mode) para remover os espaços, e utilizando-se letras
maiúsculas e minúsculas temos:
40
SQL> SELECT TO_CHAR(SYSDATE, 'fmDay, ddth Month YYYY')
2 FROM SYS.DUAL
TO_CHAR(SYSDATE,'FMDAY,DDTHMONTHYYYY')
-------------------------------------Tuesday, 25th October 1994
A função TO_CHAR pode ser usada para extrair a hora do dia apenas.
SQL> SELECT TO_CHAR(SYSDATE, 'HH:MI:SS')
2 FROM SYS.DUAL
TO_CHAR(SYSDATE,'HH:MI:SS')
--------------------------08:39:51
A função TO_CHAR pode ser usada para converter números em caracteres aplicando a
formatação desejada.
SQL> SELECT TO_CHAR(SAL,'$9,999')
2 FROM EMP
TO_CHAR(SAL,'$9,999')
--------------------$800
$1,600
$1,250
$2,975
$1,250
$2,850
$2,450
$3,000
$5,000
$1,500
$1,100
$950
$3,000
$1,300
14 rows selected.
5.3.3 Formatos Numéricos
Formato
9
0
$
.
,
MI
PR
EEEE
V
B
Significado
posição numérica (um 9 para cada número)
zeros à esquerda
cifrão à esquerda
ponto decimal na posição especificada
vírgula na posição especificada
sinal de menos à direita
números negativos entre parênteses
notação científica
multiplicar por 10 elevado a n, sendo n o
número de 9 à direita de V
mostrar resultados zero como espaços, não
como 0.
41
Exemplo
999999
099999
$99999
999999.99
999,999
099999MI
999999PR
99.999EEEE
9999V99
B9999.99
1234
001234
$1234
1234.00
1,234
1234(1234)
1.234E+03
123400
1234.00
5.3.4 TO_NUMBER(cadeia_de_caracteres)
Converte a cadeia de caracteres, que deve conter um número, em um valor numérico.
SQL> SELECT EMPNO, ENAME, JOB, SAL
2 FROM EMP
3 WHERE SAL > TO_NUMBER('1500')
EMPNO
----7499
7566
7698
7782
7788
7839
7902
ENAME
---------ALLEN
JONES
BLAKE
CLARK
SCOTT
KING
FORD
JOB
SAL
--------- ------SALESMAN
1600
MANAGER
2975
MANAGER
2850
MANAGER
2450
ANALYST
3000
PRESIDENT
5000
ANALYST
3000
7 rows selected.
5.3.5 TO_DATE('cadeia_de_caracteres','formato')
Retorna a data contida na cadeia de caracteres conforme o formato especificado. Se o
formato for omitido será assumido DD-MON-YY.
SQL> SELECT EMPNO, ENAME, HIREDATE
2 FROM EMP
3 WHERE HIREDATE = TO_DATE('June 4,1984','Month dd,yyyy')
EMPNO
----7844
7876
5.4
ENAME
---------TURNER
ADAMS
HIREDATE
--------04-JUN-84
04-JUN-84
Funções que aceitam qualquer tipo de dado
5.4.1 DECODE(coluna|expressão,pes1,res1,[pes2,res2,...],default)
Funciona como uma estrutura CASO, substituindo as pesquisas pelos resultados. A coluna
ou expressão é comparada com os argumentos de pesquisa, e se houver igualdade o
resultado correspondente é retornado. Se não houver igualdade, o valor default é retornado.
Argumentos:
coluna|expressão
pes1
res1
pes2
res2
default
a coluna ou expressão a ser avaliada
o primeiro valor a ser testado
o valor a ser retornado se coluna ou expressão igual a pes1
o segundo valor a ser testado
o valor a ser retornado se coluna ou expressão igual a pes2
o valor a ser retornado se a coluna ou expressão for diferente
de pes1, pes2,...,pesN.
Notas:
42
-coluna ou expressão podem ser de qualquer tipo de dados.
-pes(quisa) deve ter o mesmo tipo de dados da coluna ou expressão
-O valor retornado é sempre do mesmo tipo do terceiro argumento (resultado1)
No exemplo abaixo o cargo CLERK é transformado em WORKER, o cargo MANAGER é
transformado em BOSS, e os demais cargos são transformados em UNDEFINED.
SQL>
2
3
4
SELECT ENAME, JOB,
DECODE(JOB, 'CLERK','WORKER','MANAGER','BOSS','UNDEFINED')
DECODED_JOB
FROM EMP
ENAME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
JOB
--------CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
DECODED_JOB
----------WORKER
UNDEFINED
UNDEFINED
BOSS
UNDEFINED
BOSS
BOSS
UNDEFINED
UNDEFINED
UNDEFINED
WORKER
WORKER
UNDEFINED
WORKER
Para mostrar as percentagens de bônus relativas às faixas salariais:
SQL> SELECT GRADE,
2 DECODE(GRADE,'1','15%','2','10%','3','8%','5%') BONUS
3 FROM SALGRADE
GRADE
----1
2
3
4
5
BONUS
----15%
10%
8%
5%
5%
O exemplo abaixo causa um erro porque ename (caracter) tem tipo diferente do terceiro
argumento sal (numérico).
SQL> SELECT * FROM EMP
2 ORDER BY DECODE(2,1,SAL,2,ENAME,SAL)
*
ERROR: ORA-01722: invalid number
no rows selected
Para fazer um aumento salarial diferenciado por cargo:
SQL>
2
3
4
SELECT JOB, SAL,
DECODE(JOB,'ANALYST',SAL*1.1, 'CLERK',SAL*1.15,
'MANAGER',SAL*0.95, SAL) SALARIO_DECODIFICADO
FROM EMP
43
JOB
SAL SALARIO_DECODIFICADO
--------- ------- -------------------CLERK
800
920
SALESMAN
1600
1600
SALESMAN
1250
1250
MANAGER
2975
2826.25
SALESMAN
1250
1250
MANAGER
2850
2707.5
MANAGER
2450
2327.5
ANALYST
3000
3300
PRESIDENT
5000
5000
SALESMAN
1500
1500
CLERK
1100
1265
CLERK
950
1092.5
ANALYST
3000
3300
CLERK
1300
1495
14 rows selected.
5.4.2 NVL(coluna|valor, valor)
Converte valores nulos do primeiro argumento no valor especificado no segundo argumento.
SQL> SELECT SAL*12+NVL(COMM,0), NVL(COMM,1000), SAL*12+NVL(COMM,1000)
2 FROM EMP
3 WHERE DEPTNO = 10
SAL*12+NVL(COMM,0)
NVL(COMM,1000) SAL*12+NVL(COMM,1000)
------------------ ----------------- --------------------29400
1000
30400
60000
1000
61000
15600
1000
16600
5.4.3 GREATEST(coluna|valor, coluna|valor,...)
Retorna o maior dos valores de uma lista de valores.
SQL> SELECT GREATEST(1000,2000), GREATEST(SAL,COMM)
2 FROM EMP
3 WHERE DEPTNO = 30
GREATEST(1000,2000) GREATEST(SAL,COMM)
------------------- -----------------2000
1600
2000
1250
2000
1400
2000
2000
1500
2000
6 rows selected.
5.4.4 LEAST(coluna|valor, coluna|valor,...)
Retorna o menor dos valores de uma lista de valores.
SQL> SELECT LEAST(1000,2000), LEAST(SAL,COMM)
2 FROM EMP
3 WHERE DEPTNO = 30
LEAST(1000,2000)
LEAST(SAL,COMM)
----------------- -----------------
44
1000
1000
1000
1000
1000
1000
300
500
1250
0
5.4.5 VSIZE(coluna|valor)
Retorna o número de bytes utilizado internamente pelo Oracle para representar o valor.
SQL>
2
3
4
SELECT DEPTNO, VSIZE(DEPTNO), VSIZE(HIREDATE),
VSIZE(SAL), VSIZE(ENAME)
FROM EMP
WHERE DEPTNO = 10
DEPTNO
VSIZE(DEPTNO) VSIZE(HIREDATE)
VSIZE(SAL)
VSIZE(ENAME)
------- --------------- --------------- --------------- --------------10
2
7
3
5
10
2
7
2
4
10
2
7
2
6
5.4.5 Funções aninhadas revisitadas
SQL> SELECT ENAME, NVL(TO_CHAR(MGR),'NAO GERENCIAVEL')
2 FROM EMP
3 WHERE MGR IS NULL
ENAME
NVL(TO_CHAR(MGR),'NAOGERENCIAVEL')
---------- ---------------------------------KING
NAO GERENCIAVEL
SQL>
2
3
4
SELECT SYSDATE,
TO_CHAR(NEXT_DAY(ADD_MONTHS(SYSDATE,2),'FRIDAY'),
'Day dd Month YYYY')
FROM SYS.DUAL
SYSDATE
TO_CHAR(NEXT_DAY(ADD_MONTHS(SYSDATE,2),'FRIDAY'),'DAYDDMONTHYYYY')
--------- -------------------------------------------------------25-OCT-94 Friday
30 December 1994
5.5
Exercícios
a) Mostrar o nome e a data de admissão dos empregados do departamento 20. Utilize o aliás
DATA_DE_ADMISSAO para o nome da coluna.
ENAME
---------SMITH
JONES
SCOTT
ADAMS
FORD
DATA_DE_ADMISSAO
--------------------------June, Thirteenth 1983
October, Thirty-First 1983
March, Fifth 1984
June, Fourth 1984
December, Fifth 1983
b) Exibir o nome de cada empregado, juntamente com a data de admissão e a data de revisão
do salário. Assuma a data de revisão um ano após a data de admissão. Classificar a saída em
ordem ascendente da data de revisão do salário.
ENAME
HIREDATE
REVISAO
45
---------SMITH
ALLEN
JONES
MILLER
MARTIN
FORD
SCOTT
WARD
CLARK
TURNER
ADAMS
BLAKE
KING
JAMES
--------13-JUN-83
15-AUG-83
31-OCT-83
21-NOV-83
05-DEC-83
05-DEC-83
05-MAR-84
26-MAR-84
14-MAY-84
04-JUN-84
04-JUN-84
11-JUN-84
09-JUL-84
23-JUL-84
--------13-JUN-84
15-AUG-84
31-OCT-84
21-NOV-84
05-DEC-84
05-DEC-84
05-MAR-85
26-MAR-85
14-MAY-85
04-JUN-85
04-JUN-85
11-JUN-85
09-JUL-85
23-JUL-85
c) Imprimir uma lista de empregados mostrando o salário, se este for superior a 1500. Se o
salário for inferior a 1500 deve ser exibido ABAIXO DE 1500, e se for igual a 1500 deve ser
exibido NO LIMITE.
ENAME
---------ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD
SALARIO
-------------ABAIXO DE 1500
1600
2850
2450
3000
ABAIXO DE 1500
2975
5000
ABAIXO DE 1500
ABAIXO DE 1500
3000
ABAIXO DE 1500
NO LIMITE
ABAIXO DE 1500
d) Escreva uma consulta que retorne o dia da semana para qualquer data entrada no formato
DD.MM.YY.
DIA
-------SATURDAY
e) Escreva uma consulta que retorne o tempo que o empregado está trabalhando para a
empresa.
ENAME
TEMPO DE SERVICO
---------- ---------------KING
10 ANOS 3 MESES
f) Dada uma cadeia de caracteres no formato 'nn/nn', verifique se os dois primeiros e os dois
últimos caracteres são numéricos, e se o caracter do meio é uma '/'. Imprima a expressão
'SIM' se for válido, e 'NAO' se não for válido. Experimente as expressões '12/34','01/1a','e
'99\88'.
VALOR VALIDO?
----- ------12/34 SIM
46
g) Empregados admitidos até o dia 15, inclusive, são pagos na última sexta-feira do mês.
Empregados admitidos após o dia 15 são pagos na última sexta-feira do mês seguinte.
Imprima uma relação contendo os nome dos empregados, as datas de admissão, e a primeira
data de pagamento.
ENAME
---------SMITH
ALLEN
JONES
MILLER
MARTIN
FORD
SCOTT
WARD
CLARK
TURNER
ADAMS
BLAKE
KING
JAMES
HIREDATE
--------13-JUN-83
15-AUG-83
31-OCT-83
21-NOV-83
05-DEC-83
05-DEC-83
05-MAR-84
26-MAR-84
14-MAY-84
04-JUN-84
04-JUN-84
11-JUN-84
09-JUL-84
23-JUL-84
DIA_DO_PAGAMENTO
---------------24-JUN-83
26-AUG-83
25-NOV-83
30-DEC-83
30-DEC-83
30-DEC-83
30-MAR-84
27-APR-84
25-MAY-84
29-JUN-84
29-JUN-84
29-JUN-84
27-JUL-84
31-AUG-84
14 rows selected.
47
5.6
Respostas dos exercícios
a)SQL> SELECT ENAME,
2 TO_CHAR(HIREDATE,'fmMonth, Ddspth YYYY') DATA_DE_ADMISSAO
3 FROM EMP
4 WHERE DEPTNO = 20
b)SQL> SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE,12) REVISAO
2 FROM EMP
3 ORDER BY ADD_MONTHS(HIREDATE,12)
c)SQL> SELECT ENAME,
2 DECODE(SIGN(1500-SAL),1,'ABAIXO DE 1500',0,'NO LIMITE',SAL)
3 SALARIO
4 FROM EMP
5 ORDER BY ENAME
d)SQL> SELECT TO_CHAR(TO_DATE('&ANYDATE','DD.MM.YY'),'DAY') DIA
2 FROM SYS.DUAL
Enter value for anydate: 12.11.88
old
1: SELECT TO_CHAR(TO_DATE('&ANYDATE','DD.MM.YY'),'DAY') DIA
new
1: SELECT TO_CHAR(TO_DATE('12.11.88','DD.MM.YY'),'DAY') DIA
e)SQL> DEFINE TIME = MONTHS_BETWEEN(SYSDATE,HIREDATE)
SQL> SELECT ENAME, FLOOR(&TIME/12)||' ANOS '||
2 FLOOR(MOD(&TIME,12))||' MESES ' "TEMPO DE SERVICO"
3 FROM EMP
4 WHERE ENAME = UPPER('&NOME_DO_EMPREGADO')
old
1: SELECT ENAME, FLOOR(&TIME/12)||' ANOS '||
new
1: SELECT ENAME, FLOOR(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)||'
ANOS '||
old
2: FLOOR(MOD(&TIME,12))||' MESES ' "TEMPO DE SERVICO"
new
2: FLOOR(MOD(MONTHS_BETWEEN(SYSDATE,HIREDATE),12))||' MESES '
"TEMPO DE SERVICO"
Enter value for nome_do_empregado: king
old
4: WHERE ENAME = UPPER('&NOME_DO_EMPREGADO')
new
4: WHERE ENAME = UPPER('king')
f)SQL> SELECT '12/34' VALOR,
2 DECODE(TRANSLATE('12/34','1234567890','9999999999'),
3 '99/99','SIM','NAO') "VALIDO?"
4 FROM SYS.DUAL
g)SQL> SELECT ENAME, HIREDATE,
2 DECODE(SIGN(TO_CHAR(HIREDATE,'DD')-15),
3 1,NEXT_DAY(LAST_DAY(ADD_MONTHS(HIREDATE,1)),'FRIDAY')-7,
4 NEXT_DAY(LAST_DAY(HIREDATE),'FRIDAY')-7) DIA_DO_PAGAMENTO
5 FROM EMP
6 ORDER BY HIREDATE
ou
SQL>
2
3
4
5
SELECT ENAME, HIREDATE,
NEXT_DAY(LAST_DAY(ROUND(HIREDATE,'MONTH'))-7,'FRIDAY')
DIA_DO_PAGAMENTO
FROM EMP
ORDER BY HIREDATE
48
Capitulo 6: Funções de Grupo
6.1
Objetivos deste capítulo
Este capítulo explica como informações sumarizadas (média, máximo, mínimo,...) podem
ser obtidas para grupo de linhas.
As funções vistas anteriormente operavam sobre cada linha, enquanto que as funções
mostradas neste capítulo operam sobre um grupo de linhas.
6.2
Funções disponíveis
Função
AVG
([DISTINCT|ALL]n)
COUNT
([DISTINCT|ALL]exp*)
MAX
([DISTINCT|ALL]exp)
MIN
([DISTINCT|ALL]exp)
STDDEV
([DISTINCT|ALL]n)
SUM
([DISTINCT|ALL]n)
VARIANCE
([DISTINCT|ALL]n)
Valor retornado
Valor médio de 'n', ignorando os valores nulos.
Número de ocorrências não nulas da expressão. O '*'
obriga a contagem total, incluindo valores duplicados e
nulos.
Valor máximo da expressão.
Valor mínimo da expressão.
Desvio padrão de 'n', ignorando os valores nulos.
Soma dos valores de 'n', ignorando os valores nulos.
Variância de 'n', ignorando os valores nulos.
Quando DISTINCT é especificado, somente os valores não duplicados são considerados pela
função, caso contrário ALL é assumido, considerando todos os valores, inclusive os
duplicados.
Todas as funções de grupo, exceto COUNT(*), ignoram os valores nulos.
Podem ser fornecidas expressões dos tipos caracter, número ou data quando 'exp' estiver
indicado. Quando 'n' estiver indicado, somente valores numéricos são aceitos.
6.3
Usando funções de grupo
Para calcular o salário médio de todos os empregados:
SQL> SELECT AVG(SAL)
2 FROM EMP
AVG(SAL)
----------------2073.214285714286
Note que toda a tabela EMP foi tratada como um único grupo.
49
Uma função de grupo pode ser aplicada sobre um subconjunto das linhas usando a cláusula
WHERE.
Para encontrar o salário mínimo recebido por um CLERK:
SQL> SELECT MIN(SAL)
2 FROM EMP
3 WHERE JOB='CLERK'
MIN(SAL)
----------------800
Para contar o número de empregados que trabalham no departamento 20:
SQL> SELECT COUNT(*)
2 FROM EMP
3 WHERE DEPTNO = 20
COUNT(*)
----------------5
6.4
A cláusula GROUP BY
A cláusula GROUP BY é utilizada para dividir as linhas das tabelas em grupos. Funções de
grupo podem ser utilizadas para retornar informações sumarizadas para cada grupo.
Para calcular o salário médio de cada cargo:
SQL> SELECT JOB, AVG(SAL)
2 FROM EMP
3 GROUP BY JOB
JOB
AVG(SAL)
--------- ----------------ANALYST
3000
CLERK
1037.5
MANAGER
2758.333333333333
PRESIDENT
5000
SALESMAN
1400
6.5
Excluindo linhas dos grupos
A cláusula WHERE pode ser utilizada para excluir linhas, antes que estas sejam grupadas.
Para mostrar o salário médio de cada cargo, excluindo os gerentes:
SQL>
2
3
4
SELECT JOB, AVG(SAL)
FROM EMP
WHERE JOB != 'MANAGER'
GROUP BY JOB
JOB
AVG(SAL)
--------- ----------------ANALYST
3000
CLERK
1037.5
50
PRESIDENT
SALESMAN
6.6
5000
1400
Grupos dentro de grupos
Os grupos podem conter subgrupos, fornecendo resultados para grupos dentro de grupos.
Para listar o salário médio de cada cargo em cada departamento:
SQL> SELECT DEPTNO, JOB, AVG(SAL)
2 FROM EMP
3 GROUP BY DEPTNO, JOB
DEPTNO
----------------10
10
10
20
20
20
30
30
30
JOB
AVG(SAL)
--------- ----------------CLERK
1300
MANAGER
2450
PRESIDENT
5000
ANALYST
3000
CLERK
950
MANAGER
2975
CLERK
950
MANAGER
2850
SALESMAN
1400
9 rows selected.
6.7
Funções de grupo e resultados individuais
A declaração abaixo mostra o salário máximo para cada tipo de cargo. O resultado não é
muito significativo pois não mostra o cargo, mas mostra que não há obrigação de listar as
colunas pelas quais o grupamento é realizado.
SQL> SELECT MAX(SAL)
2 FROM EMP
3 GROUP BY JOB
MAX(SAL)
----------------3000
1300
2975
5000
1600
Também não há necessidade de colocar as colunas a serem grupadas antes das outras:
SQL> SELECT MAX(SAL), JOB
2 FROM EMP
3 GROUP BY JOB
MAX(SAL)
----------------3000
1300
2975
5000
1600
JOB
--------ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN
Lembre-se que quando funções de grupo são incluídas na cláusula SELECT, todas as
colunas que não contém função de grupo devem aparecer na cláusula GROUP BY.
51
O exemplo abaixo mostra o erro causado pela utilização da função de grupo MIN(SAL), sem
haver a cláusula GROUP BY em DEPTNO:
SQL> SELECT DEPTNO, MIN(SAL)
2 FROM EMP
SELECT DEPTNO, MIN(SAL)
*
ERROR at line 1:
ORA-00937: not a single-group group function
Grupando-se por DEPTNO o comando processa corretamente.
SQL> SELECT DEPTNO, MIN(SAL)
2 FROM EMP
3 GROUP BY DEPTNO
DEPTNO
MIN(SAL)
----------------- ----------------10
1300
20
800
30
950
Toda coluna ou expressão da cláusula SELECT que não é uma
função de agregação deve aparecer na cláusula GROUP BY.
6.8
A cláusula HAVING
Somente os grupos que atendem as condições especificadas na cláusula HAVING são
selecionados. A cláusula HAVING opera sobre a cláusula GROUP BY de maneira
semelhante a que a cláusula WHERE opera sobre a cláusula SELECT.
Para mostrar o salário médio dos departamento que possuem mais de três funcionários:
SQL>
2
3
4
SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) > 3
DEPTNO
AVG(SAL)
----------------- ----------------20
2175
30 1566.666666666667
Para mostrar os cargos com salário máximo acima de 3000:
SQL>
2
3
4
SELECT JOB, MAX(SAL)
FROM EMP
HAVING MAX(SAL) >= 3000
GROUP BY JOB
JOB
MAX(SAL)
--------- ----------------ANALYST
3000
PRESIDENT
5000
52
Embora a cláusula HAVING possa ser escrita antes da cláusula GROUP BY, a leitura tornase mais fácil quando a cláusula HAVING aparece após a cláusula GROUP BY. Os grupos
são formados e as funções de grupo são calculadas antes da cláusula HAVING ser aplicada.
A cláusula WHERE não pode ser usada para restringir os grupos a serem retornados. A
cláusula WHERE na declaração abaixa é ilegal:
SQL>
2
3
4
SELECT DEPTNO, AVG(SAL)
FROM EMP
WHERE AVG(SAL) > 2000
GROUP BY DEPTNO
WHERE AVG(SAL) > 2000
*
ERROR at line 3:
ORA-00934: group function is not allowed here
A cláusula HAVING deve ser usada neste caso.
SQL>
2
3
4
SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL) > 2000
DEPTNO
AVG(SAL)
----------------- ----------------10 2916.666666666667
20
2175
A cláusula WHERE é utilizada para aplicar restrições sobre linhas
individualmente, enquanto a cláusula HAVING é utilizada para
aplicar restrições sobre grupos de linhas.
As cláusulas WHERE e HAVING podem ser utilizadas em conjunto.
Para calcular o salário médio de todos os cargos, exceto o cargo de gerente:
SQL>
2
3
4
5
SELECT JOB, AVG(SAL)
FROM EMP
WHERE JOB <> 'MANAGER'
GROUP BY JOB
HAVING AVG(SAL) > 2000
JOB
AVG(SAL)
--------- ----------------ANALYST
3000
CLERK
1037.5
PRESIDENT
5000
6.9
Ordem das cláusulas
SELECT
FROM
WHERE
GROUP BY
lista_de_colunas
lista_de_tabelas
condições_de_linha
lista_de_colunas
53
HAVING
condições_de_grupo
ORDER BY lista_de_colunas
São avaliados:
WHERE
para ficar somente as linhas que atendem a cláusula
GROUP BY para montagem dos grupos
HAVING
para ficar somente os grupos que atendem a cláusula
6.10
Exercícios
a) Encontrar o menor salário entre todos os empregados.
MENOR
----------------800
b) Encontrar o salário maior, menor, e médio, entre todos os empregados.
MAX(SAL)
MIN(SAL)
AVG(SAL)
----------------- ----------------- ----------------5000
800 2073.214285714286
c) Listar o maior e o menor salário por cada tipo de cargo.
CARGO
MAIOR
MENOR
--------- ----------------- ----------------ANALYST
3000
3000
CLERK
1300
800
MANAGER
2975
2450
PRESIDENT
5000
5000
SALESMAN
1600
1250
d) Calcular o número de gerentes sem listar seus nomes.
GERENTES
----------------3
e) Encontrar o salário médio e a remuneração total média de cada tipo de cargo, lembrandose que os vendedores recebem comissão.
JOB
SALARIO_MEDIO REMUNERACAO_MEDIA
--------- ----------------- ----------------ANALYST
3000
36000
CLERK
1037.5
12450
MANAGER
2758.333333333333
33100
PRESIDENT
5000
60000
SALESMAN
1400
17237
f) Encontrar a diferença entre o maior e o menor salários.
DIFERENCA
----------------4200
g) Listar todos os departamentos que possuem mais de três empregados.
54
DEPTNO
COUNT(*)
----------------- ----------------20
5
30
6
h) Verificar se não há duplicidade no número dos empregados
no rows selected
i) Listar os empregados que recebem o menor salário em cada departamento, relacionando os
seus gerentes. Excluir os grupos onde o menor salário é inferior a 1000. Ordenar os
resultados pelo salário
MGR
MIN(SAL)
----------------- ----------------7788
1100
7782
1300
7839
2450
7566
3000
5000
55
6.11
Respostas dos exercícios
a)
SQL> SELECT MIN(SAL) MENOR
2 FROM EMP
b)
SQL> SELECT MAX(SAL), MIN(SAL), AVG(SAL)
2 FROM EMP
c)
SQL> SELECT JOB CARGO, MAX(SAL) MAIOR, MIN(SAL) MENOR
2 FROM EMP
3 GROUP BY JOB
d)
SQL> SELECT COUNT(*) GERENTES
2 FROM EMP
3 WHERE JOB = 'MANAGER'
e)
SQL>
2
3
4
SELECT JOB, AVG(SAL) SALARIO_MEDIO,
AVG(SAL*12+NVL(COMM,0)) REMUNERACAO_MEDIA
FROM EMP
GROUP BY JOB
f)
SQL> SELECT MAX(SAL)-MIN(SAL) DIFERENCA
2 FROM EMP
g)
SQL>
2
3
4
SELECT DEPTNO, COUNT(*)
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) > 3
h)
SQL>
2
3
4
SELECT EMPNO
FROM EMP
GROUP BY EMPNO
HAVING COUNT(*) > 1
i)
SQL>
2
3
4
5
SELECT MGR, MIN(SAL)
FROM EMP
GROUP BY MGR
HAVING MIN(SAL) >= 1000
ORDER BY MIN(SAL)
56
Capítulo 7: Extraindo dados de mais de uma tabela
7.1
Objetivos deste capítulo
Mostrar como obter informações de mais de uma tabela através das junções.
Linhas de uma tabela podem ser juntadas com linhas de outra tabela, de acordo com valores
comuns existentes em colunas correspondentes. Os dois principais tipos de junção são:
1-Junções equivalentes
(equi-join)
2-Junções não equivalentes (non-equi-join)
7.2
Junções equivalentes
Para levantar, manualmente, o nome do departamento em que um funcionário trabalha,
primeiro seria levantado na tabela EMP o número do departamento do empregado, e, em
seguida, seria levantado na tabela DEPT o nome correspondente ao número do
departamento. Este relacionamento entre as duas tabelas é chamado de junção equivalente
(equi-join), uma vez o número do departamento nas duas tabelas é o mesmo.
A condição de junção é especificada na cláusula WHERE usando o operador '='.
SQL> SELECT ENAME, JOB, DNAME
2 FROM EMP, DEPT
3 WHERE EMP.DEPTNO = DEPT.DEPTNO
ENAME
---------CLARK
KING
MILLER
SMITH
ADAMS
FORD
SCOTT
JONES
ALLEN
BLAKE
MARTIN
JAMES
TURNER
WARD
JOB
--------MANAGER
PRESIDENT
CLERK
CLERK
CLERK
ANALYST
ANALYST
MANAGER
SALESMAN
MANAGER
SALESMAN
CLERK
SALESMAN
SALESMAN
DNAME
-------------ACCOUNTING
ACCOUNTING
ACCOUNTING
RESEARCH
RESEARCH
RESEARCH
RESEARCH
RESEARCH
SALES
SALES
SALES
SALES
SALES
SALES
As linhas da tabela EMP foram combinadas com as linhas da tabela DEPT para obtenção
dos números dos departamentos.
O nome da tabela é colocado antes do nome da coluna, para diferenciar colunas com mesmo
nome em tabelas diferentes.
Todas as colunas podem ser prefixadas com o nome da tabela, porém este procedimento só é
obrigatório quando existe ambigüidade.
SQL> SELECT DEPT.DEPTNO, ENAME, JOB, DNAME
2 FROM EMP, DEPT
3 WHERE EMP.DEPTNO = DEPT.DEPTNO
57
4 ORDER BY DEPT.DEPTNO
DEPTNO ENAME
JOB
------- ---------- --------10 CLARK
MANAGER
10 KING
PRESIDENT
10 MILLER
CLERK
20 SMITH
CLERK
20 ADAMS
CLERK
20 FORD
ANALYST
20 SCOTT
ANALYST
20 JONES
MANAGER
30 ALLEN
SALESMAN
30 BLAKE
MANAGER
30 MARTIN
SALESMAN
30 JAMES
CLERK
30 TURNER
SALESMAN
30 WARD
SALESMAN
DNAME
-------------ACCOUNTING
ACCOUNTING
ACCOUNTING
RESEARCH
RESEARCH
RESEARCH
RESEARCH
RESEARCH
SALES
SALES
SALES
SALES
SALES
SALES
14 rows selected.
Aliás para nomes de tabelas podem ser usado de forma semelhante à que foi usada para aliás
de nomes de colunas. O nome do aliás é escrito após o nome da tabela. No exemplo abaixo
foi utilizado o aliás 'E' para a tabela EMP, e o aliás 'D' para a tabela DEPT.
SQL>
2
3
4
SELECT E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORDER BY D.DEPTNO
ENAME
DEPTNO DNAME
---------- ------- -------------CLARK
10 ACCOUNTING
KING
10 ACCOUNTING
MILLER
10 ACCOUNTING
SMITH
20 RESEARCH
ADAMS
20 RESEARCH
FORD
20 RESEARCH
SCOTT
20 RESEARCH
JONES
20 RESEARCH
ALLEN
30 SALES
BLAKE
30 SALES
MARTIN
30 SALES
JAMES
30 SALES
TURNER
30 SALES
WARD
30 SALES
14 rows selected.
7.3
Produto
Quando a condição de junção é inválida ou omitida completamente, o resultado é um
Produto Cartesiano, e todas as combinações de linha serão retornadas.
O Produto tende a gerar um grande número de linhas, e o resultado é raramente útil.
7.4
Junções não equivalentes
O relacionamento entre as tabelas EMP e SALGRADE formam uma junção não equivalente,
uma vez que não existe nenhuma coluna comum às duas tabelas. No exemplo, a função
BETWEEN ... AND ... faz o relacionamento entre as duas tabelas.
58
SQL> SELECT E.ENAME, E.SAL, S.GRADE
2 FROM EMP E, SALGRADE S
3 WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
ENAME
SAL
GRADE
---------- ------- ------SMITH
800
1
ADAMS
1100
1
JAMES
950
1
WARD
1250
2
MARTIN
1250
2
MILLER
1300
2
ALLEN
1600
3
TURNER
1500
3
JONES
2975
4
BLAKE
2850
4
CLARK
2450
4
SCOTT
3000
4
FORD
3000
4
KING
5000
5
7.5
Regra para junção de tabelas
O número mínimo de condições de junção é igual ao número de
tabelas menos um.
7.6
Sumário da Sintaxe
SELECT
FROM
WHERE
AND
OR
GROUP BY
HAVING
ORDER BY
7.7
[DISTINCT] {[tabela].*|expressão [aliás],...}
tabela [alias],...
[condição de junção]...
[condição de linha]...
[outra condição de linha]
{expressão|coluna}
{condição de grupo}
{expressão|coluna} [ASC|DESC]
Exercícios
a) Mostrar o nome dos empregados e dos departamentos onde trabalham, ordenados pelo
nome do departamento.
ENAME
---------CLARK
KING
MILLER
SMITH
ADAMS
FORD
SCOTT
JONES
ALLEN
BLAKE
MARTIN
JAMES
TURNER
DNAME
-------------ACCOUNTING
ACCOUNTING
ACCOUNTING
RESEARCH
RESEARCH
RESEARCH
RESEARCH
RESEARCH
SALES
SALES
SALES
SALES
SALES
59
WARD
SALES
14 rows selected.
b) Mostrar os nomes dos empregados, juntamente com os números e nomes dos
departamentos onde trabalham
ENAME
DEPTNO DNAME
---------- ------- -------------CLARK
10 ACCOUNTING
KING
10 ACCOUNTING
MILLER
10 ACCOUNTING
SMITH
20 RESEARCH
ADAMS
20 RESEARCH
FORD
20 RESEARCH
SCOTT
20 RESEARCH
JONES
20 RESEARCH
ALLEN
30 SALES
BLAKE
30 SALES
MARTIN
30 SALES
JAMES
30 SALES
TURNER
30 SALES
WARD
30 SALES
14 rows selected.
c) Listar o nome, o local de trabalho e o departamento dos empregados com salário superior
a 1500.
ENAME
---------CLARK
KING
JONES
SCOTT
FORD
ALLEN
BLAKE
LOCAL
------------NEW YORK
NEW YORK
DALLAS
DALLAS
DALLAS
CHICAGO
CHICAGO
DNAME
-------------ACCOUNTING
ACCOUNTING
RESEARCH
RESEARCH
RESEARCH
SALES
SALES
7 rows selected.
d) Listar as faixas salariais dos empregados.
ENAME
---------SMITH
ADAMS
JAMES
WARD
MARTIN
MILLER
ALLEN
TURNER
JONES
BLAKE
CLARK
SCOTT
FORD
KING
JOB
SAL
GRADE
--------- ------- ------CLERK
800
1
CLERK
1100
1
CLERK
950
1
SALESMAN
1250
2
SALESMAN
1250
2
CLERK
1300
2
SALESMAN
1600
3
SALESMAN
1500
3
MANAGER
2975
4
MANAGER
2850
4
MANAGER
2450
4
ANALYST
3000
4
ANALYST
3000
4
PRESIDENT
5000
5
14 rows selected.
60
e) Mostrar somente os empregados na faixa 3.
ENAME
---------ALLEN
TURNER
JOB
SAL
GRADE
--------- ------- ------SALESMAN
1600
3
SALESMAN
1500
3
f) Listar todos os empregados em Dallas.
ENAME
SAL LOCAL
---------- ------- ------------SMITH
800 DALLAS
ADAMS
1100 DALLAS
FORD
3000 DALLAS
SCOTT
3000 DALLAS
JONES
2975 DALLAS
g) Listar os nomes dos empregados, o cargo, o salário, a faixa salarial, e o nome do
departamento para todos na companhia, exceto os Clerks. Ordenar pelo salário, com os
maiores primeiro.
ENAME
---------KING
FORD
SCOTT
JONES
BLAKE
CLARK
ALLEN
TURNER
MARTIN
WARD
JOB
SAL
GRADE DNAME
--------- ------- ------- -------------PRESIDENT
5000
5 ACCOUNTING
ANALYST
3000
4 RESEARCH
ANALYST
3000
4 RESEARCH
MANAGER
2975
4 RESEARCH
MANAGER
2850
4 SALES
MANAGER
2450
4 ACCOUNTING
SALESMAN
1600
3 SALES
SALESMAN
1500
3 SALES
SALESMAN
1250
2 SALES
SALESMAN
1250
2 SALES
10 rows selected.
h) Listar os seguintes detalhes para os empregados que ganham 36.000 por ano ou que são
Clerks.
ENAME
---------MILLER
SMITH
ADAMS
JAMES
JOB
SALARIO_ANUAL DEPTNO DNAME
GRADE
--------- ------------- ------- -------------- ------CLERK
15600
10 ACCOUNTING
2
CLERK
9600
20 RESEARCH
1
CLERK
13200
20 RESEARCH
1
CLERK
11400
30 SALES
1
61
7.8
Solução dos exercícios
a)
SQL> SELECT ENAME, DNAME
2 FROM EMP, DEPT
3 WHERE EMP.DEPTNO = DEPT.DEPTNO
b)
SQL> SELECT ENAME, E.DEPTNO, DNAME
2 FROM EMP E, DEPT D
3 WHERE E.DEPTNO = D.DEPTNO
c)
SQL>
2
3
4
SELECT ENAME, LOC LOCAL, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND SAL > 1500
d)
SQL> SELECT ENAME, JOB, SAL, GRADE
2 FROM EMP, SALGRADE
3 WHERE SAL BETWEEN LOSAL AND HISAL
e)
SQL> SELECT ENAME, JOB, SAL, GRADE
2 FROM EMP, SALGRADE
3 WHERE SAL BETWEEN LOSAL AND HISAL AND GRADE = 3
f)
SQL>
2
3
4
SELECT ENAME, SAL, LOC LOCAL
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND LOC='DALLAS'
g)
SQL>
2
3
4
5
6
SELECT ENAME, JOB, SAL, GRADE, DNAME
FROM EMP, SALGRADE, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND SAL BETWEEN LOSAL AND HISAL
AND JOB != 'CLERK'
ORDER BY SAL DESC
h)
SQL>
2
3
4
5
6
7
SELECT ENAME, JOB, SAL*12+NVL(COMM,0) SALARIO_ANUAL,
D.DEPTNO, DNAME, GRADE
FROM EMP E, SALGRADE, DEPT D
WHERE E.DEPTNO = D.DEPTNO AND SAL BETWEEN LOSAL AND HISAL
AND (SAL*12+NVL(COMM,0)=3600
OR JOB = 'CLERK')
ORDER BY E.JOB
62
Capítulo 8: Outros métodos de junção
8.1
Objetivos deste capítulo
Mostrar métodos alternativos para construção de junções.
8.2
Junções externas
Se uma linha não satisfaz a condição de junção, então a linha não é mostrada no resultado da
consulta. De fato, quando fazemos a junção equivalente (equi-join) das tabelas EMP e
DEPT, o departamento 40 não aparece, uma vez que não há nenhum empregado lotado neste
departamento.
As linhas excluídas podem ser retornadas se um operador de junção externa (outer join) for
utilizado na condição de junção. O operador é um sinal de '+' colocado entre parênteses, que
deve ser ficar ao lado da tabela onde as informações que não seriam incluídas devem
aparecer.
No exemplo o operador está colocado ao lado da tabela DEPT, forçando listar os dados dos
departamentos mesmo que não haja correspondência em EMP.
SQL>
2
3
4
SELECT E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO (+) = D.DEPTNO
AND D.DEPTNO IN (30,40)
ENAME
DEPTNO DNAME
---------- ------- -------------ALLEN
30 SALES
BLAKE
30 SALES
MARTIN
30 SALES
JAMES
30 SALES
TURNER
30 SALES
WARD
30 SALES
40 OPERATIONS
7 rows selected.
O operador de junção externa (outer join) só pode aparecer em um dos lados da expressão,
causando uma junção externa esquerda (left outer join), ou uma junção externa direita (right
outer join). A junção externa completa (outer join) não é implementada diretamente pelo
Oracle.
8.3
Junção de uma tabela com ela mesmo
É possível utilizar rótulos nas tabelas (aliases) para fazer a junção de uma tabela com ela
mesmo, como se fossem duas tabelas distintas.
No exemplo abaixo são mostrados todos os funcionários que recebem menos que seus
gerentes.
SQL> SELECT E.ENAME EMP_NOME, E.SAL EMP_SAL,
2 G.ENAME GER_NOME, G.SAL GER_SAL
3 FROM EMP E, EMP G
63
4 WHERE E.MGR =
EMP_NOME
EMP_SAL
---------- ------ALLEN
1600
WARD
1250
JAMES
950
TURNER
1500
MARTIN
1250
MILLER
1300
ADAMS
1100
JONES
2975
CLARK
2450
BLAKE
2850
SMITH
800
G.EMPNO AND E.SAL < G.SAL
GER_NOME
GER_SAL
---------- ------BLAKE
2850
BLAKE
2850
BLAKE
2850
BLAKE
2850
BLAKE
2850
CLARK
2450
SCOTT
3000
KING
5000
KING
5000
KING
5000
FORD
3000
11 rows selected.
Note que EMP aparece na cláusula FROM duas vezes, uma com o aliás 'E', para os
empregados, e outra com o aliás 'G', para os gerentes.
8.4
Junções verticais
Os operadores UNION, INTERSECT e MINUS são úteis para construir consultas que se
referem a tabelas diferentes. Estes operadores combinam os resultados de dois ou mais
SELECTs em um único resultado.
8.4.1 União
O operador UNION retorna todas as linhas distintas das consultas unidas por este operador.
No exemplo são listados todos os cargos dos departamentos 10 e 30 sem repetição.
SQL>
2
3
4
5
6
7
SELECT JOB
FROM EMP
WHERE DEPTNO = 10
UNION
SELECT JOB
FROM EMP
WHERE DEPTNO = 30
JOB
--------CLERK
MANAGER
PRESIDENT
SALESMAN
8.4.2 Interseção
O operador INTERSECT retorna apenas as linhas comuns às duas consultas. Para listar os
cargos existentes tanto no departamento 10 quanto no departamento 30:
SQL>
2
3
4
5
6
7
SELECT JOB
FROM EMP
WHERE DEPTNO = 10
INTERSECT
SELECT JOB
FROM EMP
WHERE DEPTNO = 30
64
JOB
--------CLERK
MANAGER
8.4.3 Subtração
O operador MINUS retorna as linhas presentes na primeira consulta mas não presentes na
segunda consulta. Para listar os cargos existentes no departamento 10 mas não existentes no
departamento 30:
SQL>
2
3
4
5
6
7
SELECT JOB
FROM EMP
WHERE DEPTNO = 10
MINUS
SELECT JOB
FROM EMP
WHERE DEPTNO = 30
JOB
--------PRESIDENT
8.4.4 Combinação de operadores
É possível a construção de consultas com operadores UNION, INTERSECT e MINUS
combinados. Quando isto é feito a execução é efetuada de cima para baixo, porém podem
ser utilizados parênteses para alterar esta ordem.
8.4.5 A cláusula ORDER BY
Como as colunas das tabelas podem ter nomes diferentes quando usamos UNION,
INTERSECT e MINUS, o nome da coluna não pode ser usado na cláusula ORDER BY
quando estes operadores são utilizados. Ao invés do nome deve ser usado o número da
coluna, como mostrado abaixo:
SQL>
2
3
4
5
6
SELECT EMPNO, ENAME, SAL
FROM EMP
UNION
SELECT ID, NAME, SALARY
FROM EMP HISTORY
ORDER BY 2
A cláusula ORDER BY deve ser sempre a última.
8.4.6 Regras para utilizar junções verticais

As declarações SELECT devem ter todas o mesmo número de colunas.

Os tipos de dados das colunas correspondentes devem ser idênticos.

Linhas duplicadas são automaticamente eliminadas (DISTINCT não pode ser usado).

Os nomes das colunas da primeira consulta são os que aparecem no resultado.
65
8.5


A cláusula ORDER BY deve ser sempre a última.
A cláusula ORDER BY só pode conter os números das colunas, não os nomes.

Junções verticais podem ser usadas em sub-consultas.

Declarações SELECT são executadas de cima para baixo.

Múltiplas junções verticais podem ser utilizadas, com parênteses, se necessário, para
alterar a ordem de execução.
Exercícios
a) Listar os departamentos que não possuem empregados.
DEPTNO DNAME
------- -------------40 OPERATIONS
b) Listar os números e os nomes dos empregados juntamente com os números e os nomes de
seus gerentes.
EMPNO
------7788
7902
7499
7521
7900
7844
7654
7934
7876
7566
7782
7698
7369
ENAME
GER_NUM GER_NOME
---------- ------- ---------SCOTT
7566 JONES
FORD
7566 JONES
ALLEN
7698 BLAKE
WARD
7698 BLAKE
JAMES
7698 BLAKE
TURNER
7698 BLAKE
MARTIN
7698 BLAKE
MILLER
7782 CLARK
ADAMS
7788 SCOTT
JONES
7839 KING
CLARK
7839 KING
BLAKE
7839 KING
SMITH
7902 FORD
13 rows selected.
c) Alterar a solução do exercício b para mostrar KING que não possui gerente.
EMPNO
------7788
7902
7499
7521
7900
7844
7654
7934
7876
7566
7782
7698
7369
7839
ENAME
GER_NUM GER_NOME
---------- ------- ---------SCOTT
7566 JONES
FORD
7566 JONES
ALLEN
7698 BLAKE
WARD
7698 BLAKE
JAMES
7698 BLAKE
TURNER
7698 BLAKE
MARTIN
7698 BLAKE
MILLER
7782 CLARK
ADAMS
7788 SCOTT
JONES
7839 KING
CLARK
7839 KING
BLAKE
7839 KING
SMITH
7902 FORD
KING
66
14 rows selected.
d) Mostrar os cargos que foram preenchidos no primeiro semestre de 1983 e no mesmo
período em 1984.
JOB
--------CLERK
e) Listar todos os empregados admitidos antes de seus gerentes.
EMPREGADO
---------ALLEN
WARD
TURNER
MARTIN
MILLER
JONES
CLARK
BLAKE
SMITH
HIREDATE
--------15-AUG-83
26-MAR-84
04-JUN-84
05-DEC-83
21-NOV-83
31-OCT-83
14-MAY-84
11-JUN-84
13-JUN-83
GERENTE
---------BLAKE
BLAKE
BLAKE
BLAKE
CLARK
KING
KING
KING
FORD
HIREDATE
--------11-JUN-84
11-JUN-84
11-JUN-84
11-JUN-84
14-MAY-84
09-JUL-84
09-JUL-84
09-JUL-84
05-DEC-83
9 rows selected.
f) Encontrar outra maneira de resolver o exercício a.
DEPTNO DNAME
------- -------------40 OPERATIONS
67
8.6
Respostas dos exercícios
a)
SQL>
2
3
4
SELECT D.DEPTNO, DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO (+) = D.DEPTNO
AND E.EMPNO IS NULL
b)
SQL>
2
3
4
SELECT EMPS.EMPNO, EMPS.ENAME,
GERS.EMPNO GER_NUM, GERS.ENAME GER_NOME
FROM EMP EMPS, EMP GERS
WHERE EMPS.MGR = GERS.EMPNO
c)
SQL>
2
3
4
SELECT EMPS.EMPNO, EMPS.ENAME,
GERS.EMPNO GER_NUM, GERS.ENAME GER_NOME
FROM EMP EMPS, EMP GERS
WHERE EMPS.MGR = GERS.EMPNO (+)
d)
SQL>
2
3
4
5
6
7
SELECT JOB
FROM EMP
WHERE HIREDATE BETWEEN '01-JAN-83' AND '30-JUN-83'
INTERSECT
SELECT JOB
FROM EMP
WHERE HIREDATE BETWEEN '01-JAN-84' AND '30-JUN-84'
e)
SQL>
2
3
4
SELECT E.ENAME EMPREGADO, E.HIREDATE,
M.ENAME GERENTE, M.HIREDATE
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO AND E.HIREDATE < M.HIREDATE
f)
SQL>
2
3
4
5
6
SELECT DEPTNO, DNAME
FROM DEPT
MINUS
SELECT EMP.DEPTNO, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
68
Capítulo 9: Consultas aninhadas
9.1
Objetivos deste capítulo
Mostrar consultas declaradas nas cláusulas Where e Having.
9.2
Definição de consultas aninhadas
Uma consulta aninhada, ou subconsulta, é aquela que está contida dentro de uma outra
consulta, e que retorna valores intermediários.
Por exemplo:
SELECT
FROM
WHERE
coluna1, coluna2
tabela
coluna1 = (SELECT coluna FROM tabela WHERE condição)
Consultas aninhadas são muito úteis quando é necessário selecionar linhas de uma tabela sob
uma condição que depende dos dados da própria tabela.
9.3
Consultas internas que retornam apenas um valor
Para encontrar o empregado com o menor salário da empresa são necessárias duas etapas:
a) achar qual o menor salário
SQL> SELECT MIN(SAL)
2 FROM EMP
MIN(SAL)
-------800
b) localizar o empregado que recebe o menor salário
SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL = salário encontrado na etapa anterior
Os dois comandos podem ser combinados em uma consulta aninhada:
SQL> SELECT ENAME, JOB, SAL
2 FROM EMP
3 WHERE SAL = (SELECT MIN(SAL) FROM EMP)
ENAME
JOB
SAL
---------- --------- ------SMITH
CLERK
800
Antes da consulta interna que retorna apenas um valor, podem ser usados os operadores =, <,
>, <=, >=, <>.
69
9.4
Como as consultas aninhadas são executadas
Uma declaração SELECT pode ser considerada como um bloco de consulta. No exemplo
anterior, haviam dois blocos de consulta: um principal e outro interno. O bloco interno é
executado primeiro, produzindo o resultado: 800. Em seguida o bloco principal é executado,
utilizando o valor retornado pelo bloco interno.
Para listar todos os empregados com o mesmo cargo do BLAKE:
SQL> SELECT ENAME, JOB
2 FROM EMP
3 WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'BLAKE')
ENAME
---------JONES
BLAKE
CLARK
JOB
--------MANAGER
MANAGER
MANAGER
O cargo de BLAKE é obtido pela consulta interna e utilizado pela consulta principal.
9.5
Consultas internas que retornam mais de um valor
A consulta abaixo tenta localizar os empregados com o menor salário em cada
departamento.
SQL> SELECT ENAME, SAL, DEPTNO
2 FROM EMP
3 WHERE SAL IN (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO)
ENAME
SAL DEPTNO
---------- ------- ------SMITH
800
20
JAMES
950
30
MILLER
1300
10
Note que a consulta interna tem uma cláusula GROUP BY, e portanto retorna mais de um
valor. Neste caso o operador IN deve ser usado para tratar a lista de valores.
O resultado da consulta interna não indica o departamento, não havendo assim qualquer
correspondência entre o salário retornado e o departamento do funcionário.
Para haver correspondência entre o salário e o departamento a consulta foi escrita
novamente:
SQL>
2
3
4
SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE (SAL,DEPTNO) IN
(SELECT MIN(SAL), DEPTNO FROM EMP GROUP BY DEPTNO)
ENAME
SAL DEPTNO
---------- ------- ------SMITH
800
20
JAMES
950
30
MILLER
1300
10
70
Nesta nova consulta, é retornado um par de colunas que é comparado com um par de colunas
da consulta principal.
Note que as colunas à esquerda da condição de procura estão entre parênteses e são
separadas por vírgulas.
As colunas listadas na consulta interna devem estar na mesma ordem das colunas à esquerda
da condição de procura.
As colunas retornadas pela consulta interna devem ser em mesmo número e do mesmo tipo
de dados das colunas à esquerda da condição de procura.
Se uma consulta interna retornar mais de uma linha e a condição de procura utilizar um
operador de uma linha apenas gera o erro mostrado abaixo:
SQL> SELECT ENAME, SAL, DEPTNO
2 FROM EMP
3 WHERE SAL =
4 (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO)
ERROR:
ORA-01427: single-row subquery returns more than one row
no rows selected
Se a consulta interna não retornar nenhuma linha:
SQL> SELECT ENAME, JOB
2 FROM EMP
3 WHERE JOB =
4 (SELECT JOB FROM EMP WHERE ENAME = 'SMYTHE')
ERROR:
ORA-01426: sigle-row subquery returns no row
no rows selected
9.6
Operadores ANY e ALL
Os operadores ANY e ALL podem ser utilizados quando as consultas internas retornam mais
de uma valor. Estes operadores são usados nas cláusulas WHERE e HAVING em conjunção
com os operadores lógicos (=, <, >, >=, <=, <>).
ANY compara o valor com cada valor retornado pela consulta interna.
Para mostrar os empregados que recebem mais do que o menor salário do departamento 30:
SQL>
2
3
4
5
SELECT ENAME, SAL, JOB, DEPTNO
FROM EMP
WHERE SAL > ANY
(SELECT DISTINCT SAL FROM EMP WHERE DEPTNO = 30)
ORDER BY SAL DESC
ENAME
SAL JOB
DEPTNO
---------- ------- --------- ------KING
5000 PRESIDENT
10
SCOTT
3000 ANALYST
20
FORD
3000 ANALYST
20
JONES
2975 MANAGER
20
BLAKE
2850 MANAGER
30
71
CLARK
ALLEN
TURNER
MILLER
WARD
MARTIN
ADAMS
2450
1600
1500
1300
1250
1250
1100
MANAGER
SALESMAN
SALESMAN
CLERK
SALESMAN
SALESMAN
CLERK
10
30
30
10
30
30
20
12 rows selected.
Quando usamos ANY geralmente usamos DISTINCT para reduzir a lista de valores a serem
comparados.
ALL compara o valor com todos os valores retornados pela consulta interna.
A consulta abaixo encontra os empregados que ganham mais do que todos os empregados do
departamento 30.
SQL>
2
3
4
5
SELECT ENAME, SAL, JOB, DEPTNO
FROM EMP
WHERE SAL > ALL
(SELECT DISTINCT SAL FROM EMP WHERE DEPTNO = 30)
ORDER BY SAL DESC
ENAME
SAL JOB
DEPTNO
---------- ------- --------- ------KING
5000 PRESIDENT
10
SCOTT
3000 ANALYST
20
FORD
3000 ANALYST
20
JONES
2975 MANAGER
20
O operador NOT pode ser usado com IN, ANY ou ALL
9.7
Cláusula HAVING com consultas aninhadas
Para mostrar os departamentos com salário médio superior ao salário médio do
departamento 30, escrevemos:
SQL>
2
3
4
5
SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL) >
(SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 30)
DEPTNO AVG(SAL)
------- -------10 2916.67
20
2175
Para descobrir qual o cargo com maior salário médio:
SQL>
2
3
4
5
SELECT JOB, AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING AVG(SAL) =
(SELECT MAX(AVG(SAL)) FROM EMP GROUP BY JOB)
JOB
AVG(SAL)
--------- -------PRESIDENT
5000
72
9.8
9.9
Ordenação em consultas aninhadas

Não pode existir a cláusula ORDER BY na consulta interna.

A cláusula ORDER BY é sempre a última no comando SELECT.
Limite para o aninhamento

9.10
Não há limite para o aninhamento de SELECT dentro de SELECT.
Consulta interna correlacionada
Uma consulta interna correlacionada é aquela que:

é executada para cada uma das linhas consideradas candidatas na consulta principal.

a execução usa o valor da coluna da consulta principal.
Estas propriedades causam a consulta interna ser processada de maneira diferente de uma
consulta aninhada comum.
Uma consulta interna correlacionada é identificada pela presença de uma coluna da consulta
principal na consulta interna.
Passos para executar uma consulta interna correlacionada:

Pegar a linha candidata fornecida pela consulta principal.

Executar a consulta interna usando os valores da consulta principal.

Usar os resultados da consulta interna para qualificar os desqualificar a linha candidata.

Repetir enquanto houver linha candidata
Para encontrar os empregados que recebem mais do que o salário médio de seus
departamentos:
SQL>
2
3
4
5
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP E
WHERE SAL >
(SELECT AVG(SAL) FROM EMP WHERE DEPTNO = E.DEPTNO)
ORDER BY DEPTNO
EMPNO
------7839
7566
7788
7902
7499
ENAME
SAL DEPTNO
---------- ------- ------KING
5000
10
JONES
2975
20
SCOTT
3000
20
FORD
3000
20
ALLEN
1600
30
73
7698 BLAKE
2850
30
Análise da execução da consulta:


9.11
Consulta principal

Seleciona a primeira linha candidata - Smith, departamento 20, salário 800.

A linha selecionada possui a coluna DEPTNO, e a cláusula WHERE da
consulta interna também possui a mesma coluna da mesma tabela EMP,
portanto a consulta interna será realizada.

A cláusula WHERE compara o salário de Smith, 800, com o valor retornado
da consulta interna.
Consulta Interna

O número do departamento é passado da consulta externa para a interna.

O salário médio do departamento do empregado é calculado.

O salário médio do departamento do Smith é 2172.
O operador EXISTS
O operador EXISTS é freqüentemente usado com consultas aninhadas correlacionadas,
testando se o valor existe. Se o valor existir, retorna verdadeiro, caso contrário retorna falso.
Para listar todas as empregados com ao menos um funcionário subordinado:
SQL>
2
3
4
5
SELECT EMPNO, ENAME, JOB, DEPTNO
FROM EMP E
WHERE EXISTS
(SELECT EMPNO FROM EMP WHERE EMP.MGR = E.EMPNO)
ORDER BY EMPNO
EMPNO
------7566
7698
7782
7788
7839
7902
ENAME
---------JONES
BLAKE
CLARK
SCOTT
KING
FORD
JOB
DEPTNO
--------- ------MANAGER
20
MANAGER
30
MANAGER
10
ANALYST
20
PRESIDENT
10
ANALYST
20
6 rows selected.
Para listar os empregados cujos departamentos não estão na tabela de departamentos:
SQL>
2
3
4
SELECT EMPNO, ENAME, DEPTNO
FROM EMP
WHERE NOT EXISTS
(SELECT DEPTNO FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO)
74
no rows selected
Uma outra forma para encontrar os departamentos sem funcionários é:
SQL>
2
3
4
SELECT DEPTNO, DNAME
FROM DEPT D
WHERE NOT EXISTS
(SELECT 1 FROM EMP E WHERE E.DEPTNO = D.DEPTNO)
DEPTNO DNAME
------- -------------40 OPERATIONS
Note que neste exemplo a consulta interna não precisa retornar nada, porém a cláusula
SELECT precisa de uma coluna, mesmo que seja um literal.
9.12
Exercícios
a) Listar os empregados com o maior salário por cargo, em ordem descendente de salário.
ENAME
---------KING
SCOTT
FORD
JONES
ALLEN
MILLER
JOB
SAL
--------- ------PRESIDENT
5000
ANALYST
3000
ANALYST
3000
MANAGER
2975
SALESMAN
1600
CLERK
1300
b) Listar os empregados com os menores salário por cargo, em ordem ascendente de salário.
ENAME
---------SMITH
WARD
MARTIN
CLARK
SCOTT
FORD
KING
JOB
SAL
--------- ------CLERK
800
SALESMAN
1250
SALESMAN
1250
MANAGER
2450
ANALYST
3000
ANALYST
3000
PRESIDENT
5000
7 rows selected.
c) Listar os últimos empregados contratados em cada departamento.
DEPTNO
------20
10
30
ENAME
---------ADAMS
KING
JAMES
HIREDATE
--------04-JUN-84
09-JUL-84
23-JUL-84
d) Listar o nome, o salário e o número do departamento dos funcionários que recebem acima
da média de seus departamentos. Ordenar pelo número do departamento.
ENAME
SALARIO DEPTNO
---------- ------- ------KING
5000
10
JONES
2975
20
75
SCOTT
FORD
ALLEN
BLAKE
3000
3000
1600
2850
20
20
30
30
e) Listar os departamentos sem funcionários.
DEPTNO DNAME
------- -------------40 OPERATIONS
f) Mostrar o departamento que tem mais despesas com seus funcionários.
DEPTNO MAIOR_DESPESA
------- ------------20
130500
g) Quais os três empregados que ganham mais ?
ENAME
SAL
---------- ------SCOTT
3000
KING
5000
FORD
3000
h) Em que ano a empresa contratou mais ?
ANO NUMERO DE EMPREGADOS
---- -------------------1984
8
i) Modificar o exercício d para mostrar o salário médio do departamento junto com as outras
informações.
NOME
SALARIO DEPARTAMENTO MEDIA_DEPT
---------- ----------- ------------ ----------ALLEN
1,600.00
30
1,566.67
BLAKE
2,850.00
30
JONES
2,975.00
20
2,175.00
FORD
3,000.00
20
SCOTT
3,000.00
20
KING
5,000.00
10
2,916.67
6 rows selected.
j) Escreva uma consulta que mostre um '*' junto do funcionário contratado mais
recentemente
ENAME
---------ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
HIREDATE MAIOR_DATA
--------- ---------04-JUN-84
15-AUG-83
11-JUN-84
14-MAY-84
05-DEC-83
23-JUL-84 *
31-OCT-83
09-JUL-84
05-DEC-83
21-NOV-83
05-MAR-84
76
SMITH
TURNER
WARD
13-JUN-83
04-JUN-84
26-MAR-84
14 rows selected.
77
9.13
Resposta dos exercícios
a)
SQL>
2
3
4
5
SELECT ENAME, JOB, SAL
FROM EMP
WHERE (SAL,JOB) IN
(SELECT MAX(SAL), JOB FROM EMP GROUP BY JOB)
ORDER BY SAL DESC
b)
SQL>
2
3
4
5
SELECT ENAME, JOB, SAL
FROM EMP
WHERE (SAL,JOB) IN
(SELECT MIN(SAL), JOB FROM EMP GROUP BY JOB)
ORDER BY SAL
c)
SQL>
2
3
4
5
SELECT DEPTNO, ENAME, HIREDATE
FROM EMP
WHERE (HIREDATE,DEPTNO) IN
(SELECT MAX(HIREDATE), DEPTNO FROM EMP GROUP BY DEPTNO)
ORDER BY HIREDATE
d)
SQL>
2
3
4
5
SELECT ENAME, SAL SALARIO, DEPTNO
FROM EMP E
WHERE SAL >
(SELECT AVG(SAL) FROM EMP WHERE DEPTNO = E.DEPTNO)
ORDER BY DEPTNO
e)
SQL>
2
3
4
SELECT DEPTNO, DNAME
FROM DEPT D
WHERE NOT EXISTS
(SELECT 'qualquer coisa' FROM EMP WHERE DEPTNO = D.DEPTNO)
f)
SQL>
SQL>
2
3
4
5
old
new
old
new
old
new
DEFINE REM = SAL*12+NVL(COMM,0)
SELECT DEPTNO, SUM(&REM) MAIOR_DESPESA
FROM EMP
GROUP BY DEPTNO
HAVING SUM(&REM) =
(SELECT MAX(SUM(&REM)) FROM EMP GROUP BY DEPTNO)
1: SELECT DEPTNO, SUM(&REM) MAIOR_DESPESA
1: SELECT DEPTNO, SUM(SAL*12+NVL(COMM,0)) MAIOR_DESPESA
4: HAVING SUM(&REM) =
4: HAVING SUM(SAL*12+NVL(COMM,0)) =
5: (SELECT MAX(SUM(&REM)) FROM EMP GROUP BY DEPTNO)
5: (SELECT MAX(SUM(SAL*12+NVL(COMM,0))) FROM EMP GROUP BY DEPTNO)
g)
SQL> SELECT ENAME, SAL
2 FROM EMP E
3 WHERE 3 > (SELECT COUNT(*) FROM EMP WHERE E.SAL < SAL)
h)
SQL>
SQL>
SQL>
2
3
4
5
6
7
COLUMN ANO FORMAT A4
COLUMN NUMBER_OF_EMPS FORMAT 9 HEADING 'NUMERO DE EMPREGADOS'
SELECT TO_CHAR(HIREDATE,'YYYY') ANO,
COUNT(EMPNO) NUMBER_OF_EMPS
FROM EMP
GROUP BY TO_CHAR(HIREDATE,'YYYY')
HAVING COUNT(EMPNO) =
(SELECT MAX(COUNT(EMPNO))
FROM EMP
78
8
GROUP BY TO_CHAR(HIREDATE,'YYYY'))
i)
SQL>
SQL>
SQL>
SQL>
2
3
4
5
6
7
8
COLUMN SALARIO FORMAT 999,999.99
COLUMN MEDIA_DEPT LIKE SALARIO
BREAK ON DEPTNO ON MEDIA_DEPT
SELECT E.ENAME NOME, E.SAL SALARIO,
E.DEPTNO DEPARTAMENTO, AVG(A.SAL) MEDIA_DEPT
FROM EMP A, EMP E
WHERE E.DEPTNO = A.DEPTNO
AND E.SAL >
(SELECT AVG(SAL) FROM EMP WHERE DEPTNO = E.DEPTNO)
GROUP BY E.ENAME, E.SAL, E.DEPTNO
ORDER BY AVG(A.SAL)
j)
SQL>
2
3
4
5
6
7
SELECT ENAME, HIREDATE, '*' MAIOR_DATA
FROM EMP
WHERE HIREDATE = (SELECT MAX(HIREDATE) FROM EMP)
UNION
SELECT ENAME, HIREDATE, ' '
FROM EMP
WHERE HIREDATE < (SELECT MAX(HIREDATE) FROM EMP)
79
Capítulo 10: Geração de relatórios
10.1
Objetivos deste capítulo
Aprender os comandos SET que controlam o ambiente, e aprender a formatar os resultados
das consultas no SQL*PLUS.
10.2
Conjunto de comandos SET do SQL*PLUS
ECHO{OFF on}
FEED[BACK]
{6 n off on}
HEA[DING]
{off ON}
LIN[ESIZE]
{80 n}
NEWP[AGE]
{1 n}
NUMF[ORMAT]
formato
NUM[WIDTH]
{10 n}
PAGES[IZE]
{24 n}
PAU[SE]
{OFF on texto}
VERIFY
{off ON}
TIMING
{OFF on}
SPACE
{1 n}
TERM[OUT]
{off ON}
SQLCASE
{MIXED lower
upper}
ON mostra os comando executados a partir de um arquivo de
comandos. OFF não mostra.
'n' faz com que seja mostrado o número de linhas selecionadas na
tela, quando 'n' ou mais linhas são selecionadas.
ON causa a impressão do cabeçalho da coluna no relatório. OFF
suprime a impressão do cabeçalho.
Número de caracteres a serem exibidos antes da marca de nova
linha, e controle para centrar e ajustar texto à direita.
Número de linhas em branco entre o título inferior de uma página
e o título superior da página seguinte. 0 = form feed.
Formato padrão para resultados numéricos.
Largura padrão para colunas numéricas.
Número de linhas por página.
ON aguarda pressionar ENTER antes de mostrar a nova tela.
OFF suprime a espera. Texto especifica o texto a ser mostrado
ON faz com que o texto de uma linha de comando seja mostrado
antes e depois de aplicada a variável de substituição.
ON mostra as estatísticas de tempo de cada comando SQL
processado.
Número de espaços entre colunas. Máximo igual a 10.
OFF suprime a exibição dos resultados produzidos pelos
comandos executados a partir de um arquivo, mantendo o Spool.
LOWER e UPPER convertem os caracteres antes da execução.
MIXED não altera os caracteres do texto.
O comando SHOW ALL exibe todas as variáveis do comando SET.
10.3
COLUMN
Através do comando COLUMN podem ser alteradas propriedades das colunas.
Notas:

O nome da coluna deve ser o nome do aliás, se algum for usado.
80


As opções podem vir em qualquer ordem.
Uma vez emitido, o comando permanece válido até o fim da sessão, se não for
eliminado.

Para descobrir as opções atribuídas para a coluna digite COL nome_da_coluna.
10.3.1 Formato de exibição para as colunas
COL xxx FORMAT yyy ...
FORMATO
An
9
0
$
.
,
MI
PR
EEEE
V
B
SIGNIFICADO
alfanumérico, largura n.
posição numérica, como 999999.
coloca zeros à esquerda, como 099999.
cifrão flutuante, como $999999.
ponto decimal, como 999999.99.
vírgula, como 999,999.
sinal de menos à direita, como 999999MI.
número negativo entre parênteses, como 999999PR.
notação científica, como 99.9999EEEE.
multiplicar por 10**n, como 9999V99.
valores zero em branco, como B9999.99.
10.3.2 Outras opções de exibição das colunas.
WRAP
TRUNC
WORD_WRAPPED
CLEAR
HEADING
JUSTIFY
LEFT
RIGHT
CENTER
LIKE
nome_da_coluna
NEWLINE
NULL texto
PRINT
NOPRINT
TEMP
10.4
Especifica o que fazer quando o conteúdo da coluna excede a
sua largura. O padrão é WRAP.
Move a palavra toda.
Remove a formatação prévia da coluna.
Especifica um cabeçalho para a coluna.
Alinhamento do cabeçalho da coluna. O padrão é JUSTIFY
LEFT para caracteres e datas, e JUSTIFY RIGHT para
números.
Copia a especificação de uma outra coluna.
Começa uma nova linha antes da coluna especificada ser
exibida.
Coloca o texto especificado quando o valor da coluna for
nulo.
Faz com que a coluna apareça ou não no relatório.
Especifica que a formatação é válida para uma consulta
apenas.
Exemplo de formatação de colunas
81
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
DEPTNO FORMAT 099 HEADING 'Dept.'
JOB FORMAT A9 HEADING 'Cargo' JUSTIFY RIGHT
EMPNO FORMAT 9999 HEADING 'Num.|Emp.'
SAL FORMAT 99,999.99 HEADING 'Salario|Mensal'
COMM FORMAT 99,990.99 HEADING 'Comissao' NULL 'Sem com.'
REM FORMAT 999,999.99 HEADING 'Remuneracao|Total'
SQL> SELECT DEPTNO, JOB, EMPNO, SAL, COMM, SAL*12+NVL(COMM,0) REM
2 FROM EMP
Dept.
----020
030
030
020
030
030
010
020
010
030
020
030
020
010
10.5
Num.
Salario
Remuneracao
Cargo Emp.
Mensal
Comissao
Total
--------- ----- ---------- ---------- ----------CLERK
7369
800.00 Sem com.
9,600.00
SALESMAN
7499
1,600.00
300.00
19,500.00
SALESMAN
7521
1,250.00
500.00
15,500.00
MANAGER
7566
2,975.00 Sem com.
35,700.00
SALESMAN
7654
1,250.00
1,400.00
16,400.00
MANAGER
7698
2,850.00 Sem com.
34,200.00
MANAGER
7782
2,450.00 Sem com.
29,400.00
ANALYST
7788
3,000.00 Sem com.
36,000.00
PRESIDENT 7839
5,000.00 Sem com.
60,000.00
SALESMAN
7844
1,500.00
0.00
18,000.00
CLERK
7876
1,100.00 Sem com.
13,200.00
CLERK
7900
950.00 Sem com.
11,400.00
ANALYST
7902
3,000.00 Sem com.
36,000.00
CLERK
7934
1,300.00 Sem com.
15,600.00
Exemplo de formatação de título
TTITLE 'título'
BTITLE 'título'
TTITLE OFF
BTITLE OFF
Imprime a data no topo da folha, no canto esquerdo
superior, o número da folha no canto direito superior, e o
título centrado na linha seguinte.
Imprime um texto centrado ao fim de cada página. O
caracter '|' serve para quebrar linhas.
Limpa o título superior.
Limpa o título inferior.
SQL> TTITLE 'RELATORIO DA EMPRESA|Produzido pelo DP'
SQL> BTITLE '*** CONFIDENCIAL ***'
SQL> /
Thu Oct 27
page
RELATORIO DA EMPRESA
Produzido pelo DP
Dept.
----020
030
030
020
030
030
010
020
010
030
020
030
020
Num.
Salario
Remuneracao
Cargo Emp.
Mensal
Comissao
Total
--------- ----- ---------- ---------- ----------CLERK
7369
800.00 Sem com.
9,600.00
SALESMAN
7499
1,600.00
300.00
19,500.00
SALESMAN
7521
1,250.00
500.00
15,500.00
MANAGER
7566
2,975.00 Sem com.
35,700.00
SALESMAN
7654
1,250.00
1,400.00
16,400.00
MANAGER
7698
2,850.00 Sem com.
34,200.00
MANAGER
7782
2,450.00 Sem com.
29,400.00
ANALYST
7788
3,000.00 Sem com.
36,000.00
PRESIDENT 7839
5,000.00 Sem com.
60,000.00
SALESMAN
7844
1,500.00
0.00
18,000.00
CLERK
7876
1,100.00 Sem com.
13,200.00
CLERK
7900
950.00 Sem com.
11,400.00
ANALYST
7902
3,000.00 Sem com.
36,000.00
82
1
010 CLERK
7934
1,300.00 Sem com.
15,600.00
*** CONFIDENCIAL ***
10.6
Exemplo de remoção da formatação
SQL> CLEAR COLUMNS
columns cleared
SQL> TTITLE OFF
SQL> BTITLE OFF
SQL> /
DEPTNO
------20
30
30
20
30
30
10
20
10
30
20
30
20
10
JOB
EMPNO
SAL
COMM
REM
--------- ------- ------- ------- ------CLERK
7369
800
9600
SALESMAN
7499
1600
300
19500
SALESMAN
7521
1250
500
15500
MANAGER
7566
2975
35700
SALESMAN
7654
1250
1400
16400
MANAGER
7698
2850
34200
MANAGER
7782
2450
29400
ANALYST
7788
3000
36000
PRESIDENT
7839
5000
60000
SALESMAN
7844
1500
0
18000
CLERK
7876
1100
13200
CLERK
7900
950
11400
ANALYST
7902
3000
36000
CLERK
7934
1300
15600
14 rows selected.
83
Capítulo 11: Geração de Relatórios - Parte 2
11.1
Objetivos deste capítulo
Mostrar propriedades dos cabeçalhos e rodapés, mostrar a variável NEW_VALUE, e os
comandos BREAK e COMPUTE.
11.2
Propriedades dos cabeçalhos e dos rodapés
Os comandos TTITLE e BTITLE podem incluir diversas cláusulas, como mostrado abaixo:
Cláusula
COL n
SKIP n
LEFT
CENTER
RIGHT
TAB n
FORMAT
Descrição
Posicionar a impressão na coluna 'n' na linha corrente. Volta atrás
se a coluna tiver sido passada.
Saltar 'n' linhas. Se 'n' for omitido salta 1 linha. Se n=0 volta para
o começo da linha corrente.
Alinha à esquerda, no centro, ou à direita da linha corrente. Os
itens de dados que seguem esta cláusula, até o fim do comando
ou até a próxima ocorrência de uma destas cláusulas, são
alinhados como um grupo. As cláusulas LEFT e CENTER se
baseiam no comando SET LINESIZE para calcular a posição dos
itens de dados.
Avança ou recua a posição de impressão 'n' caracteres. O valor de
'n' deve ser negativo para haver o recuo.
Define o formato dos itens de dados que seguem a cláusula, até o
fim do comando ou até a próxima ocorrência desta cláusula. A
especificação do formato para os títulos é semelhante a
especificação do formato para as colunas. Somente uma
especificação de formato está ativa a cada instante. Se o tipo de
dado for conflitante com o formato, o formato não tem efeito
para este item. Se não houver nenhuma cláusula FORMAT ativa,
os valores numéricos são impressos de acordo com o comando
SET NUMFORMAT, ou na ausência deste com o formato
padrão. Valores de data são impressos no formato padrão.
Podem ser incluídas, também, variáveis de sistema.
Variável
SQL.PNO
SQL.LNO
SQL.USER
SQL.SQLCODE
Descrição
Número da pagina corrente.
Número da linha corrente.
Nome do usuário.
Código do erro mais recente.
O exemplo a seguir mostra algumas das opções disponíveis para TTITLE e BTITLE:
SQL> TTITLE LEFT FORMAT 0999 'Pagina: 'SQL.PNO > RIGHT 'Produzido por: Contabilidade' SKIP 2 > CENTER 'Relatorio Confidencial de Vendas' SKIP > CENTER '--------------------------------' SKIP 2
84
SQL> BTITLE CENTER 'FIM DO RELATORIO' SKIP >
CENTER '----------------'
SQL> SELECT ENAME, JOB, SAL, COMM
2 FROM EMP
3 WHERE COMM IS NOT NULL
Pagina:
0001
Produzido por: Contabilidade
Relatorio Confidencial de Vendas
--------------------------------
ENAME
---------ALLEN
WARD
MARTIN
TURNER
JOB
SAL
COMM
--------- ------- ------SALESMAN
1600
300
SALESMAN
1250
500
SALESMAN
1250
1400
SALESMAN
1500
0
FIM DO RELATORIO
----------------
SQL> TTITLE OFF
SQL> BTITLE OFF
11.3
A cláusula NEW_VALUE
Esta cláusula permite armazenar os valores das colunas em variáveis do SQL*PLUS. As
variáveis podem ser usadas para modificar um comando SQL dinamicamente, exibir valores
nos cabeçalhos e passar um valor de um comando SQL para outro.
SQL> COLUMN SYSDATE NEW_VALUE HOJE NOPRINT
SQL> SELECT SYSDATE
2 FROM SYS.DUAL
SQL> TTITLE LEFT 'Data:' HOJE RIGHT FORMAT 999 'Pagina:' SQL.PNO SKIP > LEFT 'Usuario: ' SQL.USER SKIP > CENTER 'Um relatorio com a data reformatada no titulo' SKIP 2
SQL> SELECT SYSDATE
2 FROM SYS.DUAL
Data: 31-OCT-94
Usuario: ALUNO1
Pagina:
1
Um relatorio com a data reformatada no titulo
SQL> TTITLE OFF
A cláusula COLUMN SYSDATE NEW_VALUE HOJE NOPRINT faz com que sempre que
a coluna com nome SYSDATE seja selecionada, seu valor passe para a variável HOJE. A
cláusula NOPRINT impede a impressão da variável SYSDATE como um relatório separado.
O comando SELECT SYSDATE FROM SYS.DUAL faz com que SYSDATE seja
selecionada a partir da tabela SYS.DUAL, que é uma tabela que contém apenas uma linha, e
vai retornar, portanto, apenas um valor.
85
O comando TTITLE LEFT 'Data: ' HOJE faz referência a variável HOJE, definida e com
valor atribuído pela cláusula NEW_VALUE. Desta forma, SYSDATE é impressa no
cabeçalho através da variável HOJE.
No exemplo abaixo, a variável MAIOR_SALARIO_MEDIO é utilizada para passar um
valor de uma consulta para a outra.
SQL> COL MAIOR_SALARIO_MEDIO NEW_VALUE MAIOR_SALARIO_MEDIO
SQL> SELECT MAX(AVG(SAL)) MAIOR_SALARIO_MEDIO
2 FROM EMP
3 GROUP BY JOB
MAIOR_SALARIO_MEDIO
------------------5000
SQL>
2
3
4
old
new
SELECT JOB
FROM EMP
GROUP BY JOB
HAVING AVG(SAL) = &MAIOR_SALARIO_MEDIO
4: HAVING AVG(SAL) = &MAIOR_SALARIO_MEDIO
4: HAVING AVG(SAL) =
5000
JOB
--------PRESIDENT
11.4
Quebras nos relatórios
O comando BREAK pode ser utilizado para quebrar os relatórios em sessões. Quando se faz
uma quebra por uma coluna, os valores duplicados da coluna são omitidos. Como a quebra
ocorre toda vez que o valor da coluna muda, o resultado deve ser ordenado pelas colunas
com quebra especificada. Somente existe um comando BREAK ativo de cada vez, portanto
todas as quebras devem ser especificadas no mesmo comando.
O comando BREAK permite as seguintes opções
PAGE
salta folha quando o valor da coluna muda.
SKIP n
salta 'n' linhas quando o valor da coluna muda.
DUP[LICATE]
mostra os valores duplicados.
Exemplos:
SQL> BREAK ON REPORT ON DEPTNO PAGE ON JOB SKIP 2
SQL> BREAK ON REPORT ON DEPTNO PAGE ON JOB DUP
SQL> CLEAR BREAKS
breaks cleared
SQL> BREAK
no break(s) defined
O exemplo abaixo ilustra o uso do comando BREAK:
SQL> COLUMN DEPTNO FORMAT 099 HEADING 'Dept.'
SQL> COLUMN JOB FORMAT A9 HEADING 'Cargo' JUSTIFY RIGHT
SQL> COLUMN EMPNO FORMAT 9999 HEADING 'Emp.|Num.'
86
SQL> COLUMN SAL FORMAT 999,999.99 HEADING 'Salario|Mensal'
SQL> COLUMN COMM FORMAT 999,990.99 > HEADING 'Comissao|Y-T-D' NULL 'Sem Com.'
SQL> COLUMN REM FORMAT 9,999,999.99 HEADING 'Remuneracao|Total'
SQL> TTITLE 'RELATORIO DA COMPANHIA|Produzido pelo Departamento
Pessoal'
SQL> BTITLE '*** CONFIDENCIAL ***'
SQL> BREAK ON DEPTNO SKIP 1 ON JOB ON REPORT
SQL> SELECT DEPTNO, JOB, EMPNO, SAL, COMM, SAL*12+NVL(COMM,0) REM
2 FROM EMP
3 ORDER BY DEPTNO, JOB
Mon Oct 31
page
1
RELATORIO DA COMPANHIA
Produzido pelo Departamento Pessoal
Emp.
Salario
Comissao
Remuneracao
Dept.
Cargo Num.
Mensal
Y-T-D
Total
----- --------- ----- ----------- ----------- ------------010 CLERK
7934
1,300.00 Sem Com.
15,600.00
MANAGER
7782
2,450.00 Sem Com.
29,400.00
PRESIDENT 7839
5,000.00 Sem Com.
60,000.00
020 ANALYST
CLERK
MANAGER
030 CLERK
MANAGER
SALESMAN
7788
7902
7369
7876
7566
3,000.00
3,000.00
800.00
1,100.00
2,975.00
Sem
Sem
Sem
Sem
Sem
Com.
Com.
Com.
Com.
Com.
7900
7698
7499
7654
7844
7521
950.00 Sem Com.
2,850.00 Sem Com.
1,600.00
300.00
1,250.00
1,400.00
1,500.00
0.00
1,250.00
500.00
*** CONFIDENCIAL ***
36,000.00
36,000.00
9,600.00
13,200.00
35,700.00
11,400.00
34,200.00
19,500.00
16,400.00
18,000.00
15,500.00
SQL> CLEAR COLUMNS
columns cleared
SQL> TTITLE OFF
SQL> BTITLE OFF
SQL> CLEAR BREAKS
breaks cleared
11.5
Cálculo de sumários
O comando COMPUTE realiza cálculos baseados nas quebras estabelecidas pelo comando
BREAK.
COMPUTE cláusulas OF colunas ON quebras
Cláusulas
AVG
COU[NT]
MAX[IMUM]
MIN[IMUM]
NUM[BER]
Descrição
calcula a média
conta os valores não nulos
valor máximo
valor mínimo
número de linhas
87
Tipo da coluna
número
qualquer
número ou caracter
número ou caracter
qualquer
STD
SUM
VAR[IANCE]
desvio padrão
soma os valores não nulos
calcula a variância
número
número
número
Pode haver vários comandos COMPUTE ativos ao mesmo tempo, mas geralmente é mais
fácil especificar tudo em um único comando, como, por exemplo:
SQL> COMPUTE SUM AVG OF SAL COMM ON DEPTNO REPORT
Para mostrar o valor corrente:
SQL> COMPUTE
COMPUTE sum avg
COMPUTE sum avg
COMPUTE sum avg
COMPUTE sum avg
OF
OF
OF
OF
SAL ON DEPTNO
SAL ON REPORT
COMM ON DEPTNO
COMM ON REPORT
Para apagar todos os valores:
SQL> CLEAR COMPUTES
computes cleared
SQL> COMPUTE
no computes currently defined
Exemplo utilizando COMPUTE:
SQL> COLUMN DEPTNO FORMAT 09999 HEADING 'Dept.'
SQL> COLUMN JOB FORMAT A9 HEADING 'Cargo' JUSTIFY RIGHT
SQL> COLUMN EMPNO FORMAT 9999 HEADING 'Emp.|Num.'
SQL> COLUMN SAL FORMAT 999,999.99 HEADING 'Salario|Mensal'
SQL> COLUMN COMM FORMAT 999,990.99 HEADING 'COMISSAO|Y-T-D' NULL 'Sem
Com.'
SQL> COLUMN REM FORMAT 9,999,999.99 HEADING 'Remuneracao|Total'
SQL> TTITLE 'Relatorio da Companhia|Produzido pelo Departamento
Pessoal'
SQL> BTITLE '*** CONFIDENCIAL ***'
SQL> BREAK ON DEPTNO SKIP 1 ON JOB ON REPORT
SQL> COMPUTE AVG SUM OF SAL COMM ON DEPTNO REPORT
SQL> SELECT DEPTNO, JOB, EMPNO, SAL, COMM, SAL*12+NVL(COMM,0) REM
2 FROM EMP
3 ORDER BY DEPTNO, JOB
Mon Oct 31
page
Relatorio da Companhia
Produzido pelo Departamento Pessoal
Emp.
Salario
Dept.
Cargo Num.
Mensal
------ --------- ----- ----------00010 CLERK
7934
1,300.00
MANAGER
7782
2,450.00
PRESIDENT 7839
5,000.00
****** *********
----------avg
2,916.67
sum
8,750.00
00020 ANALYST
CLERK
MANAGER
7788
7902
7369
7876
7566
3,000.00
3,000.00
800.00
1,100.00
2,975.00
COMISSAO
Remuneracao
Y-T-D
Total
----------- ------------Sem Com.
15,600.00
Sem Com.
29,400.00
Sem Com.
60,000.00
----------0.00
Sem
Sem
Sem
Sem
Sem
88
Com.
Com.
Com.
Com.
Com.
36,000.00
36,000.00
9,600.00
13,200.00
35,700.00
1
****** *********
avg
sum
00030 CLERK
MANAGER
SALESMAN
----------- ----------2,175.00
10,875.00
0.00
7900
7698
7499
7654
7844
7521
****** *********
avg
sum
950.00 Sem Com.
2,850.00 Sem Com.
1,600.00
300.00
1,250.00
1,400.00
1,500.00
0.00
1,250.00
500.00
----------- ----------1,566.67
550.00
9,400.00
2,200.00
avg
sum
----------- ----------2,073.21
550.00
29,025.00
2,200.00
11,400.00
34,200.00
19,500.00
16,400.00
18,000.00
15,500.00
*** CONFIDENCIAL ***
14 rows selected.
SQL> CLEAR COLUMN
columns cleared
SQL> CLEAR BREAKS
breaks cleared
SQL> CLEAR COMPUTE
computes cleared
SQL> TTITLE OFF
SQL> BTITLE OFF
11.6
Relatório matricial
Suponha que se deseje produzir um relatório sumarizando os salários por cargo e por
departamento, como mostrado abaixo:
Mon Oct 31
page
1
Relatorio da Companhia
JOB
Departamento 10 Departamento 20 Departamento 30 Total por Cargo
--------- --------------- --------------- --------------- --------------ANALYST
.00
6,000.00
.00
6,000.00
CLERK
1,300.00
1,900.00
950.00
4,150.00
MANAGER
2,450.00
2,975.00
2,850.00
8,275.00
PRESIDENT
5,000.00
.00
.00
5,000.00
SALESMAN
.00
.00
5,600.00
5,600.00
--------------- --------------- --------------- --------------sum
8,750.00
10,875.00
9,400.00
29,025.00
Confidencial
A tabela EMP tem todas as informações necessárias para produzir o relatório. Abaixo estão
descritas as etapas a serem seguidas para a obtenção do relatório:
a) Listar as informações necessárias contidas na tabela EMP, ou seja, JOB, SAL e DEPTNO.
SQL> SELECT JOB, SAL, DEPTNO
2 FROM EMP
89
JOB
SAL DEPTNO
--------- ------- ------CLERK
800
20
SALESMAN
1600
30
SALESMAN
1250
30
MANAGER
2975
20
SALESMAN
1250
30
MANAGER
2850
30
MANAGER
2450
10
ANALYST
3000
20
PRESIDENT
5000
10
SALESMAN
1500
30
CLERK
1100
20
CLERK
950
30
ANALYST
3000
20
CLERK
1300
10
14 rows selected.
b) Separar a coluna SAL em 3 departamentos separados, usando a função DECODE.
SQL>
2
3
4
5
SELECT JOB,
DECODE(DEPTNO, 10, SAL, 0) D10,
DECODE(DEPTNO, 20, SAL, 0) D20,
DECODE(DEPTNO, 30, SAL, 0) D30
FROM EMP
JOB
D10
D20
D30
--------- ------- ------- ------CLERK
0
800
0
SALESMAN
0
0
1600
SALESMAN
0
0
1250
MANAGER
0
2975
0
SALESMAN
0
0
1250
MANAGER
0
0
2850
MANAGER
2450
0
0
ANALYST
0
3000
0
PRESIDENT
5000
0
0
SALESMAN
0
0
1500
CLERK
0
1100
0
CLERK
0
0
950
ANALYST
0
3000
0
CLERK
1300
0
0
14 rows selected.
c) Sumarizar os campos de acordo com o grupo do cargo e o departamento.
SQL>
2
3
4
5
6
SELECT JOB,
SUM(DECODE(DEPTNO, 10, SAL, 0)) D10,
SUM(DECODE(DEPTNO, 20, SAL, 0)) D20,
SUM(DECODE(DEPTNO, 30, SAL, 0)) D30
FROM EMP
GROUP BY JOB
JOB
D10
D20
D30
--------- ------- ------- ------ANALYST
0
6000
0
CLERK
1300
1900
950
MANAGER
2450
2975
2850
PRESIDENT
5000
0
0
SALESMAN
0
0
5600
d) Calcular os totais para cada grupo de cargo.
90
SQL>
2
3
4
5
6
7
SELECT JOB,
SUM(DECODE(DEPTNO, 10, SAL, 0)) D10,
SUM(DECODE(DEPTNO, 20, SAL, 0)) D20,
SUM(DECODE(DEPTNO, 30, SAL, 0)) D30,
SUM(SAL) TBJ
FROM EMP
GROUP BY JOB
JOB
D10
D20
D30
TBJ
--------- ------- ------- ------- ------ANALYST
0
6000
0
6000
CLERK
1300
1900
950
4150
MANAGER
2450
2975
2850
8275
PRESIDENT
5000
0
0
5000
SALESMAN
0
0
5600
5600
e) Finalmente formatar o relatório com os comandos do SQL*PLUS.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SET PAGES 16
COLUMN D10 HEADING 'Departamento 10' FORMAT
COLUMN D20 HEADING 'Departamento 20' FORMAT
COLUMN D30 HEADING 'Departamento 30' FORMAT
COLUMN TBJ HEADING 'Total por Cargo' FORMAT
BREAK ON REPORT
COMPUTE SUM OF D10 D20 D30 TBJ ON REPORT
TTITLE 'Relatorio da Companhia'
BTITLE 'Confidencial'
/
Mon Oct 31
99,999.99
99,999.99
99,999.99
999,999.99
page
1
Relatorio da Companhia
JOB
Departamento 10 Departamento 20 Departamento 30 Total por Cargo
--------- --------------- --------------- --------------- --------------ANALYST
.00
6,000.00
.00
6,000.00
CLERK
1,300.00
1,900.00
950.00
4,150.00
MANAGER
2,450.00
2,975.00
2,850.00
8,275.00
PRESIDENT
5,000.00
.00
.00
5,000.00
SALESMAN
.00
.00
5,600.00
5,600.00
--------------- --------------- --------------- --------------sum
8,750.00
10,875.00
9,400.00
29,025.00
Confidencial
SQL> TTITLE OFF
SQL> BTITLE OFF
SQL> CLEAR COLUMN
columns cleared
SQL> CLEAR BREAKS
breaks cleared
SQL> CLEAR COMPUTE
computes cleared
11.7
Arquivo de comandos para o SQL*PLUS
Para criar um arquivo de comandos para o SQL*PLUS:
a) Prepare o comando SELECT
91
b) Salve o comando SELECT (SAVE nome-do-arquivo)
c) Abrir o arquivo salvo para edição (HOST EDIT nome-do-arquivo.SQL)
d) Adicionar os comandos
SET
COLUMN
TTILE
BTITLE
BREAK
COMPUTE
...
SELECT ...
/
TTITLE OFF
BTITLE OFF
CLEAR BREAKS
CLEAR COMPUTES
CLEAR COLUMNS
...
e) Salvar o arquivos
f) Executar o arquivo (@nome-do-arquivo)
92
11.8
Exercícios
a) Produza o relatório mostrado abaixo
Mon Oct 31
page
R E L A T O R I O
D O S
1
E M P R E G A D O S
EMP.
DEPARTAMENTO CARGO
NUM. NOME
------------ --------- ----- -------ACCOUNTING
CLERK
7934 MILLER
MANAGER
7782 CLARK
PRESIDENT 7839 KING
************ *********
sum
DATA
SALARIO
COM.
ADM.
MENSAL
ANUAL
TOTAL
----- --------- -------- ----------11/83 1,300.00
0.00
15,600.00
05/84 2,450.00
0.00
29,400.00
07/84 5,000.00
0.00
60,000.00
--------- -------- ----------8,750.00
0.00 105,000.00
RESEARCH
ANALYST
7902
7788
7369
7876
7566
FORD
SCOTT
SMITH
ADAMS
JONES
12/83
03/84
06/83
06/84
10/83
3,000.00
0.00
36,000.00
3,000.00
0.00
36,000.00
800.00
0.00
9,600.00
1,100.00
0.00
13,200.00
2,975.00
0.00
35,700.00
--------- -------- ----------10,875.00
0.00 130,500.00
7900
7698
7499
7654
7844
7521
JAMES
BLAKE
ALLEN
MARTIN
TURNER
WARD
07/84
06/84
08/83
12/83
06/84
03/84
************ *********
sum
950.00
0.00
11,400.00
2,850.00
0.00
34,200.00
1,600.00
300.00
19,500.00
1,250.00 1,400.00
16,400.00
1,500.00
0.00
18,000.00
1,250.00
500.00
15,500.00
--------- -------- ----------9,400.00 2,200.00 115,000.00
sum
--------- -------- ----------29,025.00 2,200.00 350,500.00
CLERK
MANAGER
************ *********
sum
SALES
CLERK
MANAGER
SALESMAN
CONFIDENCIAL
SQL> TTITLE OFF
SQL> BTITLE OFF
SQL> SET FEEDBACK ON
SQL> SET PAGESIZE 24
SQL> CLEAR BREAKS
breaks cleared
SQL> CLEAR COMPUTES
computes cleared
93
b) Produza o relatório abaixo. O número do departamento deve ser solicitado em tempo de
execução.
Mon Oct 31
page
R E L A T O R I O
D O S
EMP.
DEPARTAMENTO CARGO
NUM. NOME
------------ --------- ----- -------SALES
CLERK
7900 JAMES
MANAGER
7698 BLAKE
SALESMAN
7499 ALLEN
7521 WARD
7844 TURNER
7654 MARTIN
************ *********
sum
DATA
ADM.
----07/84
06/84
08/83
03/84
06/84
12/83
1
E M P R E G A D O S
SALARIO COMISSAO
MENSAL
ANUAL
TOTAL
-------- --------- ----------950.00
0.00
11,400.00
2,850.00
0.00
34,200.00
1,600.00
300.00
19,500.00
1,250.00
500.00
15,500.00
1,500.00
0.00
18,000.00
1,250.00 1,400.00
16,400.00
-------- --------- ----------9,400.00 2,200.00 115,000.00
-------- --------- ----------9,400.00 2,200.00 115,000.00
sum
CONFIDENCIAL
SQL> TTITLE OFF
SQL> BTITLE OFF
SQL> SET FEEDBACK ON
SQL> SET PAGESIZE 24
SQL> CLEAR BREAKS
breaks cleared
SQL> CLEAR COMPUTES
computes cleared
94
11.9
Respostas dos exercícios
a)
SQL> SET ECHO OFF
SQL> SET PAGESIZE 37
SQL> SET FEEDBACK OFF
SQL> SET LINESIZE 78
SQL> TTITLE 'R E L A T O R I O
D O S
E M P R E G A D O S'
SQL> BTITLE 'CONFIDENCIAL'
SQL> DEFINE COMM = 'NVL(COMM,0)'
SQL> COL A FORMAT A12 HEADING 'DEPARTAMENTO' TEMP
SQL> COL B FORMAT A9 HEADING 'CARGO' TEMP
SQL> COL C FORMAT 9999 HEADING 'EMP.|NUM.' TEMP
SQL> COL D FORMAT A8 HEADING 'NOME' TEMP
SQL> COL E FORMAT A5 HEADING 'DATA|ADM.' TEMP
SQL> COL F FORMAT B99,999.99 HEADING 'SALARIO|MENSAL' TEMP
SQL> COL G FORMAT 9,990.99 HEADING 'COM.|ANUAL' TEMP
SQL> COL H FORMAT 999,999.99 HEADING 'TOTAL' TEMP
SQL> BREAK ON REPORT ON A SKIP 2 ON B
SQL> COMPUTE SUM OF F G H ON REPORT A
SQL> SELECT DNAME A, JOB B, EMPNO C, ENAME D,
2 TO_CHAR(HIREDATE,'MM/YY') E, SAL F, &COMM G, SAL*12+&COMM H
3 FROM EMP E, DEPT D
4 WHERE E.DEPTNO = D.DEPTNO
5 ORDER BY DNAME, JOB
old
2: TO_CHAR(HIREDATE,'MM/YY') E, SAL F, &COMM G, SAL*12+&COMM H
new
2: TO_CHAR(HIREDATE,'MM/YY') E, SAL F, NVL(COMM,0) G,
SAL*12+NVL(COMM,0) H
b)
SQL> SET ECHO OFF
SQL> SET PAGESIZE 37
SQL> SET FEEDBACK OFF
SQL> SET LINESIZE 78
SQL> TTITLE'R E L A T O R I O
D O S
E M P R E G A D O S'
SQL> BTITLE 'CONFIDENCIAL'
SQL> DEFINE COMM = 'NVL(COMM,0)'
SQL> ACCEPT DEPTNO NUMBER PROMPT 'NUMERO DO DEPARTAMENTO:'
NUMERO DO DEPARTAMENTO:30
SQL> COL A FORMAT A12 HEADING 'DEPARTAMENTO' TEMP
SQL> COL B FORMAT A9 HEADING 'CARGO' TEMP
SQL> COL C FORMAT 9999 HEADING 'EMP.|NUM.' TEMP
SQL> COL D FORMAT A8 HEADING 'NOME' TEMP
SQL> COL E FORMAT A5 HEADING 'DATA|ADM.' TEMP
SQL> COL F FORMAT B99,999.99 HEADING 'SALARIO|MENSAL' TEMP
SQL> COL G FORMAT 9,990.99 HEADING 'COMISSAO|ANUAL' TEMP
SQL> COL H FORMAT 999,999.99 HEADING 'TOTAL' TEMP
SQL> BREAK ON REPORT ON A SKIP 2 ON B
SQL> COMPUTE SUM OF F G H ON REPORT A
SQL> SELECT DNAME A, JOB B, EMPNO C, ENAME D,
2 TO_CHAR(HIREDATE,'MM/YY') E, SAL F, &COMM G, SAL*12+&COMM H
3 FROM EMP E, DEPT D
4 WHERE E.DEPTNO = D.DEPTNO
5 AND E.DEPTNO = &DEPTNO
6 ORDER BY DNAME, JOB
old
2: TO_CHAR(HIREDATE,'MM/YY') E, SAL F, &COMM G, SAL*12+&COMM H
new
2: TO_CHAR(HIREDATE,'MM/YY') E, SAL F, NVL(COMM,0) G,
SAL*12+NVL(COMM,0) H
old
5: AND E.DEPTNO = &DEPTNO
new
5: AND E.DEPTNO =
30
95
Capítulo 12: Hierarquias - Caminhando na Árvore
12.1
Objetivos deste capítulo
Utilizar os relacionamentos hierárquicos para recuperar dados em uma tabela.
12.2
Quando é possível caminhar na árvore
A tabela EMP tem uma estrutura em forma de árvore, indicando os gerentes de cada
funcionário.
KING (EMPNO=7839)
|
------------------------------------- (MGR=7839)
|
|
|
CLARK
JONES
BLAKE
|
|
|
|
-------------------------------------|
|
|
|
|
|
|
|
MILLER
SCOTT FORD ALLEN
WARD
MARTIN
TURNER
JAMES
|
|
ADAMS SMITH
Um banco de dados relacional não armazena os registros em forma hierárquica. Entretanto,
quando existe um relacionamento hierárquico entre linhas de uma única tabela, existe um
processo de navegação na árvore que permite a hierarquia ser construída. A hierarquia pode
ser vista analisando-se os valores idênticos nas colunas EMPNO e MGR da tabela EMP
(Este relacionamento já foi visto quando foi feita a junção desta tabela com ela mesmo). A
coluna MGR indica o número do empregado ao qual o empregado está subordinado.
Quando uma navegação na árvore é executada, não espere ver o resultado na forma da figura
acima, o resultado é uma tabela.
O nível (LEVEL) indica a distância do nó à raiz da árvore. Para caminhar na árvore da tabela
EMP:
SQL> BREAK ON DEPTNO SKIP 1
SQL>
2
3
4
SELECT LEVEL, DEPTNO, EMPNO, ENAME, JOB, SAL
FROM EMP
CONNECT BY PRIOR EMPNO = MGR
START WITH MGR IS NULL
96
LEVEL DEPTNO
EMPNO ENAME
JOB
SAL
------- ------- ------- ---------- --------- ------1
10
7839 KING
PRESIDENT
5000
2
3
4
3
4
20
7566
7788
7876
7902
7369
JONES
SCOTT
ADAMS
FORD
SMITH
MANAGER
ANALYST
CLERK
ANALYST
CLERK
2975
3000
1100
3000
800
2
3
3
3
3
3
30
7698
7499
7521
7654
7844
7900
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
MANAGER
SALESMAN
SALESMAN
SALESMAN
SALESMAN
CLERK
2850
1600
1250
1250
1500
950
2
3
10
7782 CLARK
7934 MILLER
MANAGER
CLERK
2450
1300
Comando
Descrição
SELECT
A cláusula SELECT padrão, incluindo a pseudo coluna
LEVEL que mostra a distância do nó à raiz.
Só pode conter uma tabela.
Restringe as linhas pesquisadas durante a navegação.
Especifica as colunas onde existe o relacionamento entre as
linhas. Esta cláusula é obrigatório para caminhar na árvore.
Estabelece a direção para caminhar na árvore.
1) PRIOR expressão operador_de_comparação expressão
2) expressão operador_de_comparação PRIOR expressão
Se PRIOR aparece antes de MGR, então os valores de
MGR são pesquisados primeiro, e depois os valores
equivalentes de EMP, ou seja, pesquisa da raiz para o topo.
Se PRIOR aparece antes de EMP a árvore é percorrido do
topo para a raiz.
Especifica onde começa a pesquisa. Não pode ser usado na
forma 'START at a LEVEL'. Esta cláusula é opcional.
É a última cláusula, como sempre.
FROM
WHERE
CONNECT BY
PRIOR
START WITH
ORDER BY
No exemplo acima, a estrutura hierárquica da empresa é refletida na navegação pela árvore.
A estrutura chefe/subordinado fica claramente identificada.
A cláusula ORDER BY pode ser usada para ordenar as linhas retornadas. O comando abaixo
é totalmente legítimo:
SQL>
2
3
4
5
SELECT LEVEL, DEPTNO, EMPNO, ENAME, JOB, SAL
FROM EMP
CONNECT BY PRIOR EMPNO = MGR
START WITH MGR IS NULL
ORDER BY DEPTNO
97
LEVEL DEPTNO
EMPNO ENAME
------- ------- ------- ---------1
10
7839 KING
2
7782 CLARK
3
7934 MILLER
JOB
SAL
--------- ------PRESIDENT
5000
MANAGER
2450
CLERK
1300
2
3
4
3
4
20
7566
7788
7876
7902
7369
JONES
SCOTT
ADAMS
FORD
SMITH
MANAGER
ANALYST
CLERK
ANALYST
CLERK
2975
3000
1100
3000
800
2
3
3
3
3
3
30
7698
7499
7521
7654
7844
7900
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
MANAGER
SALESMAN
SALESMAN
SALESMAN
SALESMAN
CLERK
2850
1600
1250
1250
1500
950
Não é recomendada a utilização da cláusula ORDER BY,
porque a ordenação implícita pode ser destruída.
12.3
Excluindo um nó da árvore
As cláusulas WHERE e CONNECT BY podem ser usadas para podar a árvore, isto é,
controlar os nós que são mostrados.

WHERE ENAME <> 'SCOTT'
Quando a cláusula WHERE é utilizada para eliminar o nó, somente o nó é eliminado.
JONES
|
---------------|
|
SCOTT
FORD
|
|
ADAMS
SMITH
Neste exemplo, SCOTT é eliminado, mas ADAMS permanece.
SQL>
2
3
4
5
SELECT LEVEL, DEPTNO, EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE ENAME <> 'SCOTT'
CONNECT BY PRIOR EMPNO = MGR
START WITH MGR IS NULL
98
LEVEL DEPTNO
EMPNO ENAME
JOB
SAL
------- ------- ------- ---------- --------- ------1
10
7839 KING
PRESIDENT
5000
2
4
3
4
20
7566
7876
7902
7369
JONES
ADAMS
FORD
SMITH
MANAGER
CLERK
ANALYST
CLERK
2975
1100
3000
800
2
3
3
3
3
3
30
7698
7499
7521
7654
7844
7900
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
MANAGER
SALESMAN
SALESMAN
SALESMAN
SALESMAN
CLERK
2850
1600
1250
1250
1500
950
2
3
10
7782 CLARK
7934 MILLER
MANAGER
CLERK
2450
1300
13 rows selected.

CONNECT BY PRIOR EMPNO=MGR AND ENAME <> 'SCOTT'
Quando a cláusula CONNECT BY é utilizada para eliminar o nó, toda a sua ramificação é
eliminada.
JONES
|
---------------|
|
SCOTT
FORD
|
|
ADAMS
SMITH
Neste exemplo, SCOTT e ADAMS são eliminados.
SQL>
2
3
4
SELECT LEVEL, DEPTNO, EMPNO, ENAME, JOB, SAL
FROM EMP
CONNECT BY PRIOR EMPNO = MGR AND ENAME <> 'SCOTT'
START WITH MGR IS NULL
99
LEVEL DEPTNO
EMPNO ENAME
JOB
SAL
------- ------- ------- ---------- --------- ------1
10
7839 KING
PRESIDENT
5000
2
3
4
20
7566 JONES
7902 FORD
7369 SMITH
MANAGER
ANALYST
CLERK
2975
3000
800
2
3
3
3
3
3
30
7698
7499
7521
7654
7844
7900
MANAGER
SALESMAN
SALESMAN
SALESMAN
SALESMAN
CLERK
2850
1600
1250
1250
1500
950
2
3
10
7782 CLARK
7934 MILLER
MANAGER
CLERK
2450
1300
Inglês
Português
Descrição
NODE
Nó
ROOT
Raiz
PARENT
Pai
CHILD
TERMINAL NODE
LEAF
BRANCH
Filho
Folha
O mesmo que uma linha da
tabela.
O nó que não pertence a nenhum
outro nó.
Um nó que possui outros nós de
nível inferior.
Um nó que tem um pai.
Um nó que não possui filhos.
Ramo ou Galho
Um nó que tem filhos e netos.
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
12 rows selected.
12.4
12.5
Terminologia
Exercícios
a) Mostrar a organização do departamento 20.
100
Capítulo 13: Dicionário de Dados
13.1
Objetivos deste capítulo
Apresentar o dicionário de dados do Oracle.
13.2
O que é o dicionário de dados
O dicionário de dados é um conjunto de tabelas e visões que provê um guia de referência
sobre o banco de dados.
No dicionário de dados são permitidas apenas consultas por parte dos usuários. O conjunto
de tabelas e visões é fixo, e o conteúdo é atualizado automaticamente pelo Oracle, quando
um comando da linguagem de definição de dados (DDL), ou outros comandos, são
executados.
O dicionário de dados é criado quando o banco de dados é criado, sendo uma peça crítica no
funcionamento do gerenciador do banco de dados, que utiliza as informações contidas no
dicionário de dados para gerenciar o próprio banco de dados.
13.3
Informações contidas no dicionário de dados
As seguintes informações podem ser obtidas a partir do dicionário de dados:
13.4

Os nomes dos usuários do banco de dados.

Os direitos e privilégios concedidos aos usuários.

Nomes dos objetos do banco de dados (tabelas, visões, índices, sinônimos, seqüências.)

Restrições aplicadas sobre as tabelas.

Informações de auditoria, tais como que acessou ou atualizou um determinado objeto do
banco de dados.
Tabelas do dicionário de dados
As tabelas do dicionário de dados são criadas automaticamente quando o comando CREATE
DATABASE é executado.
Todas as tabelas do dicionário de dados pertencem ao usuário SYS, e não são acessadas
diretamente porque as informações armazenadas nestas tabelas são de difícil compreensão.
13.5
Visões do dicionário de dados
As visões do dicionário de dados contém informações em uma maneira fácil de serem
compreendidas. Acesso público ao dicionário de dados é concedido através das visões, e não
do acesso direto às tabelas.
101
As visões do dicionário de dados também pertencem aos usuários SYS.
Os nomes das visões refletem o tipo de uso para o qual elas foram criadas. As visões são
classificadas em três grupos distinguidos pelos prefixos USER, ALL e DBA.
Classe
USER_xxx
ALL_xxx
DBA_xxx
Descrição
Visões que qualquer usuário pode acessar, contendo
informações relativas aos objetos do próprio usuário.
Visões que qualquer usuário pode acessar, contendo
informações relativas tanto aos objetos do próprio usuário,
quanto informações sobre os objetos aos quais foram
concedidos privilégios ao usuário.
Visões que só podem ser acessadas pelos usuários com
privilégio de administrador do banco de dados
Existem ainda algumas visões que não possuem nenhum destes três prefixos. Abaixo é
mostrada a relação das visões do dicionário de dados, juntamente com suas descrições:
Visão
DICTIONARY
DICT_COLUMNS
TABLE_PRIVILEGES
Visão
ALL_CATALOG
ALL_COL_COMMENTS
ALL_COL_PRIVS
ALL_COL_PRIVS_MADE
ALL_COL_PRIVS_RECD
ALL_CONSTRAINTS
ALL_CONS_COLUMNS
ALL_DB_LINKS
ALL_DEF_AUDIT_OPTS
ALL_DEPENDENCIES
ALL_ERRORS
Descrição
Description of data dictionary tables and
views
Description of columns in data dictionary
tables and views
Grants on objects for which the user is the
grantor, grantee, or owner, or PUBLIC is the
grantee
Descrição
All tables, views, synonyms, sequences
accessible to the user
Comments on columns of accessible tables
and views
Grants on columns for which the user is the
grantor, grantee, owner, or an enabled role or
PUBLIC is the grantee
Grants on columns for which the user is
owner or grantor
Grants on columns for which the user,
PUBLIC or enabled role is the grantee
Constraint definitions on accessible tables
Information about accessible columns in
constraint definitions
Database links accessible to the user
Auditing options for newly created objects
Dependencies to and from objects accessible
to the user
Current errors on stored objects that user is
allowed to create
102
ALL_INDEXES
ALL_IND_COLUMNS
ALL_OBJECTS
ALL_SEQUENCES
ALL_SNAPSHOTS
ALL_SOURCE
ALL_SYNONYMS
ALL_TABLES
ALL_TAB_COLUMNS
ALL_TAB_COMMENTS
ALL_TAB_PRIVS
ALL_TAB_PRIVS_MADE
ALL_TAB_PRIVS_RECD
ALL_TRIGGERS
ALL_TRIGGER_COLS
ALL_USERS
ALL_VIEWS
Visão
DBA_2PC_NEIGHBORS
DBA_2PC_PENDING
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_CATALOG
DBA_CLUSTERS
DBA_CLU_COLUMNS
DBA_COL_COMMENTS
Descriptions of indexes on tables accessible
to the user
COLUMNs comprising INDEXes on
accessible TABLES
Objects accessible to the user
Description of SEQUENCEs accessible to
the user
Snapshots the user can look at
Current source on stored objects that user is
allowed to create
All synonyms accessible to the user
Description of tables accessible to the user
Columns of all tables, views and clusters
Comments on tables and views accessible to
the user
Grants on objects for which the user is the
grantor, grantee, owner, or an enabled role or
PUBLIC is the grantee
User's grants and grants on user's objects
Grants on objects for which the user,
PUBLIC or enabled role is the grantee
Triggers accessible to the current user
Column usage in user's triggers or in triggers
on user's tables
Information about all users of the database
Text of views accessible to the user
Descrição
information about incoming and outgoing
connections for pending transactions
info about distributed transactions awaiting
recovery
Lists audit trail entries produced by AUDIT
NOT EXISTS and AUDIT EXISTS
Audit trail records for statements concerning
objects, specifically: table, cluster, view,
index, sequence, [public] database link,
[public] synonym, procedure, trigger,
rollback segment, tablespace, role, user
Audit trail records concerning grant, revoke,
audit, noaudit and alter system
All audit trail entries
All database Tables, Views, Synonyms,
Sequences
Description of all clusters in the database
Mapping of table columns to cluster columns
Comments on columns of all tables and
103
DBA_COL_PRIVS
DBA_CONSTRAINTS
DBA_CONS_COLUMNS
DBA_DATA_FILES
DBA_DB_LINKS
DBA_DEPENDENCIES
DBA_ERRORS
DBA_EXP_FILES
DBA_EXP_OBJECTS
DBA_EXP_VERSION
DBA_EXTENTS
DBA_FREE_SPACE
DBA_INDEXES
DBA_IND_COLUMNS
DBA_OBJECTS
DBA_OBJECT_SIZE
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_PROFILES
DBA_ROLES
DBA_ROLE_PRIVS
DBA_ROLLBACK_SEGS
DBA_SEGMENTS
DBA_SEQUENCES
DBA_SNAPSHOTS
DBA_SNAPSHOT_LOGS
DBA_SOURCE
DBA_STMT_AUDIT_OPTS
DBA_SYNONYMS
DBA_SYS_PRIVS
DBA_TABLES
DBA_TABLESPACES
DBA_TAB_COLUMNS
DBA_TAB_COMMENTS
DBA_TAB_PRIVS
DBA_TRIGGERS
DBA_TRIGGER_COLS
views
All grants on columns in the database
Constraint definitions on all tables
Information about accessible columns in
constraint definitions
Information about database files
All database links in the database
Dependencies to and from objects
Current errors on all stored objects in the
database
Description of export files
Objects that have been incrementally
exported
Version number of the last export session
Extents comprising all segments in the
database
Free extents in all tablespaces
Description for all indexes in the database
COLUMNs comprising INDEXes on all
TABLEs and CLUSTERs
All objects in the database
Sizes, in bytes, of various pl/sql objects
Auditing options for all tables and views
Describes current system privileges being
audited across the system and by user
Display all profiles and their limits
All Roles which exist in the database
Roles granted to users and roles
Description of rollback segments
Storage allocated for all database segments
Description of all SEQUENCEs in the
database
All snapshots in the database
All snapshot logs in the database
Source of all stored objects in the database
Describes current system auditing options
across the system and by user
All synonyms in the database
System privileges granted to users and roles
Description of all tables in the database
Description of all tablespaces
Columns of all tables, views and clusters
Comments on all tables and views in the
database
All grants on objects in the database
All triggers in the database
Column usage in all triggers
104
DBA_TS_QUOTAS
DBA_USERS
DBA_VIEWS
Tablespace quotas for all users
Information about all users of the database
Text of all views in the database
Visão
USER_AUDIT_OBJECT
Descrição
Audit trail records for statements concerning
objects, specifically: table, cluster, view,
index, sequence, [public] database link,
[public] synonym, procedure, trigger,
rollback segment, tablespace, role, user
Audit trail records concerning grant, revoke,
audit, noaudit and alter system
Audit trail entries relevant to the user
Tables, Views, Synonyms and Sequences
owned by the user
Descriptions of user's own clusters
Mapping of table columns to cluster columns
Comments on columns of user's tables and
views
Grants on columns for which the user is the
owner, grantor or grantee
All grants on columns of objects owned by
the user
Grants on columns for which the user is the
grantee
Constraint definitions on user's own tables
Information about accessible columns in
constraint definitions
Database links owned by the user
Dependencies to and from a users objects
Current errors on stored objects owned by the
user
Extents comprising segments owned by the
user
Free extents in tablespaces accessible to the
user
Description of the user's own indexes
COLUMNs comprising user's INDEXes or
on user's TABLES
Objects owned by the user
Sizes, in bytes, of various pl/sql objects
Auditing options for user's own tables and
views
USER_AUDIT_STATEMENT
USER_AUDIT_TRAIL
USER_CATALOG
USER_CLUSTERS
USER_CLU_COLUMNS
USER_COL_COMMENTS
USER_COL_PRIVS
USER_COL_PRIVS_MADE
USER_COL_PRIVS_RECD
USER_CONSTRAINTS
USER_CONS_COLUMNS
USER_DB_LINKS
USER_DEPENDENCIES
USER_ERRORS
USER_EXTENTS
USER_FREE_SPACE
USER_INDEXES
USER_IND_COLUMNS
USER_OBJECTS
USER_OBJECT_SIZE
USER_OBJ_AUDIT_OPTS
105
USER_RESOURCE_LIMITS
USER_ROLE_PRIVS
USER_SEGMENTS
USER_SEQUENCES
USER_SNAPSHOTS
USER_SNAPSHOT_LOGS
USER_SOURCE
USER_SYNONYMS
USER_SYS_PRIVS
USER_TABLES
USER_TABLESPACES
USER_TAB_COLUMNS
USER_TAB_COMMENTS
USER_TAB_PRIVS
USER_TAB_PRIVS_MADE
USER_TAB_PRIVS_RECD
USER_TRIGGERS
USER_TRIGGER_COLS
USER_TS_QUOTAS
USER_USERS
USER_VIEWS
Display resource limit of the user
Roles granted to current user
Storage allocated for all database segments
Description of the user's own SEQUENCEs
Snapshots the user can look at
All snapshot logs owned by the user
Source of stored objects accessible to the
user
The user's private synonyms
System privileges granted to current user
Description of the user's own tables
Description of accessible tablespaces
Columns of user's tables, views and clusters
Comments on the tables and views owned by
the user
Grants on objects for which the user is the
owner, grantor or grantee
All grants on objects owned by the user
Grants on objects for which the user is the
grantee
Triggers owned by the user
Column usage in user's triggers
Tablespace quotas for the user
Information about the current user
Text of views owned by the user
Para compatibilidade com os SQL/DS e o DB2 da IBM existem aindas as visões:
SYSCATALOG, SYSCOLAUTH, SYSCOLUMNS, SYSDBLINKS, SYSEXTENTS,
SYSFILES, SYSINDEXES, SYSROLLBACKSEG, SYSSEGOBJ, SYSSTORAGE,
SYSTABALLOC, SYSTABAUTH, SYSTABSPACES, SYSTEM_AUDIT,
SYSTSQUOTAS, SYSUSERAUTH, SYSUSERLIST, SYSVIEWS, SYS_OBJECTS.
13.6
Exemplos de utilização do dicionário de dados
Listar todas as tabelas acessíveis ao usuário.
SQL> DESCRIBE ACCESSIBLE_TABLES
Name
------------------------------OWNER
TABLE_NAME
TABLE_TYPE
Null?
-------NOT NULL
NOT NULL
Type
---CHAR(30)
CHAR(30)
CHAR(11)
SQL> SELECT *
2 FROM ACCESSIBLE_TABLES
3 ORDER BY 1,2
OWNER
-------------------ALUNO1
ALUNO1
TABLE_NAME
-------------------ASSIGNMENTS
BONUS
106
TABLE_TYPE
----------TABLE
TABLE
ALUNO1
CUSTOMER
TABLE
ALUNO1
DEPT
TABLE
ALUNO1
DUMMY
TABLE
ALUNO1
EMP
TABLE
ALUNO1
ITEM
TABLE
ALUNO1
ORD
TABLE
ALUNO1
PRICE
TABLE
ALUNO1
PRODUCT
TABLE
ALUNO1
PROJECTS
TABLE
ALUNO1
SALES
VIEW
ALUNO1
SALGRADE
TABLE
SYS
ACCESSIBLE_COLUMNS
VIEW
SYS
ACCESSIBLE_TABLES
VIEW
SYS
ALL_CATALOG
VIEW
SYS
ALL_COL_COMMENTS
VIEW
SYS
ALL_COL_GRANTS_MADE VIEW
..............................................
SYS
ALL_TAB_GRANTS_RECD VIEW
SYS
ALL_USERS
VIEW
SYS
ALL_VIEWS
VIEW
SYS
AUDIT_ACCESS
VIEW
SYS
AUDIT_ACTIONS
TABLE
SYS
AUDIT_CONNECT
VIEW
SYS
AUDIT_TRAIL
VIEW
SYS
CATALOG
VIEW
SYS
CLUSTERCOLUMNS
VIEW
SYS
CLUSTERS
VIEW
..............................................
SYS
USER_AUDIT_CONNECT
VIEW
SYS
USER_AUDIT_RESOURCE VIEW
SYS
USER_AUDIT_TRAIL
VIEW
..............................................
SYS
USER_TS_QUOTAS
VIEW
SYS
USER_USERS
VIEW
SYS
USER_VIEWS
VIEW
SYS
V4EXPCLUS
VIEW
SYS
V4EXPCOL
VIEW
SYS
V4EXPEXTENTS
VIEW
SYS
V4EXPINDEX
VIEW
SYS
V4EXPSPACE
VIEW
SYS
V4EXPSYN
VIEW
SYS
V4EXPTAB
VIEW
SYS
V4EXPTABAUTH
VIEW
SYS
V4EXPUSER
VIEW
SYS
V4EXPVIEW
VIEW
SYSTEM
HELP
TABLE
136 rows selected.
Listar todos os usuários do banco de dados.
SQL> DESCRIBE ALL_USERS
Name
------------------------------USERNAME
USER_ID
CREATED
Null?
-------NOT NULL
NOT NULL
NOT NULL
Type
---CHAR(30)
NUMBER
DATE
SQL> SELECT * FROM ALL_USERS
USERNAME
USER_ID CREATED
------------------------------ ------- --------SYS
0 30-DEC-91
PUBLIC
1 30-DEC-91
SYSTEM
2 30-DEC-91
SCOTT
3 30-DEC-91
107
ALUNO1
_NEXT_USER
4 19-OCT-94
5 30-DEC-91
6 rows selected.
Listar todos os índices criados nas tabelas do usuário.
SQL> DESCRIBE USER_INDEXES
Name
------------------------------INDEX_NAME
TABLE_OWNER
TABLE_NAME
TABLE_TYPE
UNIQUENESS
TABLESPACE_NAME
INI_TRANS
MAX_TRANS
INITIAL_EXTENT
NEXT_EXTENT
MIN_EXTENTS
MAX_EXTENTS
PCT_INCREASE
PCT_FREE
STATUS
SQL>
SQL>
SQL>
SQL>
2
3
Null?
-------NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT
NOT
NOT
NOT
NULL
NULL
NULL
NULL
Type
---CHAR(30)
CHAR(30)
CHAR(30)
CHAR(11)
CHAR(9)
CHAR(30)
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
CHAR(17)
COLUMN INDEX_NAME FORMAT A20
COLUMN TABLE_OWNER FORMAT A20
COLUMN TABLE_NAME FORMAT A20
SELECT INDEX_NAME, TABLE_OWNER, TABLE_NAME, TABLE_TYPE
FROM USER_INDEXES
ORDER BY 1,2
INDEX_NAME
TABLE_TYPE
-------------------ITEM_INDEX
ORD_INDEX
PRICE_INDEX
PRODUCT_INDEX
TABLE_OWNER
TABLE_NAME
-------------------ALUNO1
ALUNO1
ALUNO1
ALUNO1
-------------------ITEM
ORD
PRICE
PRODUCT
Listar as descrições das colunas de todas as tabelas do banco de dados.
SQL> DESCRIBE DBA_COL_COMMENTS
ERROR:
ORA-00942: table or view does not exist
Esta consulta só pode ser realizada por um usuário com privilégio de DBA.
SQL> CONNECT SYSTEM
Connected.
SQL> DESCRIBE DBA_COL_COMMENTS
Name
------------------------------OWNER
TABLE_NAME
COLUMN_NAME
COMMENTS
13.7
Null?
-------NOT NULL
NOT NULL
NOT NULL
Exercícios
a) Mostrar as seguintes informações para a tabela EMP:
108
Type
---CHAR(30)
CHAR(30)
CHAR(30)
CHAR(255)
-------TABLE
TABLE
TABLE
TABLE
Coluna
Nulo?
------------------------------- -------EMPNO
NOT NULL
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
109
Tipo
-------------NUMBER(4)
VARCHAR2(10)
VARCHAR2(9)
NUMBER(4)
DATE
NUMBER(7,2)
NUMBER(7,2)
NUMBER(2)
Capítulo 14: Linguagem de Definição de Dados
14.1
Objetivos deste capítulo
Apresentar os comandos utilizados para criar, alterar, trocar de nome, adicionar comentários
e remover tabelas.
14.2
Estrutura de Dados do Oracle
Tabelas podem ser criadas a qualquer momento, mesmo com usuários acessando o banco de
dados.
Comprimento dos dados é variável. Somente os caracteres e números especificados são
armazenados. Espaços à frente e à traz não são armazenados.
Não existe a necessidade de se especificar o tamanho das tabelas. As tabelas ocupam novos
segmentos quando necessário.
A estrutura das tabelas podem ser modificadas on-line.
14.3
Criando uma tabela
Devem ser observadas as seguintes regras para o nome de uma tabela:

O nome deve começar por uma letra, de "A" a "Z", ou de "a" a "z".

O nome pode conter letras, números, e o caractere especial sublinhado (_). Os caracteres
"$" e "#” também podem ser usado, mas seu uso é desaconselhado.

O nome é o mesmo, independentemente do uso de letras minúsculas ou maiúsculas. Por
exemplo, EMP, emp. EmP, Emp, e Emp são a mesma tabela.

O nome pode conter até 30 caracteres.

O nome da tabela não pode ser igual ao de outra tabela, sinônimo ou visão, a não ser que
pertençam a usuários diferentes.

O nome não pode ser uma das palavras reservadas do Oracle.
Nome
EMP85
85EMP
LUCRO_BRUTO
LUCRO BRUTO
UPDATE
TABELA1
Válido
Sim
Não, começa com número.
Sim
Não, contém espaço.
Não, palavra reservada.
Sim, mas nome não diz nada.
110
14.4
14.5
Recomendações sobre os nomes das tabelas

Usar nomes descritivos para as tabelas, colunas, índices e outros objetos.

Abreviar de forma consistente, mantendo sempre a mesma abreviatura.

Consistência de singular e plural, não colocando o nome de algumas tabelas no singular
e de outras no plural

Usar uma regra consistente para atribuir nomes. Uma regra pode ser, todas as tabelas do
sistema financeiro começam com FIN_.

Use o mesmo nome para descrever o mesmo atributo em tabelas diferentes.
Tipo de dado das colunas
Tipo
CHAR(n)
VARCHAR(n)
VARCHAR2(n)
LONG
RAW(n)
LONG RAW
ROWID
MLSLABEL
NUMBER(p,s)
DATE
FLOAT
Conteúdo
Caracteres alfanuméricos com até 'n' caracteres. Coluna de
tamanho constante independente do que está armazenado.
Máximo de 255 caracteres.
Caracteres alfanuméricos com até 'n' caracteres. No Oracle 6 é
sinônimo de CHAR, e pode ter no máximo 255 caracteres.
Caracteres alfanuméricos com até 'n’ caracteres. Introduzida no
Oracle 7, o tamanho armazenado varia de acordo com o conteúdo
da coluna, e pode ter até 2000 caracteres.
Caracteres alfanuméricos com até 2 GigaBytes (2**31 - 1). Só
pode haver uma coluna LONG por tabela. Não pode ser
indexada. Não podem ser especificadas restrições de integridade,
exceto NOT NULL.
Dados binários, como som e imagem, até 255 bytes. Não podem
ser executadas funções de manipulação de caracteres sobre
colunas RAW. Tem as mesmas restrições de LONG. Não existe
conversão de caracteres, como, por exemplo, ANSI x PC850.
Semelhante a RAW, porém podendo armazenar até 2 GigaBytes.
Caracteres hexadecimais representando o endereço único de uma
linha em uma tabela.
Formato binário to rótulo do sistema operacional. Usado com o
Trusted ORACLE.
Número com a precisão 'p' e a escala 's'. A precisão 'p' pode
variar de 1 a 38. A escala 's' pode variar de -84 a +127
Data válida, desde 1 de janeiro de 4712 AC, até 31 de dezembro
de 4712 DC. Para cada coluna DATA são armazenados o Século,
o Ano, o Mês, o Dia, a Hora, o Minuto e o Segundo.
Número de ponto flutuante com precisão decimal de 38 dígitos,
ou precisão binária de 126 dígitos.
111
Dado
7456123,89
7456123,89
7456123,89
7456123,89
7456123,8
7456123,8
7456123,89
7456123,8
14.6
Especificação
NUMBER
NUMBER(9)
NUMBER(9,2)
NUMBER(9,1)
NUMBER(6)
NUMBER(15,1)
NUMBER(7,-2)
NUMBER(7,2)
Relação entre os tipos de dados ANSI e do Oracle
ANSI
CHARACTER(n), CHAR(n)
CHARACTER VARYING(n), CHAR VARYING(n)
NUMERIC(p,s), DECIMAL(p,s), DEC(p,s)
INTEGER, INT, SMALLINT
FLOAT(b), DOUBLE PRECISION, REAL
14.7
ORACLE
CHAR(n)
VARCHAR(n)
NUMBER(p,s)
NUMBER(38)
NUMBER
Relação entre os tipos de dados do SQL/DS e do Oracle
SQL/DS ou DB2
CHARACTER(n)
VARCHAR(n)
LONG VARCHAR
DECIMAL(p,s)
INTEGER, SMALLINT
FLOAT(b)
GRAPHIC
LONG VARGRAPHIC
VARGRAPHIC
TIMESTAMP
TIME
14.8
Valor armazenado
7456123,89
7456124
7456123,89
7456123,9
precisão excedida
7456123,8
7456100
precisão excedida
ORACLE
CHAR(n)
VARCHAR(n)
LONG
NUMBER(p,s)
NUMBER(38)
NUMBER
DATE
DATE
Criação de tabelas
14.8.1 Sintaxe do comando:
CREATE TABLE [esquema.] nome_da_tabela [restrições]
(nome_da_coluna tipo_de_dado (tamanho) [DEFAULT valor] [restrições])
[PCTFREE inteiro PCTUSED inteiro INITRANS inteiro MAXTRANS inteiro]
[TABLESPACE espaço_de_tabela STORAGE armazenamento]
112
Restrição
CONSTRAINT
nome_da_restrição
NULL
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
(coluna...)
REFERENCES
tabela(col(s))
CHECK condição
DISABLE
(ENABLE)
EXCEPTIONS
INTO
Descrição
Especifica o nome da restrição. É opcional. Quando
omitido o nome padrão tem a forma de SYS_Cn, onde 'n' é
um número inteiro atribuído pelo Oracle que identifica
unicamente a restrição.
Especifica se a coluna pode ou não conter valores nulos.
Designa uma coluna, ou uma combinação de colunas,
como chave única. Cada coluna deve ser declarada como
NOT NULL, e não podem ser chave primária.
Designa uma coluna, ou uma combinação de colunas,
como chave primária. As colunas devem ser declaradas
como NOT NULL e não podem ter a restrição UNIQUE.
Se a chave primária contiver apenas uma coluna pode ser
declarada na restrição da coluna. Se a chave primária
contiver múltiplas colunas deve ser declarada na restrição
da tabela.
Identifica a(s) coluna(s) como chave estrangeira .
REFERENCES identifica a chave primária ou chave única
que é referenciada.
Especifica a condição que uma coluna deve satisfazer para
a linha ser aceita na tabela. Só pode se referenciar a colunas
da mesma tabela. Uma restrição de coluna só pode
referenciar a uma coluna, porém uma restrição de tabela
pode referenciar múltiplas colunas.
Desativa (ativa) uma restrição de integridade. O padão é a
restrição ativa.
Identifica a tabela na qual o ORACLE armazena as
informações sobre as linhas que violaram as restrições de
integridade. A tabela deve existir antes desta condição ser
especificada.
14.8.2 Criação das tabelas do curso:
CREATE TABLE DEPT (
DEPTNO
NUMBER(2)
DNAME
CHAR(14),
LOC
CHAR(13),
PRIMARY KEY (DEPTNO)
NOT NULL,
CONSTRAINT DEPT_PRIMARY_KEY)
CREATE TABLE EMP (
EMPNO
NUMBER(4)
NOT NULL,
ENAME
CHAR(10),
JOB
CHAR(9),
MGR
NUMBER(4)
REFERENCES EMP (EMPNO) CONSTRAINT EMP_S_KEY,
HIREDATE
DATE,
SAL
NUMBER(7,2),
COMM
NUMBER(7,2),
DEPTNO
NUMBER(2)
NOT NULL,
113
FOREIGN KEY (DEPTNO)
PRIMARY KEY (EMPNO)
REFERENCES DEPT (DEPTNO) CONSTRAINT EMP_F_K,
CONSTRAINT EMP_PRIMARY_KEY)
CREATE TABLE SALGRADE (
GRADE
NUMBER,
LOSAL
NUMBER,
HISAL
NUMBER)
14.8.3 Exemplos de restrições
Tabela SAL não permitindo salário nulo.
ALTER TABLE EMP MODIFY (SAL NUMBER CONTRAINT NN_SAL NOT NULL)
Tabela DEPT com restrições de coluna.
CREATE TABLE DEPT (
DEPTNO
NUMBER(2)
CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME
VARCHAR2(9) CONSTRAINT UNQ_DNAME UNIQUE,
LOC
VARCHAR2(10))
Tabela DEPT com chave primária definida como restrição de tabela.
CREATE TABLE DEPT (
DEPTNO
NUMBER(2)
NOT NULL,
DNAME
VARCHAR2(9),
LOC
CHAR(13),
PRIMARY KEY (DEPTNO) CONSTRAINT DEPT_PRIMARY_KEY)
Tabela DEPT com nome do departamento único definido como restrição de tabela.
CREATE TABLE DEPT (
DEPTNO
NUMBER(2) NOT NULL,
DNAME
CHAR(14),
LOC
CHAR(13),
CONSTRAINT UNQ_DNAME UNIQUE (DNAME)
USING INDEX PCTFREE 20 TABLESDPACE TBLSP_DEPT
STORAGE (INITIAL 8K NEXT 6K))
Tabela DEPT com opção CHECK.
CREATE TABLE DEPT (
DEPTNO
NUMBER
CONSTRAINT CHECK_DEPTNO
CHECK (DEPTNO BETWEEN 10 AND 99)
DISABLE,
DNAME
CHAR(14)
CONSTRAINT CHECK_DNAME
CHECK(DNAME=UPPER(DNAME))
DISABLE,
LOC
CHAR(13)
CONSTRAINT CHECK_LOC
CHECK (LOC IN ('DALLAS','BOSTON',...))
DISABLE)
Tabela EMP com opção ON DELETE CACADE.
CREATE TABLE EMP (
EMPNO
NUMBER(4)
ENAME
CHAR(10),
JOB
CHAR(9),
NOT NULL,
114
MGR
NUMBER(4)
REFERENCES EMP (EMPNO) CONSTRAINT EMP_S_KEY,
HIREDATE
DATE,
SAL
NUMBER(7,2),
COMM
NUMBER(7,2),
DEPTNO
NUMBER(2)
NOT NULL,
CONSTRAINT FK_DEPTNO REFERENCES DEPT(DEPTNO) ON DELETE CASCADE)
Tabela CENSUS com um índice único composto
CREATE TABLE CENSUS
ADD CONSTRAINT UNQ_CITY_STATE UNIQUE (CITY, STATE)
USING INDEX PCTFREE 5 TABLESPACE TBLSP_CESUS
EXCEPTIONS INTO BAD_KEYS_IN_SHIP_CONT
14.9
Criando uma tabela a partir de outra tabela
É possível criar uma tabela a partir de outra tabela já existente, através do comando:
CREATE TABLE nome_da_tabela
[(especificação das colunas)]
AS SELECT...
Exemplo:
SQL> CREATE TABLE EMPREGADOS AS SELECT * FROM EMP
Table created.
O comando DESCRIBE permite visualizar a estrutura das tabelas.
SQL> DESCRIBE EMP
Name
------------------------------EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
Null?
Type
-------- ---NOT NULL NUMBER(4)
CHAR(10)
CHAR(9)
NUMBER(4)
DATE
NUMBER(7,2)
NUMBER(7,2)
NOT NULL NUMBER(2)
SQL> DESCRIBE EMPREGADOS
Name
------------------------------EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
Null?
Type
-------- ---NOT NULL NUMBER(4)
CHAR(10)
CHAR(9)
NUMBER(4)
DATE
NUMBER(7,2)
NUMBER(7,2)
NOT NULL NUMBER(2)
SQL> SELECT * FROM EMPREGADOS
EMPNO ENAME
JOB
MGR HIREDATE
SAL
COMM DEPTNO
------- ---------- --------- ------- --------- ------- ------- -------
115
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
7902
7698
7698
7839
7698
7839
7839
7566
7698
7788
7698
7566
7782
13-JUN-83
15-AUG-83
26-MAR-84
31-OCT-83
05-DEC-83
11-JUN-84
14-MAY-84
05-MAR-84
09-JUL-84
04-JUN-84
04-JUN-84
23-JUL-84
05-DEC-83
21-NOV-83
800
1600
1250
2975
1250
2850
2450
3000
5000
1500
1100
950
3000
1300
300
500
1400
0
20
30
30
20
30
30
10
20
10
30
20
30
20
10
14 rows selected.
Como pode ser visto, as duas tabelas tem exatamente a mesma estrutura e o mesmo
conteúdo.
Tabelas também podem ser criadas a partir da junção de outras tabelas.
SQL>
2
3
4
5
6
CREATE TABLE SALARIOS_DOS_EMPREGADOS
(NOME, SALARIO, FAIXA)
AS
SELECT
ENAME, SAL, GRADE
FROM
EMP, SALGRADE
WHERE
SAL BETWEEN LOSAL AND HISAL
Table created.
SQL> DESCRIBE SALARIOS_DOS_EMPREGADOS
Name
Null?
------------------------------- -------NOME
SALARIO
FAIXA
SQL> SELECT *
2 FROM
SALARIOS_DOS_EMPREGADOS
NOME
SALARIO
FAIXA
---------- ------- ------SMITH
800
1
ADAMS
1100
1
JAMES
950
1
WARD
1250
2
MARTIN
1250
2
MILLER
1300
2
ALLEN
1600
3
TURNER
1500
3
JONES
2975
4
BLAKE
2850
4
CLARK
2450
4
SCOTT
3000
4
FORD
3000
4
KING
5000
5
14 rows selected.
14.10 Alterando uma tabela
Para adicionar uma coluna na tabela usamos:
116
Type
---CHAR(10)
NUMBER(7,2)
NUMBER
ALTER TABLE nome_da_tabela
ADD ( nome_da_coluna tipo_de_dado [DEFAULT valor] [restrições_de_coluna])
SQL> ALTER TABLE EMPREGADOS
2 ADD (NOME_DA_ESPOSA CHAR(10))
Table altered.
SQL> DESCRIBE EMPREGADOS
Name
------------------------------EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
NOME_DA_ESPOSA
Null?
Type
-------- ---NOT NULL NUMBER(4)
CHAR(10)
CHAR(9)
NUMBER(4)
DATE
NUMBER(7,2)
NUMBER(7,2)
NOT NULL NUMBER(2)
CHAR(10)
Para alterar a tabela empregados e garantir todos tenham salário inferior a 5000:
SQL> ALTER TABLE EMPREGADOS
2 ADD (CHECK(SAL<=5000))
Table altered.
Para modificar uma coluna na tabela usamos:
ALTER TABLE nome_da_tabela
MODIFY ( nome_da_coluna tipo_de_dado [DEFAULT valor] [restrições_de_coluna])
Para modificar a coluna NOME_DA_ESPOSA para aceitar até 25 caracteres fazemos:
SQL> ALTER TABLE EMPREGADOS
2 MODIFY (NOME_DA_ESPOSA CHAR(25))
Table altered.
SQL> DESCRIBE EMPREGADOS
Name
------------------------------EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
NOME_DA_ESPOSA
Null?
Type
-------- ---NOT NULL NUMBER(4)
CHAR(10)
CHAR(9)
NUMBER(4)
DATE
NUMBER(7,2)
NUMBER(7,2)
NOT NULL NUMBER(2)
CHAR(25)
14.11 Trocando o nome de uma tabela
Os nomes dos objetos do banco de dados podem ser trocado através do comando:
RENAME nome_antigo TO nome_novo
117
Para trocar o nome da tabela de EMPREGADOS para FUNCIONARIOS:
SQL> RENAME EMPREGADOS TO FUNCIONARIOS
Table renamed.
14.12 Descrição das tabelas e colunas
A descrição das tabelas e das colunas pode ser armazenada no dicionário de dados através do
comando COMMENT.
Para adicionar a descrição da tabela FUNCIONARIOS:
SQL> COMMENT ON TABLE FUNCIONARIOS IS 'CADASTRO DE FUNCIONARIOS'
Comment created.
Para adicionar a descrição da coluna NOME_DA_ESPOSA da tabela FUNCIONARIOS:
SQL> COMMENT ON COLUMN FUNCIONARIOS.NOME_DA_ESPOSA IS > 'NOME DA ESPOSA DO FUNCIONARIO'
Comment created.
14.13 Removendo tabelas
As tabelas podem ser removidas do banco de dados através do comando:
DROP TABLE nome_da_tabela
Para remover a tabelas SALARIOS_DOS_EMPREGADOS e FUNCIONARIOS:
SQL> DROP TABLE SALARIOS_DOS_EMPREGADOS
Table dropped.
SQL> DROP TABLE FUNCIONARIOS
Table dropped.
14.14 Exercícios
a) Criar as seguintes tabelas:
Tabela: PROJECTS
PROJID
P_DESC
P_START_DATE
P_END_DATE
BUDGET_AMOUNT
MAX_NO_STAFF
NUMBER
CHARACTER
DATE
DATE
NUMBER
NUMBER
4
20
Tabela: ASSIGNMENTS
PROJID
EMPNO
NUMBER
NUMBER
4
4
NOT NULL
7,2
2
118
NOT NULL
NOT NULL
A_START_DATE
A_END_DATE
BILL_RATE
ASSIGN_TYPE
DATE
DATE
NUMBER
CHAR
4,2
2
b) Com as tabelas criadas adicionar a seguinte coluna a tabela ASSIGNMENTS:
HOURS
NUMBER
2
c) Adicionar comentários às tabelas criadas para descrever o conteúdo.
d) Adicionar comentários à coluna PROJID da tabela PROJECTS descrevendo seu
conteúdo.
e) Mostrar a especificação das colunas das visão USER_COL_COMMENTS.
f) Mostrar os nomes das tabelas, das colunas, e os comentários para as colunas.
g) Mostrar a especificação das colunas das visão USER_TAB_COMMENTS.
h) Mostrar os nomes das tabelas e os comentários para as tabelas.
119
14.15 Respostas dos exercícios
a)
SQL>
2
3
4
5
6
7
CREATE TABLE PROJECTS (
PROJID
NUMBER(4)
P_DESC
CHAR(20),
P_START_DATE
DATE,
P_END_DATE
DATE,
BUDGET_AMOUNT
NUMBER(7,2),
MAX_NO_STAFF
NUMBER(2))
SQL>
2
3
4
5
6
7
CREATE TABLE ASSIGNMENTS (
PROJID
NUMBER(4)
EMPNO
NUMBER(4)
A_START_DATE
DATE,
A_END_DATE
DATE,
BILL_RATE
NUMBER(4,2),
ASSIGN_TYPE
CHAR(2))
NOT NULL,
NOT NULL,
NOT NULL,
b)
SQL> ALTER TABLE ASSIGNMENTS
2 ADD (HOURS NUMBER(2))
c)
SQL> COMMENT ON TABLE PROJECTS IS 'UNIQUE PROJECT DETAILS'
SQL> COMMENT ON TABLE ASSIGNMENTS IS > 'ASSIGNMENTS FOR ANY EMPLOYEE ON A PROJECT'
d)
SQL> COMMENT ON COLUMN PROJECTS.PROJID IS > 'UNIQUE IDENTIFIER FOR A PROJECT'
e)
SQL> DESCRIBE USER_COL_COMMENTS
Name
------------------------------TABLE_NAME
COLUMN_NAME
COMMENTS
Null?
-------NOT NULL
NOT NULL
Type
---CHAR(30)
CHAR(30)
CHAR(255)
f)
SQL> SELECT * FROM USER_COL_COMMENTS WHERE COMMENTS IS NOT NULL
TABLE_NAME
COLUMN_NAME
COMMENTS
--------------- --------------- --------------------------------------PROJECTS
PROJID
UNIQUE IDENTIFIER FOR A PROJECT
g)
SQL> DESCRIBE USER_TAB_COMMENTS
Name
Null?
------------------------------- -------TABLE_NAME
NOT NULL
TABLE_TYPE
COMMENTS
Type
--------CHAR(30)
CHAR(11)
CHAR(255)
h)
SQL> SELECT * FROM USER_TAB_COMMENTS WHERE COMMENTS IS NOT NULL
TABLE_NAME
--------------ASSIGNMENTS
PROJECTS
TABLE_TYPE
----------TABLE
TABLE
COMMENTS
-----------------------------------------ASSIGNMENTS FOR ANY EMPLOYEE ON A PROJECT
UNIQUE PROJECT DETAILS
120
Capítulo 15: Linguagem de Manipulação de Dados
15.1
Objetivos deste capítulo
Mostrar a linguagem de manipulação de dados (DML), utilizada para inserir, atualizar e
eliminar registros do banco de dados.
15.2
Inserindo novas linhas em uma tabela
Para inserir linhas em uma tabela é utilizado o comando INSERT.
INSERT INTO nome_da_tabela [(coluna, coluna, ...)]
VALUES (valor, valor, ...)
Os exemplos serão efetuados sobre a tabela Department criada a partir da tabela Dept.
SQL>
2
3
4
CREATE TABLE DEPARTMENT
AS
SELECT *
FROM DEPT
Table created.
Quando os novos valores são inseridos em todas as colunas da tabela, na mesma ordem das
colunas da tabela, a lista de colunas pode ser omitida. É recomendado que a lista de colunas
seja sempre especificada, para não haver necessidade de alterar o programa quando uma
nova coluna for adicionada à tabela.
Para inserir um novo departamento.
SQL> INSERT INTO DEPARTMENT ( DEPTNO, DNAME, LOC )
2 VALUES (50, 'MARKETING', 'SAN JOSE' )
1 row created.
Para inserir apenas o número e a localidade do novo departamento.
SQL> INSERT INTO DEPARTMENT ( DEPTNO, LOC )
2 VALUES ( 60, 'ALBERTA' )
1 row created.
Em vez que omitir o nome do departamento na lista de colunas, pode ser especificado o
valor nulo para o nome do departamento, o resultado é o mesmo.
SQL> INSERT INTO DEPARTMENT ( DEPTNO, DNAME, LOC )
2 VALUES ( 70, NULL, 'DETROIT' )
1 row created.
Podem ser utilizadas variáveis de substituição para fornecimento dos valores.
SQL> INSERT
2 VALUES
Enter value
Enter value
INTO DEPARTMENT ( DEPTNO, DNAME, LOC )
( &DEPT_NUM, '&DEPT_NAME', '&DEPT_LOC' )
for dept_num: 80
for dept_name: RESEARCH
121
Enter value for dept_loc: ATLANTA
old
2: VALUES ( &DEPT_NUM, '&DEPT_NAME', '&DEPT_LOC' )
new
2: VALUES ( 80, 'RESEARCH', 'ATLANTA' )
1 row created.
Para inserir um novo departamento omitindo a lista de colunas.
SQL> INSERT INTO DEPARTMENT
2 VALUES ( 90, 'INSURANCE', 'LONDON' )
1 row created.
Listando a tabela de departamentos após as inserções:
SQL> SELECT *
2 FROM DEPARTMENT
3 ORDER BY DEPTNO
DEPTNO
------10
20
30
40
50
60
70
80
90
DNAME
-------------ACCOUNTING
RESEARCH
SALES
OPERATIONS
MARKETING
RESEARCH
INSURANCE
LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON
SAN JOSE
ALBERTA
DETROIT
ATLANTA
LONDON
9 rows selected.
Para os exemplos de inserção de valores de data e hora será criada a tabela Employee a partir
da tabela Emp.
SQL>
2
3
4
CREATE TABLE EMPLOYEE
AS
SELECT *
FROM EMP
Table created.
Quando uma data é inserida, o formato DD-MON-YY é geralmente usado. Com este
formato, o século padrão é o século 20 (19xx). O campo data também contém informação de
hora, que quando não é especificada assume o valor padrão de zero horas (00:00:00).
Se for necessário especificar a data em outro século, ou for necessário especificar a hora, a
função TO_DATE é utilizada.
SQL>
2
3
4
5
6
7
8
9
10
INSERT INTO EMPLOYEE
( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO )
VALUES (
7658,
'CODD',
'ANALYST',
7566,
TO_DATE('24/06/2084 9:30','DD/MM/YYYY HH:MI'),
3000,
NULL,
122
11 20)
1 row created.
Para exemplo de inserção de valores a partir de uma outra tabela será criada a tabela
SALARY_GRADE a partir da tabela SALGRADE.
SQL>
2
3
4
CREATE TABLE SALARY_GRADE
AS
SELECT * FROM SALGRADE
WHERE GRADE=0
Table created.
Como não existe nenhum departamento com número zero na tabela SALGRADE, não foi
incluída nenhuma linha na tabela SALARY_GRADE durante a criação.
SQL> SELECT *
2 FROM SALARY_GRADE
no rows selected
Para inserir na tabela SALARY_GRADE, a partir da tabela SALGRADE, os valores
correspondentes à faixa 1.
SQL>
2
3
4
INSERT INTO SALARY_GRADE
SELECT *
FROM SALGRADE
WHERE GRADE = 1
1 row created.
Para verificar se a inclusão foi feita corretamente.
SQL> SELECT *
2 FROM SALARY_GRADE
GRADE
LOSAL
HISAL
------- ------- ------1
700
1200
15.3
Atualizando linhas em uma tabela
Para atualizar linhas de uma tabela é utilizado o comando UPDATE.
UPDATE
SET
[WHERE
nome_da_tabela [aliás]
coluna, [,coluna...] = {expressão ou sub-consulta}
condição]
Para atualizar as informações do funcionário SCOTT.
SQL>
2
3
4
5
6
UPDATE EMPLOYEE
SET
JOB
= 'SALESMAN',
HIREDATE = SYSDATE,
SAL
= SAL*1.1
WHERE ENAME = 'SCOTT'
1 row updated.
123
Para trocar todos os cargos de SALESMAN por vendedor.
SQL> UPDATE EMPLOYEE
2 SET JOB
= 'VENDEDOR'
3 WHERE JOB = 'SALESMAN'
5 rows updated.
Para listar a tabela após as atualizações.
SQL> SELECT ENAME, JOB
2 FROM EMPLOYEE
3 ORDER BY JOB, ENAME
ENAME
---------CODD
FORD
ADAMS
JAMES
MILLER
SMITH
BLAKE
CLARK
JONES
KING
ALLEN
MARTIN
SCOTT
TURNER
WARD
JOB
--------ANALYST
ANALYST
CLERK
CLERK
CLERK
CLERK
MANAGER
MANAGER
MANAGER
PRESIDENT
VENDEDOR
VENDEDOR
VENDEDOR
VENDEDOR
VENDEDOR
15 rows selected.
15.4
Eliminando linhas de uma tabela
Para eliminar linhas de uma tabela é utilizado o comando DELETE.
DELETE FROM nome_da_tabela
[WHERE condição]
Para eliminar todos os empregados que trabalham no departamento 10.
SQL> DELETE FROM EMPLOYEE
2 WHERE DEPTNO = 10
3 rows deleted.
Para eliminar todos os empregados.
SQL> DELETE FROM EMPLOYEE
12 rows deleted.
Para verificar como ficou a tabela após a eliminação.
SQL> SELECT * FROM EMPLOYEE
124
no rows selected
Apesar de não possuir nenhuma linha, a tabela Employee ainda existe, para eliminá-la é
utilizado o comando da linguagem de definição de dados (DDL) DROP TABLE.
SQL> DROP TABLE EMPLOYEE
Table dropped.
15.5
Exercícios
a) Inserir as seguintes linhas na tabela PROJECTS
PROJID
P_DESC
P_START_DATE
P_END_DATE
BUDGET_AMOUNT
MAX_NO_STAFF
1
WRITE C030 COURSE
02-JAN-88
07-JAN-88
500
1
2
PROOF READ NOTES
01-JAN-89
10-JAN-89
600
1
b) Inserir as seguintes linhas na tabela ASSIGNMENTS
PROJID
EMPNO
A_START_DATE
A_END_DATE
BILL_RATE
ASSIGN_TYPE
HOURS
1
7369
01-JAN-88
03-JAN-88
50.00
WR
15
1
7902
04-JAN-88
07-JAN-88
55.00
WR
20
c) Alterar ASSIGNMENT_TYPE: trocar WR por WT.
125
2
7844
01-JAN-89
10-JAN-89
45.50
PF
30
15.6
Respostas dos exercícios
a)
SQL>
2
3
4
INSERT INTO PROJECTS
(PROJID,P_DESC,P_START_DATE,P_END_DATE,BUDGET_AMOUNT,MAX_NO_STAFF)
VALUES
(1,'WRITE C030 COURSE','02-JAN-88','07-JAN-88',500,2)
SQL>
2
3
4
INSERT INTO PROJECTS
(PROJID,P_DESC,P_START_DATE,P_END_DATE,BUDGET_AMOUNT,MAX_NO_STAFF)
VALUES
(2,'PROOF READ NOTES','01-JAN-89','10-JAN-89',600,1)
b)
SQL>
2
3
4
INSERT INTO ASSIGNMENTS
(PROJID,EMPNO,A_START_DATE,A_END_DATE,BILL_RATE,ASSIGN_TYPE,HOURS)
VALUES
(1,7369,'01-JAN-88','03-JAN-88',50.00,'WR',15)
SQL>
2
3
4
INSERT INTO ASSIGNMENTS
(PROJID,EMPNO,A_START_DATE,A_END_DATE,BILL_RATE,ASSIGN_TYPE,HOURS)
VALUES
(1,7902,'04-JAN-88','07-JAN-88',55.00,'WR',20)
SQL>
2
3
4
INSERT INTO ASSIGNMENTS
(PROJID,EMPNO,A_START_DATE,A_END_DATE,BILL_RATE,ASSIGN_TYPE,HOURS)
VALUES
(2,7844,'01-JAN-89','10-JAN-89',45.50,'PF',30)
c)
SQL> UPDATE ASSIGNMENTS
2 SET ASSIGN_TYPE = 'WT'
3 WHERE ASSIGN_TYPE = 'WR'
126
Capítulo 16: Processamento de transações
16.1
Objetivos deste capítulo
Fazer uma introdução ao conceito de transação.
16.2
O que é uma transação
Uma transação é um conjunto de operações efetuadas em um banco de dados, que causam
alterações em uma ou mais tabelas.
Existem duas classes de transações. Transações produzidas pela linguagem de manipulação
de dados (DML), que consistem em um ou mais comandos de DML, as quais o Oracle trata
como uma única entidade ou unidade lógica de trabalho (LUW). Transações causadas pela
linguagem de definição de dados (DDL) contém apenas um comando.
As transações não podem ficar pela metade no banco de dados, ou todas as transações de
uma unidade lógica de trabalho são efetivadas, ou nenhuma é efetivada no banco de dados.
Não pode haver o caso em que umas são efetivadas e outras não.
Uma transação começa com o primeiro comando de DML ou DDL executado, e termina
com um dos seguintes comandos.

COMMIT ou ROLLBACK

comando de DDL

Erros

Desconexão (logoff, exit)

Falha de máquina
Um comando de DDL é efetivado automaticamente, e portanto termina implicitamente uma
transação.
Após o fim de uma transação, a próxima declaração SQL inicia uma nova transação
automaticamente.
16.3
Efetivando as mudanças
Para tornar as mudanças no banco de dados permanentes, elas devem ser efetivadas. O
comando COMMIT é utilizado para efetivar as mudanças, e o comando ROLLBACK é
utilizado para descartar as mudanças.
Os comandos emitidos entre dois comandos COMMIT definem uma transação.
Até que o comando Commit seja executado, as mudanças são vistas apenas pelo usuário que
as efetuou, os demais usuários enxergam a situação anterior as modificações.
127
16.4
Removendo mudanças
Mudanças não efetivadas podem ser descartadas através do comando ROLLBACK.
O comando ROLLBACK retorna os dados ao estado em que se encontrava
após o último COMMIT.
16.5
Falhas do sistema
Quando uma transação é interrompida por um erro, como, por exemplo, uma falha do
sistema, toda a transação é descartada (Rolled back). Este procedimento previne que os erros
causem mudanças não desejadas aos dados.
O Rollback automático é mais freqüentemente causado por falhas no sistema, tais como falta
de luz ou queda do sistema operacional. Erros causados pela entrada de comandos, tais como
digitar o nome errado para uma coluna ou tentar realizar uma operação não permitida na
tabela de outro usuário, não interrompem uma transação, nem causam um Rollback
automático, porque estes erros são detectados na fase de 'parse' do comando (quando o
comando é lido e verificado), e não durante a fase de execução.
16.6
O significado de uma transação
O Oracle garante a consistência dos dados baseado nas transações. Transações fornecem
mais flexibilidade e controle para trabalhar com os dados. Por exemplo, quando um valor é
debitado de uma conta e creditado em outra, esta operação é uma transação, que deve ser
totalmente efetivada (Commit), ou descartada (Rollback), não pode haver o caso em que
existe o débito sem que haja o crédito correspondente.
16.7
Controlando transações
Os seguintes comandos SQL são utilizados para controlar uma transação:
16.8

COMMIT [WORK]

SAVEPOINT nome_do_savepoint

ROLLBACK [WORK]

ROLLBACK TO nome_do_savepoint
COMMIT [WORK]

Torna as mudanças causadas pela transação corrente permanente

Elimina todos os SavePoints

Termina a transação
128

Libera os Locks causados pela transação

A palavra WORK é opcional

Deve ser executada explicitamente e não implicitamente, caso contrário em caso de
término anormal do programa a última transação é desfeita (Rollback).

COMMIT implícito ou automático ocorre nas seguintes situações:
-
antes de um comando de DDL
-
após um comando de DDL
-
quando de uma desconexão normal
Comandos de DDL sempre geram um Commit quando são executados. Quando um
comando de DDL é executado após a execução de vários comandos de DML, o Commit é
executado antes do comando de DDL ser executado. Após o comando DDL ser executado,
se a execução não causar erro, esta também é efetivada.
16.9
SAVEPOINT savepoint_name

Pode ser utilizado para dividir uma transação em porções menores.

Permite guardar o trabalho até um determinado ponto, para que mais tarde seja
possível desfazê-lo totalmente, ou apenas até este determinado ponto.

Quando um novo SavePoint é criado com o mesmo nome de um anterior, o anterior é
eliminado.

O número máximo de SavePoints por processo de usuário é 5, por padrão. Este
padrão pode ser mudado.

Só existe Rollback to SavePoint, não existe Commit to SavePoint.
16.10 ROLLBACK[WORK] to [SAVEPOINT] savepoint_name

O comando Rollback é utilizado para desfazer alterações.

A palavra WORK é opcional.

Rollback sem To SavePoint causa:
-
Fim da transação
-
Desfaz as alterações
-
Elimina todos os SavePoints da transação
129
-
Libera os Locks da transação
16.11 Rollback a nível de declaração
Se uma única declaração de DML falha, somente esta transação é desfeita. Esta característica
é chamada de Rollback a nível de declaração, e permite que se efetue tanto o Rollback
quanto o Commit das transações anteriores a transação que falhou.
Se a transação for de DDL, o Commit executado antes da transação não permite o Rollback
das transações anteriores.
Oracle realiza um Rollback a nível de declaração através da criação de um SavePoint
implícito antes de cada transação de DML. Este SavePoint não é acessível diretamente pelo
usuário.
Quando é feito um ROLLBACK TO SAVEPOINT:

Uma porção da transação é desfeita.

mantém o SavePoint até onde foi feito o Rollback, mas perde os SavePoints criados
após o SavePoint para o qual o Rollback foi feito.

Libera tabelas e Locks a nível de linha.
16.12 Rollbacks implícitos
Rollbacks implícitos ocorrem em caso de término anormal
16.13 Autocommit
Commit pode ser executado automaticamente através do comando SET AUTOCOMMIT
ON. Neste caso, após cada comando INSERT, UPDATE ou DELETE é executado um
comando COMMIT automaticamente.
Com SET AUTOCOMMIT OFF, o próprio usuário tem que executar o comando COMMIT
manualmente.
O comando SET AUTOCOMMIT é um comando do SQL*PLUS, não do Oracle.
16.14 Exemplo de utilização de COMMIT e ROLLBACK
Para eliminar os departamentos com número maior do que 40.
SQL> DELETE FROM DEPARTMENT
2 WHERE DEPTNO > 40
5 rows deleted.
130
Para efetivar o comando anterior.
SQL> COMMIT
Commit complete.
Para verificar que os departamentos foram realmente eliminados.
SQL> SELECT * FROM DEPARTMENT
DEPTNO
------10
20
30
40
DNAME
-------------ACCOUNTING
RESEARCH
SALES
OPERATIONS
LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON
Para inserir o departamento 50, Testing, de Las Vegas.
SQL> INSERT INTO DEPARTMENT VALUES ( 50, 'TESTING', 'LAS VEGAS')
1 row created.
Para verificar como ficou a tabela após a inserção.
SQL> SELECT * FROM DEPARTMENT
DEPTNO
------10
20
30
40
50
DNAME
-------------ACCOUNTING
RESEARCH
SALES
OPERATIONS
TESTING
LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON
LAS VEGAS
Para criar um SavePoint neste ponto.
SQL> SAVEPOINT INSERT_DONE
Savepoint created.
Para, por erro, trocar o nome de todos os departamentos por MARKETING.
SQL> UPDATE DEPARTMENT
2 SET DNAME = 'MARKETING'
5 rows updated.
Para verificar o erro cometido.
SQL> SELECT * FROM DEPARTMENT
DEPTNO DNAME
LOC
------- -------------- ------------10 MARKETING
NEW YORK
20 MARKETING
DALLAS
30 MARKETING
CHICAGO
40 MARKETING
BOSTON
50 MARKETING
LAS VEGAS
131
Para desfazer o erro da última declaração, mas sem desfazer a inclusão do departamento 50.
SQL> ROLLBACK TO INSERT_DONE
Rollback complete.
Para verificar como ficou a tabela.
SQL> SELECT * FROM DEPARTMENT
DEPTNO
------10
20
30
40
50
DNAME
-------------ACCOUNTING
RESEARCH
SALES
OPERATIONS
TESTING
LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON
LAS VEGAS
Para atualizar apenas o nome do departamento SALES, como seria correto.
SQL> UPDATE DEPARTMENT
2 SET DNAME = 'MARKETING'
3 WHERE DNAME = 'SALES'
1 row updated.
Para efetivar todas as mudanças.
SQL> COMMIT
Commit complete.
Para verificar como ficou a tabela.
SQL> SELECT * FROM DEPARTMENT
DEPTNO
------10
20
30
40
50
DNAME
-------------ACCOUNTING
RESEARCH
MARKETING
OPERATIONS
TESTING
LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON
LAS VEGAS
Para desabilitar a efetivação automática (padrão).
SQL> SET AUTOCOMMIT OFF
Para eliminar todas as linhas da tabela.
SQL> DELETE FROM DEPARTMENT
5 rows deleted.
Para verificar como ficou a tabela.
132
SQL> SELECT * FROM DEPARTMENT
no rows selected
Para desfazer a eliminação das linhas.
SQL> ROLLBACK
Rollback complete.
Para verificar como ficou a tabela.
SQL> SELECT * FROM DEPARTMENT
DEPTNO
------10
20
30
40
50
DNAME
-------------ACCOUNTING
RESEARCH
MARKETING
OPERATIONS
TESTING
LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON
LAS VEGAS
Para habilitar a efetivação automática.
SQL> SET AUTOCOMMIT ON
Quando se apaga as linhas da tabela não há retorno com efetivação automática habilitada.
SQL> DELETE FROM DEPARTMENT
Commit complete.
5 rows deleted.
SQL> ROLLBACK
Rollback complete.
SQL> SELECT * FROM DEPARTMENT
no rows selected
16.15 Consistência de leitura
Usuários de banco de dados realizam dois tipos de acesso ao banco de dados:

Operações de leitura (comando Select)

Operações de gravação (comandos Insert, Update e Delete)
É necessário se garantir uma consistência de leitura tanto para os usuários que estão
gravando quanto para os usuários que estão lendo do banco de dados. Os usuários que estão
lendo não devem enxergar os dados que estão em processo de alteração. Os usuários que
estão gravando devem enxergar os dados da forma como foram alterados, mesmo que as
mudanças não tenham ainda sido efetivadas.
A finalidade da consistência de leitura é garantir que cada usuário veja os dados da forma
como existiam antes do último Commit.
133
A consistência de leitura é implementada mantendo-se uma cópia parcial do banco de dados
nos segmentos de Rollback.
Quando uma inserção, uma atualização ou uma eliminação é feita no banco de dados, o
Oracle faz uma cópia dos dados antes das mudanças, e guarda esta cópia no Segmento de
Rollback.
Todos os usuários que efetuam leituras, exceto aquele que causou as mudanças, enxergam o
banco de dados como ele existia antes das mudanças, através da cópia mantida nos
Segmentos de Rollback.
Antes das mudanças serem efetivadas, somente o usuário que modificou os dados enxerga o
banco de dados com as modificações incorporadas.
Após a efetivação das mudanças, todos os usuários passam a enxergar os dados com as
mudanças efetuadas, e os Segmentos de Rollback são liberados.
Quando ocorre um Rollback, os dados gravados nos Segmentos de Rollback são escritos de
volta nas tabelas.
16.16 Transações somente de leitura
Por padrão, o modelo de consistência do Oracle garante que os resultados de um comando
são consistentes. Entretanto, em algumas situações, pode ser necessário processar várias
consultas em dados de várias tabelas e garantir que os dados são consistentes, ou seja, os
resultados produzidos pela consulta à uma tabela são consistentes com os resultados das
consultas a qualquer outra tabela.
A declaração SQL “SET TRANSACTION READ ONLY” é utilizada para iniciar uma
transação de leitura apenas.
A consistência de leitura que READ ONLY provê é implementada da mesma maneira que a
consistência à nível de declaração - usando segmentos de rollback. Cada declaração por
padrão enxerga uma visão consistente dos dados na hora em que a declaração foi feita. Esta
funcionalidade é muito útil para relatórios que processam múltiplas consultas enquanto os
usuários atualizam as mesmas tabelas.
Notas:
 A declaração SET TRANSACTION READ ONLY deve ser a primeira da transação.
 Somente consultas são permitidas na transação.
 COMMIT, ROLLBACK ou uma declaração DDL terminam a transação. Com DDL
nenhuma indicação é dada que a transação terminou.
 Durante a transação, todas as consultas se referem ao mesmo instantâneo (snapshot)
do banco de dados (mudanças efetivadas antes da transação começar).
134
 Outros usuários podem continuar a atualizar os dados.
Exemplo:
SQL> SET TRANSACTION READ ONLY
Transaction set.
SQL> SELECT * FROM DEPT
DEPTNO
------10
20
30
40
DNAME
-------------ACCOUNTING
RESEARCH
SALES
OPERATIONS
LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON
SQL> COMMIT
Commit complete.
135
Capítulo 17: Concorrência e Bloqueio
17.1
Objetivos deste capítulo
 Mostrar como o Oracle manuseia os bloqueios (locks).
 Tipos e níveis de bloqueio.
 Bloqueio padrão.
 Bloqueio implícito e explícito.
 Bloqueio mortal (Deadlock).
Este capítulo não possui exercícios.
17.2
Introdução ao bloqueio
Uma das maiores tarefas de um sistema gerenciador de banco de dados (SGBD) é controlar a
concorrência, ou seja, o acesso aos mesmos dados por vários usuários. Sem um controle
correto de concorrência os dados podem ser atualizados incorretamente ou fora de
seqüência, comprometendo, portanto, a integridade dos dados.
Oracle resolve os problemas resultantes das atualizações concorrentes através de bloqueios.
Bloqueios de tabelas e linhas são uma parte essencial para manter a consistência e a
integridade do banco de dados.
Bloqueios são usados para:
 proteger os dados
 controlar os usuários
Qualquer estratégia de bloqueio deve balancear os objetivos de máxima concorrência, isto é,
permitir o maior número de usuários no sistema, com relação a máxima proteção dos dados.
17.3
O que é um bloqueio
O bloqueio é o mecanismo que é usado para controlar o acesso aos dados em um sistema
multi-usuário, prevenindo que dois usuários atualizem o mesmo dado ao mesmo tempo.
17.4
Quando os bloqueios são necessários
Bloqueios são ativados sempre que um usuário começa a realizar alterações no banco de
dados.
O Oracle permite que qualquer número de usuários leiam os dados ao mesmo tempo, porque
os bloqueios não são necessários para a leitura.
136
Usuários consultando dados nunca bloqueiam usuários modificando dados, e
usuários modificando dados nunca bloqueiam usuários lendo dados.
17.5
Quando os bloqueios são liberados
Os bloqueios são liberados após o Commit ou o Rollback, ou seja, no final da transação.
17.6
Tipos de bloqueios
17.6.1 Bloqueios do dicionário de dados (DDL)
 controla o acesso às definições dos objetos do banco de dados.
 usado para controlar as operações SQL que modificam o dicionário de dados, tais
como, Create Table, Alter Table, Drop Table...
 controlado automaticamente pelo SGBD Oracle.
17.6.2 Bloqueios da manipulação de dados (DML)
 controla o acesso aos dados nas tabelas dos usuários.
 o Oracle automaticamente bloqueia as tabelas que estão sendo atualizadas pelos
usuários (bloqueio implícito).
 o usuário pode solicitar o bloqueio através de comandos SQL (bloqueio explícito)
Este capítulo trata de bloqueios causados pela DML.
17.7
Níveis de bloqueio
 Bloqueio a nível de tabela - toda a tabela é bloqueada.
 Bloqueio a nível de linha - somente as linhas da tabela são bloqueadas.
17.8
Descrição dos bloqueios
Modo de bloqueio
Compartilhado
“Share locks”
Exclusivo
“Exclusive lock”
Severidade
Pode ser adquirido por mais de um usuário para a mesma
tabela ao mesmo tempo. Permite o compartilhamento do
recurso dependendo da operação.
Pode ser adquirido por apenas um usuário de cada vez. O
primeiro usuário a adquirir um bloqueio exclusivo é o
único que pode atualizar até o bloqueio exclusivo ser
liberado.
137
Níveis de Bloqueio
Nível de Linha
“Row Locks (TX)”
Nível de Tabela
“Table Locks (TM)”
Descrição
- Um bloqueio exclusivo é adquirido para cada linha
modificada pelos comandos INSERT, UPDATE,
DELETE ou SELECT FOR UPDATE.
- Uma linha é sempre bloqueada de forma exclusiva, para
não permitir outros usuários atualizar a mesma linha ao
mesmo tempo.
- Bloqueios de linha são sempre adquiridos
automaticamente pelo Oracle quando um dos comandos
listados anteriormente é executado.
- Existem diversos modos de bloqueio para tabelas.
- Um bloqueio para a tabela é adquirido quando a tabela
é modificada pelos comandos INSERT, UPDATE,
DELETE, SELECT FOR UPDATE ou LOCK TABLE.
- As operações de DML necessitam de bloqueio a nível
de tabela para impedir comandos de DDL serem
executados sobre uma tabela sendo modificada.
Descrição dos modos de bloqueio às tabelas.
Bloqueio de Tabela
Row Share (RS)
Row Exclusive (RX)
Share Lock (S)
Descrição
- Indica que a transação tem intenção de atualizar linhas
da tabela.
- Adquirido quando um dos comandos SELECT FOR
UPDATE ou LOCK TABLE IN ROW SHARE MODE é
executado.
- É o menos restritivo dos bloqueios, permitindo o maior
grau de concorrência para a tabela.
- Permite: consultas, inserções, atualizações , eliminação
e bloqueio de linhas da mesma tabela.
- Não permite: LOCK TABLE IN EXCLUSIVE MODE.
- Indica que a transação atualizou uma ou mais linhas da
tabela.
- É automaticamente adquirido quando um dos comandos
INSERT, UPDATE, DELETE ou LOCK TABLE IN
ROW EXCLUSIVE MODE é executado.
- É um pouco mais restritivo do que Row Share.
- Permite: consultas, inserções, atualizações,, eliminação
e bloqueios de linhas na mesma tabela.
- Não permite: LOCK TABLE IN SHARE MODE,
LOCK TABLE IN SHARE EXCLUSIVE MODE e
LOCK TABLE IN EXCLUSIVE MODE.
- É ativado pelo comando LOCK TABLE IN SHARE
MODE.
- Permite: consultas, SELECT FOR UPDATE e LOCK
TABLE IN SHARE MODE.
- Não permite: INSERT, DELETE, UPDATE, LOCK
TABLE IN SHARE ROW EXCLUSIVE MODE, LOCK
138
TABLE IN EXCLUSIVE MODE, LOCK TABLE IN
ROW EXCLUSIVE MODE.
Share Row Exclusive - É ativado pelo comando LOCK TABLE IN SHARE
(SRX)
ROW EXCLUSIVE MODE.
- Permite: consultas à tabela e bloqueios através do
comando SELECT FOR UPDATE.
- Não permite: outras transações atualizar a tabela.
Exclusive (X)
- É ativado pelo comando LOCK TABLE IN
EXCLUSIVE MODE.
- É o mais restritivo dos bloqueios.
- Permite: consultas à tabela.
- Não permite: qualquer operação de DML por outros
usuários.
A tabela abaixo indica os modos de bloqueio adquiridos pelos comandos SQL e as
operações que estes bloqueios permitem e proibem.
DECLARAÇÃO SQL
SELECT
INSERT
UPDATE
DELETE
SELECT FOR UPDATE
LOCK TABLE IN ROW SHARE
MODE
LOCK TABLE IN ROW EXCLUSIVE
MODE
LOCK TABLE IN SHARE MODE
LOCK TABLE IN SHARE ROW
EXCLUSIVE MODE
LOCK TABLE IN EXCLUSIVE
MODE
17.9
Modo
de lock
da
tabela
none
RX
RX
RX
RS
RS
RS
RX
S
SRX X
S
S
S*
S*
S*
S
S
S
S*
S*
S*
S
S
N
N
N
S*
S
S
N
N
N
S*
S
S
N
N
N
N
N
RX
S
S
N
N
N
S
SRX
S
S
N
N
Y
N
N
N
N
N
X
N
N
N
N
N
Bloqueios implícitos
Relação dos bloqueios adquiridos automaticamente pelo Oracle:
Comando
SELECT
INSERT
UPDATE
DELETE
SELECT FOR UPDATE
DDL
LINHA
X
X
X
X
139
TABELA
RX
RX
RX
RS
RX
17.10 Bloqueios explícitos
Comando
LOCK TABLE tabela IN ROW SHARE MODE
LOCK TABLE tabela IN ROW EXCLUSIVE MODE
LOCK TABLE tabela IN SHARE MODE
LOCK TABLE tabela IN SHARE EXCLUSIVE MODE
LOCK TABLE tabela IN EXCLUSIVE MODE
Bloqueio
RS
RX
S
SRX
X
17.11 Identificação da linha e bloqueio
É importante executar os comandos Commit ou Rollback o quanto antes para liberar os
bloqueios adquiridos pela transação.
O processo de efetivação pode ser acelerado utilizando ROWID para localizar as linhas
dentro das tabelas. ROWID é uma pseudo-coluna que tem um valor único para cada linha da
tabela. ROWID contém o endereço da linha, sendo, portanto, o meio mais rápido de acesso à
uma linha.
ROWID
00004C90.0001.0001
Descrição:
Bloco:
00004C90
Linha do bloco:
0001
Arquivo do banco de dados: 0001
ROWID é uma chave única para uma linha de uma tabela,
mesmo que a linha esteja repetida na tabela.
Exemplo:
COLUMN ROWID NEW_VALUE ROW_IDENT
SELECT ENAME, JOB, HIREDATE, SAL, ROWID
FROM EMP
WHERE ENAME = ‘SCOTT’
FOR UPDATE OF JOB, HIREDATE, SAL
140
UPDATE EMP
SET JOB = ‘SALESMAN’, HIREDATE = SYSDATE, SAL = 1.1 * SAL
WHERE ROWID = ‘&ROW_IDENT’
17.12 Impasse (Deadlock)
Imagine a seguinte situação:
 Transação A:
UPDATE EMP SET SAL = 1200 WHERE ENAME = ‘LEWIS’
 Transação B:
UPDATE DEPT SET LOC = ‘LONDON’ WHERE DEPTNO = 20
 Transação A:
UPDATE DEPT SET LOC = ‘RICHMOND’ WHERE DEPTNO = 20
 Transação B:
UPDATE EMP SET SAL = 1750 WHERE ENAME = ‘LEWIS’
Desta forma:
 a transação A só pode prosseguir após a transação B liberar a linha da tabela
DEPT bloqueada
 a transação B só pode prosseguir quando a transação A liberar a linha da tabela
EMP bloqueada
ou seja, temos um caso de DEADLOCK.
Deadlocks podem ocorrer sempre que dois ou mais usuários estão acessando as mesmas
tabelas do banco de dados. Ocorre quando o usuário ‘A’ está aguardando a liberação de uma
linha bloqueada pelo usuário ‘B’, e o usuário ‘B’ está na mesma situação com relação ao
usuário ‘A’.
Esta situação pode ser evitada quando os dois usuários acessando a mesma tabela o fazem na
mesma ordem. Desta forma um segue o outro, não havendo Deadlock.
Quando o acesso é feito a mais de uma tabela, deve ser estabelecida uma ordem de acesso às
tabelas para todas as aplicações. Pode ser criado para isto uma tabela no banco de dados,
com uma identificação única de sua ordem de acesso. As tabelas de menor ordem são
acessadas primeiro.
TABELA
LOCK
SEQÜÊNCIA
141
-----------------EMP
DEPT
SALGRADE
---------RS
RS
X
-----------------2
1
10
142
Capítulo 18 - Visões
18.1
Objetivos deste capítulo
Mostrar a criação e utilização de visões (views).
18.2
O que é uma visão
 Uma visão é como uma janela através da qual os dados das tabelas podem ser vistos
e alterados.
 Uma visão é derivada de uma tabela ou de outra visão, a qual é chamada de tabela ou
visão base. - uma tabela real com os dados fisicamente armazenados.
 Uma visão é armazenada na forma de um comando SELECT apenas. É uma tabela
virtual, ou seja, uma tabela que não existe fisicamente no banco de dados, mas parece
existir.
 Uma visão não tem dados próprios. Os dados são manipulados a partir das tabelas
base.
Visões são úteis pelas seguintes razões:
 Restringir o acesso ao banco de dados. Permite enxergar apenas parte das tabelas.
 Permite aos usuários executar consultas simples para obter resultados de consultas
complexas. Podem ser recuperados dados de várias tabelas como se fosse uma única
tabela.
 Provê independência de dados, permitindo alterar as tabelas base sem afetar as
aplicações dos usuários.
18.3
Classificação das visões
18.3.1 Visões simples
 dados derivados de uma única tabela
 não contém funções ou dados grupados.
18.3.2 Visões complexas
 dados derivados de múltiplas tabelas
 contém funções ou dados grupados.
143
18.4
O comando CREATE VIEW
Sintaxe:
CREATE VIEW nome_da_view [(coluna1, coluna2,...)]
AS
SELECT cláusula_select
[WITH CHECK OPTION [CONSTRAINT nome_da_restrição]]
Para criar uma visão simples chamada D10EMP, a partir da tabela EMP, contendo certos
detalhes dos funcionários do departamento 10:
SQL>
2
3
4
5
6
CREATE VIEW D10EMP
AS
SELECT EMPNO, ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = 10
WITH CHECK OPTION
View created.
Para recuperar os dados através da visão:
SQL> SELECT *
2 FROM D10EMP
EMPNO
------7782
7839
7934
ENAME
DEPTNO
---------- ------CLARK
10
KING
10
MILLER
10
Para criar uma visão complexa, chamada DEPT_SUMMARY, contendo funções de grupo e
dados de mais de uma tabela:
CREATE VIEW DEPT_SUMMARY ( NAME, MINSAL, MAXSAL, AVGSAL )
AS
SELECT DNAME, MIN(SAL), MAX(SAL), AVG(SAL)
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
GROUP BY DNAME
Note que nomes alternativos para as colunas foram especificados na visão, o que é
necessário quando os itens da cláusula Select não estão de acordo com as regras para nomes
de colunas, ou se alguma coluna é derivada de uma função ou expressão.
Quando um aliás é utilizado para o nome da coluna na cláusula Select, não é necessário
colocar um nome para a coluna na cláusula Create View.
CREATE VIEW DEPT20
AS
SELECT ENAME, SAL*12 ANNSAL
FROM EMP
WHERE DEPTNO = 20
144
18.5
Usando uma visão para operações de DML
Visões são poderosas porque permitem realizar verificação de integridade referencial nos
dados modificados através delas.
A cláusula WITH CHECK OPTION especifica que inserções e atualizações realizadas
através da visão não podem gerar linhas que a visão não pode enxergar. Se na visão
D10EMP tentarmos inserir um funcionário do departamento 20 um erro é gerado.
SQL> INSERT INTO D10EMP ( EMPNO, ENAME, DEPTNO )
2 VALUES ( 9999, 'MICHAEL', 20 )
INSERT INTO D10EMP ( EMPNO, ENAME, DEPTNO )
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
A visão EMP_DATA só permite inserções ou atualizações se o salário estiver na faixa 1000
a 2000, o gerente estiver cadastrado na tabela de empregados, e o departamento estiver
cadastrado na tabela de departamentos.
SQL>
2
3
4
5
6
7
8
CREATE VIEW EMP_DATA
AS
SELECT EMPNO, ENAME, JOB, MGR, SAL, DEPTNO
FROM EMP
WHERE SAL BETWEEN 1000 AND 2000
AND MGR IN ( SELECT DISTINCT EMPNO FROM EMP )
AND DEPTNO IN ( SELECT DEPTNO FROM DEPT )
WITH CHECK OPTION
View created.
A visão EMP_DETAILS restringe o acesso aos dados do próprio usuário, no período das 7
às 17 horas, de segunda a sexta-feira.
SQL>
2
3
4
5
6
7
8
CREATE VIEW EMP_DETAILS
AS
SELECT EMPNO, ENAME, JOB, DEPTNO
FROM EMP
WHERE ENAME = USER
AND TO_CHAR(SYSDATE,'HH24') BETWEEN 7 AND 17
AND TO_CHAR(SYSDATE,'D') BETWEEN 2 AND 6
WITH CHECK OPTION
View created.
A restrição WITH CHECK OPTION pode ter um nome próprio.
SQL>
2
3
4
5
6
CREATE VIEW EMPLOYEES ( ID_NUMBER, NAME, POSITION, DEPARTMENT )
AS
SELECT EMPNO, ENAME, JOB, DEPTNO
FROM EMP
WHERE DEPTNO IN ( SELECT DISTINCT DEPTNO FROM DEPT )
WITH CHECK OPTION CONSTRAINT DEPT_CHECK
View created.
145
Quando a visão é criada, o comando SELECT não é executado, o comando
SELECT é simplesmente armazenado no dicionário de dados.
Quando os dados são acessados através da visão, são realizadas as seguintes operações:
 Recuperação da definição da visão do dicionário de dados.
 Verificação dos privilégios de acesso.
 Converter a consulta da visão em uma operação equivalente sobre a(s) tabela(s) base.
Através da visão USER_VIEWS do dicionário de dados, a definição da visão pode ser
recuperada. Alguns parâmetros SET influenciam no texto mostrado:
 Maxdata
 Arraysize
 Long
SQL> DESCRIBE USER_VIEWS
Name
Null?
------------------------------- -------VIEW_NAME
NOT NULL
TEXT_LENGTH
TEXT
Type
---CHAR(30)
NUMBER
LONG
SQL> COLUMN VIEW_NAME FORMAT A15
SQL> COLUMN TEXT FORMAT A50
SQL> SELECT *
2 FROM USER_VIEWS
VIEW_NAME
TEXT_LENGTH TEXT
--------------- ----------- ------------------------------------------D10EMP
73 SELECT EMPNO, ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = 10
WITH CHECK OPTION
EMPLOYEES
113 SELECT EMPNO, ENAME, JOB, DEPTNO
FROM EMP
WHERE DEPTNO IN ( SELECT DISTINCT DEPT
EMP_DATA
190 SELECT EMPNO, ENAME, JOB, MGR, SAL, DEPTNO
FROM EMP
WHERE SAL BETWEEN 1000 AND 2
EMP_DETAILS
165 SELECT EMPNO, ENAME, JOB, DEPTNO
FROM EMP
WHERE ENAME = USER
AND TO_CHAR(SYSDATE
SALES
291 SELECT REPID,ORD.CUSTID,CUSTOMER.NAME CUSTNAME
PRODUCT.PRODID,
DESCRIP PRODNA
146
18.6
Alterando dados através das visões
As seguintes restrições se aplicam quando os dados são alterados através das visões:
Eliminação é proibida de a visão contém:
 Junção.
 Funções de grupo.
 Cláusula GROUP BY.
 Cláusula DISTINCT.
 Coluna ROWNUM.
 Sub-consulta correlacionada.
Atualização é proibida se a visão contém:
 Alguma das condições anteriores.
 Colunas definidas por expressão.
Inserção é proibida se a visão contém:
 Alguma das condições acima.
 Coluna NOT NULL da tabela que não consta da visão.
18.7
Eliminação de visões
As visões são eliminadas através do comando “DROP VIEW nome_da_visão”.
SQL> DROP VIEW D10EMP
View dropped.
SQL> DROP VIEW EMPLOYEES
View dropped.
SQL> DROP VIEW EMP_DATA
View dropped.
SQL> DROP VIEW EMP_DETAILS
View dropped.
147
18.8
Exercícios
a) Criar uma visão que produza o seguinte resultado.
SQL> SELECT * FROM AGGREGATES
DEPTNO
------10
20
30
AVERAGE MAXIMUM MINIMUM
SUM NO_SALS NO_COMMS
------- ------- ------- ------- ------- -------2916.67
5000
1300
8750
3
0
2175
3000
800
10875
5
0
1566.67
2850
950
9400
6
4
b) Utilizando a visão do exercício anterior, extrair as seguintes informações. O número do
empregado deve ser solicitado em tempo de execução.
EMPNO ENAME JOB
SAL HIREDATE MINIMUM MAXIMUM AVERAGE
------- ------ --------- ------- --------- ------- ------- ------7902 FORD
ANALYST
3,000 05-DEC-83
800
3,000
2,175
c) Criar uma visão que garanta as seguintes restrições ao se inserir dados na tabela
ASSIGNMENTS:
-
número do projeto menor do que 2000.
-
Término do projeto após o início do projeto.
-
ASSIGN_TYPE válidos são PF, WT e ED.
-
BILL_RATE menor do que 50 para ASSIGN_TYPE PF.
BILL_RATE menor do que 60 para ASSIGN_TYPE WT.
BILL_RATE menor do que 70 para ASSIGN_TYPE ED.
-
O número do empregado deve ser válido.
-
Não esqueça a cláusula WITH CHECK OPTION
SQL> SELECT * FROM ASG_VAL
PROJID
EMPNO A_START_D A_END_DAT BILL_RATE AS
HOURS
------- ------- --------- --------- --------- -- ------1
7369 01-JAN-88 03-JAN-88
50 WT
15
2
7844 01-JAN-89 10-JAN-89
45.5 PF
30
1
7902 04-JAN-88 07-JAN-88
55 WT
20
d) Inserir valores na tabela ASSIGNMENTS através da visão criada.
Enter
Enter
Enter
Enter
Enter
Enter
Enter
value
value
value
value
value
value
value
for
for
for
for
for
for
for
id: 1
empno: 7566
start: 01-JAN-89
end: 01-JAN-88
bill_r: 40.00
asgt: ED
hours: 20
old
3: (&ID,&EMPNO,'&START','&END','&BILL_R','&ASGT',&HOURS)
148
new
3: (1,7566,'01-JAN-89','01-JAN-88','40.00','ED',20)
(1,7566,'01-JAN-89','01-JAN-88','40.00','ED',20)
*
ERROR at line 3:
ORA-01402: view WITH CHECK OPTION where-clause violation
SQL> /
Enter value for id: 2
Enter value for empno: 7698
Enter value for start: 01-FEB-89
Enter value for end: 20-FEB-89
Enter value for bill_r: 55.00
Enter value for asgt: WT
Enter value for hours: 30
old
3: (&ID,&EMPNO,'&START','&END','&BILL_R','&ASGT',&HOURS)
new
3: (2,7698,'01-FEB-89','20-FEB-89','55.00','WT',30)
1 row created.
SQL> /
Enter value for id: 2
Enter value for empno: 8000
Enter value for start: 01-MAR-89
Enter value for end: 31-DEC-89
Enter value for bill_r: 69.00
Enter value for asgt: ED
Enter value for hours: 40
old
3: (&ID,&EMPNO,'&START','&END','&BILL_R','&ASGT',&HOURS)
new
3: (2,8000,'01-MAR-89','31-DEC-89','69.00','ED',40)
(2,8000,'01-MAR-89','31-DEC-89','69.00','ED',40)
*
ERROR at line 3:
ORA-01402: view WITH CHECK OPTION where-clause violation
e) Consulte o dicionário de dados para ver a cláusula SELECT da visão.
149
18.9
Respostas dos exercícios
a)
SQL>
2
3
4
5
6
7
CREATE VIEW AGGREGATES
(DEPTNO, AVERAGE, MAXIMUM, MINIMUM, SUM, NO_SALS, NO_COMMS)
AS
SELECT DEPTNO, AVG(SAL), MAX(SAL), MIN(SAL),
SUM(SAL), COUNT(SAL), COUNT(COMM)
FROM EMP
GROUP BY DEPTNO
b)
SQL> COLUMN MAXIMUM FORMAT 99,999
SQL> COLUMN MINIMUM LIKE MAXIMUM
SQL> COLUMN AVERAGE LIKE MAXIMUM
SQL> COLUMN SAL LIKE MAXIMUM
SQL> COLUM JOB FORMAT A9
SQL> COLUMN ENAME FORMAT A6
SQL> SELECT EMP.EMPNO, ENAME, JOB, SAL, HIREDATE,
2 MINIMUM, MAXIMUM, AVERAGE
3 FROM EMP, AGGREGATES AGG
4 WHERE EMP.DEPTNO = AGG.DEPTNO
5 AND EMP.EMPNO = &EMPNO
Enter value for empno: 7902
old
5: AND EMP.EMPNO = &EMPNO
new
5: AND EMP.EMPNO = 7902
c)
SQL>
2
3
4
5
6
7
8
9
10
11
CREATE VIEW ASG_VAL
AS
SELECT PROJID, EMPNO, A_START_DATE, A_END_DATE,
BILL_RATE, ASSIGN_TYPE, HOURS
FROM ASSIGNMENTS
WHERE A_START_DATE < A_END_DATE
AND PROJID < 2000
AND BILL_RATE <= DECODE(ASSIGN_TYPE, 'PF', 50, 'WT', 60, 70)
AND ASSIGN_TYPE IN ('PF', 'WT', 'ED')
AND EMPNO IN (SELECT EMPNO FROM EMP)
WITH CHECK OPTION
d)
SQL> INSERT INTO ASG_VAL
2 VALUES
3 (&ID,&EMPNO,'&START','&END','&BILL_R','&ASGT',&HOURS)
e)
SQL> SELECT VIEW_NAME, TEXT
2 FROM USER_VIEWS
3 WHERE VIEW_NAME = ‘ASG_VAL’
VIEW_NAME
TEXT
--------------- -------------------------------------------------ASG_VAL
SELECT PROJID, EMPNO, A_START_DATE, A_END_DATE,
BILL_RATE, ASSIGN_TYPE, HOURS
FR........
150
Capítulo 19 - Índices
19.1
Objetivos deste capítulo
Mostrar a criação e a utilidade dos índices.
19.2
Finalidades dos índices
Os índices do Oracle têm duas finalidades principais.
- Otimizar o tempo de resposta de uma consulta.
- Garantir unicidade de valores para uma coluna ou conjunto de colunas.
A utilização de índices é altamente recomendada para obter melhor desempenho, e
geralmente um dos primeiros índices a serem criados na tabela é o da chave primária.
Os índices são criados, normalmente, pelos donos das tabelas, mas qualquer usuário que
tenha privilégio de índice sobre a tabela também pode criar índices para a tabela.
Uma vez criado, o índice será utilizado pelo Oracle, sempre que for possível, para acelerar o
acesso aos dados. Note que os índices são utilizados automaticamente, sem requerer
qualquer ação por parte do usuário, que nem precisa saber da existência dos índices.
19.3
Estrutura dos índices
O Oracle utiliza árvores binárias balanceadas para os índices, o que garante o mesmo tempo,
aproximadamente, para o acesso a qualquer linha da tabela, independente de sua posição. O
tempo de acesso também é bastante independente do volume de dados indexados.
19.4
Tipos de índices
UNIQUE
Garante que os valores especificados para a(s) coluna(s) são únicos.
NON UNIQUE
É o padrão, usado para melhorar o tempo de acesso.
SINGLE COLUMN
Índice composto de apenas uma coluna.
CONCATENATED
Índice composto de até 16 colunas.
151
19.5
Criação dos índices
Os índices são criados juntamente com as tabelas, ou através do comando:
CREATE [UNIQUE] INDEX nome-do-índice
ON nome-da-tabela (coluna1, [,coluna2...])
19.5.1 Criação de um índice para melhorar o acesso.
Para criar um índice chamado ENAME_IDX para melhorar o tempo de acesso das consultas
feitas através do nome do funcionário:
CREATE INDEX ENAME_IDX
ON EMP(ENAME)
19.5.2 Criação de um índice para garantir unicidade.
Para evitar a duplicação do nome do departamento:
CREATE UNIQUE INDEX DNAME_IDX
ON DEPT(DNAME)
O índice abaixo garante que não há duplicidade do par fornecedor/material fornecido.
CREATE UNIQUE INDEX FORNEC_IDX
ON FORNECIMENTO (NUM_FORNECEDOR, NUM-MATERIAL)
19.6
Eliminação dos índices
Os índices são eliminados através do comando:
DROP INDEX nome-do-índice
19.7
Quando um índice é utilizado
O Oracle decide quando é apropriado utilizar um índice. O Oracle sabe quais colunas estão
indexadas e o tipo do índice, e decide de acordo com regras específicas.
a) A coluna indexada deve ser referenciada na cláusula Where.
A consulta mostrada abaixo não utiliza índice porque não possui cláusula Where.
SELECT
FROM
ENAME, JOB, SAL
EMP
A consulta abaixo utiliza o índice criado para a coluna ENAME:
SELECT
*
152
FROM
WHERE
EMP
ENAME = ‘JONES’
b) O índice não é utilizado se a coluna referenciada na cláusula Where é parte de uma função
ou de uma expressão.
No exemplo abaixo o índice não é utilizado porque a coluna ENAME é parte de uma
função:
SELECT
FROM
WHERE
*
EMP
UPPER(ENAME) = ‘JONES’
No exemplo abaixo o índice não é utilizado porque a coluna é parte de uma expressão:
SELECT
FROM
WHERE
19.8
*
EMP
HIREDATE+7 = ‘01-JAN-84’
Índices e Junções
Se não existirem índices nas colunas utilizadas para realizar a junção equivalente, o Oracle é
obrigado a realizar uma operação de SORT/MERGE para responder a consulta. Isto significa
que cada tabela é ordenada separadamente, e depois as duas são unidas de acordo com a
condição de junção.
19.9
Sugestões para criação dos índices
As colunas que não permitem valores duplicados devem ser indexadas.
As colunas que são utilizadas habitualmente na cláusula Where devem ser indexadas.
As colunas utilizadas nas condições de junção equivalente devem ser indexadas.
Não devem ser utilizados mais de 3 índices por tabela para não prejudicar as operações
realizadas através das operações de DML.
19.10 Exercícios
a) Criar um índice único na coluna PROJID da tabela PROJECTS. Teste o índice inserindo
um valor para PROJID já existente.
b) Criar um índice não único na coluna PROJID da tabela ASSIGNMENTS.
c) Consulte o dicionário de dados para obter as informações de seus índices.
153
19.11 Respostas dos exercícios
a) CREATE
ON
UNIQUE INDEX PROJ_PROJID
PROJECTS(PROJID)
b) CREATE
ON
INDEX ASG_PROJID
ASSIGNMENTS(PROJID)
c) SELECT
FROM
*
USER_INDEXES
154
Capítulo 20: Seqüências
20.1
Objetivos deste capítulo
Mostrar a criação e a utilização das seqüências.
20.2
O gerador de seqüências
O gerador de seqüências do Oracle é utilizado para gerar automaticamente seqüências de
números para as linhas das tabelas.
Para gerar os números seqüenciais automaticamente, primeiro a seqüência deve ser definida
utilizando a declaração CREATE SEQUENCE, conforme a sintaxe mostrada abaixo:
CREATE SEQUENCE [esquema.]nome-da-seqüência
[INCREMENTED BY n]
[START WITH n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
esquema
nome-da-seqüência
INCREMENT WITH
START WITH
MINVALUE|NOMINVALUE
MAXVALUE|NOMAXVALUE
identificação do dono da seqüência.
nome válido para a seqüência.
incremento positivo ou negativo. Padrão: 1.
primeiro número a ser gerado. Padrão: 1.
menor valor a ser gerado. Padrão: 1 para seqüências
ascendentes, 10**27-1 para seqüências descendentes.
maior valor a ser gerado. Padrão: 1 para seqüências
descendentes, 10**27-1 para seqüências ascendentes.
Para um usuário poder criar uma seqüência deve possuir o privilégio de ‘resource’. O
comando mostrada abaixo cria uma seqüência para a coluna DEPTNO da tabela DEPT.
CREATE SEQUENCE DEPT_SEQ
INCREMENT BY 10
START WITH 10
MAXVALUE 10000
20.1
Geração de números seqüenciais com NEXTVAL
A pseudo-coluna NEXTVAL é utilizada para gerar números seqüenciais sucessivos de uma
seqüência especificada. Quando a coluna NEXTVAL é especificada um novo número
seqüencial é gerado.
SELECT
FROM
DEPT_SEQ.NEXTVAL
SYS.DUAL
NEXTVAL
------10
Se o comando for executado novamente, o valor é incrementado de 10.
155
SELECT
FROM
DEPT_SEQ.NEXTVAL
SYS.DUAL
NEXTVAL
------20
Todo os valores subsequentes serão incrementados de 10.
Notas:
A coluna NEXTVAL sempre deve ser prefixada pelo nome da seqüência.
Se a coluna NEXTVAL for referenciada diversas vezes dentro do mesmo comando
SQL, todas as referências retornam o mesmo valor.
A coluna NEXTVAL é mais útil em comandos de DML. Por exemplo, quando são inseridas
linhas na tabela, a seqüência pode ser utilizada para gerar valores únicos para a chave
primária.
INSERT INTO DEPT
VALUES
(DEPT_SEQ.NEXTVAL, ‘ACCOUNTING’, ‘NEW YORK’)
Quando o número seqüencial é gerado, a seqüência é incrementada independentemente de
haver um Commit ou um Rollback para a transação. Quando dois usuários acessam a mesma
seqüência ao mesmo tempo, os dois usuários podem ver descontinuidades na seqüência,
devido a valores gerados para o outro usuário que ainda não receberam o Commit. Os
números produzidos pelo gerador de seqüências podem ser saltados devido a Rollbacks.
20.1
Valor atual da seqüência
Para se referir ao valor atual da seqüência, pode ser utilizada a pseudo-coluna CURRVAL.
Toda vez que a pseudo-coluna NEXTVAL é utilizada, o valor gerado é armazenado em
CURRVAL, que só pode ser utilizada após NEXTVAL ser referenciado na sessão atual do
usuário.
INSERT INTO DEPT_HISTORY
VALUES
(DEPT_SEQ.CURRVAL, ‘ACCOUNTING’, ‘NEW YORK’)
20.2
Regras para utilizar CURRVAL E NEXTVAL
Currval e Nextval podem ser utilizados na:
Cláusula SELECT da declaração SELECT (exceto para visões).
Lista de valores da declaração INSERT.
Cláusula SET da declaração UPDATE.
156
SELECT mais externo (consulta principal) de uma subconsulta.
Currval e Nextval não podem ser utilizados:
Em visões.
Com a palavra chave DISTINCT.
Com as cláusulas Order By, Group By, Connect By, ou Having da declaração
SELECT.
Com os operadores Intersect, Union e Minus.
Dentro de uma consulta interna.
Seqüências são tratadas de maneira similar às tabelas, podendo ser alteradas e eliminadas. O
dono de uma seqüência pode conceder privilégios a outros usuários.
20.3
Alterando uma seqüência
O comando ALTER SEQUENCE é utilizado para modificar uma seqüência existente.
ALTER SEQUENCE [esquema.]nome-da-sequencia
[INCREMENT BY n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
Por exemplo, para modificar o valor máximo para a seqüência DEPT_SEQ:
ALTER SEQUENCE dept_seq
MAXVALUE 100000
Notas:
Somente os números a serem gerados no futuro são alterados pelo comando Alter
Sequence. Os números que já foram gerados não são alterados.
Validações são realizadas. Não pode ser especificado, por exemplo, um novo valor
máximo menor que o valor corrente.
O parâmetro START WITH não pode ser alterado pelo comando Alter Sequence.
A seqüência deve ser eliminada e recriada para ser iniciada com um novo número.
20.4
Concedendo privilégios em seqüências
O dono de uma seqüência pode conceder os privilégios Select e Alter.
20.5
Eliminando uma seqüência
157
O comando Drop Sequence remove a definição da seqüência do dicionário de dados.
DROP SEQUENCE [esquema.]nome-da-seqüência
Apenas o dono da seqüência e o DBA podem utilizar este comando.
20.6
Listando seqüências
As visões USER_SEQUENCES ou ALL_SEQUENCES podem ser utilizadas para descobrir
as seqüências existentes.
158
Apêndice A - Tabelas do Curso
Tabela EMP - Empregados
EMPNO
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
ENAME
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
JOB
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
MGR
7902
7698
7698
7839
7698
7839
7839
7566
7698
7788
7698
7566
7782
HIREDATE
13-JUN-83
15-AUG-83
26-MAR-83
31-OCT-83
05-DEC-83
11-JUN-84
14-MAY-84
05-MAR-84
09-JUL-84
04-JUN-84
04-JUN-84
23-JUL-84
05-DEC-83
21-NOV-83
SAL
800
1600
1250
2975
1250
2850
2450
3000
5000
1500
1100
950
3000
1300
Tabela DEPT - Departamentos
DEPTNO
10
20
30
40
DNAME
ACCOUNTING
RESEARCH
SALES
OPERATIONS
LOC
NEW YORK
DALLAS
CHICAGO
BOSTON
Tabela SALGRADE - Faixas Salariais
GRADE
1
2
3
4
5
LOSAL
700
1201
1401
2001
3001
159
HISAL
1200
1400
2000
3000
9999
COMM
300
50
1400
0
DEPTNO
20
30
30
20
30
30
10
20
10
30
20
30
20
10
CAPÍTULO 1: CONCEITOS DE BANCO DE DADOS ............................................................................................... 1
1.1 OBJETIVOS DESTE CAPÍTULO .......................................................................................................................................... 1
1.2 SISTEMA DE GERENCIAMENTO DE BANCO DE DADOS .................................................................................................... 1
1.3 BANCO DE DADOS RELACIONAL ..................................................................................................................................... 1
1.4 ARQUITETURA DO ORACLE ............................................................................................................................................ 4
CAPÍTULO 2: INTRODUÇÃO ÀS CONSULTAS........................................................................................................ 6
2.1 OBJETIVOS DESTE CAPÍTULO .......................................................................................................................................... 6
2.2 O UTILITÁRIO SQL*PLUS.............................................................................................................................................. 6
2.3 CONSTRUÇÃO BÁSICA DE UMA CONSULTA...................................................................................................................... 6
2.4 EXPRESSÕES ARITMÉTICAS............................................................................................................................................. 7
2.5 NOMES ALTERNATIVOS PARA TÍTULOS DE COLUNAS....................................................................................................... 9
2.6 O OPERADOR DE CONCATENAÇÃO .................................................................................................................................. 9
2.7 LITERAIS ...................................................................................................................................................................... 10
2.8 MANUSEIO DE VALORES NULOS.................................................................................................................................... 10
2.9 ELIMINAÇÃO DE LINHAS DUPLICADAS .......................................................................................................................... 12
2.10 ORDENAÇÃO DAS LINHAS ........................................................................................................................................... 13
2.11 CRITÉRIOS DE PESQUISA ............................................................................................................................................. 14
2.12 CONSULTAS COM CONDIÇÕES MÚLTIPLAS .................................................................................................................. 18
2.13 PRECEDÊNCIA DOS OPERADORES................................................................................................................................ 19
2.14 RESUMO DO COMANDO SELECT ............................................................................................................................... 20
2.15 EXERCÍCIOS................................................................................................................................................................ 20
2.16 RESPOSTAS DOS EXERCÍCIOS ...................................................................................................................................... 23
CAPÍTULO 3: VARIÁVEIS DE SUBSTITUIÇÃO ..................................................................................................... 24
3.1 OBJETIVOS DESTE CAPÍTULO ........................................................................................................................................ 24
3.2 VARIÁVEIS DE SUBSTITUIÇÃO COM UM "&" ................................................................................................................. 24
3.3 VARIÁVEIS DE SUBSTITUIÇÃO COM DOIS "&" ............................................................................................................... 24
CAPÍTULO 4: FUNÇÕES NUMÉRICAS E DE CARACTERES.............................................................................. 25
4.1 OBJETIVOS DESTE CAPÍTULO ........................................................................................................................................ 25
4.2 FUNÇÕES QUE MANIPULAM CARACTERES .................................................................................................................... 25
4.2.1 LOWER(coluna | literal) ..................................................................................................................................... 25
4.2.2 UPPER(coluna | literal) ...................................................................................................................................... 25
4.2.3 INITCAP(coluna | literal) ................................................................................................................................... 25
4.2.4 LPAD(coluna | literal, tamanho, 'caracter') ....................................................................................................... 25
4.2.5 RPAD(coluna | literal, tamanho, 'caracter') ....................................................................................................... 26
4.2.6 SUBSTR(coluna | literal, posição, comprimento) ............................................................................................... 26
4.2.7 INSTR(coluna | literal, ‘seqüência de caracteres', posição, n) ........................................................................... 26
4.2.8 LTRIM(coluna | literal, 'caracteres')................................................................................................................... 27
4.2.9 RTRIM(coluna | literal, 'caracteres') .................................................................................................................. 27
4.2.10 SOUNDEX(coluna | literal) .............................................................................................................................. 27
4.2.11 LENGTH(coluna | literal) ................................................................................................................................. 28
4.2.12 TRANSLATE(coluna | literal, de, para) ............................................................................................................ 28
4.2.13 REPLACE(coluna | literal, de, para) ................................................................................................................ 28
4.2.14 Funções aninhadas............................................................................................................................................ 29
4.3 FUNÇÕES NUMÉRICAS .................................................................................................................................................. 30
4.3.1 ROUND(coluna | literal, n) ................................................................................................................................. 30
4.3.2 TRUNC(coluna | literal, n) .................................................................................................................................. 30
4.3.3 CEIL(coluna | literal) .......................................................................................................................................... 30
4.3.4 FLOOR(coluna | literal) ...................................................................................................................................... 30
4.3.5 POWER(coluna | literal, n) ................................................................................................................................. 31
4.3.6 EXP(coluna | literal) ........................................................................................................................................... 31
4.3.7 LOG(base, coluna | literal) ................................................................................................................................. 31
4.3.8 LN(coluna | literal).............................................................................................................................................. 31
4.3.9 SQRT(coluna | literal) ......................................................................................................................................... 31
4.3.10 SIGN(coluna | literal) ........................................................................................................................................ 32
4.3.11 ABS(coluna | literal).......................................................................................................................................... 32
4.3.12 MOD(valor1, valor2) ........................................................................................................................................ 32
160
4.3.13 Funções trigonométricas ................................................................................................................................... 33
4.3.14 Funções hiperbólicas ........................................................................................................................................ 33
4.4 EXERCÍCIOS.................................................................................................................................................................. 33
4.5 RESPOSTAS DOS EXERCÍCIOS ........................................................................................................................................ 36
CAPÍTULO 5: FUNÇÕES DE DATA E DE CONVERSÃO ...................................................................................... 37
5.1 OBJETIVOS DESTE CAPÍTULO ........................................................................................................................................ 37
5.2 FUNÇÕES QUE MANIPULAM DATAS ............................................................................................................................... 37
5.2.1 SYSDATE............................................................................................................................................................. 37
5.2.2 Aritmética de datas.............................................................................................................................................. 37
5.2.3 MONTHS_BETWEEN(data1,data2) ................................................................................................................... 37
5.2.4 ADD_MONTHS(data, meses).............................................................................................................................. 38
5.2.5 NEXT_DAY(data, dia_da_semana) ..................................................................................................................... 38
5.2.6 LAST_DAY(data) ................................................................................................................................................. 38
5.2.7 ROUND(data) ..................................................................................................................................................... 39
5.2.7 TRUNC(data) ...................................................................................................................................................... 39
5.3 FUNÇÕES DE CONVERSÃO ............................................................................................................................................ 39
5.3.1 Formatos de Data ............................................................................................................................................... 39
5.3.2 TO_CHAR(número|data,['formato']) .................................................................................................................. 40
5.3.3 Formatos Numéricos ........................................................................................................................................... 41
5.3.4 TO_NUMBER(cadeia_de_caracteres) ................................................................................................................ 42
5.3.5 TO_DATE('cadeia_de_caracteres','formato') ..................................................................................................... 42
5.4 FUNÇÕES QUE ACEITAM QUALQUER TIPO DE DADO ...................................................................................................... 42
5.4.1 DECODE(coluna|expressão,pes1,res1,[pes2,res2,...],default) ........................................................................... 42
5.4.2 NVL(coluna|valor, valor) .................................................................................................................................... 44
5.4.3 GREATEST(coluna|valor, coluna|valor,...) ........................................................................................................ 44
5.4.4 LEAST(coluna|valor, coluna|valor,...) ................................................................................................................ 44
5.4.5 VSIZE(coluna|valor) ........................................................................................................................................... 45
5.4.5 Funções aninhadas revisitadas ........................................................................................................................... 45
5.5 EXERCÍCIOS.................................................................................................................................................................. 45
5.6 RESPOSTAS DOS EXERCÍCIOS ........................................................................................................................................ 48
CAPITULO 6: FUNÇÕES DE GRUPO........................................................................................................................ 49
6.1 OBJETIVOS DESTE CAPÍTULO ........................................................................................................................................ 49
6.2 FUNÇÕES DISPONÍVEIS.................................................................................................................................................. 49
6.3 USANDO FUNÇÕES DE GRUPO ....................................................................................................................................... 49
6.4 A CLÁUSULA GROUP BY ............................................................................................................................................ 50
6.5 EXCLUINDO LINHAS DOS GRUPOS ................................................................................................................................. 50
6.6 GRUPOS DENTRO DE GRUPOS ....................................................................................................................................... 51
6.7 FUNÇÕES DE GRUPO E RESULTADOS INDIVIDUAIS ......................................................................................................... 51
6.8 A CLÁUSULA HAVING ................................................................................................................................................ 52
6.9 ORDEM DAS CLÁUSULAS .............................................................................................................................................. 53
6.10 EXERCÍCIOS................................................................................................................................................................ 54
6.11 RESPOSTAS DOS EXERCÍCIOS ...................................................................................................................................... 56
CAPÍTULO 7: EXTRAINDO DADOS DE MAIS DE UMA TABELA ..................................................................... 57
7.1 OBJETIVOS DESTE CAPÍTULO ........................................................................................................................................ 57
7.2 JUNÇÕES EQUIVALENTES .............................................................................................................................................. 57
7.3 PRODUTO ..................................................................................................................................................................... 58
7.4 JUNÇÕES NÃO EQUIVALENTES ...................................................................................................................................... 58
7.5 REGRA PARA JUNÇÃO DE TABELAS ............................................................................................................................... 59
7.6 SUMÁRIO DA SINTAXE .................................................................................................................................................. 59
7.7 EXERCÍCIOS.................................................................................................................................................................. 59
7.8 SOLUÇÃO DOS EXERCÍCIOS ........................................................................................................................................... 62
CAPÍTULO 8: OUTROS MÉTODOS DE JUNÇÃO .................................................................................................. 63
8.1 OBJETIVOS DESTE CAPÍTULO ........................................................................................................................................ 63
8.2 JUNÇÕES EXTERNAS ..................................................................................................................................................... 63
8.3 JUNÇÃO DE UMA TABELA COM ELA MESMO .................................................................................................................. 63
8.4 JUNÇÕES VERTICAIS ..................................................................................................................................................... 64
161
8.4.1 União ................................................................................................................................................................... 64
8.4.2 Interseção ............................................................................................................................................................ 64
8.4.3 Subtração ............................................................................................................................................................ 65
8.4.4 Combinação de operadores ................................................................................................................................ 65
8.4.5 A cláusula ORDER BY ........................................................................................................................................ 65
8.4.6 Regras para utilizar junções verticais ................................................................................................................. 65
8.5 EXERCÍCIOS.................................................................................................................................................................. 66
8.6 RESPOSTAS DOS EXERCÍCIOS ........................................................................................................................................ 68
CAPÍTULO 9: CONSULTAS ANINHADAS ............................................................................................................... 69
9.1 OBJETIVOS DESTE CAPÍTULO ........................................................................................................................................ 69
9.2 DEFINIÇÃO DE CONSULTAS ANINHADAS ....................................................................................................................... 69
9.3 CONSULTAS INTERNAS QUE RETORNAM APENAS UM VALOR ......................................................................................... 69
9.4 COMO AS CONSULTAS ANINHADAS SÃO EXECUTADAS .................................................................................................. 70
9.5 CONSULTAS INTERNAS QUE RETORNAM MAIS DE UM VALOR ........................................................................................ 70
9.6 OPERADORES ANY E ALL ........................................................................................................................................... 71
9.7 CLÁUSULA HAVING COM CONSULTAS ANINHADAS .................................................................................................... 72
9.8 ORDENAÇÃO EM CONSULTAS ANINHADAS .................................................................................................................... 73
9.9 LIMITE PARA O ANINHAMENTO ..................................................................................................................................... 73
9.10 CONSULTA INTERNA CORRELACIONADA ..................................................................................................................... 73
9.11 O OPERADOR EXISTS................................................................................................................................................ 74
9.12 EXERCÍCIOS................................................................................................................................................................ 75
9.13 RESPOSTA DOS EXERCÍCIOS ........................................................................................................................................ 78
CAPÍTULO 10: GERAÇÃO DE RELATÓRIOS ........................................................................................................ 80
10.1 OBJETIVOS DESTE CAPÍTULO ...................................................................................................................................... 80
10.2 CONJUNTO DE COMANDOS SET DO SQL*PLUS ........................................................................................................ 80
10.3 COLUMN ................................................................................................................................................................. 80
10.3.1 Formato de exibição para as colunas ............................................................................................................... 81
10.3.2 Outras opções de exibição das colunas. ........................................................................................................... 81
10.4 EXEMPLO DE FORMATAÇÃO DE COLUNAS................................................................................................................... 81
10.5 EXEMPLO DE FORMATAÇÃO DE TÍTULO ...................................................................................................................... 82
10.6 EXEMPLO DE REMOÇÃO DA FORMATAÇÃO ................................................................................................................. 83
CAPÍTULO 11: GERAÇÃO DE RELATÓRIOS - PARTE 2 .................................................................................... 84
11.1 OBJETIVOS DESTE CAPÍTULO ...................................................................................................................................... 84
11.2 PROPRIEDADES DOS CABEÇALHOS E DOS RODAPÉS .................................................................................................... 84
11.3 A CLÁUSULA NEW_VALUE ..................................................................................................................................... 85
11.4 QUEBRAS NOS RELATÓRIOS ........................................................................................................................................ 86
11.5 CÁLCULO DE SUMÁRIOS ............................................................................................................................................. 87
11.6 RELATÓRIO MATRICIAL .............................................................................................................................................. 89
11.7 ARQUIVO DE COMANDOS PARA O SQL*PLUS ........................................................................................................... 91
11.8 EXERCÍCIOS................................................................................................................................................................ 93
11.9 RESPOSTAS DOS EXERCÍCIOS ...................................................................................................................................... 95
CAPÍTULO 12: HIERARQUIAS - CAMINHANDO NA ÁRVORE ......................................................................... 96
12.1 OBJETIVOS DESTE CAPÍTULO ...................................................................................................................................... 96
12.2 QUANDO É POSSÍVEL CAMINHAR NA ÁRVORE ............................................................................................................. 96
12.3 EXCLUINDO UM NÓ DA ÁRVORE ................................................................................................................................. 98
12.4 TERMINOLOGIA ........................................................................................................................................................ 100
12.5 EXERCÍCIOS.............................................................................................................................................................. 100
CAPÍTULO 13: DICIONÁRIO DE DADOS .............................................................................................................. 101
13.1 OBJETIVOS DESTE CAPÍTULO .................................................................................................................................... 101
13.2 O QUE É O DICIONÁRIO DE DADOS ............................................................................................................................ 101
13.3 INFORMAÇÕES CONTIDAS NO DICIONÁRIO DE DADOS ............................................................................................... 101
13.4 TABELAS DO DICIONÁRIO DE DADOS ........................................................................................................................ 101
13.5 VISÕES DO DICIONÁRIO DE DADOS............................................................................................................................ 101
13.6 EXEMPLOS DE UTILIZAÇÃO DO DICIONÁRIO DE DADOS ............................................................................................. 106
13.7 EXERCÍCIOS.............................................................................................................................................................. 108
162
CAPÍTULO 14: LINGUAGEM DE DEFINIÇÃO DE DADOS................................................................................ 110
14.1 OBJETIVOS DESTE CAPÍTULO .................................................................................................................................... 110
14.2 ESTRUTURA DE DADOS DO ORACLE ......................................................................................................................... 110
14.3 CRIANDO UMA TABELA ............................................................................................................................................ 110
14.4 RECOMENDAÇÕES SOBRE OS NOMES DAS TABELAS .................................................................................................. 111
14.5 TIPO DE DADO DAS COLUNAS ................................................................................................................................... 111
14.6 RELAÇÃO ENTRE OS TIPOS DE DADOS ANSI E DO ORACLE ....................................................................................... 112
14.7 RELAÇÃO ENTRE OS TIPOS DE DADOS DO SQL/DS E DO ORACLE ............................................................................. 112
14.8 CRIAÇÃO DE TABELAS .............................................................................................................................................. 112
14.8.1 Sintaxe do comando: ....................................................................................................................................... 112
14.8.2 Criação das tabelas do curso: ........................................................................................................................ 113
14.8.3 Exemplos de restrições .................................................................................................................................... 114
14.9 CRIANDO UMA TABELA A PARTIR DE OUTRA TABELA................................................................................................ 115
14.10 ALTERANDO UMA TABELA ..................................................................................................................................... 116
14.11 TROCANDO O NOME DE UMA TABELA ..................................................................................................................... 117
14.12 DESCRIÇÃO DAS TABELAS E COLUNAS .................................................................................................................... 118
14.13 REMOVENDO TABELAS ........................................................................................................................................... 118
14.14 EXERCÍCIOS............................................................................................................................................................ 118
CAPÍTULO 15: LINGUAGEM DE MANIPULAÇÃO DE DADOS........................................................................ 121
15.1 OBJETIVOS DESTE CAPÍTULO .................................................................................................................................... 121
15.2 INSERINDO NOVAS LINHAS EM UMA TABELA ............................................................................................................ 121
15.3 ATUALIZANDO LINHAS EM UMA TABELA .................................................................................................................. 123
15.4 ELIMINANDO LINHAS DE UMA TABELA...................................................................................................................... 124
15.5 EXERCÍCIOS.............................................................................................................................................................. 125
15.6 RESPOSTAS DOS EXERCÍCIOS .................................................................................................................................... 126
CAPÍTULO 16: PROCESSAMENTO DE TRANSAÇÕES ...................................................................................... 127
16.1 OBJETIVOS DESTE CAPÍTULO .................................................................................................................................... 127
16.2 O QUE É UMA TRANSAÇÃO ....................................................................................................................................... 127
16.3 EFETIVANDO AS MUDANÇAS..................................................................................................................................... 127
16.4 REMOVENDO MUDANÇAS ......................................................................................................................................... 128
16.5 FALHAS DO SISTEMA ................................................................................................................................................ 128
16.6 O SIGNIFICADO DE UMA TRANSAÇÃO ........................................................................................................................ 128
16.7 CONTROLANDO TRANSAÇÕES................................................................................................................................... 128
16.8 COMMIT [WORK] ................................................................................................................................................. 128
16.9 SAVEPOINT SAVEPOINT_NAME ............................................................................................................................. 129
16.10 ROLLBACK[WORK] TO [SAVEPOINT] SAVEPOINT_NAME............................................................................... 129
16.11 ROLLBACK A NÍVEL DE DECLARAÇÃO..................................................................................................................... 130
16.12 ROLLBACKS IMPLÍCITOS ......................................................................................................................................... 130
16.13 AUTOCOMMIT ........................................................................................................................................................ 130
16.14 EXEMPLO DE UTILIZAÇÃO DE COMMIT E ROLLBACK........................................................................................ 130
16.15 CONSISTÊNCIA DE LEITURA .................................................................................................................................... 133
16.16 TRANSAÇÕES SOMENTE DE LEITURA ...................................................................................................................... 134
CAPÍTULO 17: CONCORRÊNCIA E BLOQUEIO ................................................................................................. 136
17.1 OBJETIVOS DESTE CAPÍTULO .................................................................................................................................... 136
17.2 INTRODUÇÃO AO BLOQUEIO ..................................................................................................................................... 136
17.3 O QUE É UM BLOQUEIO ............................................................................................................................................. 136
17.4 QUANDO OS BLOQUEIOS SÃO NECESSÁRIOS .............................................................................................................. 136
17.5 QUANDO OS BLOQUEIOS SÃO LIBERADOS ................................................................................................................. 137
17.6 TIPOS DE BLOQUEIOS ................................................................................................................................................ 137
17.6.1 Bloqueios do dicionário de dados (DDL) ....................................................................................................... 137
17.6.2 Bloqueios da manipulação de dados (DML) ................................................................................................... 137
17.7 NÍVEIS DE BLOQUEIO ................................................................................................................................................ 137
17.8 DESCRIÇÃO DOS BLOQUEIOS .................................................................................................................................... 137
17.9 BLOQUEIOS IMPLÍCITOS ............................................................................................................................................ 139
17.10 BLOQUEIOS EXPLÍCITOS.......................................................................................................................................... 140
17.11 IDENTIFICAÇÃO DA LINHA E BLOQUEIO ................................................................................................................... 140
163
17.12 IMPASSE (DEADLOCK)............................................................................................................................................ 141
CAPÍTULO 18 - VISÕES ............................................................................................................................................. 143
18.1 OBJETIVOS DESTE CAPÍTULO .................................................................................................................................... 143
18.2 O QUE É UMA VISÃO ................................................................................................................................................. 143
18.3 CLASSIFICAÇÃO DAS VISÕES ..................................................................................................................................... 143
18.3.1 Visões simples ................................................................................................................................................. 143
18.3.2 Visões complexas............................................................................................................................................. 143
18.4 O COMANDO CREATE VIEW ................................................................................................................................. 144
18.5 USANDO UMA VISÃO PARA OPERAÇÕES DE DML ..................................................................................................... 145
18.6 ALTERANDO DADOS ATRAVÉS DAS VISÕES ............................................................................................................... 147
18.7 ELIMINAÇÃO DE VISÕES ........................................................................................................................................... 147
18.8 EXERCÍCIOS.............................................................................................................................................................. 148
18.9 RESPOSTAS DOS EXERCÍCIOS .................................................................................................................................... 150
CAPÍTULO 19 - ÍNDICES ........................................................................................................................................... 151
19.1 OBJETIVOS DESTE CAPÍTULO .................................................................................................................................... 151
19.2 FINALIDADES DOS ÍNDICES ....................................................................................................................................... 151
19.3 ESTRUTURA DOS ÍNDICES ......................................................................................................................................... 151
19.4 TIPOS DE ÍNDICES ..................................................................................................................................................... 151
19.5 CRIAÇÃO DOS ÍNDICES .............................................................................................................................................. 152
19.5.1 Criação de um índice para melhorar o acesso. .............................................................................................. 152
19.5.2 Criação de um índice para garantir unicidade. .............................................................................................. 152
19.6 ELIMINAÇÃO DOS ÍNDICES ........................................................................................................................................ 152
19.7 QUANDO UM ÍNDICE É UTILIZADO ............................................................................................................................. 152
19.8 ÍNDICES E JUNÇÕES .................................................................................................................................................. 153
19.9 SUGESTÕES PARA CRIAÇÃO DOS ÍNDICES .................................................................................................................. 153
19.10 EXERCÍCIOS............................................................................................................................................................ 153
19.11 RESPOSTAS DOS EXERCÍCIOS .................................................................................................................................. 154
CAPÍTULO 20: SEQÜÊNCIAS................................................................................................................................... 155
20.1 OBJETIVOS DESTE CAPÍTULO .................................................................................................................................... 155
20.2 O GERADOR DE SEQÜÊNCIAS .................................................................................................................................... 155
20.1 GERAÇÃO DE NÚMEROS SEQÜENCIAIS COM NEXTVAL .......................................................................................... 155
20.1 VALOR ATUAL DA SEQÜÊNCIA .................................................................................................................................. 156
20.2 REGRAS PARA UTILIZAR CURRVAL E NEXTVAL ................................................................................................. 156
20.3 ALTERANDO UMA SEQÜÊNCIA .................................................................................................................................. 157
20.4 CONCEDENDO PRIVILÉGIOS EM SEQÜÊNCIAS ............................................................................................................ 157
20.5 ELIMINANDO UMA SEQÜÊNCIA ................................................................................................................................. 157
20.6 LISTANDO SEQÜÊNCIAS ............................................................................................................................................ 158
APÊNDICE A - TABELAS DO CURSO .................................................................................................................... 159
164