Apostila Oracle

1

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

2

Os operadores relacionais são:

Seleção/Restriçã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.

Projeção é uma operação que recupera e mostra dados de apenas algumas colunas, e portanto é chamada de subconjunto vertical.

União mostra todas as linhas que aparecem em duas tabelas. Interseção mostra todas as linhas que aparecem nas duas tabelas. Diferença mostra todas as linhas que aparecem em apenas uma das

tabelas Produto é 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.

Junção é 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

3

União

Interseção

Diferença

4

Produto

João x Pintor = João Pintor José Escritor João Escritor Manoel José Pintor José Escritor Manoel Pintor Manoel Escritor

Junção

João 10 10 Vendas João 10 10 Vendas José 10 20 Pesq. José 10 10 Vendas Maria 20 30 Cont. Maria 20 20 Pesq. Ana 30 40 Secret. Ana 30 30 Cont.

1.4 Arquitetura do Oracle

Background Processes

System Global Area

Servers

Users

Redo Log Files

Data Files

Control Files

Banco de Dados

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

5

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.

6

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: • realizar cálculos • manusear valores nulos corretamente • nomes alternativos para títulos das colunas • concatenar colunas • ordenar linhas • fornecer critérios de pesquisa

2.2 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:

7

SQL> SELECT DEPTNO, ENAME, MGR 2 FROM EMP DEPTNO ENAME MGR ------- ---------- ------- 20 SMI TH 7902 30 ALLEN 7698 30 WARD 7698 20 JONES 7839 30 MARTIN 7698 30 BLAKE 7839 10 CLARK 7839 20 SCOTT 7566 10 KING 30 TURNER 7698 20 ADAMS 7788 30 JAMES 7698 20 FORD 7566 10 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 ENAME JOB MGR HIREDATE SA L COMM DEPTNO ------- ---------- --------- ------- --------- ---- --- ------- ------- 7369 SMITH CLERK 7902 13-JUN- 83 800 20 7499 ALLEN SALESMAN 7698 15-AUG- 83 1600 300 30 7521 WARD SALESMAN 7698 26-MAR- 84 1250 500 30 7566 JONES MANAGER 7839 31-OCT- 83 2975 20 7654 MARTIN SALESMAN 7698 05-DEC- 83 1250 1400 30 7698 BLAKE MANAGER 7839 11-JUN- 84 2850 30 7782 CLARK MANAGER 7839 14-MAY- 84 2450 10 7788 SCOTT ANALYST 7566 05-MAR-84 3000 20 7839 KING PRESIDENT 09-JUL- 84 5000 10 7844 TURNER SALESMAN 7698 04-JUN- 84 1500 0 30 7876 ADAMS CLERK 7788 04-JUN-84 1 100 20 7900 JAMES CLERK 7698 23-JUL- 84 950 30 7902 FORD ANALYST 7566 05-DEC- 83 3000 20 7934 MILLER 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

8

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

9

BLAKE 37200 CLARK 32400 SCOTT 39000 KING 63000 TURNER 21000 ADAMS 16200 JAMES 14400 FORD 39000 MILLER 18600

2.5 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”, COM M 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

10

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> COLUMN EMPREGADO FORMAT A20 SQL> SELECT EMPNO||'-'||ENAME EMPREGADO, 2 'TRABALHA NO DEPARTAMENTO', 3 DEPTNO 4 FROM EMP EMPREGADO 'TRABALHANODEPARTAMENTO' DEPT NO -------------------- ------------------------ ----- -- 7369- SMITH TRABALHA NO DEPARTAMENTO 20 7499- ALLEN TRABALHA NO DEPARTAMENTO 30 7521-WARD TRABA LHA NO DEPARTAMENTO 30 7566- JONES TRABALHA NO DEPARTAMENTO 20 7654- MARTIN TRABALHA NO DEPARTAMENTO 30 7698-BLAKE TRABALHA NO DEPARTAME NTO 30 7782- CLARK TRABALHA NO DEPARTAMENTO 10 7788- SCOTT TRABALHA NO DEPARTAMENTO 20 7839-KING TRABALHA NO DEPARTAMENTO 10 7844- TURNER TRABALHA NO DEPARTAMENTO 30 7876- ADAMS TRABALHA NO DEPARTAMENTO 20 7900-JAMES TRABALHA NO DEPARTAMENTO 30 7902- FORD TRABALHA NO DEPARTAMENTO 20 7934- MILLER TRABALHA NO DEPARTAMENTO 10 14 rows selected.

2.8 Manuseio de valores nulos

11

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_ANUA L 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

12

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 JOB ------- --------- 10 CLERK 10 MANAGER 10 PRESIDENT 20 ANALYST 20 CLERK 20 MANAGER

13

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 JOB SAL*12 DEPTNO ---------- --------- ------- ------- ADAMS CLERK 1 3200 20 ALLEN SALESMAN 19200 30 BLAKE MANAGER 34200 30 CLARK MANAGER 29400 10 FORD ANALYST 36000 20 JAMES CLERK 11400 30 JONES MANAGER 35700 20 KING PRESIDENT 60000 10 MARTIN SALESMAN 15000 30 MILLER CLERK 15600 10 SCOTT ANALYST 36000 20 SMITH CLERK 9600 20 TURNER SALESMAN 18000 30 WARD 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 JOB HIREDATE ---------- --------- --------- JAMES CLERK 23-JUL- 84 KING PRESIDENT 09-JUL-84 BLAKE MANAGER 11-JUN- 84 TURNER SALESMAN 04-JUN- 84 ADAMS CLERK 04-JUN-84 CLARK MANAGER 14-MAY- 84

14

WARD SALESMAN 26-MAR- 84 SCOTT ANALYST 05-MAR- 84 MARTIN SALESMAN 05-DEC- 83 FORD ANALYST 05-DEC- 83 MILLER CLERK 21-NOV- 83 JONES MANAGER 31-OCT- 83 ALLEN SALESMAN 15-AUG- 83 SMITH CLERK 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 JOB ENAME ------- --------- ---------- 10 PRESIDENT KING 10 MANAGER CLARK 10 CLERK MILLER 20 ANALYST SCOTT 20 ANALYST FORD 20 MANAGER JONES 20 CLERK ADAMS 20 CLERK SMITH 30 MANAGER BLAKE 30 SALESMAN ALLEN 30 SALESMAN TURNER 30 SALESMAN WARD 30 SALESMAN MARTIN 30 CLERK 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

15

<> 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 CLE RK 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... entre dois valores (inclusive) IN(lista) idênticos a uma lista de valores LIKE semelhante a um modelo de caracteres IS NULL 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

16

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 ENAME SAL MGR ------- ---------- ------- ------- 7369 SMITH 800 7902 7788 SCOTT 3000 7566 7876 ADAMS 1100 7788 7902 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

17

Os operadores mostrados abaixo podem ser utilizados para testes de negação: != ^= <> não igual NOT NomeDaColuna = não igual a NOT NomeDaColuna > não maior do que NOT BETWEEN fora da faixa especificada NOT IN fora da lista especificada NOT LIKE não semelhante ao modelo IS NOT NULL 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 JOB ---------- --------- SMITH CLERK ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN SCOTT ANALYST KING PRESIDENT TURNER SALESMAN ADAMS CLERK JAMES CLERK FORD ANALYST MILLER 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

18

ALLEN 7698 WARD 7698 JONES 7839 MARTIN 7698 BLAKE 7839 CLARK 7839 SCOTT 7 566 TURNER 7698 ADAMS 7788 JAMES 7698 FORD 7566 MILLER 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> SELECT EMPNO, ENAME, JOB, SAL 2 FROM EMP 3 WHERE SAL BETWEEN 1000 AND 2000 4 AND JOB = 'CLERK' EMPNO ENAME JOB SAL ------- ---------- --------- ------- 7876 ADAM S CLERK 1100 7934 MILLER CLERK 1300

Para listar os funcionários que são CLERK, ou que recebem entre 1000 e 2000, usamos:

SQL> SELECT EMPNO, ENAME, JOB, SAL 2 FROM EMP 3 WHERE SAL BETWEEN 1000 AND 2000 4 OR JOB = ‘CLERK' EMPNO ENAME JOB SAL ------- ---------- --------- ------- 7369 SMITH CLERK 800 7499 ALLEN SALESMAN 1600 7521 WARD SALESMAN 1250 7654 MARTIN SALES MAN 1250 7844 TURNER SALESMAN 1500 7876 ADAMS CLERK 1100 7900 JAMES CLERK 950 7934 MILLER 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.

19

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> SELECT EMPNO, ENAME, JOB, SAL, DEPTNO 2 FROM EMP 3 WHERE SAL > 1500 4 AND JOB = 'MANAGER' 5 OR JOB = 'SALESMAN' EMPNO ENAME JOB SAL DEPTNO ------- ---------- --------- ------- ------- 7499 ALLEN SALESMAN 1600 30 752 1 WARD SALESMAN 1250 30 7566 JONES MANAGER 2975 20 7654 MARTIN SALESMAN 1250 30 7698 BLAKE MAN AGER 2850 30 7782 CLARK MANAGER 2450 10 7844 TURNER SALESMAN 1500 30

Para selecionar todos os vendedores e gerentes com salário superior a 1500, usamos:

SQL> SELECT EMPNO, ENAME, JOB, SAL, DEPTNO 2 FROM EMP 3 WHERE SAL > 1500 4 AND ( JOB = 'MANAGER' OR JOB = ‘SALESMAN' ) EMPNO ENAME JOB SAL DEPTNO ------- ---------- --------- ------- ------- 7499 ALLEN SALESMAN 1600 30 7566 JONES MANAGER 2975 20 7698 BLAKE MANAGER 2850 30 7782 CLARK 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) AND 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 DE PTNO = 10) EMPNO ENAME JOB MGR HIREDATE SA L COMM DEPTNO ------- ---------- --------- ------- --------- ---- --- ------- ------- 7566 JONES MANAGER 7839 31-OCT- 83 2975 20

20

7698 BLAKE MANAGER 7839 11-JUN- 84 2850 30 7782 CLARK MANAGER 7839 14-MAY- 84 2450 10 7934 MILLER CLERK 7782 21-NOV- 83 1300 10

Neste caso os parênteses são desnecessários, uma vez que o AND processa primeiro.

2.14 Resumo do comando SELECT SELECT [DISTINCT] {* | coluna [aliás],....} FROM tabela WHERE condições ORDER BY {coluna | expressão} [ASC|DESC]

2.15 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 ENAME JOB MGR HIREDATE SA L COMM DEPTNO ------- ---------- --------- ------- --------- ---- --- ------- ------- 7369 SMITH CLERK 7902 13-JUN-83 800 20 7499 ALLEN SALESMAN 7698 15-AUG- 83 1600 300 30 7521 WARD SALESMAN 7698 26-MAR- 84 1250 500 30 7566 JONES MANAGER 7839 31-OCT- 83 2975 20 7654 MARTIN SALESMAN 7698 05-DEC- 83 1250 1400 30 7698 BLAKE MANAGER 7839 11-JUN- 84 2850 30 7782 CLARK MANAGER 7839 14-MAY- 84 2450 10 7788 SCOTT ANALYST 7566 05-MAR- 84 3000 20 7839 KING PRESIDENT 09-JUL- 84 5000 10 7844 TURNER SALESMAN 7698 04-JUN- 84 1500 0 30 7876 ADAMS CLERK 7788 04-JUN- 84 1100 20 7900 JAMES CLERK 7698 23-JUL- 84 950 30 7902 FORD ANALYST 7566 05-DEC- 83 3000 20 7934 MILLER 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

21

DEPTNO DNAME ------- -------------- 10 ACCOUNTING 40 OPERATIONS 20 RESEARCH 30 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 ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------- ---------- --------- ------- --------- ---- --- ------- ------- 7876 ADAMS CLERK 7788 04-JUN- 84 1100 20 7782 CLARK MANAGER 7839 14-MAY- 84 2450 10 7902 FORD ANALYST 7566 05-DEC- 83 3000 20 7566 JONES MANAGER 7839 31-OCT- 83 2975 20 7839 KING PRESIDENT 09-JUL- 84 5000 10 7934 MILLER CLERK 7782 21-NOV- 83 1300 10 7788 SCOTT ANALYST 7566 05-MAR- 84 3000 20 7369 SMITH CLERK 7902 13-JUN-83 800 20

g) Listar os nomes e os cargos de todos os CLERK do departamento 20. ENAME JOB ---------- --------- SMITH CLERK ADAMS 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 JOB SAL ---------- --------- ------- SMITH CLERK 800 ALLEN SALESMAN 1600 WARD SALESMAN 1250 JONES MANAGER 2975 MARTIN SALESMAN 1250 BLAKE MANAGER 2850 CLARK MANAGER 2450 SCOTT ANALYST 3000 TURNER SALESMAN 1500 ADAMS CLERK 1100 JAMES CLERK 950

22

FORD ANALYST 3000 MILLER CLERK 1300

j) Mostrar o nome e a remuneração total de todos os empregados.

ENAME REMUNERACAO ---------- ----------- SMITH 9600 ALLEN 19500 WARD 155 00 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 HIR EDATE ---------- ------- --------- 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

23

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) REMUNERACA O 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

24

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 3: WHERE DEPTNO = &NUMERO_DO_DEPARTAMENTO new 3: WHERE DEPTNO = 10 EMPNO ENAME SAL ------- ---------- ------- 7782 CLARK 2450 7839 KING 5000 7934 MILLER 1300

3.3 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

25

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) LOWER('CURSODESQL') -------------- -------------------- accounting curso de sql research curso de sql sales curso de sql opera tions 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) INITCAP(LOC) -------------------- -------------------- Accounting New York Research Dallas Sales Chicago Operations 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.

26

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(DEPT NO,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,'*') RPAD(DNAME,20) RPAD(DEPT NO,20,'.') -------------------- -------------------- --------- ----------- ACCOUNTING********** ACCOUNTING 10....... ........... RESEARCH************ RESEARCH 20....... ........... SALES*************** SALES 30...... ............ OPERATIONS********** OPERATIONS 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) SUBSTR(DNAME,2) SUBSTR(DNAME,3 ,5) -------------------- --------------- -------------- ------ RACL CCOUNTING COUNT RACL ESEARCH SEARC RACL ALES LES RACL PERATIONS 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(DNA ME,'C',1,2) 3 FROM DEPT

