ORACLE 9I FUNDAMENTAL
SQL, PL/SQL E
EDUARDO TERRA MORELLI
tmorelli@tmorelli.com.br
ORACLE 9I FUNDAMENTAL
SQL, PL/SQL E ADMINISTRAÇÃO
Morelli, Eduardo M. Terra, 1966-
1. Banco de dados - Gerência 2. Oracle 3. PL/SQL (Linguagem de
programação para computadores) 4. SQL (Linguagem de programação para computadores) I. Título.
Índices para Catálogo Sistemático:
1. Oracle: Banco de dados : Sistemas de gerência 005.75
“Algumas imagens utilizadas neste livro foram obtidas a partir do CorelDRAW 7, 8 e 9 e da Coleção do MasterClips/ MasterPhotos© da IMSI, 1985 Francisco Blvd. East, San Rafael, CA 94901-5506, USA.”
FABRICANTE
Produto: Oracle 9i Oracle do Brasil
Rua José Guerra, 127 - Chácara Santo Antônio CEP: 04719-030 - São Paulo - SP
Tel: (11) 5189-1000 Site: www.oracle.com.br
REQUISITOS DE HARDWARE E DE SOFTWARE
Para que os assuntos aqui tratados possam ser postos em prática, deve-se atender a um conjunto mínimo de requisitos, tanto de hardware quanto de software. Tomou-se por base a plataforma Windows, utilizada ao longo do livro.
Seguem os requisitos mínimos de hardware para utilização do Oracle Server 9i na plataforma Windows:
Servidor:
PC baseado em processador Pentium 166 MHz (recomendado: Pentium
233 MHz).
RAM: mínimo de 128 MB; recomenda-se 512 MB.
Espaço em disco configurado como NTFS: 2,8 GB (não incluído espaço de bases de dados) mais 275 MB no disco de sistema. Para discos configurados como FAT: 4,5 GB mais 140 MB no disco de sistema. Disco contendo diretório apontado pela variável de ambiente TEMP deve possuir 400 MB livres, no mínimo. Área de paginação (swap) deve partir de 200 MB.
Placa de vídeo SuperVGA com 2 MB de memória.
Máquinas clientes devem partir de Pentium 166 MHz, possuir 64 MB de RAM e 800 MB de espaço livre em disco.
Software mínimo:
Servidor: Windows 2000 com último Service Pack instalado; Microsoft Internet Explorer 5.5; Oracle Server.
Cliente: Windows 98; Microsoft Internet Explorer 5.5; Oracle Client.
O ambiente necessário para praticar os ensinamentos do livro consiste no Windows 2000 com último Service Pack; Oracle Server versão 9.2; SQL Navigator da Quest, para os capítulos de programação; Uma planilha compatível com Microsoft Excel para abrir o arquivo Livros_Oracle9i.xls. Para realizar instalações em outras plataformas, deve-se consultar documentação específica do Sistema Operacional em questão. Inicie a
busca por:
http://technet.oracle.com/docs/products/oracle9i/content.html. Mais detalhes no Apêndice G.
DEDICATÓRIA
Ao Thiago, ainda alguns Bytes;
Ao Leonardo, já com alguns Kilobytes; À Monick, todo Exabyte que houver.
AGRADECIMENTOS
Nenhum livro pode ser escrito apenas pelo seu autor. Todos aqueles que contribuam para incrementar os conhecimentos teóricos e dêem oportunidades para aplicá-los também são um pouco autores. Sendo assim, gostaria de agradecer às contribuições da GINFO da Companhia Libra de Navegação representada por Marcelo, Fernando, Eduardo, Marcia, Luís Eduardo, Leme, Mariana, Marcos, Pedro, Cristina, Bruno, Ricardo, Américo, Hamilton, Fátima e, principalmente, Luís Paulo Soares Dutra, o Lupa (as dicas sobre LOBs foram cruciais).
Mas, apenas oportunidade de estudo e prática não são o bastante para produzir um livro. Palavras de incentivo, quando emitidas no momento certo e da forma adequada, representam um estímulo sem igual. E não existiram palavras tão instigantes como as de Patrícia de Andrade Bueno Garcia, da UFPR (Curitiba-PR).
Incentivo é pouco diante da persistência de Rosana Arruda. Graças à sua infinita paciência com prazos expirados, este trabalho pôde ser concluído. Obrigado a Pieter Reckman da Starsoft pela confiança depositada nestes três anos de convivência.
A Luiz Paulo Maia pelo excelente artigo do Capítulo 17.
Deveriam inventar uma palavra que expressasse uma imensa gratidão, pois ela seria destinada aos meus pais, Enrique e Guillermina, pelo apoio em todos estes anos.
Um agradecimento especial às minhas irmãs, Mariana, Victoria, Maria José e Maria Pia, pelo exemplo de persistência e integridade.
Já que inventarão uma palavra para imensa gratidão, deveriam fazê-lo também para expressar um sentimento que revelasse extrema felicidade por compartilhar a vida de outra pessoa especial, pois essa palavra representaria uma ínfima parcela do que significa viver com Monick.
Aos meus segundos pais, Sergio e Elizabeth pelo apoio nestes meses de trabalho árduo.
Ao Leonardo sou grato pelo brilho em seus olhos a cada capítulo concluído. Ao Thiago, pela paz que emana de seu ser.
SOBRE O AUTOR
Eduardo Terra Morelli, autor dos livros Oracle 8: SQL, PL/SQL e Administração (2000) e de SQL Server 2000 Fundamental (2001), trabalha com treinamento e confecção de material didático desde 1986. Neste período, foram mais de 6.300 horas de sala de aula, com cerca de 2.600 alunos, sem contar com oito anos como professor universitário na PUC-RJ (350 alunos) e ainda outro na Faculdade Carioca (100 alunos). Atualmente trabalha como DBA Oracle e SQL Server em diversas empresas, além de, claro, ministrar cursos sobre estes assuntos. Já escreveu mais de trinta apostilas, além de diversas contribuições para revistas do ramo, tais como MicroSistemas.
SOBRE O MATERIAL DISPONÍVEL NA INTERNET
O material disponível na Internet contém arquivos de exercícios e exemplos utilizados ao longo do livro. Em cada capítulo (seção Arquivos Necessários) aparece uma lista com os arquivos a serem utilizados no capítulo em questão. Após a descompactação, os arquivos estarão distribuídos por quatro diretórios: C:\ParteI, C:\ParteII, C:\ParteIII e C:\Hots.
Para utilizar esses arquivos, é imprescindível que o leitor possua o Oracle 9i Server (servidor) e Client (cliente) instalados sua máquina, Servidora e Cliente, respectiva-mente. Para acompanhar os exemplos descritos na Parte II, também recomendam-se as presenças do SQL Navigator da Quest e de algum editor de textos compatível com Microsoft Word no Cliente. Vale ressaltar que todos os trabalhos podem ser efetuados apenas na máquina servidora, caso exista uma para fins de testes.
Arquivo Tamanho ORACLE_9I.EXE
EXERCICIO.EXE
96 KB 734 KB Procedimento para Download
Acesse o site do Autor: www.tmorelli.com.br. Na página principal do site, selecione Livros e clique no item “Download”. Dê um clique sobre o arquivo executável e este será transferido.
Procedimento para Descompactação
Primeiro passo: após ter transferido o arquivo, verifique o diretório em que se encontra e dê um duplo-clique sobre ele. Será exibida uma tela do programa WINZIP SELF-EXTRACTOR que conduzirá você ao processo de descompactação. Abaixo do Unzip To Folder, existe um campo que indica o destino dos arquivos que serão copiados para o disco rígido do seu computador.
Segundo passo: prossiga com a instalação, clicando no botão Unzip, o qual se encarrega de descompactar os arquivos. Logo abaixo dessa tela, aparece a barra de status a qual monitora o processo para que você acompanhe. Após o término, outra tela de informação surgirá, indicando que os arquivos foram des-compactados com sucesso e estão no diretório criado. Para sair dessa tela, clique no botão OK. Para finalizar o programa WINZIP SELF-EXTRACTOR, clique no botão Close.
PREFÁCIO
Recente estudo conduzido na Universidade da Califórnia, em Berkeley, concluiu que o mundo produz entre um e dois exabytes (EB) por ano. Já Richard Niemiec afirma que se estima em 12 EB o total de informações no mundo1.
Só para que se tenha uma noção do que isso significa, analise a seguinte tabela:
Potência
de 10 Nome Armazena
0 Byte (B) Um caractere representa um byte.
3 Kilobyte (KB) Uma página de um livro típico ocupa 25 linhas e 80 colunas, resultando em pouco menos de 2 KB.
6 Megabyte (MB)
Supondo que uma página ocupe 2000 bytes, um livro com 500 páginas gastaria 1 MB; um arquivo MP3 contendo uma mú-sica típica gasta cerca de 4 MB.
9 Gigabyte (GB)
Uma sinfonia completa em alta fidelidade ocuparia 1 GB; típicos Bancos de Dados comerciais
possuem entre 5 e 200 GB.
12 Terabyte (TB) Papel equivalente a 50.000 árvores.
15 Petabyte (PB) Estima-se em 8 PB o volume de toda a informação disponível na Internet. 18 Exabyte (EB) Todas as palavras já ditas por todos os seres
humanos ocupariam 5 EB. 21 Zettabyte (ZB) ?
24 Yottabyte (YB) ?
Desde 1979, quando foi lançado o primeiro SGBDR comercial, a Oracle notabilizou-se por nortear os rumos do mercado de Bancos de Dados Corporativos. Em junho de 2001, a Oracle lançou a versão 9i de seu SGBDR2, o primeiro a permitir acesso a dados em barramento de 64 bits. Se memória física não for empecilho, com esta característica é possível endereçar 264, o que equivale a 16 exabytes! Só para se ter uma idéia do
que representa este vanguardismo, a Microsoft ainda não possui um Sistema Operacional com este poder de endereçamento.
1 Leia a íntegra da pesquisa em http://www.sims.berkeley.edu/research
/projects/how-much-info/summary.html. A afirmação de Richard Niemiec
consta na edição de setembro/outubro de 2001 da Oracle Magazine (página 29).
Este livro destina-se ao profissional que percebeu a importância dos bancos de dados Oracle em plena Era da Informação. A meta principal consiste em, objetivamente, abrir as portas do Mundo Oracle. O leitor não pode se conformar em “parar” aqui. Há muitos pontos que podem e devem ser explorados mais a fundo.
Composição do Livro
O livro divide-se em quatro partes. A primeira trata de SQL (capítulos 1 a 7), na qual são ensinados os fundamentos desta importantíssima linguagem de manipulação de bancos de dados relacionais. A partir de um estudo de caso, que, aliás, continua sendo usado ao longo do livro, apresentam-se a terminologia básica, como se conectar as bases Oracle e os comandos SQL mais importantes. Leitores com pouca ou nenhuma experiência em SQL devem estudar a fundo estes capítulos.
A segunda parte (capítulos 8 a 17) destina-se aos programadores que desejam desenvolver código na linguagem PL/SQL. Diferente de outras linguagens, tais como: Pascal, C ou VBA, os “programas” escritos em PL/SQL são executados no Servidor em que estão os dados. Há muitos tópicos interessantes nesses capítulos: Cursores, Packages, Triggers e Objetos, em que há um texto primoroso sobre as bases conceituais do Paradigma da Orientação por Objetos escrito por Luiz Paulo Maia.
O melhor do livro está na terceira parte (capítulos 18 a 26) que explica como um banco de dados Oracle deve ser administrado, ou melhor, como trabalha um DBA. Há que se comentar quase todos os capítulos: o 19 apresenta a arquitetura de um BD Oracle; o 20 trata sobre Tablespaces; do 21 ao 23, explica-se Segmento e as suas variações: Undo (21), Tabela (22) e Índice (23); o 24 discute aspectos de segurança e o 25 sobre como implementar uma Política de Backup consistente. Finalmente, há breves comentários sobre a utilização do Database Configuration Assistant sob Windows 2000.
A última parte reúne os sete apêndices do livro. Destaque para o C no qual se comentam diversos livros de Oracle que complementam os assuntos aqui tratados. Seguindo o princípio do aprendizado contínuo, este apêndice apresenta os próximos desafios. O arquivo fornecido Livros_Oracle9i traz uma relação mais completa com editora, páginas e ISBN.
Devo enfatizar o apêndice D apresentando um exemplo de relatório produzidos em um típico dia de trabalho de um DBA. Vale a pena conferir o E, no qual se detalha um roteiro que permite implementar um Banco Standby, isto é, uma fiel cópia do original por motivos de performance (abriga pesadas consultas) e disponibilidade (caindo a produção, rapidamente se mudam as conexões para o novo Banco).
Finalmente, o apêndice F oferece uma lista de endereços na Internet (mais de 30) coletada por horas a fio. Lembre-se que a Internet é muito dinâmica, assim, quando chegar nele, outros endereços podem existir.
Por falar em Internet, há no site do Autor (www.tmorelli.com.br) uma lista de scripts que podem e devem ser carregados. Os quase cem arquivos estão distribuídos em quatro diretórios: ParteI, ParteII, ParteIII e ParteIV. Este último, abriga o HOTS, contendo um conjunto de scripts que devem ser utilizados regularmente.
O ambiente necessário para praticar os ensinamentos do livro consiste no Windows 2000 com último Service Pack; Oracle Server versão 9.2; SQL Navigator da Quest, para os capítulos de programação.
Composição dos Capítulos
A organização interna dos capítulos segue o estilo dos outros livros do autor, sendo resultado da experiência adquirida em escrever materiais didáticos ao longo dos últimos 16 anos.
Todo capítulo divide-se em:
Objetivos: determina-se o que o leitor deve aprender com o capítulo corrente. Deve-se voltar a esta seção freqüentemente para que o foco não seja perdido. Novidades presente a partir do capítulo 19, revela os novos
recursos in-troduzidos na versão 9i. Arquivos
Necessários:
scripts utilizados como exemplo ou exercício. Há mais de cem arquivos fornecidos.
Pré-requisitos:
destinado ao leitor que não prefira uma leitura seqüencial, para que avalie se possui os conhecimentos necessários para estudar o capítulo. Motivação: nem sempre presente, exibe uma situação problema
que in-troduz os tópicos a serem aprendidos.
Teoria: apresenta conceitos, exemplifica, fornece sintaxe de coman-dos.
Exercícios/ dicas:
complementa a teoria a partir de instigantes exercícios. Existem alguns, inclusive, que levam o leitor a conhecer novos recursos (por exemplo, trigger em startup no capítulo 19).
Há quase 270 exercícios, cujas soluções comentadas estão disponíveis no site do Autor (maiores informações na página “Sobre o Material Disponível na Internet”).
Referência Rápida:
célere fonte de acesso à teoria. Também possui caráter complementar e às vezes apresenta recursos inéditos (por exemplo, comando CREATE PFILE no capítulo 19).
Por que vale a pena ler este livro
Quem adquiriu o primeiro livro, ORACLE8: SQL, PL/SQL e ADMINISTRAÇÃO pode pensar que comprar este seria um desperdício de tempo e dinheiro, entretanto, há muitas razões para não pensar desta forma:
Já se passaram mais de dois anos desde o lançamento do primeiro livro. Neste tempo, o autor aumentou seus conhecimentos dramaticamente, já que vivenciou Bancos de Dados Oracle diariamente;
Aquele livro tratava da versão 8. Depois dela, já foram lançadas a 8i, 9i e 9i Release II, estas duas últimas com grande destaque neste trabalho;
Há muito mais páginas, exercícios (todos corrigidos!), dicas, notas de rodapé, exemplos e scripts;
A maioria dos capítulos foram quase totalmente reescritos, como por exemplo o 15–Packages (há grande ênfase em LOBs); 21-Segmentos de Undo/Rollback (aborda SMU e DBMS_FLASHBACK), 22-Segmentos de Tabelas (IOTs, Materialized Views), 23-22-Segmentos de Índice (Bitmaps), 24-Segurança (mais recursos de gerência de usuários) e, principalmente, o 25-Backup & Recover tratando de assuntos inéditos como LogMiner, Tablespaces Transportáveis e Recovery Manager.
ÍNDICE ANALÍTICO
Capítulo 1 – Bancos de Dados ... 21
1.1 - O Problema ... 22 1.1.1 - A Situação Ideal ... 23 1.2 - A Solução... 23 1.2.1 - Projeto Conceitual... 24 1.2.2 - Projeto Lógico ... 25 1.3 - SQL ... 25 Capítulo 2 – TABELAS ... 28 2.1 - Conexão... 28 2.2 - Criação de Tabelas ... 29 2.2.1 - Tipos de Dados ... 30 2.2.2 - Constraints ... 32 2.3 - Alteração de Tabelas ... 35 2.4 - Exclusão de Tabelas ... 37 2.5 - Inserção de Dados... 38
Capítulo 3 – SELEÇÃO DE DADOS ... 42
3.1 - Seleções Simples ... 42
3.2 - Ordenações e Filtros ... 45
3.3 - Visualizando Dados em Tabelas Diferentes ... 48
3.3.1 - Relacionamento EquiJoin... 48 3.3.2 - Relacionamento OuterJoin... 52 3.3.3 - Relacionamento NonEquiJoin ... 52 3.3.4 - Relacionamento SelfJoin ... 53 3.4 - Expressões Calculadas ... 53 3.4.1 - Funções de Caracteres ... 54 3.4.2 - Funções de Datas ... 55 3.4.3 - Funções Numéricas ... 57
3.4.4 - Funções de Conversão de Dados ... 57
3.4.5 - Funções de Grupo ... 60
3.5 - Operadores de Conjunto ... 62
3.6 - Dicionário de Dados ... 64
4.1 - Fundamentos ... 70 4.2 - Alteração de Linhas... 71 4.3 - Eliminação de Linhas ... 71 4.4 - Transações ... 72 Capítulo 5 – RELATÓRIOS ... 79 5.1 - Fundamentos ... 79 5.2 - Variáveis de Ambiente... 80
5.3 - Comandos SQL*Plus Worksheet de Formatação ... 81
5.3.1 - Column ... 81
5.3.2 - Ttitle e Btitle ... 83
5.3.3 - Break e Compute ... 83
5.4 - Bind Variables... 84
5.5 - Geração de SQL... 86
Capítulo 6 – OUTRAS ESTRUTURAS DE DADOS ... 89
6.1 - Fundamentos ... 90 6.2 - Índices ... 90 6.3 - Views ... 92 6.4 - Sequences ... 95 6.5 - Synonyms ... 97 Capítulo 7 – SUBQUERIES ... 101 7.1 - Fundamentos ... 101
7.2 - Subqueries de Uma Linha ... 102
7.3 - Subqueries de Múltiplas Linhas ... 104
7.4 - Subqueries de Múltiplas Colunas... 106
7.5 - Subqueries Correlatas ... 106
Capítulo 8 – PROGRAMAÇÃO CLIENTE/ SERVIDOR ... 110
Criando Programas sob Enfoque Cliente-Servidor ... 110
8.1 - PL/SQL ... 112 8.2 - PL/SQL ou Java? ... 113 8.3 - Unidades de Programa ... 114 8.4 - Procedures... 115 8.5 - Functions ... 116 Capítulo 9 – SQL NAVIGATOR ... 120 9.1 - Fundamentos ... 120
9.2 - Elementos do Ambiente ... 121
9.2.1 - DB Navigator... 121
9.2.2 - Stored Program Editor ... 122
9.2.3 - SQL Editor ... 123 9.2.4 - Trigger Editor ... 123 Capítulo 10 – VARIÁVEIS ... 126 10.1 - Fundamentos ... 126 10.2 - Tipos Escalares ... 127 10.3 - Tipos Compostos ... 129 10.3.1 - Vetores (Tables)... 129 10.3.2 - Registros (Records) ... 130 10.4 - Escopo ... 131 Capítulo 11 – PL/SQL: SQL ... 134 11.1 - Seleção (Select/Into) ... 134 11.2 - Inserção (Insert) ... 136 11.3 - Alteração (Update)... 137 11.4 - Exclusão (Delete)... 138 11.5 - SQL Dinâmica ... 139 Capítulo 12 – pl/sql:pl ... 142 12.1 - Processamento Condicional ... 142 12.2 - Processamento Repetitivo ... 145 12.2.1 - FOR ... 145 12.2.2 - WHILE ... 146 12.2.3 - LOOP ... 147 12.2.4 - FORALL... 148 12.3 - Depuração ... 149 Capítulo 13 – CURSORES ... 153
Exemplo de processamento precisando Cursores ... 153
13.1 - Fundamentos ... 154 13.2 - Cursores Explícitos ... 156 13.3 - Cursores de Atualização ... 159 Capítulo 14 – EXCEÇÕES ... 163 14.1 - Fundamentos ... 163 14.2 - Exceções Predefinidas... 164
14.3 - Exceções não Predefinidas ... 166
14.5 - Exceções em Blocos Anônimos... 169 Capítulo 15 – PACKAGES ... 173 15.1 - Fundamentos ... 174 15.2 - Criação ... 175 15.2.1 - Especificação ... 175 15.2.2 - Corpo... 176 15.3 - Ativação ... 177 15.4 - Dependências ... 178 15.5 - Packages Embutidos... 180 15.5.1 - DBMS_JOB ... 180 15.5.2 - DBMS_LOB ... 183 Capítulo 16 – TRIGGERS ... 191 16.1 - Fundamentos ... 191 16.2 - Criação ... 194 16.3 - DML ... 196 16.3.1 - INSERT ... 196 16.3.2 - UPDATE ... 196 16.3.3 - Delete ... 197 16.3.4 - Múltiplos ... 198 16.4 - Atualização de Views... 199 16.5 - Schema ... 200 16.6 - DBMS_ALERT ... 201 Capítulo 17 – OBJETOS ... 205
O Paradigma da orientação por Objetos ... 206
17.1 - Fundamentos ... 210 17.2 - Tipos Objeto ... 211 17.3 - Herança ... 214 17.4 - Tabelas de Objetos ... 215 17.5 - Tabelas Aninhadas ... 217 17.6 - Vetores Variáveis... 219
Capítulo 18 – INTRODUÇÃO À ADMNISTRAÇÃO ... 223
Um Dia na Vida de Daniel Barbosa Alves (DBA) ... 224
18.1 - O DBA ... 224
18.2 - Organização Interna ... 226
18.2.1 - Organização Física ... 226
18.3 - Objetos de um Banco de Dados ... 227
Capítulo 19 – ORGANIZAÇÃO FÍSICA ... 230
19.1 - Arquivos ... 231
19.1.1 - Data Files ... 232
19.1.2 - Control Files ... 233
19.1.3 - Redo Log Files... 233
19.1.4 - Arquivos de Parâmetros (INIT.ORA e SPFILE.ORA) ... 235
19.1.5 - Arquivos de Acompanhamento... 236
19.1.6 - Arquivo de Senhas ... 237
19.2 - Memória ... 237
19.2.1 - Program Global Area (PGA) ... 237
19.2.2 - System Global Area (SGA) ... 237
19.3 - Processos... 239 19.3.1 - Processo Usuário ... 239 19.3.2 - Processo Servidor ... 239 19.3.3 - Processo Background ... 240 19.4 - Instância ... 242 19.4.1 - Parâmetros ... 242 19.4.2 - Etapas ... 245 19.4.3 - Fechando: Shutdown ... 246 19.4.4 - Abrindo: Startup... 247 19.5 - Visão Geral ... 248
19.6 - O Que Ocorre Durante um SELECT... 248
19.7 - O Que Ocorre Durante um UPDATE ... 249
19.8 - Notas sobre o Oracle sob Windows 2000 ... 250
Capítulo 20 – ORGANIZAÇÃO LÓGICA: TABLESPACES ... 258
20.1 - Fundamentos ... 259
20.2 - OFA (Optimal Flexible Architecture)... 260
20.3 - Criação ... 261
20.4 - Alteração ... 267
20.5 - Eliminação ... 269
20.6 - Enterprise Manager Console ... 270
Capítulo 21 – SEGMENTOS DE ROLLBACK/UNDO ... 274
21.1 - Introdução aos Segmentos ... 275
21.2 - Introdução aos Segmentos de Rollback/ Undo ... 276
21.4 - Flashback Queries ... 281
21.5 - Gerência Manual... 283
21.6 - Regras de Ouro para Gerência Manual ... 286
21.7 - Enterprise Manager Console ... 286
Capítulo 22 – SEGMENTOS DE TABELA ... 291
22.1 - Fundamentos ... 292
22.2 - Alteração de Segmentos de Tabela ... 295
22.3 - Identificação de Linhas ... 297
22.4 - Encadeamento de Linhas ... 299
22.5 - Materialized Views ... 300
22.6 - Index-Organized Tables (IOTs) ... 301
22.7 - Partições ... 302
22.8 - Estatísticas ... 305
22.9 - Reorganização ... 307
22.9.1 - Testando Viabilidade do Processo ... 307
22.9.2 - Criação da Tabela Intermediária ... 308
22.9.3 - Preenchimento dos Dados ... 308
22.9.4 - Novas Características ... 308
22.9.5 - Sincronismo ... 309
22.9.6 - Troca de Tabelas... 309
22.9.7 - Eliminação ... 309
22.10 - Tabelas Externas... 310
Capítulo 23 – SEGMENTOS DE ÍNDICE ... 315
23.1 - Fundamentos ... 315
23.2 - Organização Interna ... 319
23.2.1 - Árvore-B ... 319
23.2.2 - Bitmap ... 321
23.3 - Alteração de Segmentos de Índice ... 323
23.4 - Acompanhamento ... 324 23.5 - Partições ... 325 Capítulo 24 – SEGURANÇA ... 329 24.1 - Fundamentos ... 330 24.2 - Usuários ... 331 24.3 - Privilégios ... 333 24.3.1 - Privilégios de Sistema ... 333
24.3.2 - Privilégios de Objeto ... 335
24.4 - Roles ... 338
24.5 - Profiles... 339
24.6 - Autenticação via Sistema Operacional ... 341
24.7 - Auditoria ... 342
24.8 - Regras de Ouro ... 344
Capítulo 25 – BACKUP & RECOVER ... 354
25.1 - Fundamentos ... 355 25.2 - Operações Lógicas... 359 25.2.1 - Exportação ... 359 25.2.2 - Importação ... 362 25.3 LogMiner ... 365 25.4 DBMS_FLASHBACK ... 368 25.5 - Tablespaces Transportáveis ... 369 25.6 - Cold Backup ... 371 25.7 - Hot Backup ... 372 25.7.1 - Modo ArchiveLog... 372 25.7.2 - Hot Backup ... 374
25.7.3 - Recuperação a partir de um Hot Backup ... 376
25.8 Recovery Manager ... 378 25.8.1 - Terminologia ... 378 25.8.2 - Preparação ... 380 25.8.3 - Backup ... 381 25.8.4 - Recuperação ... 384 25.8.5 - Automação ... 387 25.9 - Conclusão ... 388
Capítulo 26 – CRIAÇÃO DE UM BANCO DE DADOS ... 398
26.1 - Fundamentos ... 398
26.2 - Database Configuration Assistant ... 399
26.3 - Net Configuration Assistant ... 400
26.4 - Eliminação de um Banco de Dados ... 401
APÊNDICE a: SQL*PLUS ... 402
APÊNDICE b: MODELO DE CONTROLE ACADÊMICO ... 404
APÊNDICE C: BIBLIOGRAFIA COMENTADA ... 406
APÊNDICE D: EXEMPLO DE RELATÓRIO DE LEVANTAMENTO ... 411
APÊNDICE E: STANDBY DATABASES ... 421
E.1 - Fundamentos ... 421
E.2 - Parâmetros no Banco Principal ... 422
E.3 - Parâmetros no Banco Standby... 424
E.4 - Implantando Modo Manual ... 424
E.5 - Implantando Modo Automático ... 425
E.6 - Implantando Modo Leitura ... 426
E.7 - Desativando Servidor Standby ... 427
E.8 - Conclusões ... 427
APÊNDICE F: APOIO NA INTERNET ... 429
APÊNDICE G: INSTALAÇÃO NO WINDOWS 2000 ... 434
G.1 - Fundamentos ... 434 G.2 - Requerimentos de Hardware ... 434 G.3 - Requerimentos de Software ... 434 G.4 - Processo ... 435 G.5 - Verificação ... 435 G.6 - Instalação no Cliente ... 437
MARCAS REGISTRADAS
Oracle é marca registrada da Oracle Inc.
Windows, Access, MS-SQL Server são marcas registradas da Microsoft Corporation.
SQL Navigator é marca registrada da Quest Inc.
Todos os demais nomes registrados, marcas registradas, ou direitos de uso citados neste livro, pertencem aos respectivos proprietários.
Capítulo 1 – Bancos de Dados
OBJETIVOS
• Conhecer o estudo de caso utilizado ao longo do livro; • Rever termos básicos relacionados a Bancos de Dados; • Travar um primeiro contato com a linguagem SQL; • Informar sobre o real significado do mundo Oracle.
ARQUIVOS NECESSÁRIOS
• Nenhum.
PRÉ-REQUISITOS
• Noções de Modelagem de Dados;
• Definir entidade, atributo, relacionamento;
• Compreender o papel da Normalização no Projeto de um Banco de Dados;
• Identificar as diferentes etapas prévias à construção de um Banco de Dados.
MOTIVAÇÃO
O Mundo Oracle
O maior mérito da Oracle Corporation foi apostar decisivamente em uma ferramenta de gerência de bancos de dados relacionais. O estrondoso sucesso no início dos anos setentas levou à criação de versões específicas às plataformas de hardware. No "boom" do PC, a Oracle lançou sua versão para micros.
Há muito o nome Oracle deixou de significar apenas uma poderosa ferramenta para implementação de Bancos de Dados corporativos, cujo único objetivo era ler, gravar e proteger dados. Hoje a Oracle tem produtos na área de desenvolvimento (Forms Developer, Designer, Reports Developer, JDeveloper), Servidor de Aplicações (Oracle Application Server), ERP (Financials, Human Resources, etc.), Internet (Commerce Server, Self-Service Web Applications), OLAP (Data Mart Suite, Data Mining Suite, Discoverer) e, evidentemente, administração de Bancos de Dados (Oracle-Server, Enterprise Manager).
Uma das grandes razões do sucesso da Oracle foi criar produtos que pudessem ser utilizados em diversas plataformas: desde o VMS até o Unix, passando pelo Windows (3.1, 9x, NT, 2000, XP).
Ao longo do livro serão construídas e alimentadas com dados as tabelas discutidas no presente capítulo. Desta forma, juntamente com o Oracle-Server, será utilizada a ferramenta SQL*Plus Worksheet que, além de responsável pela conexão entre Cliente e Servidor (podem estar em uma mesma máquina ou em duas), reconhece e submete comandos SQL para execução no Servidor e permite, entre outras facilidades, a entrada de dados interativa, formatação de saída e edição de comandos SQL e próprios por meio de um editor de textos.
TEORIA
1.1 - O Problema
O famoso CEVOG (Centro Educacional de Volta Grande), melhor escola da região, completa trezentos anos de existência. Dizem que até Tiradentes rabiscou suas primeiras letras por lá.
Em breve, o atual diretor, Moura XI, passará o cargo para seu filho, o jovem promis-sor Moura XII. Cargo este já ocupado pelo lendário Moura I, o Fundador.
Para comemorar tal importante data e também a passagem do cargo, finalmente o CEVOG resolveu render-se à modernidade: a partir deste ano, o notório centro também oferecerá cursos de informática à comunidade. O jovem Moura XII está bastante preocupado: como disponibilizar cursos de computação se todo o controle interno ainda é manual?
O Controle Acadêmico a ser implantado tentará copiar o utilizado há tanto tempo pelos cursos regulares. Existem dois cadastros: o de Alunos (Nome, Endereço, Telefone) e o de Cursos (Descrição, Carga Horária, Pré-requisitos). Há dez cursos e, a princípio, uns trezentos alunos.
Nada foi definido quanto à criação de turmas, nem há a menor idéia de como implantar o controle de instrutores. Seria interessante guardar um histórico de cada aluno (notas, freqüências) para poder controlar pré-requisitos em futuras matrículas.
Os problemas são muitos, mas nada que possa arrefecer os ânimos dos Moura. Se eles já foram até citados como centro de excelência de ensino nos idos anos quarentas (do século XIX!), não será um curso de informática que os fará recuar ante o inexorável avanço do tempo.
1.1.1 - A Situação Ideal
Moura, o Doze, deseja implantar o seguinte:
• Criar um cadastro de Instrutores (Nome, Endereço, Telefone, Data de admissão) para que seja implantado um eficiente sistema de alocação;
• Integrar os cadastros de Alunos e Cursos para saber, em questão de minutos, se um determinado indivíduo está fazendo ou já fez um dado curso;
• Criar outro cadastro de Notas (Turma, Matrícula, Nota) para que se tenha uma idéia do nível de cada aluno.
Numa próxima ocasião será implantado um controle rigoroso sobre cobrança, prevendo, inclusive, uma modalidade de pagamento a prazo mais sofisticada.
A idéia é oferecer os cursos para o mundo via Internet, assim os dados armazenados serão da ordem de peta bytes, o que justifica a utilização do Sistema Gerenciador de Bases de Dados Objeto-Relacionais Oracle 9i.
1.2 - A Solução
Projeto Conceitual
Inicialmente são identificadas entidades, relacionamentos e atributos. Não existe preocupação com eventuais limitações de hardware e de software. O resultado desta etapa é o Modelo de Entidades e Relacionamentos (M.E.R.).
Projeto Lógico
Nesta fase identificam-se as tabelas físicas do futuro Banco de Dados. Levam-se em conta as regras de normalização para que não haja redundâncias na montagem final.
Projeto Físico Consiste na implementação do Banco em Oracle, que será feito ao longo do livro.
1.2.1 - Projeto Conceitual
Analisando o problema, vê-se claramente a presença de três entidades: Alunos, Instrutores e Cursos, cujos atributos poderiam ser os listados em seguida:
Entidade Atributos
Alunos Matrícula, Nome, Telefone, Endereço, UF Instrutores Nome, Telefone, Data admissão
Cursos Nome, Carga horária, Preço, Pré-Requisitos
Vê-se ainda que o relacionamento entre um aluno e um curso é a Turma, o mesmo, aliás, entre instrutor e curso. Um atributo do relacionamento Aluno-Turma poderia ser sua nota.
As cardinalidades seriam as seguintes: Alunos x Turmas: N:N Cursos x Turmas: 1:N Instrutores x Turmas: 1:N O resultado final poderia ser este:
1.2.2 - Projeto Lógico
Como já foi dito, nesta etapa são especificadas as tabelas físicas, com as respectivas chaves, do Banco de Dados e são resolvidos problemas referentes à normalização.
Infelizmente não é possível implementar relacionamentos muitos-para-muitos, assim haverá mais uma tabela: AlunosTurmas, ou melhor, Histórico:
Tabela Campos
Alunos Matrícula, Nome, Telefone, Endereço, UF Histórico Código Turma, Matrícula, Nota
Turmas Código Turma, Sala, Código Instrutor, Código Curso Cursos Código Curso, Nome, Carga horária, Preço, Pré-requisito
Instrutores Código Instrutor, Nome, Telefone, Data admissão
Observação
Os campos em negrito representam as chaves primárias.
Como era esperado, todos os relacionamentos têm a cardinalidade um-para-muitos.
Na próxima etapa, o Projeto Físico, serão criadas as tabelas reais, levando-se em consideração os tipos e tamanhos permitidos pelo Sistema Gerenciador de Banco de Dados Relacionais (SGBDR), no caso, o Oracle.
1.3 - SQL
Toda interação com o Oracle acontece, em última análise, via SQL. Trata-se de uma linguagem cujos comandos dividem-se em três categorias:
• DML (Data Manipulation Language); • DDL (Data Definition Language); • DCL (Data Control Language).
Ao contrário de linguagens ditas procedurais (COBOL, Pascal, Basic, etc.), a SQL permite especificar o que deve ser feito em vez do como. Por exemplo, o trecho apresentado em seguida, escrito em uma linguagem procedural qualquer, percorreria todos os registros de uma tabela:
Inicio
Enquanto não-fim (Tabela) Leia Registro Avance Fim-Enquanto Fim
Já em SQL, teríamos apenas um comando:
select * from Tabela
A título de exemplo, a relação seguinte explica alguns comandos SQL:
Comando Descrição Tipo
select Recupera dados de uma ou mais tabelas. É o comando mais utilizado da SQL. DML insert
update delete
Servem para incluir, alterar e eliminar linhas de uma tabela respectivamente. DML commit
rollback
Responsáveis pelo controle de transações, permitem que o usuário desfaça (ROLLBACK) ou confirme (COMMIT) alterações em tabelas.
DML create
alter drop
Úteis para definir, alterar e remover estruturas
do Banco de Dados. DDL
grant revoke
Permitem remover direitos de acesso dos usuários do Banco de Dados e seus componentes, ou concedê-los.
EXERCÍCIOS
1. Na tabela seguinte numere a terceira coluna para que os termos que estão na segunda sejam explicados na quarta coluna:
Número Termo Seu
Número Definição
1 Banco de Dados Sinônimo de Atributo, representa um tipo de informação.
2 Tabela Campo que identifica univocamente cada registro.
3 Campo
Utilizado para unir duas tabelas que tenham um campo em comum. Uma fornece sua Chave Primária e a outra, uma Chave Estrangeira. 4 Chave Primária
Mecanismo que permite visualizar dados que estejam em tabelas diferentes.
5 Chave
Estrangeira
"Sistema de armazenamento de dados baseado em computador, cujo objetivo global é registrar e manter informação considerada significativa à organização" [Date].
6 Registro
Depósito de dados em forma matri-cial em que as linhas representam registros e as colunas, campos. 7 Relacionamento Linguagem de manipulação de dados.
8 Visão Campo idêntico a uma Chave
Primária em outra tabela.
9 SQL
O mesmo que tupla. Imaginando que uma tabela fosse um conjunto, então seriam os elementos desse conjunto.
Observação
[Date] refere-se a C.J. Date; a frase em questão encontra-se no livro "Introdução a Sistemas de Bancos de Dados", página 26, Editora Campus.
Capítulo 2 – TABELAS
OBJETIVOS
• Realizar conexões ao Servidor;
• Criar tabelas, levando em consideração nomes de campos, tipos e constraints;
• Utilizar os primeiros comandos da ferramenta SQL*Plus Worksheet;
• Alterar tabelas; • Eliminar tabelas;
• Alimentar tabelas com dados.
ARQUIVOS NECESSÁRIOS
• Monta_Tabela_Instrutores.sql; • Monta_Tabelas.sql.
PRÉ-REQUISITOS
• Noções de Modelagem de Dados;
• Definir entidade, atributo, relacionamento;
• Compreender o papel da Normalização no Projeto de um Banco de Dados;
• Identificar as diferentes etapas prévias à construção de um Banco de Dados;
• Conhecer conceitos básicos, tais como: Banco de Dados, Tabela, Campo, Registro, Chave Primária, Chave Estrangeira e Relacionamento.
TEORIA
2.1 - Conexão
Para que seja possível trabalhar com Oracle, é preciso, antes de tudo, realizar uma conexão. Ela pode ser feita com inúmeras ferramentas, por exemplo, SQL*Plus Worksheet, utilizada ao longo do livro.
Em ambientes gráficos, como Windows 98, NT ou 2000, segue-se um caminho de menus, tal como: Start, Programs, Oracle – OraHome92, Application Development, SQL*Plus Worksheet3. Será mostrada a caixa
de diálogo seguinte:
Atenção No momento dessa conexão saiba que já existe um Banco de Dados (conhecido no Cliente como Serviço) e ele está pronto para ser utilizado. Futuramente, nos capítulos de Administração de Bancos de Dados (18 em diante), você aprenderá como criar novos Bancos de Dados e disponibilizá-los para uso.
Em ambientes não gráficos deve-se utilizar outra ferramenta, a SQL*Plus, cuja ativação seria:
sqlplus usuário/senha@banco
Em que usuário, senha e banco são fornecidos pelo Administrador (DBA).
2.2 - Criação de Tabelas
Uma vez realizada a conexão, pode-se começar a interagir com o Oracle-Server via comandos SQL. Por exemplo, a criação das tabelas do estudo de caso será feita pelo comando create table cuja sintaxe simplificada é
mostrada em seguida:
3 A localização das ferramentas pode variar de uma instalação para outra.
Ao longo deste livro, assumiremos a instalação padrão do Oracle 9i sob Windows 2000 Server.
CREATE TABLE [schema.]tabela
(coluna1 tipo_de_dado [DEFAULT expressão] [constraint_coluna],
...
(colunaN tipo_de_dado [DEFAULT expressão] [constraint_coluna],
[constraint_tabela] );
Os colchetes representam cláusulas opcionais. A tabela seguinte explica a sintaxe do comando:
Opções Descrição
Schema Representa o proprietário da tabela. Quando omitido, assume-se que
a tabela pertencerá ao usuário corrente.
Tabela Nome da tabela a ser criada.
Coluna
Nome da coluna (campo). Deve iniciar por uma letra; pode ter no má-ximo 30 caracteres (A-Z, a-z, 0-9, underscore e $) e também não po-dem ser palavras reservadas do Oracle. Popo-dem existir até inconcebíveis mil colunas.
Tipo_de_ dado
Tamanho e tipo de dado da coluna. Veja, ainda neste capítulo, uma descrição dos tipos possíveis.
Expressão
Especifica um valor predefinido para a coluna. Utilizado para evitar NULL quando uma coluna for omitida em um comando INSERT (inserção de dados). Pode ser um literal, uma expressão, funções SQL, tais como: SYSDATE (data corrente) ou USER (usuário corrente), mas não pode ser utilizado o nome de outra coluna ou pseudocolunas geradas por Sequences (NEXTVAL ou CURRVAL)4.
Constraint_ coluna
Especifica a restrição de integridade para uma coluna, como, por exemplo, valores que podem ser armazenados. Recomenda-se que tenha nome.
Constraint_ tabela
Restrição de integridade como parte da definição de uma tabela. Utilizada para definir chaves compostas. Também aconselha-se denominá-la.
Atenção
Para que você possa criar uma tabela, alguém, geralmente o DBA, deve ter lhe dado o direito de criação de tabelas.
2.2.1 - Tipos de Dados
Cada campo em uma tabela deve possuir um tipo previamente estabelecido. Por tipo entende-se um domínio de valores que o campo pode conter. A tabela seguinte explicita os tipos de dados mais importantes utilizados pelo Oracle-Server:
Tipo Descrição
Char (n) Cadeia de caracteres com tamanho fixo n. O default é 1 e o máximo, 2.000.
Varchar2 (n)
Cadeia contendo caracteres de tamanho variável com o máximo de n, em que n pode ter o valor de até 4.000. Para que dois campos desse tipo sejam consideradas iguais, elas devem possuir, além do mesmo conteúdo, o mesmo tamanho.
Clob
Significa Character Long Object. Permite armazenar extensas cadeias con-tendo caracteres de tamanho variável com o máximo de 4 gigabytes. Existem funções especiais que manipulam campos deste tipo. Mais detalhes no Capítulo 15, Packages.
Long
Antigo mecanismo que permitia armazenar longas cadeias contendo caracteres de tamanho variável com o máximo de 2 gigabytes. Só pode existir um campo desse tipo por tabela. Um campo deste tipo pode ser substituído por outro do tipo CLOB.
Blob
Significa Binary Long Object. Permite armazenar extensa cadeia contendo dados binários (não são interpretados pelo Oracle, como, por exemplo, sons, imagens, etc.). Possuem tamanho variável com o máximo de 4 gigabytes. Para manipulá-los, devem-se utilizar funções especiais. Mais detalhes no Capítulo 15, Packages.
Raw e Long raw
Quando não existia o tipo BLOB, eram utilizados para armazenar dados binários. Limites raw: 2.000 bytes; long raw: 2.GB
Number (p,e)
Valores numéricos em que p indica a precisão (máximo de 38) e indica a escala (número de casas decimais) que varia de –84 a +127. Por exemplo, number(5,2) especifica um número na faixa entre -999,99 e +999,99.
Date
Armazena data e hora, incluindo século, ano, mês, dia, hora, minuto e se-gundo. Intervalo: 1/1/4712 a.C. até 31/12/9999. Campos deste tipo ocupam 7 bytes.
Timestamp
Permite armazenar datas/horas com maior precisão (máxima: 9, default: 6). Por exemplo, caso seja necessário especificar milésimos de segundo, a precisão deve ser 3.
Interval Year (p) to month
Armazenam intervalos de tempo em anos/meses. P representa a quan-tidade dígitos para o ano (possíveis: 0 a 9; default: 2). Intervalos podem ser obtidos, por exemplo, ao subtrair uma data de outra.
Interval Day (dp) to second (sp)
Armazenam intervalos de tempo em dias, horas, minutos e segundos. DP representa a quantidade de dígitos para o dia (possíveis: 0 a 9; default: 2). SP representa a quantidade de dígitos para o segundo (possíveis: 0 a 9; default: 6).
2.2.2 - Constraints
Um constraint ou restrição representa um mecanismo capaz de implementar controles que garantam a consistência dos dados (Integridade de dados e refe-rencial). Pode ser definido tanto em nível de coluna (afeta apenas um campo) como em nível de tabela (afeta todos os campos). Uma alternativa a esses controles é a utilização de Triggers que apresentam performance menor, mas oferecem toda a flexibilidade de uma linguagem de programação (PL/SQL)5.
A tabela seguinte mostra as restrições. Logo após, exemplos revelam como aplicá-las.
Nome Uso
Null Informa se o campo em questão pode receber valores nulos. Caso
não possa, deve ser precedido pela palavra not.
Unique
Indica que os valores na coluna, ou conjunto de colunas em questão, não podem ser repetidos. Cria um índice6
automaticamente.
Check
Determina uma regra de validação, isto é, especifica os valores que uma coluna pode assumir. Em nível de campo, somente pode fazer referência ao próprio campo que está sendo trabalhado. Já em nível de tabela, pode referenciar várias colunas.
Primary key Identifica a Chave Primária da tabela. Também cria um índice
automaticamente.
Foreign key Identifica uma Chave Estrangeira da tabela. Implementada pela
cláusula references.
Primeiro exemplo: Criação da tabela Instrutores7
create table instrutores
(cod_instrutor number(3)
constraint instrutores_pk primary key, nome_instrutor varchar2(30)
constraint instrutores_nome_nu not null, tel_instrutor varchar2(10),
admissao date default sysdate);
Observações
• A tabela recém-criada possui quatro campos (cod_instrutor, nome_instrutor, tel_instrutor e admissao);
5 O assunto triggers será visto no capítulo 16. 6 Índices serão estudados nos capítulos 6 e 23.
7 O código apresentado em seguida encontra-se no Script
• Os dois primeiros campos têm restrição. Note a convenção utilizada no sufixo (PK para Primary Key e NU para Not Null); • O campo admissao apresenta um valor default: a data atual; • Estando o SQL*Plus Worksheet aberto, uma vez concluída a
digitação, executa-se o comando anterior, pressionando a tecla F5 ou clicando no botão Executar, presente na barra de ferramentas à esquerda;
• Todo comando SQL deve ser finalizado por ponto-e-vírgula;
A digitação do comando pode ser feita opcionalmente em um editor externo. Para ativar o arquivo gerado, utilize o comando start. Por exemplo:
start G:\LivroO9i\Scripts\ParteI\Monta_Tabela_Instrutores.sql
Também é possível abrir diretamente o arquivo contendo comandos e executá-los. Utilize o comando Arquivo, Abrir do SQL*Plus Worksheet. O Comando start pertence ao SQL*Plus Worksheet e pode ser abreviado como @.
Para comprovar a criação da tabela, deve ser usado o comando describe do SQL*Plus Worksheet. Este comando pode ser abreviado como desc.
describe instrutores
Nome Nulo? Tipo
--- --- --- COD_INSTRUTOR NOT NULL NUMBER(3)
NOME_INSTRUTOR NOT NULL VARCHAR2(30) TEL_INSTRUTOR VARCHAR2(10)
ADMISSAO DATE
O SQL*Plus Worksheet trabalha com um buffer em que fica armazenado o histórico de comandos emitidos. Para acessá-lo, ative o comando Folha de Trabalho, Histórico de Comandos:
Segundo exemplo: Criação da tabela Alunos
A próxima criação de tabela traz exemplo de regra de validação (constraint check):
create table alunos
(matricula number(3) constraint alunos_pk primary key, nome_aluno varchar2(30) constraint alunos_nome_nu not null, tel_aluno varchar2(10),
endereco_aluno varchar2(30),
cidade_aluno varchar2(20) default 'Rio de Janeiro', uf char(2) default 'RJ'
constraint alunos_uf_ch check (uf in ('SP', 'MG', 'RJ', 'ES')) );
Observações
• Não podem existir dois constraints homônimos;
• Evite usar nomes com palavras acentuadas (matrícula, endereço);
• A restrição check serve para qualquer regra de validação. Por exemplo, caso fosse preciso restringir os valores no campo nota da tabela Historico:
... check (nota > 0), ...
Terceiro exemplo: Criação da tabela Turmas
A próxima criação de tabela traz exemplo de referência (constraint foreign key):
create table turmas
(cod_turma number(3) constraint turmas_pk primary key, cod_curso
constraint turmas_curso_fk references cursos(cod_curso), cod_instrutor
constraint turmas_instrutor_fk references instrutores(cod_instrutor),
sala number(2) );
Observações
• Antes de criar a tabela apresentada anteriormente, outra já deve ter sido construída: CURSOS. Se preferir, extraia o comando que cria essa tabela do arquivo Monta_Tabelas.sql;
• A cláusula references informa que a tabela corrente estará relacionada com a mencionada na forma de 1 (mencionada) para N (corrente). O campo refe-renciado deve ser chave primária (neste caso, pode ser omitido) ou possuir restrição unique e ser do mesmo tipo do campo corrente;
• Note a impossibilidade da criação de um relacionamento N:N. Já o 1:1 deve ser controlado via programação (Triggers).
Quarto exemplo: Criação da tabela Historico
A próxima tabela possui um constraint em nível de tabela (especificação da chave primária composta) e duas chaves estrangeiras:
create table historico
(cod_turma constraint historico_turma_fk references turmas (cod_turma), matricula constraint historico_aluno_fk references alunos (matricula), nota number (7,2),
constraint historico_pk
primary key (cod_turma, matricula));
Observações
• Para que esta criação tenha sucesso, já devem existir as tabelas TURMAS e ALUNOS. Se elas ainda não existirem, obtenha os comandos de criação a partir do Script Monta_Tabelas.sql;
• Constraints em nível de tabela não podem ser not null;
• Como as chaves estrangeiras devem ser de mesmo tipo e tamanho das chaves primárias equivalentes, não é preciso especificar os tipos desses campos.
2.3 - Alteração de Tabelas
Para alterar a estrutura de uma tabela, deve-se utilizar o comando alter table. Por alterar, entende-se:
• Criar, alterar ou eliminar colunas; • Renomear a tabela;
• Criar ou eliminar constraints; • Habilitar ou desabilitar constraints. A sintaxe simplificada é a seguinte:
alter table [schema.]tabela
[add (coluna tipo_de_dado [default expressão] [constraint_coluna],...)]
[modify (coluna tipo_de_dado [default expressão] [constraint_coluna], ...)]
[rename to novo_nome_tabela]
[add constraint_coluna/constraint_tabela]
[drop constraint_coluna/constraint_tabela [cascade]] [enable constraint_coluna/constraint_tabela]
Primeiro exemplo: Inserção da coluna de endereços
alter table instrutores add endereco varchar2(40);
Segundo exemplo: Alteração da coluna de endereços
alter table instrutores modify endereco varchar2(60);
Terceiro exemplo: Exclusão da coluna de endereços
alter table instrutores drop column endereco;
Atenção
Tenha muito cuidado ao emitir um comando que exclua uma coluna! Lembre-se que os dados nela contidos TAMBÉM serão eliminados.
Quarto exemplo: Inserção da restrição para notas
alter table historico
add constraint historico_nota_ch check (nota >=0 and nota <= 10);
Atenção
Evidentemente, é possível comprovar a existência do novo constraint. Isto será devidamente explicado no próximo capítulo, mas apenas para matar sua curiosidade, emita o comando:
select constraint_name from user_constraints;
Ele mostra todas as restrições criadas pelo usuário corrente.
Infelizmente, só podemos alterar o estado de uma restrição, mas não seu corpo. Deve-se eliminá-la (veja exemplos adiante) e recriá-la.
Quinto exemplo: Desabilitar a restrição para notas
alter table historico
disable constraint historico_nota_ch;
Conseqüentemente, os dados inseridos a partir deste comando não levarão em conta os valores especificados nesta restrição, e ela não poderá ser novamente habilitada, caso existam dados que a contrariam.
Sexto exemplo: Exclusão do constraint para notas
alter table historico
drop constraint historico_nota_ch;
Sétimo exemplo: Inclusão de uma chave estrangeira
Supondo que não exista chave estrangeira referenciando Cursos na tabela Turmas, poderíamos inseri-la a posteriori:
alter table turmas
add constraint turmas_curso_fk foreign key (cod_curso) references cursos(cod_curso);
Oitavo exemplo: Exclusão de dados em cascata
O relacionamento entre ALUNOS e HISTORICO poderia contemplar uma exclusão em cascata, isto é, quando um aluno fosse eliminado, suas notas também o seriam:
alter table historico
add constraint historico_alunos_fk foreign key (matricula)
references alunos (matricula) on delete cascade;
Atenção Trata-se de um procedimento bastante recomendável criar primeiro as tabelas sem constraints e depois incluí-los via comando ALTER TABLE. Isto desobriga uma ordem específica na criação de tabelas, já que não existirão chaves estrangeiras fazendo referência a tabelas inexistentes.
2.4 - Exclusão de Tabelas
Para apagar uma tabela e os constraints nela inseridos, deve-se utilizar o comando drop table:
DROP TABLE [schema.]tabela [CASCADE CONSTRAINTS];
A tabela seguinte explica a sintaxe do comando:
Opções Descrição
Schema
Representa o proprietário da tabela. Quando omitido, assume-se que a tabela pertence ao usuário corrente. Para que seja possível apagar tabelas de outros, permissões especiais devem ser conferidas.
Tabela Nome da tabela a ser eliminada.
cascade constraints
Elimina todas as restrições presentes em outras tabelas que façam refe-rência à tabela que está sendo eliminada. Os dados nas tabelas relacio-nadas, cujos constraints foram eliminados, não desaparecem.
Caso esta cláusula seja omitida e existirem referências, o Oracle-Server re-tornará um erro.
serão eliminados.
Exemplo: Exclusão mal-sucedida
Por que a eliminação seguinte provocaria um erro?
drop table cursos;
Como esse comando poderia ser alterado para que a eliminação tivesse sucesso? Somente um comando bastaria?
2.5 - Inserção de Dados
Uma vez criadas as tabelas, podem-se inserir dados nelas. Geralmente, isto é feito via aplicativos produzidos por ferramentas front-end, tais como: Oracle Forms, MS-Visual Basic ou Borland Delphi. Entretanto, também podem-se alimentar tabelas via SQL.
Em seguida mostra-se a sintaxe simplificada do comando insert, responsável pela inserção de dados:
INSERT INTO [schema.]tabela coluna1, coluna2, colunaN VALUES
(valor1, valor2, valorN);
Cada comando inserteqüivale à inserção de um registro, portanto devem-se levar em consideração os campos cujo preenchimento é obrigatório (chaves primárias e campos com restrição not null sem cláusula default).
Futuramente será visto como inserir vários registros de uma vez.
Primeiro exemplo: Inserção de um curso
insert into cursos
(cod_curso, nome_curso, carga_horaria, preco) values
(1, 'Introdução à Lógica de Programação', 32, 800);
Dica A comprovação de que o dado realmente está na tabela ficará para o próximo capítulo. Por enquanto, use o comando seguinte:
select * from cursos;
Ele mostra todos os registros da tabela Cursos.
Atenção Logo após o comando insert, os dados ainda não foram "gravados" em definitivo. Em breve, você aprenderá o conceito de transação que explicará melhor essa gravação. Por enquanto, após alguns inserts, emita o comando:
commit;
E os dados serão definitivamente gravados.
Segundo exemplo: Inserção de um aluno com valores defaults
insert into alunos (matricula, nome_aluno) values (11, 'Álvaro Lepus');
Note que os campos cidade_alunoe uf foram omitidos, já que seus valores
predeterminados são Riode Janeiroe RJ respectivamente. Uma alternativa poderia ser:
insert into alunos (matricula, nome_aluno, cidade_aluno) values (12, 'Thiago Eridanus', default);
Terceiro exemplo: Inserção simplificada de um histórico
insert into historico values (1, 1, 7.5);
Este comando traria problemas caso houvesse colunas que não estivessem na "ponta", com cláusula default.
O que ocorreria se em vez do primeiro 1 estivesse escrito 1111?
EXERCÍCIOS
Aos itens 1 a 4 responda objetivamente.
1. Quando deve-se utilizar um constraint em nível de tabela? 2. Faz sentido um constraint ser null e unique?
3. Qual erro teríamos no comando seguinte?
create table instrutores
(cod_instrutor number(3) constraint primary key, nome_instrutor varchar2(30) constraint not null, tel_instrutor varchar2(10),
admissao date default sysdate);
4. O comando seguinte funciona?
create table instrutores
(cod_instrutor number(3) primary key, nome_instrutor varchar2(30) not null, tel_instrutor varchar2(10),
admissao date default sysdate);
Qual é seu inconveniente?
5. Conecte-se ao Oracle Server e crie a tabela Cursos cuja estrutura é mostrada em seguida:
Campo Tipo Observações
Cod_curso number(3) chave primária
Nome_curso varchar2(60) não nulo, único
Carga_horaria number (3)
Preco number(7,2) valor default = 0
6. Comprove a criação da tabela.
Utilize o comando Describe no SQL*Plus Worksheet. Por que ele não termina com ponto-e-vírgula?
7. Acrescente mais uma coluna:
Pre_requisito Number(3)
8. Impeça cargas horárias negativas. 9. Insira o curso:
COD_CURSO NOME_CURSO CARGA_HORARIA PRECO PRE_REQUISITO 1 Curso1 32 800 (nulo)
10. Desabilite a restrição do exercício 8. 11. Insira o curso:
COD_CURSO NOME_CURSO CARGA_HORARIA PRECO PRE_REQUISITO 2 C2 -1 90 (nulo)
1. Tente habilitar a restrição. Explique a razão do fracasso.
12. Além dos estados enable ou disable, um constraint ainda pode estar como validate ou novalidate.
Enquanto o estado validate assegura que os dados já presentes estão de acordo com a restrição, o novalidate permite que existam "furos". Como exercício, teste as quatro combinações possíveis (enable, validate, enable, novalidate, etc.) e anote suas conclusões, isto é, quais seriam as conseqüências de um constraint estar enable, novalidate?
13. Execute o script Monta_Tabelas.sql. 14. Confirme a criação das tabelas.
Em vez de emitir vários DESCRIBEs, utilize o comando:
REFERÊNCIA RÁPIDA
Tarefa Como fazer
Conectar-se ao Oracle Server
Ative o SQL*Plus Worksheet e forneça um username e password válidos. Se o Servidor não for a própria má-quina, deve-se digitar também o serviço. Em caso de pro-blemas, contacte o DBA.
Mostrar usuário corrente Comando SQL*Plus Worksheet: show user.
Criar tabela Utilize o comando create table. Veja sintaxe e exem-plos em 2.2.
Mostrar estrutura de uma tabela
Comando SQL*Plus Worksheet: describe. Pode ser abreviado como desc.
Listar tabelas criadas select * from cat;
Listar constraints de uma tabela qualquer, digamos, cursos
select constraint_name from user_constraints
where table_name = 'CURSOS';
Executar comandos que constem em um script
Comando SQL*Plus Worksheet: start. Pode ser abreviado como @.
Alterar uma tabela Utilize o comando alter table. Veja sintaxe e exemplos em 2.3.
Excluir uma tabela Utilize o comando drop table. Veja sintaxe e exemplos em 2.4.
Inserir uma linha em uma tabela
Utilize o comando insert. Veja sintaxe e exemplos em 2.5.
Listar registros de uma tabela qualquer, digamos Alunos
Capítulo 3 – SELEÇÃO DE DADOS
OBJETIVOS
• Realizar consultas aos dados previamente armazenados em uma ou mais tabelas;
• Mostrar dados com filtros por coluna e linha; • Mostrar dados ordenados segundo algum critério; • Mostrar dados que estejam em tabelas diferentes;
• Utilizar operadores relacionais, aritméticos, lógicos ou de conjuntos;
• Exibir dados transformados por funções embutidas do SQL; • Criar consolidações;
• Recorrer à documentação do Oracle; • Pesquisar metadados.
ARQUIVOS NECESSÁRIOS
• Monta_Cursos_Velhos.sql; • Cria_Alimenta_Historico.sql; • Saida_Nao_Formatada.sql.PRÉ-REQUISITOS
• Conectar-se ao Servidor via SQL*Plus Worksheet; • Criar tabelas (comando CREATE TABLE);
• Alterar a estrutura de tabelas previamente criadas (comando ALTER TABLE);
• Eliminar tabelas (comando DROP TABLE); • Inserir dados em tabelas.
TEORIA
3.1 - Seleções Simples
Com tabelas criadas e devidamente alimentadas, pode-se realizar uma série de consultas à base de dados. Os resultados podem ser desde lista simples
de registros de uma tabela, até uma complexa interseção envolvendo vários campos de tabelas diferentes e com critérios diversos. Por exemplo:
Forneça os cursos disponíveis;
Informe quais alunos foram matriculados em turmas de Oracle; Calcule a média paga a instrutores em agosto; etc.
Consultas respondem a questões cruciais. Na verdade, cria-se um Banco de Dados para que "perguntas" possam ser respondidas.
Em qualquer consulta utiliza-se o comando SQL select cuja sintaxe simplificada é mostrada em seguida:
SELECT [DISTINCT] {*, colunas [alias], expressões, funções, ...} FROM {tabelas [alias],}
[WHERE condição] [GROUP BY colunas] [HAVING condição]
[ORDER BY colunas [ASC | DESC]];
Os colchetes representam cláusulas opcionais, as chaves indicam que os elementos podem aparecer repetidas vezes e a barra vertical indica que as opções são mutuamente exclusivas (ou uma ou outra).
A tabela seguinte explica a sintaxe do comando:
Opções Descrição
Distinct
Informa que os valores não serão mostrados repetidas vezes. Isto ocorre, por exemplo, ao listar tabelas que nasceram de relacionamentos (HISTORICO) ou aquelas que possuam valores repetidos (TURMAS terá vários códigos de cursos iguais).
* Indica que todos os campos da tabela especificada na cláusula from
devem ser mostrados.
Alias Trata-se de um "apelido" para uma tabela, campo ou expressão.
Usado para melhorar a legibilidade do comando.
Expressões
Expressões aritméticas que envolvem um ou mais campos das tabelas citadas na cláusula from. No Banco de Dados do estudo do caso serão usadas expressões para revelar quanto foi pago a um instrutor em um curso (preco_hora_instrutor * carga_horaria).
Funções Funções embutidas do SQL. Mais adiante, ainda neste capítulo,
serão mostrados vários exemplos.
Tabelas Uma ou mais tabelas cujas colunas deseja-se pesquisar.
where condição
Especifica um filtro que deve ser aplicado à saída. Em vez de mostrar todos os registros das tabelas citadas, mostram-se apenas aqueles que obedeçam a determinadas regras.
group by colunas
Indica que o resultado final será agrupado pelas colunas especificadas. Cria uma espécie de "quebra".
having condição
Limita os grupos a serem mostrados. Semelhante à cláusula where, mas aplicando filtros a colunas que tenham valores agrupados.
Opções Descrição
Order by Determina a ordenação que será aplicada ao resultado da consulta.
Ela pode ser crescente (asc)ou descendente (desc).
Primeiro exemplo: Mostra os registros da tabela instrutores8
select * from instrutores;
COD_INSTRUTOR NOME_INSTRUTOR TEL_INSTRU ADMISSAO --- --- --- --- 1 Maria Carolina Sírio 344-8788 01-FEB-97 2 Pedro Paulo Canopo 274-9018 03-AUG-96 3 Augusto Lemos Vega 722-1300 12-NOV-98 4 Mônica Silveira Capela 212-7938 30-DEC-97 5 Almir Altair 220-6022 03-OCT-96 6 Leonardo Guimarães Rigel 17-JUL-97 7 Beatriz Bellatrix 18-SEP-98 8 Carlos Regulos 01-MAR-97 9 Joana Aldebaran 12-FEB-00 10 Matias Agena 12-FEB-00
Segundo exemplo: Mostra nome e carga horária da tabela cursos
Esta consulta não mostra todos os campos da tabela CURSOS e sim apenas dois:
select nome_curso, carga_horaria from cursos; NOME_CURSO CARGA_HORARIA --- --- Introdução à Lógica de Programação 32 Fundamentos da Modelagem de Dados 40 Redes I
Introdução a Sistemas Operacionais Análise Orientada por Objetos
Delphi: Recursos Básicos 24 Delphi: Acesso a Bancos de Dados 24 Oracle: SQLPlus e SQL 32 Oracle: PL/SQL 24 Redes II
10 linhas selecionadas.
Observação
Os campos nome_curso e carga_horaria podem ser mostrados de forma conca-tenada:
... nome_curso || ' com carga horária: ' || carga_horaria
E, com um alias, o resultado seria este:
CURSOS
8 Os exemplos deste capítulo presumem que realizou-se uma conexão com
sucesso via SQL*Plus Worksheet e que o Script Monta_Tabelas.sql foi executado.
--- Introdução à Lógica de Programação com carga horária: 32 Fundamentos da Modelagem de Dados com carga horária: 40 Redes I com carga horária:
Introdução a Sistemas Operacionais com carga horária: Análise Orientada por Objetos com carga horária: Delphi: Recursos Básicos com carga horária: 24
Delphi: Acesso a Bancos de Dados com carga horária: 24 Oracle: SQLPlus e SQL com carga horária: 32
Oracle: PL/SQL com carga horária: 24 Redes II com carga horária:
Note a presença dos campos com conteúdo nulo. Este problema é contornado com a utilização da função embutida de conversão nvl.
3.2 - Ordenações e Filtros
O resultado de um comando select, caso não se informe algo diferente, mostra os registros na ordem em que foram inseridos na tabela. Além de mudar a ordenação, também é possível especificar condições que filtrem os dados.
Primeiro exemplo: Cursos ordenados decrescentemente por carga horária
select nome_curso, carga_horaria from cursos
order by carga_horaria desc;
NOME_CURSO CARGA_HORARIA --- --- Redes I
Introdução a Sistemas Operacionais Análise Orientada por Objetos Redes II
Fundamentos da Modelagem de Dados 40 Introdução à Lógica de Programação 32 Oracle: SQLPlus e SQL 32 Delphi: Recursos Básicos 24 Oracle: PL/SQL 24 Delphi: Acesso a Bancos de Dados 24
A cláusula order by pode ser substituída por:
order by 2 desc;
E já se sabe que a ordenação dar-se-ia pela segunda coluna especificada
(carga_horaria).
Observe que valores nulos são considerados maiores que qualquer número.
Segundo exemplo: Mostra a lista anterior, mas para cursos que durem mais de 30 horas
select nome_curso, carga_horaria from cursos
where carga_horaria > 30
order by carga_horaria desc;
NOME_CURSO CARGA_HORARIA --- --- Fundamentos da Modelagem de Dados 40 Introdução à Lógica de Programação 32 Oracle: SQLPlus e SQL 32
Note que a ordem das cláusulas (primeiro where, depois order by) deve ser obedecida.
A cláusula whereapresenta um operador relacional. Como esse (>) existem outros: Operador Descrição = Igual <> Diferente > Maior < Menor >= Maior ou igual <= Menor ou igual
Além dos operadores relacionais, existem os aritméticos (+, -, *, /), lógicos (and, or, not) e miscelânea:
Between Entre um valor e outro, inclusive.
Like Usado para pesquisar subcadeias de caracteres. In
Pertence a algum conjunto. Já foi utilizado na definição do constraint CHECK e será novamente usado mais adiante,
no capítulo que trata sobre Subqueries.