27

DNAME INSTR(DNAME,'A') INSTR(DNAME,'ES') INS TR(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(DNA ME,'ASOP') 3 FROM DEPT DNAME LTRIM(DNAME,'A') LTRIM(DNAME,'AS') L TRIM(DNAME,'ASOP') -------------- ---------------- ----------------- - ------------------ ACCOUNTING CCOUNTING CCOUNTING C COUNTING RESEARCH RESEARCH RESEARCH R ESEARCH SALES SALES LES L ES OPERATIONS OPERATIONS OPERATIONS E RATIONS

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(DN AME,'N') 3 FROM DEPT DNAME RTRIM(DNAME,'G') RTRIM(DNAME,'GHS') RTRIM(DNAME,'N') -------------- ---------------- ------------------ ---------------- ACCOUNTING ACCOUNTIN ACCOUNTIN ACCOUNTING RESEARCH RESEARCH RESEARC RESEARCH SALES SALES SALE SALES OPERATIONS OPERATIONS OPERATION 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

28

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 TRANSLATE(ENAME,'C','P') ---------- ------------------------- CLARK PLARK KING KING MILLER 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 TRANSLATE(JOB,'AR','IT') --------- ------------------------------ MANAGER MINIGET PRESIDENT PTESIDENT CLERK 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

29

JOB REPLACE(JOB,'SALESMAN' ,'VENDEDOR') --------- ---------------------------------------- CLERK CLERK SALESMAN VENDEDOR SALESMAN VENDEDOR MANAGER MANAGER SALESMAN VENDEDOR MANAGER MANAGER MANAGER MANAGER ANALYST ANALYST PRESIDENT PRESIDENT SALESMAN VENDEDOR CLERK CLERK CLERK CLERK ANALYST ANALYST CLERK CLERK 14 rows selected.

No exemplo abaixo, "CO" foi substituído por "PX":

SELECT ENAME, REPLACE(ENAME,'CO','PX') FROM EMP ENAME REPLACE(ENAME,'CO','PX') ---------- ------------------------ SMITH SMITH ALLEN ALLEN WARD WARD JONES JONES MARTIN MARTIN BLAKE BLAKE CLARK CLARK SCOTT SPXTT <-------------- KING KING TURNER TURNER ADAMS ADAMS JAMES JAMES FORD FORD MILLER 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

30

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(4 2.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(4 2.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), CE IL(-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

31

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 31 2500000 1300 1690000 2197000000 31 2500000

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.

32

SQL> SELECT SAL, SQRT(SAL), SQRT(40), SQRT(COMM) 2 FROM EMP 3 WHERE COMM > 0 SAL SQRT(SAL) SQRT(40) SQR T(COMM) ---- ----------------- ----------------- ---------- ------- 1600 40 6.324555320336759 17.3205080 7568877 1250 35.35533905932738 6.324555320336759 22.360679 7749979 1250 35.35533905932738 6.324555320336759 37.4165738 6773941

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, SIG N(COMM-SAL) 2 FROM EMP 3 WHERE DEPTNO = 30 SAL-COMM SIGN(SAL-COMM) COMM-S AL SIGN(COMM- SAL) ----------------- ----------------- --------------- -- ----------------- 1300 1 -13 00 - 1 750 1 -7 50 - 1 -150 - 1 150 1 1500 1 -15 00 - 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-S AL 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.

33

SQL> SELECT SAL, COMM, MOD(SAL,COMM), MOD(100,40) 2 FROM EMP 3 WHERE DEPTNO = 30 4 ORDER BY COMM SAL COMM MOD(SAL,COMM) MOD(1 00,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 COS(60*3.1 TAN(45*3.1 ---------- ---------- ---------- .5 .5 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) COSH(0) TANH(.5) ---------- ------- ---------- 1.17520119 1 .462117157

4.4 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 ENAME PCTSAL ----------------- ---------- ----------------- 20 SMITH 920 30 ALLEN 1840 30 WARD 1438 20 JONES 3421 30 MARTIN 1438 30 BLAKE 3278 10 CLARK 2818 20 SCOTT 3450 10 KING 5750 30 TURNER 1725 20 ADAMS 1265 30 JAMES 1093 20 FORD 3450 10 MILLER 1495

34

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 CODIGO ---------- -------- SMITH CL36S530 ALLEN SA49A450 WARD SA52W630 JONES MA56J520 MARTIN SA65 M635 BLAKE MA69B420 CLARK MA78C462 SCOTT AN78S300 KING PR83K520 TURNER SA84T656 ADAMS CL87A352 JAMES CL90J520 FORD AN90F630 MILLER CL93M460

d) Faça uma procura não sensitiva a letras maiúsculas ou minúsculas por um cargo fornecido pelo usuário.

EMPNO ENAME JOB MGR HIREDATE SAL C OMM DEPTNO ----- ---------- --------- ----- --------- ----- -- --- ------ 7369 SMITH CLERK 7902 13-JUN- 83 800 20 7876 ADAMS CLERK 7788 04-JUN- 84 1100 20 7900 JAMES CLERK 7698 23-JUL-84 950 30 7934 MILLER 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

35

f) Encontrar a primeira ocorrência da letra "L" nos nomes dos empregados e substituir pela letra "X".

ENAME PRIMEIRA_OCORRENCIA_ ---------- -------------------- SMITH SMITH ALLEN AXLEN WARD WARD JONES JONES MARTIN MARTIN BLAKE BX AKE CLARK CXARK SCOTT SCOTT KING KING TURNER TURNER ADAMS ADAMS JAMES JAMES FORD FORD MILLER MIXLER 14 rows selected.

36


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(EN AME) 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 D EPARTAMENTO 2 FROM DEPT f) SQL> SELECT ENAME, 2 TRANSLATE(SUBSTR(ENAME,1,INSTR(ENAME,'L')),'L' ,'X')|| 3 SUBSTR(ENAME,INSTR(ENAME,'L')+1) PRIMEIRA_OCOR RENCIA_DE_L 4 FROM EMP

37

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 Soma o número de dias à data, produzindo outra data data-número Subtrai o número de dias à data, produzindo outra data data-data Subtrai uma data da outra, produzindo número de dias data+número/24 Soma número de horas à data, produzindo outra data

SQL> SELECT HIREDATE, HIREDATE+7, HIREDATE-7, SYSDA TE-HIREDATE 2 FROM EMP 3 WHERE HIREDATE LIKE '%JUN%' HIREDATE HIREDATE+7 HIREDATE-7 SYSDATE-HIREDATE --------- ---------- ---------- ----------------- 13-JUN-83 20-JUN-83 06-JUN-83 4152.351851851852 11-JUN-84 18-JUN-84 04-JUN-84 3788.351851851852 04-JUN-84 11-JUN-84 28-MAY-84 3795.351851851852 04-JUN-84 11-JUN-84 28-MAY-84 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> SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE), 2 MONTHS_BETWEEN('01-JAN-84','05-NOV-88') 3 FROM EMP 4 WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE) > 59

38

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.1 290322580645 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_M ONTHS(HIREDATE,-3) 2 FROM EMP 3 WHERE DEPTNO=20 HIREDATE ADD_MONTHS(HIREDATE,3) ADD_MONTHS(HIREDATE,-3) --------- ---------------------- ------------------ ----- 13-JUN-83 13-SEP-83 13-MAR-83 31-OCT-83 31-JAN-84 31-JUL-83 05-MAR-84 05-JUN-84 05-DEC-83 04-JUN-84 04-SEP-84 04-MAR-84 05-DEC-83 05-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 NEXT_DAY(HIREDATE,'FRIDAY') NEXT_DAY(HIRE DATE,6) --------- --------------------------- ------------- ------- 14-MAY-84 18-MAY-84 18-MAY-84 09-JUL-84 13-JUL-84 13-JUL-84 21-NOV-83 25-NOV-83 25-NOV-83

5.2.6 LAST_DAY(data) Retorna a data do último dia do mês da data especificada.

SQL> SELECT SYSDATE, LAST_DAY(SYSDATE), HIREDATE, 2 LAST_DAY(HIREDATE), LAST_DAY('15-FEB-88') 3 FROM EMP 4 WHERE DEPTNO = 20

39

SYSDATE LAST_DAY HIREDATE LAST_DAY LAST_DAY (SYSDATE) (HIREDATE) ('15-FEB- 88') --------- ---------- --------- ---------- --------- ---- 25-OCT-94 31-OCT-94 13-JUN-83 30-JUN-83 29-FEB-88 25-OCT-94 31-OCT-94 31-OCT-83 31-OCT-83 29-FEB-88 25-OCT-94 31-OCT-94 05-MAR-84 31-MAR-84 29-FEB-88 25-OCT-94 31-OCT-94 04-JUN-84 30-JUN-84 29-FEB-88 25-OCT-94 31-OCT-94 05-DEC-83 31-DEC-83 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,'YEA R') --------- ---------------------- ------------------ --- 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,'YEA R') --------- ---------------------- ------------------ --- 25-OCT-94 01-OCT-94 01-JAN-94

5.3 Funções de conversão

5.3.1 Formatos de Data

Formato Significado CC ou SCC Século; S prefixa datas Antes de Cristo com "-" YYYY ou SYYYY Ano; S prefixa datas Antes de Cristo com "-" YYY, YY, Y Últimos 3,2,1 dígitos do ano

40

Y,YYY Ano com vírgula separando os milhares YEAR ou SYEAR Ano por extenso. S prefixa datas Antes de Cristo com "-

" BC ou AD Indicador de Antes ou Depois de Cristo B.C. ou A.D. Idêntico a BC/AD com pontos Q Quarto do ano (trimestre) MM Mês Month Mês por extenso, com 9 caracteres, espaços à direita MON Nome do mês, com 3 caracteres WW ou W Semana do ano ou do mês DDD, DD, D Dia do ano, do mês, ou da semana DAY Dia por extenso, com 9 caracteres, espaços à direita DY Nome do dia, abreviado com 3 caracteres J Dia Juliano (dias desde 31/12/4713 AC) AM ou PM Indicador de meridiano A.M. ou P.M. Indicador de meridiano com pontos HH ou HH12 Hora do dia no intervalo 1 a 12 HH24 Hora do dia no intervalo 0 a 23 MI Minuto SS Segundo SSSS Segundos após à meia noite (0-86399) /., etc... 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:

41

SQL> SELECT TO_CHAR(SYSDATE, 'fmDay, ddth Month YYY Y') 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 Significado Exemplo 9 posição numérica (um 9 para cada número) 999999 1234 0 zeros à esquerda 099999 001234 $ cifrão à esquerda $99999 $1234 . ponto decimal na posição especificada 999999.99 1234.00 , vírgula na posição especificada 999,999 1,234 MI sinal de menos à direita 099999MI 1234- PR números negativos entre parênteses 999999PR (1234) EEEE notação científica 99.999EEEE 1.234E+03 V multiplicar por 10 elevado a n, sendo n o

número de 9 à direita de V 9999V99 123400

B mostrar resultados zero como espaços, não como 0.

B9999.99 1234.00

42

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 ENAME JOB SAL ----- ---------- --------- ------- 7499 ALLEN SALESMAN 1600 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7788 SCOTT ANALYST 3000 7839 KING PRESIDENT 5000 7902 FORD 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 ENAME HIREDATE ----- ---------- --------- 7844 TURNER 04-JUN-84 7876 ADAMS 04-JUN-84

5.4 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 a coluna ou expressão a ser avaliada pes1 o primeiro valor a ser testado res1 o valor a ser retornado se coluna ou expressão igual a pes1 pes2 o segundo valor a ser testado res2 o valor a ser retornado se coluna ou expressão igual a pes2 default o valor a ser retornado se a coluna ou expressão for diferente de pes1, pes2,...,pesN. Notas:

43

-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> SELECT ENAME, JOB, 2 DECODE(JOB, 'CLERK','WORKER','MANAGER','BOSS', 'UNDEFINED') 3 DECODED_JOB 4 FROM EMP ENAME JOB DECODED_JOB ---------- --------- ----------- SMITH CLERK WORKER ALLEN SALESMAN UNDEFINED WARD SALESMAN UNDEFINED JONES MANAGER BOSS MARTIN SALESMAN UNDEFINED BLAKE MANAGER BOSS CLARK MANAGER BOSS SCOTT ANALYST UNDEFINED KING PRESIDENT UNDEFINED TURNER SALESMAN UNDEFINED ADAMS CLERK WORKER JAMES CLERK WORKER FORD ANALYST UNDEFINED MILLER CLERK 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 BONUS ----- ----- 1 15% 2 10% 3 8% 4 5% 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> SELECT JOB, SAL, 2 DECODE(JOB,'ANALYST',SAL*1.1, 'CLERK',SAL*1.15 , 3 'MANAGER',SAL*0.95, SAL) SALARIO_DECODIFICADO 4 FROM EMP

44

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(COM M,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,C OMM) ------------------- ------------------ 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) ----------------- -----------------

45

1000 300 1000 500 1000 1250 1000 1000 0 1000

5.4.5 VSIZE(coluna|valor) Retorna o número de bytes utilizado internamente pelo Oracle para representar o valor.

SQL> SELECT DEPTNO, VSIZE(DEPTNO), VSIZE(HIREDATE), 2 VSIZE(SAL), VSIZE(ENAME) 3 FROM EMP 4 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> SELECT SYSDATE, 2 TO_CHAR(NEXT_DAY(ADD_MONTHS(SYSDATE,2),'FRIDAY '), 3 'Day dd Month YYYY') 4 FROM SYS.DUAL SYSDATE TO_CHAR(NEXT_DAY(ADD_MONTHS(SYSDATE,2),'FRIDAY'),'D AYDDMONTHYYYY') --------- ----------------------------------------- --------------- 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 DATA_DE_ADMISSAO ---------- --------------------------- SMITH June, Thirteenth 1983 JONES October, Thirty-First 1983 SCOTT March, Fifth 1984 ADAMS June, Fourth 1984 FORD 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

46

---------- --------- --------- SMITH 13-JUN-83 13-JUN- 84 ALLEN 15-AUG-83 15-AUG- 84 JONES 31-OCT-83 31-OCT-84 MILLER 21-NOV-83 21-NOV- 84 MARTIN 05-DEC-83 05-DEC- 84 FORD 05-DEC-83 05-DEC-84 SCOTT 05-MAR-84 05-MAR- 85 WARD 26-MAR-84 26-MAR- 85 CLARK 14-MAY-84 14-MAY- 85 TURNER 04-JUN-84 04-JUN- 85 ADAMS 04-JUN-84 04-JUN- 85 BLAKE 11-JUN-84 11-JUN- 85 KING 09-JUL-84 09-JUL- 85 JAMES 23-JUL-84 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 SALARIO ---------- -------------- ADAMS ABAIXO DE 1500 ALLEN 1600 BLAKE 2850 CLARK 2450 FORD 3000 JAMES ABAIXO DE 1500 JONES 2975 KING 5000 MARTIN ABAIXO DE 1500 MILLER ABAIXO DE 1500 SCOTT 3000 SMITH ABAIXO DE 1500 TURNER NO LIMITE WARD 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 A NOS 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

47

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 HIREDATE DIA_DO_PAGAMENTO ---------- --------- ---------------- SMITH 13-JUN-83 24-JUN- 83 ALLEN 15-AUG-83 26-AUG- 83 JONES 31-OCT-83 25-NOV- 83 MILLER 21-NOV-83 30-DEC- 83 MARTIN 05-DEC-83 30-DEC- 83 FORD 05-DEC-83 30-DEC- 83 SCOTT 05-MAR-84 30-MAR-84 WARD 26-MAR-84 27-APR- 84 CLARK 14-MAY-84 25-MAY- 84 TURNER 04-JUN-84 29-JUN-84 ADAMS 04-JUN-84 29-JUN- 84 BLAKE 11-JUN-84 29-JUN- 84 KING 09-JUL-84 27-JUL-84 JAMES 23-JUL-84 31-AUG- 84 14 rows selected.

48


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.Y Y'),'DAY') DIA new 1: SELECT TO_CHAR(TO_DATE('12.11.88','DD.MM.Y Y'),'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 SERV ICO" 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','9999999 999'), 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)),'F RIDAY')-7, 4 NEXT_DAY(LAST_DAY(HIREDATE),'FRIDAY')-7) DIA_D O_PAGAMENTO 5 FROM EMP 6 ORDER BY HIREDATE ou SQL> SELECT ENAME, HIREDATE, 2 NEXT_DAY(LAST_DAY(ROUND(HIREDATE,'MONTH'))-7,' FRIDAY') 3 DIA_DO_PAGAMENTO 4 FROM EMP 5 ORDER BY HIREDATE

49

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 Valor retornado AVG ([DISTINCT|ALL]n)

Valor médio de 'n', ignorando os valores nulos.

COUNT ([DISTINCT|ALL]exp*)

Número de ocorrências não nulas da expressão. O '*' obriga a contagem total, incluindo valores duplicados e nulos.

MAX ([DISTINCT|ALL]exp)

Valor máximo da expressão.

MIN ([DISTINCT|ALL]exp)

Valor mínimo da expressão.

STDDEV ([DISTINCT|ALL]n)

Desvio padrão de 'n', ignorando os valores nulos.

SUM ([DISTINCT|ALL]n)

Soma dos valores de 'n', ignorando os valores nulos.

VARIANCE ([DISTINCT|ALL]n)

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.

50

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> SELECT JOB, AVG(SAL) 2 FROM EMP 3 WHERE JOB != 'MANAGER' 4 GROUP BY JOB JOB AVG(SAL) --------- ----------------- ANALYST 3000 CLERK 1037.5

51

PRESIDENT 5000 SALESMAN 1400

6.6 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 JOB AVG(SAL) ----------------- --------- ----------------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 3000 20 CLERK 950 20 MANAGER 2975 30 CLERK 950 30 MANAGER 2850 30 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) JOB ----------------- --------- 3000 ANALYST 1300 CLERK 2975 MANAGER 5000 PRESIDENT 1600 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.

52

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> SELECT DEPTNO, AVG(SAL) 2 FROM EMP 3 GROUP BY DEPTNO 4 HAVING COUNT(*) > 3 DEPTNO AVG(SAL) ----------------- ----------------- 20 2175 30 1566.666666666667

Para mostrar os cargos com salário máximo acima de 3000:

SQL> SELECT JOB, MAX(SAL) 2 FROM EMP 3 HAVING MAX(SAL) >= 3000 4 GROUP BY JOB JOB MAX(SAL) --------- ----------------- ANALYST 3000 PRESIDENT 5000

53

Embora a cláusula HAVING possa ser escrita antes da cláusula GROUP BY, a leitura torna-se 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> SELECT DEPTNO, AVG(SAL) 2 FROM EMP 3 WHERE AVG(SAL) > 2000 4 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> SELECT DEPTNO, AVG(SAL) 2 FROM EMP 3 GROUP BY DEPTNO 4 HAVING AVG(SAL) > 2000 DEPTNO AVG(SAL) ----------------- ----------------- 10 29 16.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> SELECT JOB, AVG(SAL) 2 FROM EMP 3 WHERE JOB <> 'MANAGER' 4 GROUP BY JOB 5 HAVING AVG(SAL) > 2000 JOB AVG(SAL) --------- ----------------- ANALYST 3000 CLERK 1037.5 PRESIDENT 5000

6.9 Ordem das cláusulas

SELECT lista_de_colunas FROM lista_de_tabelas WHERE condições_de_linha GROUP BY lista_de_colunas

54

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, lembrando-se 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.

55

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

56


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) MEN OR 2 FROM EMP 3 GROUP BY JOB d) SQL> SELECT COUNT(*) GERENTES 2 FROM EMP 3 WHERE JOB = 'MANAGER' e) SQL> SELECT JOB, AVG(SAL) SALARIO_MEDIO, 2 AVG(SAL*12+NVL(COMM,0)) REMUNERACAO_MEDIA 3 FROM EMP 4 GROUP BY JOB f) SQL> SELECT MAX(SAL)-MIN(SAL) DIFERENCA 2 FROM EMP g) SQL> SELECT DEPTNO, COUNT(*) 2 FROM EMP 3 GROUP BY DEPTNO 4 HAVING COUNT(*) > 3 h) SQL> SELECT EMPNO 2 FROM EMP 3 GROUP BY EMPNO 4 HAVING COUNT(*) > 1 i) SQL> SELECT MGR, MIN(SAL) 2 FROM EMP 3 GROUP BY MGR 4 HAVING MIN(SAL) >= 1000 5 ORDER BY MIN(SAL)

57

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 JOB DNAME ---------- --------- -------------- CLARK MANAGER ACCOUNTING KING PRESIDENT ACC OUNTING MILLER CLERK ACCOUNTING SMITH CLERK RESEARCH ADAMS CLERK RESEARCH FORD ANALYST RESEARCH SCOTT ANALYST RESEARCH JONES MANAGER RESEARCH ALLEN SALESMAN SALES BLAKE MANAGER SALES MARTIN SALESMAN SALES JAMES CLERK SALES TURNER SALESMAN SALES WARD SALESMAN 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

58

4 ORDER BY DEPT.DEPTNO DEPTNO ENAME JOB DNA ME ------- ---------- --------- -------------- 10 CLARK MANAGER ACCOUNTING 10 KING PRESIDENT ACCOUNTING 10 MILLER CLERK ACCOUNTING 20 SMITH CLERK RESEARCH 20 ADAMS CLERK RESEARCH 20 FORD ANALYST RESEARCH 20 SCOTT ANALYST RESEARCH 20 JONES MANAGER RESEARCH 30 ALLEN SALESMAN SALES 30 BLAKE MANAGER SALES 30 MARTIN SALESMAN SALES 30 JAMES CLERK SALES 30 TURNER SALESMAN SALES 30 WARD SALESMAN 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> SELECT E.ENAME, D.DEPTNO, D.DNAME 2 FROM EMP E, DEPT D 3 WHERE E.DEPTNO = D.DEPTNO 4 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.

59

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 SA L 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 [DISTINCT] {[tabela].*|expressão [aliás],...} FROM tabela [alias],... WHERE [condição de junção]... AND [condição de linha]... OR [outra condição de linha] GROUP BY {expressão|coluna} HAVING {condição de grupo} ORDER BY {expressão|coluna} [ASC|DESC]

7.7 Exercícios

a) Mostrar o nome dos empregados e dos departamentos onde trabalham, ordenados pelo nome do departamento.

ENAME DNAME ---------- -------------- CLARK ACCOUNTING KING ACCOUNTING MILLER ACCOUNTING SMITH RESEARCH ADAMS RESEARCH FORD RESEARCH SCOTT RESEARCH JONES RESEARCH ALLEN SALES BLAKE SALES MARTIN SALES JAMES SALES TURNER SALES

60

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 LOCAL DNAME ---------- ------------- -------------- CLARK NEW YORK ACCOUNTING KING NEW YORK ACCOUNTIN G JONES DALLAS RESEARCH SCOTT DALLAS RESEARCH FORD DALLAS RESEARCH ALLEN CHICAGO SALES BLAKE CHICAGO SALES 7 rows selected.

d) Listar as faixas salariais dos empregados.

ENAME JOB SAL GRADE ---------- --------- ------- ------- SMITH CLERK 800 1 ADAMS CLERK 1100 1 JAMES CLERK 950 1 WARD SALESMAN 1250 2 MARTIN SALESMAN 1250 2 MILLER CLERK 1300 2 ALLEN SALESMAN 1600 3 TURNER SALESMAN 1500 3 JONES MANAGER 2975 4 BLAKE MANAGER 2850 4 CLARK MANAGER 2450 4 SCOTT ANALYST 3000 4 FORD ANALYST 3000 4 KING PRESIDENT 5000 5 14 rows selected.

61

e) Mostrar somente os empregados na faixa 3. ENAME JOB SAL GRADE ---------- --------- ------- ------- ALLEN SALESMAN 1600 3 TURNER 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 JOB SAL GRADE DNAME ---------- --------- ------- ------- -------------- KING PRESIDENT 5000 5 ACCOUNTING FORD ANALYST 3000 4 RESEARCH SCOTT ANALYST 3000 4 RESEARCH JONES MANAGER 2975 4 RESEARCH BLAKE MANAGER 2850 4 SALES CLARK MANAGER 2450 4 ACCOUNTING ALLEN SALESMAN 1600 3 SALES TURNER SALESMAN 1500 3 SALES MARTIN SALESMAN 1250 2 SALES WARD 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 JOB SALARIO_ANUAL DEPTNO DNAME GRADE ---------- --------- ------------- ------- -------- ------ ------- MILLER CLERK 15600 10 ACCOUNTI NG 2 SMITH CLERK 9600 20 RESEARCH 1 ADAMS CLERK 13200 20 RESEARCH 1 JAMES CLERK 11400 30 SALES 1

62

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> SELECT ENAME, LOC LOCAL, DNAME 2 FROM EMP, DEPT 3 WHERE EMP.DEPTNO = DEPT.DEPTNO 4 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> SELECT ENAME, SAL, LOC LOCAL 2 FROM EMP, DEPT 3 WHERE EMP.DEPTNO = DEPT.DEPTNO 4 AND LOC='DALLAS' g) SQL> SELECT ENAME, JOB, SAL, GRADE, DNAME 2 FROM EMP, SALGRADE, DEPT 3 WHERE EMP.DEPTNO = DEPT.DEPTNO 4 AND SAL BETWEEN LOSAL AND HISAL 5 AND JOB != 'CLERK' 6 ORDER BY SAL DESC h) SQL> SELECT ENAME, JOB, SAL*12+NVL(COMM,0) SALARIO_ ANUAL, 2 D.DEPTNO, DNAME, GRADE 3 FROM EMP E, SALGRADE, DEPT D 4 WHERE E.DEPTNO = D.DEPTNO AND SAL BETWEEN LOSA L AND HISAL 5 AND (SAL*12+NVL(COMM,0)=3600 6 OR JOB = 'CLERK') 7 ORDER BY E.JOB

63

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> SELECT E.ENAME, D.DEPTNO, D.DNAME 2 FROM EMP E, DEPT D 3 WHERE E.DEPTNO (+) = D.DEPTNO 4 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

64

4 WHERE E.MGR = G.EMPNO AND E.SAL < G.SAL EMP_NOME EMP_SAL GER_NOME GER_SAL ---------- ------- ---------- ------- ALLEN 1600 BLAKE 2850 WARD 1250 BLAKE 2850 JAMES 950 BLAKE 2850 TURNER 1500 BLAKE 2850 MARTIN 1250 BLAKE 2850 MILLER 1300 CLARK 2450 ADAMS 1100 SCOTT 3000 JONES 2975 KING 5000 CLARK 2450 KING 5000 BLAKE 2850 KING 5000 SMITH 800 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> SELECT JOB 2 FROM EMP 3 WHERE DEPTNO = 10 4 UNION 5 SELECT JOB 6 FROM EMP 7 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> SELECT JOB 2 FROM EMP 3 WHERE DEPTNO = 10 4 INTERSECT 5 SELECT JOB 6 FROM EMP 7 WHERE DEPTNO = 30

65

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> SELECT JOB 2 FROM EMP 3 WHERE DEPTNO = 10 4 MINUS 5 SELECT JOB 6 FROM EMP 7 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> SELECT EMPNO, ENAME, SAL 2 FROM EMP 3 UNION 4 SELECT ID, NAME, SALARY 5 FROM EMP HISTORY 6 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.

66

• 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.

8.5 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 ENAME GER_NUM GER_NOME ------- ---------- ------- ---------- 7788 SCOTT 7566 JONES 7902 FORD 7566 JONES 7499 ALLEN 7698 BLAKE 7521 WARD 7698 BLAKE 7900 JAMES 7698 BLAKE 7844 TURNER 7698 BLAKE 7654 MARTIN 7698 BLAKE 7934 MILLER 7782 CLARK 7876 ADAMS 7788 SCOTT 7566 JONES 7839 KING 7782 CLARK 7839 KING 7698 BLAKE 7839 KING 7369 SMITH 7902 FORD 13 rows selected.

c) Alterar a solução do exercício b para mostrar KING que não possui gerente.

EMPNO ENAME GER_NUM GER_NOME ------- ---------- ------- ---------- 7788 SCOTT 7566 JONES 7902 FORD 7566 JONES 7499 ALLEN 7698 BLAKE 7521 WARD 7698 BLAKE 7900 JAMES 7698 BLAKE 7844 TURNER 7698 BLAKE 7654 MARTIN 7698 BLAKE 7934 MILLER 7782 CLARK 7876 ADAMS 7788 SCOTT 7566 JONES 7839 KIN G 7782 CLARK 7839 KING 7698 BLAKE 7839 KING 7369 SMITH 7902 FORD 7839 KING

67

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 HIREDATE GERENTE HIREDATE ---------- --------- ---------- --------- ALLEN 15-AUG-83 BLAKE 11-JUN-84 WARD 26-MAR-84 BLAKE 11-JUN- 84 TURNER 04-JUN-84 BLAKE 11-JUN- 84 MARTIN 05-DEC-83 BLAKE 11-JUN- 84 MILLER 21-NOV-83 CLARK 14-MAY- 84 JONES 31-OCT-83 KING 09-JUL- 84 CLARK 14-MAY-84 KING 09-JUL- 84 BLAKE 11-JUN-84 KING 09-JUL- 84 SMITH 13-JUN-83 FORD 05-DEC- 83 9 rows selected.

f) Encontrar outra maneira de resolver o exercício a.

DEPTNO DNAME ------- -------------- 40 OPERATIONS

68


a) SQL> SELECT D.DEPTNO, DNAME 2 FROM EMP E, DEPT D 3 WHERE E.DEPTNO (+) = D.DEPTNO 4 AND E.EMPNO IS NULL b) SQL> SELECT EMPS.EMPNO, EMPS.ENAME, 2 GERS.EMPNO GER_NUM, GERS.ENAME GER_NOME 3 FROM EMP EMPS, EMP GERS 4 WHERE EMPS.MGR = GERS.EMPNO c) SQL> SELECT EMPS.EMPNO, EMPS.ENAME, 2 GERS.EMPNO GER_NUM, GERS.ENAME GER_NOME 3 FROM EMP EMPS, EMP GERS 4 WHERE EMPS.MGR = GERS.EMPNO (+) d) SQL> SELECT JOB 2 FROM EMP 3 WHERE HIREDATE BETWEEN '01-JAN-83' AND '30-JUN -83' 4 INTERSECT 5 SELECT JOB 6 FROM EMP 7 WHERE HIREDATE BETWEEN '01-JAN-84' AND '30-JUN -84' e) SQL> SELECT E.ENAME EMPREGADO, E.HIREDATE, 2 M.ENAME GERENTE, M.HIREDATE 3 FROM EMP E, EMP M 4 WHERE E.MGR = M.EMPNO AND E.HIREDATE < M.HIRED ATE f) SQL> SELECT DEPTNO, DNAME 2 FROM DEPT 3 MINUS 4 SELECT EMP.DEPTNO, DNAME 5 FROM EMP, DEPT 6 WHERE EMP.DEPTNO = DEPT.DEPTNO

69

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 coluna1, coluna2 FROM tabela WHERE 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 =, <, >, <=, >=, <>.

70

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 JOB ---------- --------- JONES MANAGER BLAKE MANAGER CLARK 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 B Y 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> SELECT ENAME, SAL, DEPTNO 2 FROM EMP 3 WHERE (SAL,DEPTNO) IN 4 (SELECT MIN(SAL), DEPTNO FROM EMP GROUP BY DEP TNO) ENAME SAL DEPTNO ---------- ------- ------- SMITH 800 20 JAMES 950 30 MILLER 1300 10

71

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 on e 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> SELECT ENAME, SAL, JOB, DEPTNO 2 FROM EMP 3 WHERE SAL > ANY 4 (SELECT DISTINCT SAL FROM EMP WHERE DEPTNO = 3 0) 5 ORDER BY SAL DESC ENAME SAL JOB DEPTNO ---------- ------- --------- ------- KING 5000 PRESIDENT 10 SCOTT 3000 ANALYST 20 FORD 3000 ANALYST 20 JONES 2975 MANAGER 2 0 BLAKE 2850 MANAGER 30

72

CLARK 2450 MANAGER 10 ALLEN 1600 SALESMAN 30 TURNER 1500 SALESMAN 30 MILLER 1300 CLERK 10 WARD 1250 SALESMAN 30 MARTIN 1250 SALESMAN 30 ADAMS 1100 CLERK 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> SELECT ENAME, SAL, JOB, DEPTNO 2 FROM EMP 3 WHERE SAL > ALL 4 (SELECT DISTINCT SAL FROM EMP WHERE DEPTNO = 3 0) 5 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> SELECT DEPTNO, AVG(SAL) 2 FROM EMP 3 GROUP BY DEPTNO 4 HAVING AVG(SAL) > 5 (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> SELECT JOB, AVG(SAL) 2 FROM EMP 3 GROUP BY JOB 4 HAVING AVG(SAL) = 5 (SELECT MAX(AVG(SAL)) FROM EMP GROUP BY JOB) JOB AVG(SAL) --------- -------- PRESIDENT 5000

73

9.8 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.

9.9 Limite para o aninhamento • Não há limite para o aninhamento de SELECT dentro de SELECT.

9.10 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> SELECT EMPNO, ENAME, SAL, DEPTNO 2 FROM EMP E 3 WHERE SAL > 4 (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = E.DEP TNO) 5 ORDER BY DEPTNO EMPNO ENAME SAL DEPTNO ------- ---------- ------- ------- 7839 KING 5000 10 7566 JONES 2975 20 7788 SCOTT 3000 20 7902 FORD 3000 20 7499 ALLEN 1600 30

74

7698 BLAKE 2850 30

Análise da execução da consulta:

• 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.

9.11 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> SELECT EMPNO, ENAME, JOB, DEPTNO 2 FROM EMP E 3 WHERE EXISTS 4 (SELECT EMPNO FROM EMP WHERE EMP.MGR = E.EMPNO ) 5 ORDER BY EMPNO EMPNO ENAME JOB DEPTNO ------- ---------- --------- ------- 7566 JONES MANAGER 20 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7788 SCOTT ANAL YST 20 7839 KING PRESIDENT 10 7902 FORD ANALYST 20 6 rows selected.

Para listar os empregados cujos departamentos não estão na tabela de departamentos:

SQL> SELECT EMPNO, ENAME, DEPTNO 2 FROM EMP 3 WHERE NOT EXISTS 4 (SELECT DEPTNO FROM DEPT WHERE DEPT.DEPTNO = E MP.DEPTNO)

75

no rows selected

Uma outra forma para encontrar os departamentos sem funcionários é:

SQL> SELECT DEPTNO, DNAME 2 FROM DEPT D 3 WHERE NOT EXISTS 4 (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 JOB SAL ---------- --------- ------- KING PRESIDENT 5000 SCOTT ANALYST 3000 FORD ANALYST 3000 JONES MANAGER 2975 ALLEN SALESMAN 1600 MILLER CLERK 1300

b) Listar os empregados com os menores salário por cargo, em ordem ascendente de salário. ENAME JOB SAL ---------- --------- ------- SMITH CLERK 800 WARD SALESMAN 1250 MARTIN SALESMAN 1250 CLARK MANAGER 2450 SCOTT ANALYST 3000 FORD ANALYST 3000 KING PRESIDENT 5000 7 rows selected.

c) Listar os últimos empregados contratados em cada departamento.

DEPTNO ENAME HIREDATE ------- ---------- --------- 20 ADAMS 04-JUN- 84 10 KING 09-JUL- 84 30 JAMES 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

76

SCOTT 3000 20 FORD 3000 20 ALLEN 1600 30 BLAKE 2850 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 HIREDATE MAIOR_DATA ---------- --------- ---------- ADAMS 04-JUN-84 ALLEN 15-AUG- 83 BLAKE 11-JUN- 84 CLARK 14-MAY- 84 FORD 05-DEC- 83 JAMES 23-JUL- 84 * JONES 31-OCT- 83 KING 09-JUL- 84 MARTIN 05-DEC- 83 MILLER 21-NOV- 83 SCOTT 05-MAR-84

77

SMITH 13-JUN- 83 TURNER 04-JUN- 84 WARD 26-MAR-84 14 rows selected.

78

9.13 Resposta dos exercícios

a) SQL> SELECT ENAME, JOB, SAL 2 FROM EMP 3 WHERE (SAL,JOB) IN 4 (SELECT MAX(SAL), JOB FROM EMP GROUP BY JOB) 5 ORDER BY SAL DESC b) SQL> SELECT ENAME, JOB, SAL 2 FROM EMP 3 WHERE (SAL,JOB) IN 4 (SELECT MIN(SAL), JOB FROM EMP GROUP BY JOB) 5 ORDER BY SAL c) SQL> SELECT DEPTNO, ENAME, HIREDATE 2 FROM EMP 3 WHERE (HIREDATE,DEPTNO) IN 4 (SELECT MAX(HIREDATE), DEPTNO FROM EMP GROUP B Y DEPTNO) 5 ORDER BY HIREDATE d) SQL> SELECT ENAME, SAL SALARIO, DEPTNO 2 FROM EMP E 3 WHERE SAL > 4 (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = E.DEP TNO) 5 ORDER BY DEPTNO e) SQL> SELECT DEPTNO, DNAME 2 FROM DEPT D 3 WHERE NOT EXISTS 4 (SELECT 'qualquer coisa' FROM EMP WHERE DEPTNO = D.DEPTNO) f) SQL> DEFINE REM = SAL*12+NVL(COMM,0) SQL> SELECT DEPTNO, SUM(&REM) MAIOR_DESPESA 2 FROM EMP 3 GROUP BY DEPTNO 4 HAVING SUM(&REM) = 5 (SELECT MAX(SUM(&REM)) FROM EMP GROUP BY DEPTN O) old 1: SELECT DEPTNO, SUM(&REM) MAIOR_DESPESA new 1: SELECT DEPTNO, SUM(SAL*12+NVL(COMM,0)) MAI OR_DESPESA old 4: HAVING SUM(&REM) = new 4: HAVING SUM(SAL*12+NVL(COMM,0)) = old 5: (SELECT MAX(SUM(&REM)) FROM EMP GROUP BY D EPTNO) new 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.SA L < SAL) h) SQL> COLUMN ANO FORMAT A4 SQL> COLUMN NUMBER_OF_EMPS FORMAT 9 HEADING 'NUMERO DE EMPREGADOS' SQL> SELECT TO_CHAR(HIREDATE,'YYYY') ANO, 2 COUNT(EMPNO) NUMBER_OF_EMPS 3 FROM EMP 4 GROUP BY TO_CHAR(HIREDATE,'YYYY') 5 HAVING COUNT(EMPNO) = 6 (SELECT MAX(COUNT(EMPNO)) 7 FROM EMP

79

8 GROUP BY TO_CHAR(HIREDATE,'YYYY')) i) SQL> COLUMN SALARIO FORMAT 999,999.99 SQL> COLUMN MEDIA_DEPT LIKE SALARIO SQL> BREAK ON DEPTNO ON MEDIA_DEPT SQL> SELECT E.ENAME NOME, E.SAL SALARIO, 2 E.DEPTNO DEPARTAMENTO, AVG(A.SAL) MEDIA_DEPT 3 FROM EMP A, EMP E 4 WHERE E.DEPTNO = A.DEPTNO 5 AND E.SAL > 6 (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = E.DEP TNO) 7 GROUP BY E.ENAME, E.SAL, E.DEPTNO 8 ORDER BY AVG(A.SAL) j) SQL> SELECT ENAME, HIREDATE, '*' MAIOR_DATA 2 FROM EMP 3 WHERE HIREDATE = (SELECT MAX(HIREDATE) FROM EM P) 4 UNION 5 SELECT ENAME, HIREDATE, ' ' 6 FROM EMP 7 WHERE HIREDATE < (SELECT MAX(HIREDATE) FROM EM P)

80

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} ON mostra os comando executados a partir de um arquivo de comandos. OFF não mostra.

FEED[BACK] { 6 n off on}

'n' faz com que seja mostrado o número de linhas selecionadas na tela, quando 'n' ou mais linhas são selecionadas.

HEA[DING] {off ON}

ON causa a impressão do cabeçalho da coluna no relatório. OFF suprime a impressão do cabeçalho.

LIN[ESIZE] { 80 n}

Número de caracteres a serem exibidos antes da marca de nova linha, e controle para centrar e ajustar texto à direita.

NEWP[AGE] { 1 n}

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.

NUMF[ORMAT] formato

Formato padrão para resultados numéricos.

NUM[WIDTH] { 10 n}

Largura padrão para colunas numéricas.

PAGES[IZE] { 24 n}

Número de linhas por página.

PAU[SE] { OFF on texto}

ON aguarda pressionar ENTER antes de mostrar a nova tela. OFF suprime a espera. Texto especifica o texto a ser mostrado

VERIFY {off ON}

ON faz com que o texto de uma linha de comando seja mostrado antes e depois de aplicada a variável de substituição.

TIMING { OFF on}

ON mostra as estatísticas de tempo de cada comando SQL processado.

SPACE { 1 n}

Número de espaços entre colunas. Máximo igual a 10.

TERM[OUT] {off ON}

OFF suprime a exibição dos resultados produzidos pelos comandos executados a partir de um arquivo, mantendo o Spool.

SQLCASE {MIXED lower upper}

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.

81

• 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 SIGNIFICADO An alfanumérico, largura n. 9 posição numérica, como 999999. 0 coloca zeros à esquerda, como 099999. $ cifrão flutuante, como $999999. . ponto decimal, como 999999.99. , vírgula, como 999,999. MI sinal de menos à direita, como 999999MI. PR número negativo entre parênteses, como 999999PR. EEEE notação científica, como 99.9999EEEE. V multiplicar por 10**n, como 9999V99. B valores zero em branco, como B9999.99.

10.3.2 Outras opções de exibição das colunas.

WRAP TRUNC

Especifica o que fazer quando o conteúdo da coluna excede a sua largura. O padrão é WRAP.

WORD_WRAPPED Move a palavra toda. CLEAR Remove a formatação prévia da coluna. HEADING Especifica um cabeçalho para a coluna. JUSTIFY LEFT RIGHT CENTER

Alinhamento do cabeçalho da coluna. O padrão é JUSTIFY LEFT para caracteres e datas, e JUSTIFY RIGHT para números.

LIKE nome_da_coluna

Copia a especificação de uma outra coluna.

NEWLINE Começa uma nova linha antes da coluna especificada ser exibida.

NULL texto Coloca o texto especificado quando o valor da coluna for nulo.

PRINT NOPRINT

Faz com que a coluna apareça ou não no relatório.

TEMP Especifica que a formatação é válida para uma consulta apenas.

10.4 Exemplo de formatação de colunas

82

SQL> COLUMN DEPTNO FORMAT 099 HEADING 'Dept.' SQL> COLUMN JOB FORMAT A9 HEADING 'Cargo' JUSTIFY R IGHT SQL> COLUMN EMPNO FORMAT 9999 HEADING 'Num.|Emp.' SQL> COLUMN SAL FORMAT 99,999.99 HEADING 'Salario|M ensal' SQL> COLUMN COMM FORMAT 99,990.99 HEADING 'Comissao ' NULL 'Sem com.' SQL> COLUMN REM FORMAT 999,999.99 HEADING 'Remunera cao|Total' SQL> SELECT DEPTNO, JOB, EMPNO, SAL, COMM, SAL*12+N VL(COMM,0) REM 2 FROM EMP Num. Salario Remuneracao Dept. Cargo Emp. Mensal Comissao T otal ----- --------- ----- ---------- ---------- ------- ---- 020 CLER K 7369 800.00 Sem com. 9,600.00 030 SALESMAN 7499 1,600.00 300.00 19,500. 00 030 SALESMAN 7521 1,250.00 500.00 15,500. 00 020 MANAGER 7566 2, 975.00 Sem com. 35,700.00 030 SALESMAN 7654 1,250.00 1,400.00 16,400. 00 030 MANAGER 7698 2,850.00 Sem com. 34,200. 00 010 MANAGER 7782 2,450.00 Sem com. 29,400.00 020 ANALYST 7788 3,000.00 Sem com. 36,000. 00 010 PRESIDENT 7839 5,000.00 Sem com. 60,000. 00 030 SALESMAN 7844 1,500.00 0.00 18,00 0.00 020 CLERK 7876 1,100.00 Sem com. 13,200. 00 030 CLERK 7900 950.00 Sem com. 11,400. 00 020 ANALYST 7902 3,000.00 Sem com. 36,00 0.00 010 CLERK 7934 1,300.00 Sem com. 15,600. 00

10.5 Exemplo de formatação de título

TTITLE 'título' 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.

BTITLE 'título' Imprime um texto centrado ao fim de cada página. O caracter '|' serve para quebrar linhas.

TTITLE OFF Limpa o título superior. BTITLE OFF Limpa o título inferior.

SQL> TTITLE 'RELATORIO DA EMPRESA|Produzido pelo DP ' SQL> BTITLE '*** CONFIDENCIAL ***' SQL> / Thu Oct 27 page 1 RELATORIO DA EMPRESA Produzido pelo DP Num. Salario Remuner acao Dept. Cargo Emp. Mensal Comissao T otal ----- --------- ----- ---------- ---------- ------- ---- 020 CLERK 7369 800.00 Sem com. 9,600. 00 030 SALESMAN 7499 1,600.00 300.00 19,500.00 030 SALESMAN 7521 1,250.00 500.00 15,500. 00 020 MANAGER 7566 2,975.00 Sem com. 35,700. 00 030 SALESMAN 7654 1,250. 00 1,400.00 16,400.00 030 MANAGER 7698 2,850.00 Sem com. 34,200. 00 010 MANAGER 7782 2,450.00 Sem com. 29,400. 00 020 ANALYST 7788 3,000.00 Sem com. 36,000.00 010 PRESIDENT 7839 5,000.00 Sem com. 60,000. 00 030 SALESMAN 7844 1,500.00 0.00 18,000. 00 020 CLERK 7876 1,100.00 Sem com. 13,20 0.00 030 CLERK 7900 950.00 Sem com. 11,400. 00 020 ANALYST 7902 3,000.00 Sem com. 36,000. 00

83

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 JOB EMPNO SAL COMM REM ------- --------- ------- ------- ------- ------- 20 CLERK 7369 800 9600 30 SALESMAN 7499 1600 300 19500 30 SALESMAN 752 1 1250 500 15500 20 MANAGER 7566 2975 35700 30 SALESMAN 7654 1250 1400 16400 30 MANAGER 7698 2850 34200 10 MANAGER 7782 2450 29400 20 ANALYST 7788 3000 36000 10 PRESIDENT 7839 5000 60000 30 SALESMAN 7844 1500 0 18000 20 CLERK 7876 1100 13200 30 CLERK 7900 950 11400 20 ANALYST 7902 3000 36000 10 CLERK 7934 1300 15600 14 rows selected.

84

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 Descrição COL n Posicionar a impressão na coluna 'n' na linha corrente. Volta atrás

se a coluna tiver sido passada. SKIP n Saltar 'n' linhas. Se 'n' for omitido salta 1 linha. Se n=0 volta para

o começo da linha corrente. LEFT CENTER RIGHT

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.

TAB n Avança ou recua a posição de impressão 'n' caracteres. O valor de 'n' deve ser negativo para haver o recuo.

FORMAT 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 Descrição SQL.PNO Número da pagina corrente. SQL.LNO Número da linha corrente. SQL.USER Nome do usuário. SQL.SQLCODE 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

85

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 Produzid o por: Contabilidade Relatorio Confidencial de Vendas --------------------------- ----- ENAME JOB SAL COMM ---------- --------- ------- ------- ALLEN SALESMAN 1600 300 WARD SALESMAN 1250 500 MARTIN SALESMAN 1250 1400 TURNER 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 'Pag ina:' SQL.PNO SKIP - > LEFT 'Usuario: ' SQL.USER SKIP - > CENTER 'Um relatorio com a data reformatada no ti tulo' SKIP 2 SQL> SELECT SYSDATE 2 FROM SYS.DUAL Data: 31-OCT-94 Pagina: 1 Usuario: ALUNO1 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.

86

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> SELECT JOB 2 FROM EMP 3 GROUP BY JOB 4 HAVING AVG(SAL) = &MAIOR_SALARIO_MEDIO old 4: HAVING AVG(SAL) = &MAIOR_SALARIO_MEDIO new 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 R IGHT SQL> COLUMN EMPNO FORMAT 9999 HEADING 'Emp.|Num.'

87

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 'Remune racao|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+N VL(COMM,0) REM 2 FROM EMP 3 ORDER BY DEPTNO, JOB Mon Oct 31 page 1 RELATORIO DA COMPANHIA Produzido pelo Departamento P essoal 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 7788 3,000.00 Sem Com. 36, 000.00 7902 3,000. 00 Sem Com. 36,000.00 CLERK 7369 800.00 Sem Com. 9,600. 00 7876 1,100.00 Sem Com. 13,200.00 MANAGER 7566 2,975.00 Sem Com. 35,700.00 030 CLERK 7900 950.00 Sem Com. 11, 400.00 MANAGER 7698 2,850.00 Sem Com. 3 4,200.00 SALESMAN 7499 1,600.00 300.00 19,500. 00 7654 1,250.00 1,400.00 16,400.00 7844 1,500.00 0.00 18,000.00 7521 1,250.00 500.00 15,500.00 *** CONFIDENCIAL *** 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 Descrição Tipo da coluna AVG calcula a média número COU[NT] conta os valores não nulos qualquer MAX[IMUM] valor máximo número ou caracter MIN[IMUM] valor mínimo número ou caracter NUM[BER] número de linhas qualquer

88

STD desvio padrão número SUM soma os valores não nulos número VAR[IANCE] calcula a variância 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 OF SAL ON DEPTNO COMPUTE sum avg OF SAL ON REPORT COMPUTE sum avg OF COMM ON DEPTNO COMPUTE sum avg OF 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 R IGHT 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 'COMISSA O|Y-T-D' NULL 'Sem Com.' SQL> COLUMN REM FORMAT 9,999,999.99 HEADING 'Remune racao|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+N VL(COMM,0) REM 2 FROM EMP 3 ORDER BY DEPTNO, JOB Mon Oct 31 page 1 Relatorio da Companhia Produzido pelo Departamento P essoal Emp. Salario COMISSAO Remuneracao Dept. Cargo Num. Mensal Y-T- D Total ------ --------- ----- ----------- ----------- ---- --------- 00010 CLERK 7934 1,300.00 Sem Com. 1 5,600.00 MANAGER 7782 2,450.00 Sem Com. 29,400. 00 PRESIDENT 7839 5,000.00 Sem Com. 60,000.00 ****** ********* ----------- ----------- avg 2,916.67 sum 8,750.00 0.00 00020 ANALYST 7788 3,000.00 Sem Com. 3 6,000.00 7902 3,000.00 Sem Com. 36,000.00 CLERK 7369 800.00 Sem Com. 9,600.00 7876 1,100.00 Sem Com. 13,200.00 MANAGER 7566 2,975.00 Sem Com. 35,700. 00

89

****** ********* ----------- ----------- avg 2,175.00 sum 10,875.00 0.00 00030 CLERK 7900 950.00 Sem Com. 1 1,400.00 MANAGER 7698 2,850.00 Sem Com. 34,200. 00 SALESMAN 7499 1,600.00 300.00 19,500.00 7654 1,250.00 1,400.00 16,400.00 7844 1,500.00 0.00 18,000.00 7521 1,250.00 500.00 15,500.00 ****** ********* ----------- ----------- avg 1,566.67 550.00 sum 9,400.00 2,200.00 ----------- ----------- avg 2,073.21 550.00 sum 29,025.00 2, 200.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 Departame nto 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

90

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> SELECT JOB, 2 DECODE(DEPTNO, 10, SAL, 0) D10, 3 DECODE(DEPTNO, 20, SAL, 0) D20, 4 DECODE(DEPTNO, 30, SAL, 0) D30 5 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> SELECT JOB, 2 SUM(DECODE(DEPTNO, 10, SAL, 0)) D10, 3 SUM(DECODE(DEPTNO, 20, SAL, 0)) D20, 4 SUM(DECODE(DEPTNO, 30, SAL, 0)) D30 5 FROM EMP 6 GROUP BY JOB JOB D10 D20 D30 --------- ------- ------- ------- ANALYST 0 6000 0 CLERK 1300 190 0 950 MANAGER 2450 2975 2850 PRESIDENT 5000 0 0 SALESMAN 0 0 5600

d) Calcular os totais para cada grupo de cargo.

91

SQL> SELECT JOB, 2 SUM(DECODE(DEPTNO, 10, SAL, 0)) D10, 3 SUM(DECODE(DEPTNO, 20, SAL, 0)) D20, 4 SUM(DECODE(DEPTNO, 30, SAL, 0)) D30, 5 SUM(SAL) TBJ 6 FROM EMP 7 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> SET PAGES 16 SQL> COLUMN D10 HEADING 'Departamento 10' FORMAT 99 ,999.99 SQL> COLUMN D20 HEADING 'Departamento 20' FORMAT 99 ,999.99 SQL> COLUMN D30 HEADING 'Departamento 30' FORMAT 99 ,999.99 SQL> COLUMN TBJ HEADING 'Total por Cargo' FORMAT 99 9,999.99 SQL> BREAK ON REPORT SQL> COMPUTE SUM OF D10 D20 D30 TBJ ON REPORT SQL> TTITLE 'Relatorio da Companhia' SQL> BTITLE 'Confidencial' SQL> /

Mon Oct 31 page 1 Relatorio da Companhia JOB Departamento 10 Departamento 20 Departame nto 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

92

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)

93

11.8 Exercícios a) Produza o relatório mostrado abaixo Mon Oct 31 page 1 R E L A T O R I O D O S E M P R E G A D O S EMP. DATA SALAR IO COM. DEPARTAMENTO CARGO NUM. NOME ADM. MENS AL ANUAL TOTAL ------------ --------- ----- -------- ----- ------- -- -------- ----------- ACCOUNTING CLERK 7934 MILLER 11/83 1,300. 00 0.00 15,600.00 MANAGER 7782 CLARK 05/84 2,450. 00 0.00 29,400.00 PRESIDENT 7839 KING 07/84 5,000. 00 0.00 60,000.00 ************ ********* ------- -- -------- ----------- sum 8,750. 00 0.00 105,000.00 RESEARCH ANALYST 7902 FORD 12/83 3,000. 00 0.00 36,000.00 7788 SCOTT 03/84 3,000. 00 0.00 36,000.00 CLERK 7369 SMITH 06/83 800. 00 0.00 9,600.00 7876 ADAMS 06/84 1,100. 00 0.00 13,200.00 MANAGER 7566 JONES 10/83 2,975. 00 0.00 35,700.00 ************ ********* ------- -- -------- ----------- sum 10,875. 00 0.00 130,500.00 SALES CLERK 7900 JAMES 07/84 950. 00 0.00 11,400.00 MANAGER 7698 BLAKE 06/84 2,850. 00 0.00 34,200.00 SALESMAN 7499 ALLEN 08/83 1,600. 00 300.00 19,500.00 7654 MARTIN 12/83 1,250. 00 1,400.00 16,400.00 7844 TURNER 06/84 1,500. 00 0.00 18,000.00 7521 WARD 03/84 1,250. 00 500.00 15,500.00 ************ ********* ------- -- -------- ----------- sum 9,400. 00 2,200.00 115,000.00 ------- -- -------- ----------- sum 29,025. 00 2,200.00 350,500.00 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

b) Produza o relatório abaixo. O número do departamento deve ser solicitado em tempo de execução. Mon Oct 31 page 1 R E L A T O R I O D O S E M P R E G A D O S EMP. DATA SALARI O COMISSAO DEPARTAMENTO CARGO NUM. NOME ADM. MENSA L ANUAL TOTAL ------------ --------- ----- -------- ----- ------- - --------- ----------- SALES CLERK 7900 JAMES 07/84 950.0 0 0.00 11,400.00 MANAGER 7698 BLAKE 06/84 2,850.0 0 0.00 34,200.00 SALESMAN 7499 ALLEN 08/83 1,600.0 0 300.00 19,500.00 7521 WARD 03/84 1,250.0 0 500.00 15,500.00 7844 TURNER 06/84 1,500.0 0 0.00 18,000.00 7654 MARTIN 12/83 1,250.0 0 1,400.00 16,400.00 ************ ********* ------- - --------- ----------- sum 9,400.0 0 2,200.00 115,000.00 ------- - --------- ----------- sum 9,400.0 0 2,200.00 115,000.00 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

95


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|MENSA L' TEMP SQL> COL G FORMAT 9,990.99 HEADING 'COM.|ANUAL' TEM P 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, S AL*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(CO MM,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|MENSA L' 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, S AL*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(CO MM,0) G, SAL*12+NVL(COMM,0) H old 5: AND E.DEPTNO = &DEPTNO new 5: AND E.DEPTNO = 30

96

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=783 9) | | | 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> SELECT LEVEL, DEPTNO, EMPNO, ENAME, JOB, SAL 2 FROM EMP 3 CONNECT BY PRIOR EMPNO = MGR 4 START WITH MGR IS NULL

97

LEVEL DEPTNO EMPNO ENAME JOB SAL ------- ------- ------- ---------- --------- ------ - 1 10 7839 KING PRESIDENT 5000 2 20 7566 JONES MANAGER 2975 3 7788 SCOTT ANALYST 3000 4 7876 ADAMS CLERK 1100 3 7902 FORD ANALYST 3000 4 7369 SMITH CLERK 800 2 30 7698 BLAKE MANAGER 2850 3 7499 ALLEN SALESMAN 160 0 3 7521 WARD SALESMAN 1250 3 7654 MARTIN SALESMAN 1250 3 7844 TURNER SALESMAN 150 0 3 7900 JAMES CLERK 950 2 10 7782 CLARK MANAGER 2450 3 7934 MILLER CLERK 1300

Comando Descrição

SELECT A cláusula SELECT padrão, incluindo a pseudo coluna LEVEL que mostra a distância do nó à raiz.

FROM Só pode conter uma tabela. WHERE Restringe as linhas pesquisadas durante a navegação. CONNECT BY Especifica as colunas onde existe o relacionamento entre as

linhas. Esta cláusula é obrigatório para caminhar na árvore. PRIOR 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.

START WITH Especifica onde começa a pesquisa. Não pode ser usado na forma 'START at a LEVEL'. Esta cláusula é opcional.

ORDER BY É a última cláusula, como sempre. 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> SELECT LEVEL, DEPTNO, EMPNO, ENAME, JOB, SAL 2 FROM EMP 3 CONNECT BY PRIOR EMPNO = MGR 4 START WITH MGR IS NULL 5 ORDER BY DEPTNO

98

LEVEL DEPTNO EMPNO ENAME JOB SA L ------- ------- ------- ---------- --------- ------ - 1 10 7839 KING PRESIDENT 5000 2 7782 CLARK MANAGER 2450 3 7934 MILLER CLERK 1300 2 20 7566 JONES MANAGER 2975 3 7788 SCOTT ANALYST 3000 4 7876 ADAMS CLERK 1100 3 7902 FORD ANALYST 3000 4 7369 SMITH CLERK 800 2 30 7698 BLAKE MANAGER 2850 3 7499 ALLEN SALESMAN 1600 3 7521 WARD SALESMAN 1250 3 7654 MARTIN SALESMAN 1250 3 7844 TURNER SALESMAN 150 0 3 7900 JAMES CLERK 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> SELECT LEVEL, DEPTNO, EMPNO, ENAME, JOB, SAL 2 FROM EMP 3 WHERE ENAME <> 'SCOTT' 4 CONNECT BY PRIOR EMPNO = MGR 5 START WITH MGR IS NULL

99

LEVEL DEPTNO EMPNO ENAME JOB SAL ------- ------- ------- ---------- --------- ------ - 1 10 7839 KING PRESIDENT 5000 2 20 7566 JONES MANAGER 2975 4 7876 ADAMS CLERK 1100 3 7902 FORD ANALYST 3000 4 7369 SMITH CLERK 800 2 30 7698 BLAKE MANAGER 2850 3 7499 ALLEN SALESMAN 1600 3 7521 WARD SALESMAN 1250 3 7654 MARTIN SALESMAN 1250 3 7844 TURNER SALESMAN 1500 3 7900 JAMES CLERK 950 2 10 7782 CLARK MANAGER 2450 3 7934 MILLER CLERK 130 0 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> SELECT LEVEL, DEPTNO, EMPNO, ENAME, JOB, SAL 2 FROM EMP 3 CONNECT BY PRIOR EMPNO = MGR AND ENAME <> 'SCO TT' 4 START WITH MGR IS NULL

100

LEVEL DEPTNO EMPNO ENAME JOB SAL ------- ------- ------- ---------- --------- ------ - 1 10 7839 KING PRESIDENT 5000 2 20 7566 JONES MA NAGER 2975 3 7902 FORD ANALYST 3000 4 7369 SMITH CLERK 800 2 30 7698 BLAKE MANAGER 2850 3 7499 ALLEN SALESMAN 1600 3 7521 WARD SALESMAN 125 0 3 7654 MARTIN SALESMAN 1250 3 7844 TURNER SALESMAN 1500 3 7900 JAMES CLERK 950 2 10 7782 CLARK MANAGER 2450 3 7934 MILLER CLERK 1300 12 rows selected.

12.4 Terminologia

Inglês Português Descrição

NODE Nó O mesmo que uma linha da tabela.

ROOT Raiz O nó que não pertence a nenhum outro nó.

PARENT Pai Um nó que possui outros nós de nível inferior.

CHILD Filho Um nó que tem um pai. TERMINAL NODE LEAF

Folha Um nó que não possui filhos.

BRANCH Ramo ou Galho Um nó que tem filhos e netos.

12.5 Exercícios a) Mostrar a organização do departamento 20.

101

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: • 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.

13.4 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.

102

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 Descrição USER_xxx Visões que qualquer usuário pode acessar, contendo

informações relativas aos objetos do próprio usuário. ALL_xxx 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.

DBA_xxx 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 Descrição DICTIONARY Description of data dictionary tables and

views DICT_COLUMNS Description of columns in data dictionary

tables and views TABLE_PRIVILEGES Grants on objects for which the user is the

grantor, grantee, or owner, or PUBLIC is the grantee

Visão Descrição ALL_CATALOG All tables, views, synonyms, sequences

accessible to the user ALL_COL_COMMENTS Comments on columns of accessible tables

and views ALL_COL_PRIVS Grants on columns for which the user is the

grantor, grantee, owner, or an enabled role or PUBLIC is the grantee

ALL_COL_PRIVS_MADE Grants on columns for which the user is owner or grantor

ALL_COL_PRIVS_RECD Grants on columns for which the user, PUBLIC or enabled role is the grantee

ALL_CONSTRAINTS Constraint definitions on accessible tables ALL_CONS_COLUMNS Information about accessible columns in

constraint definitions ALL_DB_LINKS Database links accessible to the user ALL_DEF_AUDIT_OPTS Auditing options for newly created objects ALL_DEPENDENCIES Dependencies to and from objects accessible

to the user ALL_ERRORS Current errors on stored objects that user is

allowed to create

103

ALL_INDEXES Descriptions of indexes on tables accessible to the user

ALL_IND_COLUMNS COLUMNs comprising INDEXes on accessible TABLES

ALL_OBJECTS Objects accessible to the user ALL_SEQUENCES Description of SEQUENCEs accessible to

the user ALL_SNAPSHOTS Snapshots the user can look at ALL_SOURCE Current source on stored objects that user is

allowed to create ALL_SYNONYMS All synonyms accessible to the user ALL_TABLES Description of tables accessible to the user ALL_TAB_COLUMNS Columns of all tables, views and clusters ALL_TAB_COMMENTS Comments on tables and views accessible to

the user ALL_TAB_PRIVS Grants on objects for which the user is the

grantor, grantee, owner, or an enabled role or PUBLIC is the grantee

ALL_TAB_PRIVS_MADE User's grants and grants on user's objects ALL_TAB_PRIVS_RECD Grants on objects for which the user,

PUBLIC or enabled role is the grantee ALL_TRIGGERS Triggers accessible to the current user ALL_TRIGGER_COLS Column usage in user's triggers or in triggers

on user's tables ALL_USERS Information about all users of the database ALL_VIEWS Text of views accessible to the user

Visão Descrição DBA_2PC_NEIGHBORS information about incoming and outgoing

connections for pending transactions DBA_2PC_PENDING info about distributed transactions awaiting

recovery DBA_AUDIT_EXISTS Lists audit trail entries produced by AUDIT

NOT EXISTS and AUDIT EXISTS DBA_AUDIT_OBJECT 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

DBA_AUDIT_STATEMENT Audit trail records concerning grant, revoke, audit, noaudit and alter system

DBA_AUDIT_TRAIL All audit trail entries DBA_CATALOG All database Tables, Views, Synonyms,

Sequences DBA_CLUSTERS Description of all clusters in the database DBA_CLU_COLUMNS Mapping of table columns to cluster columns DBA_COL_COMMENTS Comments on columns of all tables and

104

views DBA_COL_PRIVS All grants on columns in the database DBA_CONSTRAINTS Constraint definitions on all tables DBA_CONS_COLUMNS Information about accessible columns in

constraint definitions DBA_DATA_FILES Information about database files DBA_DB_LINKS All database links in the database DBA_DEPENDENCIES Dependencies to and from objects DBA_ERRORS Current errors on all stored objects in the

database DBA_EXP_FILES Description of export files DBA_EXP_OBJECTS Objects that have been incrementally

exported DBA_EXP_VERSION Version number of the last export session DBA_EXTENTS Extents comprising all segments in the

database DBA_FREE_SPACE Free extents in all tablespaces DBA_INDEXES Description for all indexes in the database DBA_IND_COLUMNS COLUMNs comprising INDEXes on all

TABLEs and CLUSTERs DBA_OBJECTS All objects in the database DBA_OBJECT_SIZE Sizes, in bytes, of various pl/sql objects DBA_OBJ_AUDIT_OPTS Auditing options for all tables and views DBA_PRIV_AUDIT_OPTS Describes current system privileges being

audited across the system and by user DBA_PROFILES Display all profiles and their limits DBA_ROLES All Roles which exist in the database DBA_ROLE_PRIVS Roles granted to users and roles DBA_ROLLBACK_SEGS Description of rollback segments DBA_SEGMENTS Storage allocated for all database segments DBA_SEQUENCES Description of all SEQUENCEs in the

database DBA_SNAPSHOTS All snapshots in the database DBA_SNAPSHOT_LOGS All snapshot logs in the database DBA_SOURCE Source of all stored objects in the database DBA_STMT_AUDIT_OPTS Describes current system auditing options

across the system and by user DBA_SYNONYMS All synonyms in the database DBA_SYS_PRIVS System privileges granted to users and roles DBA_TABLES Description of all tables in the database DBA_TABLESPACES Description of all tablespaces DBA_TAB_COLUMNS Columns of all tables, views and clusters DBA_TAB_COMMENTS Comments on all tables and views in the

database DBA_TAB_PRIVS All grants on objects in the database DBA_TRIGGERS All triggers in the database DBA_TRIGGER_COLS Column usage in all triggers

105

DBA_TS_QUOTAS Tablespace quotas for all users DBA_USERS Information about all users of the database DBA_VIEWS Text of all views in the database

Visão Descrição USER_AUDIT_OBJECT 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

USER_AUDIT_STATEMENT Audit trail records concerning grant, revoke, audit, noaudit and alter system

USER_AUDIT_TRAIL Audit trail entries relevant to the user USER_CATALOG Tables, Views, Synonyms and Sequences

owned by the user USER_CLUSTERS Descriptions of user's own clusters USER_CLU_COLUMNS Mapping of table columns to cluster columns USER_COL_COMMENTS Comments on columns of user's tables and

views USER_COL_PRIVS Grants on columns for which the user is the

owner, grantor or grantee USER_COL_PRIVS_MADE All grants on columns of objects owned by

the user USER_COL_PRIVS_RECD Grants on columns for which the user is the

grantee USER_CONSTRAINTS Constraint definitions on user's own tables USER_CONS_COLUMNS Information about accessible columns in

constraint definitions USER_DB_LINKS Database links owned by the user USER_DEPENDENCIES Dependencies to and from a users objects USER_ERRORS Current errors on stored objects owned by the

user USER_EXTENTS Extents comprising segments owned by the

user USER_FREE_SPACE Free extents in tablespaces accessible to the

user USER_INDEXES Description of the user's own indexes USER_IND_COLUMNS COLUMNs comprising user's INDEXes or

on user's TABLES USER_OBJECTS Objects owned by the user USER_OBJECT_SIZE Sizes, in bytes, of various pl/sql objects USER_OBJ_AUDIT_OPTS Auditing options for user's own tables and

views

106

USER_RESOURCE_LIMITS Display resource limit of the user USER_ROLE_PRIVS Roles granted to current user USER_SEGMENTS Storage allocated for all database segments USER_SEQUENCES Description of the user's own SEQUENCEs USER_SNAPSHOTS Snapshots the user can look at USER_SNAPSHOT_LOGS All snapshot logs owned by the user USER_SOURCE Source of stored objects accessible to the

user USER_SYNONYMS The user's private synonyms USER_SYS_PRIVS System privileges granted to current user USER_TABLES Description of the user's own tables USER_TABLESPACES Description of accessible tablespaces USER_TAB_COLUMNS Columns of user's tables, views and clusters USER_TAB_COMMENTS Comments on the tables and views owned by

the user USER_TAB_PRIVS Grants on objects for which the user is the

owner, grantor or grantee USER_TAB_PRIVS_MADE All grants on objects owned by the user USER_TAB_PRIVS_RECD Grants on objects for which the user is the

grantee USER_TRIGGERS Triggers owned by the user USER_TRIGGER_COLS Column usage in user's triggers USER_TS_QUOTAS Tablespace quotas for the user USER_USERS Information about the current user USER_VIEWS 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 Null? Type ------------------------------- -------- ---- OWNER NOT NULL CHAR(30) TABLE_NAME NOT NULL CHAR(30) TABLE_TYPE CHAR(11) SQL> SELECT * 2 FROM ACCESSIBLE_TABLES 3 ORDER BY 1,2 OWNER TABLE_NA ME TABLE_TYPE -------------------- -------------------- --------- -- ALUNO1 ASSIGNMENTS TABLE ALUNO1 BONUS TAB LE

107

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 ACCESSIB LE_TABLES VIEW SYS ALL_CATALOG VIEW SYS ALL_COL_COMMENTS VIEW SYS ALL_COL_GRANTS_MADE VIE W .............................................. SYS ALL_TAB_GRANTS_RECD VIEW SYS ALL_USERS VIEW SYS ALL_VIEWS VIEW SYS AUDIT_ACCESS VIEW SYS AUDIT_ACTIONS TABLE SYS AUDIT_CON NECT 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 V4EXPTABAUT H 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 Null? Type ------------------------------- -------- ---- USERNAME NOT NULL CHAR(30) USER_ID NOT NULL NUMBER CREATED NOT NULL 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

108

ALUNO1 4 19-OCT- 94 _NEXT_USER 5 30-DEC-91 6 rows selected.

Listar todos os índices criados nas tabelas do usuário. SQL> DESCRIBE USER_INDEXES Name Null? Type ------------------------------- -------- ---- INDEX_NAME NOT NULL CHAR(30) TABLE_OWNER NOT NULL CHAR(30) TABLE_NAME NOT NULL CHAR(30) TABLE_TYPE CHAR(11) UNIQUENESS CHAR(9) TABLESPACE_NAME NOT NULL CHAR(30) INI_TRANS NOT NULL NUMBER MAX_TRANS NOT NULL NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NOT NULL NUMBER MAX_EXTENTS NOT NULL NUMBER PCT_INCREASE NOT NULL NUMBER PCT_FREE NOT NULL NUMBER STATUS CHAR(17) SQL> COLUMN INDEX_NAME FORMAT A20 SQL> COLUMN TABLE_OWNER FORMAT A20 SQL> COLUMN TABLE_NAME FORMAT A20 SQL> SELECT INDEX_NAME, TABLE_OWNER, TABLE_NAME, TABLE_TYPE 2 FROM USER_INDEXES 3 ORDER BY 1,2 INDEX_NAME TABLE_OWNER TABLE_NAM E TABLE_TYPE -------------------- -------------------- --------- ----------- -------- ITEM_INDEX ALUNO1 ITEM TABLE ORD_INDEX ALUNO1 ORD TABLE PRICE_INDEX ALUNO1 PRICE TABLE PRODUCT_INDEX ALUNO1 PRODUCT TABLE

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 Null? Type ------------------------------- -------- ---- OWNER NOT NULL CHAR(30) TABLE_NAME NOT NULL CHAR(30) COLUMN_NAME NOT NULL CHAR(30) COMMENTS CHAR(255)

13.7 Exercícios a) Mostrar as seguintes informações para a tabela EMP:

109

Coluna Nulo? Tipo ------------------------------- -------- ---------- ---- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(1 0) JOB VARCHAR2(9 ) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2 ) COMM NUMBER(7,2 ) DEPTNO NUMBER(2)

110

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 Válido EMP85 Sim 85EMP Não, começa com número. LUCRO_BRUTO Sim LUCRO BRUTO Não, contém espaço. UPDATE Não, palavra reservada. TABELA1 Sim, mas nome não diz nada.

111

14.4 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.

14.5 Tipo de dado das colunas

Tipo Conteúdo CHAR(n) Caracteres alfanuméricos com até 'n' caracteres. Coluna de

tamanho constante independente do que está armazenado. Máximo de 255 caracteres.

VARCHAR(n) Caracteres alfanuméricos com até 'n' caracteres. No Oracle 6 é sinônimo de CHAR, e pode ter no máximo 255 caracteres.

VARCHAR2(n) 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.

LONG 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.

RAW(n) 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.

LONG RAW Semelhante a RAW, porém podendo armazenar até 2 GigaBytes. ROWID Caracteres hexadecimais representando o endereço único de uma

linha em uma tabela. MLSLABEL Formato binário to rótulo do sistema operacional. Usado com o

Trusted ORACLE. NUMBER(p,s) 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 DATE 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.

FLOAT Número de ponto flutuante com precisão decimal de 38 dígitos, ou precisão binária de 126 dígitos.

112

Dado Especificação Valor armazenado 7456123,89 NUMBER 7456123,89 7456123,89 NUMBER(9) 7456124 7456123,89 NUMBER(9,2) 7456123,89 7456123,89 NUMBER(9,1) 7456123,9 7456123,8 NUMBER(6) precisão excedida 7456123,8 NUMBER(15,1) 7456123,8 7456123,89 NUMBER(7,-2) 7456100 7456123,8 NUMBER(7,2) precisão excedida

14.6 Relação entre os tipos de dados ANSI e do Oracle

ANSI ORACLE CHARACTER(n), CHAR(n) CHAR(n) CHARACTER VARYING(n), CHAR VARYING(n) VARCHAR(n) NUMERIC(p,s), DECIMAL(p,s), DEC(p,s) NUMBER(p,s) INTEGER, INT, SMALLINT NUMBER(38) FLOAT(b), DOUBLE PRECISION, REAL NUMBER

14.7 Relação entre os tipos de dados do SQL/DS e do Oracle

SQL/DS ou DB2 ORACLE CHARACTER(n) CHAR(n) VARCHAR(n) VARCHAR(n) LONG VARCHAR LONG DECIMAL(p,s) NUMBER(p,s) INTEGER, SMALLINT NUMBER(38) FLOAT(b) NUMBER GRAPHIC - LONG VARGRAPHIC - VARGRAPHIC - TIMESTAMP DATE TIME DATE

14.8 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]

113

Restrição Descrição CONSTRAINT nome_da_restriçã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.

NULL NOT NULL

Especifica se a coluna pode ou não conter valores nulos.

UNIQUE 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.

PRIMARY KEY 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.

FOREIGN KEY (coluna...) REFERENCES tabela(col(s))

Identifica a(s) coluna(s) como chave estrangeira . REFERENCES identifica a chave primária ou chave única que é referenciada.

CHECK condição 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.

DISABLE (ENABLE)

Desativa (ativa) uma restrição de integridade. O padão é a restrição ativa.

EXCEPTIONS INTO

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) NOT NULL, DNAME CHAR(14), LOC CHAR(13), PRIMARY KEY (DEPTNO) 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,

114

FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) CON STRAINT EMP_F_K, PRIMARY KEY (EMPNO) 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) NOT NULL, ENAME CHAR(10), JOB CHAR(9),

115

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 Null? Type ------------------------------- -------- ---- EMPNO NOT NULL NUMBER(4) ENAME CHAR(10) JOB CHAR(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7, 2) COMM NUMBER(7, 2) DEPTNO NOT NULL NUMBER(2) SQL> DESCRIBE EMPREGADOS Name Null? Type ------------------------------- -------- ---- EMPNO NOT NULL NUMBER(4) ENAME CHAR(10) JOB CHAR(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7, 2) COMM NUMBER(7, 2) DEPTNO NOT NULL NUMBER(2) SQL> SELECT * FROM EMPREGADOS EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------- ---------- --------- ------- --------- ---- --- ------- -------

116

7369 SMITH CLERK 7902 13-JUN- 83 800 20 7499 ALLEN SALESMAN 7698 15-AUG- 83 1600 300 30 7521 WARD SALESMAN 7698 26-MAR- 84 1250 500 30 7566 JONES MANAGER 7839 31-OCT- 83 2975 20 7654 MARTIN SALESMAN 7698 05-DEC-83 1 250 1400 30 7698 BLAKE MANAGER 7839 11-JUN- 84 2850 30 7782 CLARK MANAGER 7839 14-MAY- 84 2450 10 7788 SCOTT ANALYST 7566 05-MAR-84 3 000 20 7839 KING PRESIDENT 09-JUL- 84 5000 10 7844 TURNER SALESMAN 7698 04-JUN- 84 1500 0 30 7876 ADAMS CLERK 7788 04-JUN- 84 1100 20 7900 JAMES CLERK 7698 23-JUL- 84 950 30 7902 FORD ANALYST 7566 05-DEC- 83 3000 20 7934 MILLER CLERK 7782 21-NOV- 83 1300 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> CREATE TABLE SALARIOS_DOS_EMPREGADOS 2 (NOME, SALARIO, FAIXA) 3 AS 4 SELECT ENAME, SAL, GRADE 5 FROM EMP, SALGRADE 6 WHERE SAL BETWEEN LOSAL AND HISAL Table created. SQL> DESCRIBE SALARIOS_DOS_EMPREGADOS Name Null? Type ------------------------------- -------- ---- NOME CHAR(10) SALARIO NUMBER(7, 2) FAIXA NUMBER 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:

117

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 Null? Type ------------------------------- -------- ---- EMPNO NOT NULL NUMBER(4) ENAME CHAR(10) JOB CHAR(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7, 2) COMM NUMBER(7, 2) DEPTNO NOT NULL NUMBER(2) NOME_DA_ESPOSA 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 Null? Type ------------------------------- -------- ---- EMPNO NOT NULL NUMBER(4) ENAME CHAR(10) JOB CHAR(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7, 2) COMM NUMBER(7, 2) DEPTNO NOT NULL NUMBER(2) NOME_DA_ESPOSA 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

118

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 NUMBER 4 NOT NULL P_DESC CHARACTER 20 P_START_DATE DATE P_END_DATE DATE BUDGET_AMOUNT NUMBER 7,2 MAX_NO_STAFF NUMBER 2 Tabela: ASSIGNMENTS PROJID NUMBER 4 NOT NULL EMPNO NUMBER 4 NOT NULL

119

A_START_DATE DATE A_END_DATE DATE BILL_RATE NUMBER 4,2 ASSIGN_TYPE CHAR 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.

120


a) SQL> CREATE TABLE PROJECTS ( 2 PROJID NUMBER(4) NOT NULL, 3 P_DESC CHAR(20), 4 P_START_DATE DATE, 5 P_END_DATE DATE, 6 BUDGET_AMOUNT NUMBER(7,2), 7 MAX_NO_STAFF NUMBER(2)) SQL> CREATE TABLE ASSIGNMENTS ( 2 PROJID NUMBER(4) NOT NULL, 3 EMPNO NUMBER(4) NOT NULL, 4 A_START_DATE DATE, 5 A_END_DATE DATE, 6 BILL_RATE NUMBER(4,2), 7 ASSIGN_TYPE CHAR(2)) b) SQL> ALTER TABLE ASSIGNMENTS 2 ADD (HOURS NUMBER(2)) c) SQL> COMMENT ON TABLE PROJECTS IS 'UNIQUE PROJECT D ETAILS' 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 Null? Type ------------------------------- -------- ---- TABLE_NAME NOT NULL CHAR(30) COLUMN_NAME NOT NULL CHAR(30) COMMENTS 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? Type ------------------------------- -------- --------- TABLE_NAME NOT NULL CHAR(30) TABLE_TYPE CHAR(11) COMMENTS CHAR(255) h) SQL> SELECT * FROM USER_TAB_COMMENTS WHERE COMMENTS IS NOT NULL TABLE_NAME TABLE_TYPE COMMENTS --------------- ----------- ----------------------- ------------------- ASSIGNMENTS TABLE ASSIGNMENTS FOR ANY EMP LOYEE ON A PROJECT PROJECTS TABLE UNIQUE PROJECT DETAILS

121

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> CREATE TABLE DEPARTMENT 2 AS 3 SELECT * 4 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 INTO DEPARTMENT ( DEPTNO, DNAME, LOC ) 2 VALUES ( &DEPT_NUM, '&DEPT_NAME', '&DEPT_LOC' ) Enter value for dept_num: 80 Enter value for dept_name: RESEARCH

122

Enter value for dept_loc: ATLANTA old 2: VALUES ( &DEPT_NUM, '&DEPT_NAME', '&DEPT_L OC' ) 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 DNAME LOC ------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 MARKET ING SAN JOSE 60 ALBERTA 70 DETROIT 80 RESEARCH ATLANTA 90 INSURANCE 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> CREATE TABLE EMPLOYEE 2 AS 3 SELECT * 4 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> INSERT INTO EMPLOYEE 2 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) 3 VALUES ( 4 7658, 5 'CODD', 6 'ANALYST', 7 7566, 8 TO_DATE('24/06/2084 9:30','DD/MM/YYYY HH:MI'), 9 3000, 10 NULL,

123

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> CREATE TABLE SALARY_GRADE 2 AS 3 SELECT * FROM SALGRADE 4 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> INSERT INTO SALARY_GRADE 2 SELECT * 3 FROM SALGRADE 4 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 nome_da_tabela [aliás] SET coluna, [,coluna...] = {expressão ou sub-consulta} [WHERE condição] Para atualizar as informações do funcionário SCOTT.

SQL> UPDATE EMPLOYEE 2 SET 3 JOB = 'SALESMAN', 4 HIREDATE = SYSDATE, 5 SAL = SAL*1.1 6 WHERE ENAME = 'SCOTT' 1 row updated.

124

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 JOB ---------- --------- CODD ANALYST FORD ANALYST ADAMS CLERK JAMES CLERK MILLER CLERK SMITH CLERK BLAKE MANAGER CLARK MANAGER JONES MANAGER KING PRESIDENT ALLEN VENDEDOR MARTIN VENDEDOR SCOTT VENDEDOR TURNER VENDEDOR WARD 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

125

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 1 2 P_DESC WRITE C030 COURSE PROOF READ NOTES P_START_DATE 02-JAN-88 01-JAN-89 P_END_DATE 07-JAN-88 10-JAN-89 BUDGET_AMOUNT 500 600 MAX_NO_STAFF 1 1 b) Inserir as seguintes linhas na tabela ASSIGNMENTS PROJID 1 1 2 EMPNO 7369 7902 7844 A_START_DATE 01-JAN-88 04-JAN-88 01-JAN-89 A_END_DATE 03-JAN-88 07-JAN-88 10-JAN-89 BILL_RATE 50.00 55.00 45.50 ASSIGN_TYPE WR WR PF HOURS 15 20 30 c) Alterar ASSIGNMENT_TYPE: trocar WR por WT.

126


a) SQL> INSERT INTO PROJECTS 2 (PROJID,P_DESC,P_START_DATE,P_END_DATE,BUDGET_ AMOUNT,MAX_NO_STAFF) 3 VALUES 4 (1,'WRITE C030 COURSE','02-JAN-88','07-JAN-88' ,500,2) SQL> INSERT INTO PROJECTS 2 (PROJID,P_DESC,P_START_DATE,P_END_DATE,BUDGET_ AMOUNT,MAX_NO_STAFF) 3 VALUES 4 (2,'PROOF READ NOTES','01-JAN-89','10-JAN-89', 600,1) b) SQL> INSERT INTO ASSIGNMENTS 2 (PROJID,EMPNO,A_START_DATE,A_END_DATE,BILL_RAT E,ASSIGN_TYPE,HOURS) 3 VALUES 4 (1,7369,'01-JAN-88','03-JAN-88',50.00,'WR',15) SQL> INSERT INTO ASSIGNMENTS 2 (PROJID,EMPNO,A_START_DATE,A_END_DATE,BILL_RAT E,ASSIGN_TYPE,HOURS) 3 VALUES 4 (1,7902,'04-JAN-88','07-JAN-88',55.00,'WR',20) SQL> INSERT INTO ASSIGNMENTS 2 (PROJID,EMPNO,A_START_DATE,A_END_DATE,BILL_RAT E,ASSIGN_TYPE,HOURS) 3 VALUES 4 (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'

127

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.

128

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: • COMMIT [WORK] • SAVEPOINT nome_do_savepoint • ROLLBACK [WORK] • ROLLBACK TO nome_do_savepoint

16.8 COMMIT [WORK]

• Torna as mudanças causadas pela transação corrente permanente • Elimina todos os SavePoints • Termina a transação

129

• 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

130

- 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.

131

Para efetivar o comando anterior.

SQL> COMMIT Commit complete.

Para verificar que os departamentos foram realmente eliminados.

SQL> SELECT * FROM DEPARTMENT DEPTNO DNAME LOC ------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS 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 DNAME LOC ------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 TESTING 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

132

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 DNAME LOC ------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLA S 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 TESTING 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.


SQL> SELECT * FROM DEPARTMENT DEPTNO DNAME LOC ------- -------------- ------------- 10 ACCO UNTING NEW YORK 20 RESEARCH DALLAS 30 MARKETING CHICAGO 40 OPERATIONS BOSTO N 50 TESTING 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.


133

SQL> SELECT * FROM DEPARTMENT no rows selected

Para desfazer a eliminação das linhas.

SQL> ROLLBACK Rollback complete.


SQL> SELECT * FROM DEPARTMENT DEPTNO DNAME LOC ------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 MARKETING CHICAGO 40 OPERATIONS BOSTON 50 TESTING 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.

134

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).

135

• Outros usuários podem continuar a atualizar os dados.

Exemplo:

SQL> SET TRANSACTION READ ONLY Transaction set. SQL> SELECT * FROM DEPT DEPTNO DNAME LOC ------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SA LES CHICAGO 40 OPERATIONS BOSTON SQL> COMMIT Commit complete.

136

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.

137

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 Severidade Compartilhado “Share locks”

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.

Exclusivo “Exclusive lock”

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.

138

Níveis de Bloqueio Descrição Nível de Linha “Row Locks (TX)”

- 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.

Nível de Tabela “Table Locks (TM)”

- 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 Descrição Row Share (RS) - 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.

Row Exclusive (RX) - 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.

Share Lock (S) - É 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

139

TABLE IN EXCLUSIVE MODE, LOCK TABLE IN ROW EXCLUSIVE MODE.

Share Row Exclusive (SRX)

- É ativado pelo comando LOCK TABLE IN SHARE 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

Modo de lock da tabela

RS

RX

S

SRX

X

SELECT none S S S S S INSERT RX S S N N N UPDATE RX S* S* N N N DELETE RX S* S* N N N SELECT FOR UPDATE RS S* S* S* S* N LOCK TABLE IN ROW SHARE MODE

RS S S S S N

LOCK TABLE IN ROW EXCLUSIVE MODE

RX S S N N N

LOCK TABLE IN SHARE MODE S S N Y N N LOCK TABLE IN SHARE ROW EXCLUSIVE MODE

SRX S N N N N

LOCK TABLE IN EXCLUSIVE MODE

X N N N N N

17.9 Bloqueios implícitos Relação dos bloqueios adquiridos automaticamente pelo Oracle:

Comando LINHA TABELA SELECT - - INSERT X RX UPDATE X RX DELETE X RX SELECT FOR UPDATE X RS DDL - RX

140

17.10 Bloqueios explícitos

Comando Bloqueio LOCK TABLE tabela IN ROW SHARE MODE RS LOCK TABLE tabela IN ROW EXCLUSIVE MODE RX LOCK TABLE tabela IN SHARE MODE S LOCK TABLE tabela IN SHARE EXCLUSIVE MODE SRX LOCK TABLE tabela IN EXCLUSIVE MODE 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

141

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

142

------------------ ---------- ------------------ EMP RS 2 DEPT RS 1 SALGRADE X 10

143

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.

144

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> CREATE VIEW D10EMP 2 AS 3 SELECT EMPNO, ENAME, DEPTNO 4 FROM EMP 5 WHERE DEPTNO = 10 6 WITH CHECK OPTION View created.

Para recuperar os dados através da visão:

SQL> SELECT * 2 FROM D10EMP EMPNO ENAME DEPTNO ------- ---------- ------- 7782 CLARK 10 7839 KING 10 7934 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, AV GSAL ) 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

145

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 viol ation

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> CREATE VIEW EMP_DATA 2 AS 3 SELECT EMPNO, ENAME, JOB, MGR, SAL, DEPTNO 4 FROM EMP 5 WHERE SAL BETWEEN 1000 AND 2000 6 AND MGR IN ( SELECT DISTINCT EMPNO FROM EMP ) 7 AND DEPTNO IN ( SELECT DEPTNO FROM DEPT ) 8 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> CREATE VIEW EMP_DETAILS 2 AS 3 SELECT EMPNO, ENAME, JOB, DEPTNO 4 FROM EMP 5 WHERE ENAME = USER 6 AND TO_CHAR(SYSDATE,'HH24') BETWEEN 7 AND 17 7 AND TO_CHAR(SYSDATE,'D') BETWEEN 2 AND 6 8 WITH CHECK OPTION View created.

A restrição WITH CHECK OPTION pode ter um nome próprio.

SQL> CREATE VIEW EMPLOYEES ( ID_NUMBER, NAME, POSIT ION, DEPARTMENT ) 2 AS 3 SELECT EMPNO, ENAME, JOB, DEPTNO 4 FROM EMP 5 WHERE DEPTNO IN ( SELECT DISTINCT DEPTNO FROM DEPT ) 6 WITH CHECK OPTION CONSTRAINT DEPT_CHECK View created.

146

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? Type ------------------------------- -------- ---- VIEW_NAME NOT NULL CHAR(30) TEXT_LENGTH NUMBER TEXT 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, DE PTNO FROM EMP WHERE DEPTNO = 10 WITH CHECK OPTION EMPLOYEES 113 SELECT EMPNO, ENAME, JO B, DEPTNO FROM EMP WHERE DEPTNO IN ( SELECT DISTINCT DEPT EMP_DATA 190 SELE CT EMPNO, ENAME, JOB, MGR, SAL, DEPTNO FROM EMP WHERE SAL BETWEEN 1000 AND 2 EMP_DETAILS 165 SELECT EMPNO, ENAME, JO B, DEPTNO FROM EMP WHERE ENAME = USER AND TO_CHAR(SYSDATE SALES 291 SELECT REPID,ORD.CUSTID ,CUSTOMER.NAME CUSTNAME PRODUCT.PRODID, DESCRIP PRODNA

147

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.

148

18.8 Exercícios a) Criar uma visão que produza o seguinte resultado.

SQL> SELECT * FROM AGGREGATES DEPTNO AVERAGE MAXIMUM MINIMUM SUM NO_SALS NO_COMMS ------- ------- ------- ------- ------- ------- --- ----- 10 2916.67 5000 1300 8750 3 0 20 2175 3000 800 10875 5 0 30 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 MA XIMUM 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_STA RT_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 value for id: 1 Enter value for empno: 7566 Enter value for start: 01-JAN-89 Enter value for end: 01-JAN-88 Enter value for bill_r: 40.00 Enter value for asgt: ED Enter value for hours: 20

old 3: (&ID,&EMPNO,'&START','&END','&BILL_R','&AS GT',&HOURS)

149

new 3: (1,7566,'01-JAN-89','01-JAN-88','40.00','E D',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 viol ation 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','&AS GT',&HOURS) new 3: (2,7698,'01-FEB-89','20-FEB-89','55.00','W T',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','&AS GT',&HOURS) new 3: (2,8000,'01-MAR-89','31-DEC-89','69.00','E D',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 viol ation

e) Consulte o dicionário de dados para ver a cláusula SELECT da visão.

150


a) SQL> CREATE VIEW AGGREGATES 2 (DEPTNO, AVERAGE, MAXIMUM, MINIMUM, SUM, NO_SA LS, NO_COMMS) 3 AS 4 SELECT DEPTNO, AVG(SAL), MAX(SAL), MIN(SAL), 5 SUM(SAL), COUNT(SAL), COUNT(COMM) 6 FROM EMP 7 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> CREATE VIEW ASG_VAL 2 AS 3 SELECT PROJID, EMPNO, A_START_DATE, A_END_DATE , 4 BILL_RATE, ASSIGN_TYPE, HOURS 5 FROM ASSIGNMENTS 6 WHERE A_START_DATE < A_END_DATE 7 AND PROJID < 2000 8 AND BILL_RATE <= DECODE(ASSIGN_TYPE, 'PF', 50, 'WT', 60, 70) 9 AND ASSIGN_TYPE IN ('PF', 'WT', 'ED') 10 AND EMPNO IN (SELECT EMPNO FROM EMP) 11 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_STAR T_DATE, A_END_DATE, BILL_RATE, ASSIGN_TYPE, HOURS FR........

151

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.

152

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 ENAME, JOB, SAL FROM EMP A consulta abaixo utiliza o índice criado para a coluna ENAME: SELECT *

153

FROM EMP WHERE 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 EMP WHERE UPPER(ENAME) = ‘JONES’ No exemplo abaixo o índice não é utilizado porque a coluna é parte de uma expressão: SELECT * FROM EMP WHERE HIREDATE+7 = ‘01-JAN-84’

19.8 Í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.

154


a) CREATE UNIQUE INDEX PROJ_PROJID ON PROJECTS(PROJID) b) CREATE INDEX ASG_PROJID ON ASSIGNMENTS(PROJID) c) SELECT * FROM USER_INDEXES

155

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 identificação do dono da seqüência. nome-da-seqüência nome válido para a seqüência. INCREMENT WITH incremento positivo ou negativo. Padrão: 1. START WITH primeiro número a ser gerado. Padrão: 1. MINVALUE|NOMINVALUE menor valor a ser gerado. Padrão: 1 para seqüências ascendentes, 10**27-1 para seqüências descendentes. MAXVALUE|NOMAXVALUE 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 DEPT_SEQ.NEXTVAL FROM SYS.DUAL NEXTVAL ------- 10

Se o comando for executado novamente, o valor é incrementado de 10.

156

SELECT DEPT_SEQ.NEXTVAL FROM 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.

157

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

158

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.

159

Apêndice A - Tabelas do Curso

Tabela EMP - Empregados

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 13-JUN-83 800 20 7499 ALLEN SALESMAN 7698 15-AUG-83 1600 300 30 7521 WARD SALESMAN 7698 26-MAR-83 1250 50 30 7566 JONES MANAGER 7839 31-OCT-83 2975 20 7654 MARTIN SALESMAN 7698 05-DEC-83 1250 1400 30 7698 BLAKE MANAGER 7839 11-JUN-84 2850 30 7782 CLARK MANAGER 7839 14-MAY-84 2450 10 7788 SCOTT ANALYST 7566 05-MAR-84 3000 20 7839 KING PRESIDENT 09-JUL-84 5000 10 7844 TURNER SALESMAN 7698 04-JUN-84 1500 0 30 7876 ADAMS CLERK 7788 04-JUN-84 1100 20 7900 JAMES CLERK 7698 23-JUL-84 950 30 7902 FORD ANALYST 7566 05-DEC-83 3000 20 7934 MILLER CLERK 7782 21-NOV-83 1300 10

Tabela DEPT - Departamentos

DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

Tabela SALGRADE - Faixas Salariais

GRADE LOSAL HISAL 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999

160

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

161

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


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

162

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


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

163

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

164

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

Documents

Apostila Oracle