Introdução ao Oracle: SQL e PL/SQL Guia do Estudante Volume 1

(1)

Introdução ao Oracle: SQL e PL/SQL

Guia do Estudante • Volume 1

41010BP13 Produção 1.3 Fevereiro de 2000 M08944-BP

(2)

Esta documentação contém informações de propriedade da Oracle Corporation.

É fornecida sob um contrato de licença que contém restrições sobre seu uso e sua divulgação, sendo também protegida pela legislação de direitos autorais. Não é permitida a engenharia reversa dos programas de computador. Se esta

documentação for entregue/distribuída a uma Agência do Departamento de Defesa do Governo dos Estados Unidos da América do Norte, será então entregue/distribuída com Direitos Restritos e a seguinte legenda será aplicável:

Legenda de Direitos Restritos

O uso, duplicação ou divulgação por aquele Governo estão sujeitos às restrições aplicáveis aos programas comerciais de computadores e serão considerados como programas de computador com Direitos Restritos de acordo com a legislação federal daquele Governo, conforme descrito no subparágrafo da legislação norte-americana (c) (1) (ii) de DFARS 252.227-7013, Direitos sobre Dados Técnicos e Programas de Computador (outubro de 1988).

Proibida a reprodução total ou parcial desta documentação sem a expressa

autorização prévia por escrito da Oracle Corporation ou da Oracle do Brasil Sistemas Ltda. A cópia deste material, de qualquer forma ou por qualquer meio, eletrônico, mecânico ou de outra natureza, inclusive através de processos xerográficos, de fotocópia e de gravação, constitui violação da legislação de direitos autorais e será punida civil e-ou criminalmente na forma da lei.

Se esta documentação for entregue / distribuída a uma Agência do Governo dos Estados Unidos da América do Norte que não esteja subordinada ao Departamento de Defesa, será então entregue / distribuída com "Direitos Restritos", conforme definido no FAR 52.227-14, Direitos sobre Dados - Geral, inclusive a Alternativa III (junho de 1987).

As informações contidas neste documento estão sujeitas a alterações sem aviso prévio. Se você encontrar algum problema na documentação, envie a Products Education - Oracle Corporation ou a Education - Oracle do Brasil Sistemas Ltda. uma descrição de tal problema por escrito.

Education Products, Oracle Corporation, 500 Oracle Parkway, Box SB-6, Redwood Shores, CA 94065.

Distribuidor no Brasil: Oracle do Brasil Sistemas Ltda.

Rua José Guerra, 127, São Paulo, SP - 04719-030 - Brasil

CGC: 59.456.277/0001-76.

A Oracle Corporation e a Oracle do Brasil Sistemas Ltda. não garantem que este documento esteja isento de erros.

Oracle e todos os demais produtos Oracle citados nesta documentação são marcas comerciais ou marcas comerciais registradas da Oracle Corporation.

Todos os outros nomes de produtos ou de empresas aqui mencionados o são apenas para fins de identificação e podem ser marcas comerciais registradas de seus respectivos proprietários.

Autores Neena Kochhar Ellen Gravina Priya Nathan

Colaboradores Técnicos e Revisores

Claire Bennet Christa Miethaner Tony Hickman Sherin Nassa Nancy Greenberg Hazel Russell Kenneth Goetz Piet van Zon Ulrike Dietrich Helen Robertson Thomas Nguyen Lisa Jansson Kuljit Jassar

Editor Jerry Brosnan

(3)

Prefácio

Mapa de Curso Introdução

Objetivos I-2

Ciclo de Vida de Desenvolvimento do Sistema I-3 Armazenamento de Dados em Diferentes Mídias I-5 Conceito de Banco de Dados Relacional I-6

Definição de Banco de Dados Relacional I-7 Modelos de Dados I-8

Modelo de Relacionamento de Entidades I-9

Convenções de Modelo para Relacionamento de Entidades I-11 Terminologia de Banco de Dados Relacional I-13

Relacionando Várias Tabelas I-15

Propriedades de Banco de Dados Relacional I-17 Comunicando-se com um RDBMS Usando o SQL I-18

Sistema de Gerenciamento de Banco de Dados Relacional I-19 Oracle8: Sistema de Gerenciamento de Banco de Dados Relacional de Objeto I-20

Oracle8i: Banco de Dados de Plataforma Internet para Recursos de Computação na Internet I-21

Plataforma Internet da Oracle I-23 Instruções SQL I-24

Sobre PL/SQL I-25 Ambiente PL/SQL I-26

Tabelas Usadas no Curso I-27 Sumário I-28

1 Criando Instruções SQL Básicas Objetivos 1-2

Recursos das Instruções SELECT SQL 1-3 Instrução SELECT Básica 1-4

Criando Instruções SQL 1-5

Selecionando Todas as Colunas 1-6 Selecionando Colunas Específicas 1-7 Defaults de Cabeçalho de Coluna 1-8 Expressões Aritméticas 1-9

Usando Operadores Aritméticos 1-10

Sumário

iii

(4)

Precedência do Operador 1-11 Usando Parênteses 1-13 Definindo um Valor Nulo 1-14

Valores Nulos nas Expressões Aritméticas 1-15 Definindo um Apelido de Coluna 1-16

Usando Apelidos de Coluna 1-17 Operador de Concatenação 1-18

Usando um Operador de Concatenação 1-19 Strings Literais de Caracteres 1-20

Usando Strings Literais de Caracteres 1-21 Linhas Duplicadas 1-22

Eliminando Linhas Duplicadas 1-23 Interação SQL e SQL*Plus 1-24

Instruções SQL Versus Comandos SQL*Plus 1-25 Visão Geral do SQL*Plus 1-26

Estabelecendo Login no SQL*Plus 1-27 Exibindo a Estrutura de Tabela 1-28 Comandos de Edição do SQL*Plus 1-30 Comandos de Arquivo do SQL*Plus 1-32 Sumário 1-33

Visão Geral do Exercício 1-34

2 Restringindo e Classificando Dados Objetivos 2-2

Limitando Linhas Usando uma Seleção 2-3 Limitando Linhas Selecionadas 2-4

Usando a Cláusula WHERE 2-5 Strings de Caractere e Datas 2-6 Operadores de Comparação 2-7

Usando Operadores de Comparação 2-8 Outros Operadores de Comparação 2-9 Usando o Operador BETWEEN 2-10 Usando o Operador IN 2-11

Usando o Operador LIKE 2-12 Usando o Operador IS NULL 2-14 Operadores Lógicos 2-15

Usando o Operador AND 2-16 Usando o Operador OR 2-17 Usando o Operador NOT 2-18 Regras de Precedência 2-19 Cláusula ORDER BY 2-22

(5)

Classificando em Ordem Decrescente 2-23 Classificando por Apelido de Coluna 2-24 Classificando por Várias Colunas 2-25 Sumário 2-26

Visão Geral do Exercício 2-27 3 Funções de Uma Única Linha

Objetivos 3-2 Funções SQL 3-3

Dois Tipos de Funções SQL 3-4 Funções de Uma Única Linha 3-5 Funções de Caractere 3-7

Funções de Conversão de Maiúsculas e Minúsculas 3-9

Usando Funções de Conversão de Maiúsculas e Minúsculas 3-10 Funções de Manipulação de Caractere 3-11

Usando as Funções de Manipulação de Caractere 3-12 Funções Numéricas 3-13

Usando a Função ROUND 3-14 Usando a Função TRUNC 3-15 Usando a Função MOD 3-16 Trabalhando com Datas 3-17 Aritmética com Datas 3-18

Usando Operadores Aritméticos com Datas 3-19 Funções de Data 3-20

Usando Funções de Data 3-21 Funções de Conversão 3-23

Conversão Implícita de Tipo de Dados 3-24 Conversão Explícita de Tipo de Dados 3-26 Função TO_CHAR com Datas 3-29

Elementos de Modelo de Formato de Data 3-30 Usando a Função TO_CHAR com Datas 3-32 Função TO_CHAR com Números 3-33

Usando a Função TO_CHAR com Números 3-34 Funções TO_NUMBER e TO_DATE 3-35

Formato de Data RR 3-36 Função NVL 3-37

Usando a Função NVL 3-38 Função DECODE 3-39

Usando a Função DECODE 3-40 Aninhando Funções 3-42

Sumário 3-44

v

(6)

4 Exibindo Dados de Várias Tabelas Objetivos 4-2

Obtendo Dados de Várias Tabelas 4-3 O Que É uma Junção? 4-4

Produto Cartesiano 4-5

Gerando um Produto Cartesiano 4-6 Tipos de Junções 4-7

O Que É uma Junção Idêntica? 4-8

Recuperando Registros com Junções Idênticas 4-9 Qualificando Nomes de Coluna Ambíguos 4-10

Condições de Pesquisa Adicional Usando o Operador AND 4-11 Usando Apelidos de Tabela 4-12

Unindo Mais de Duas Tabelas 4-13 Junções Não-idênticas 4-14

Recuperando Registros com Junções Não-idênticas 4-15 Junções Externas 4-16

Usando Junções Externas 4-18 Autojunções 4-19

Unindo uma Tabela a Ela Mesma 4-20 Sumário 4-21

5 Agregando Dados Usando Funções de Grupo Objetivos 5-2

O Que São Funções de Grupo? 5-3 Tipos de Funções de Grupo 5-4 Usando Funções de Grupo 5-5 Usando Funções AVG e SUM 5-6 Usando Funções MIN e MAX 5-7 Usando a Função COUNT 5-8

Funções de Grupo e Valores Nulos 5-10

Usando a Função NVL com Funções de Grupo 5-11 Criando Grupos de Dados 5-12

Criando Grupos de Dados: Cláusula GROUP BY 5-13 Usando a Cláusula GROUP BY 5-14

Agrupando por Mais de Uma Coluna 5-16

Usando a Cláusula GROUP BY em Várias Colunas 5-17 Consultas Ilegais Usando Funções de Grupo 5-18 Excluindo Resultados do Grupo 5-20

Excluindo Resultados do Grupo: Cláusula HAVING 5-21 Usando a Cláusula HAVING 5-22

(7)

Aninhando Funções de Grupo 5-24 Sumário 5-25

Visão Geral do Exercício 5-26 6 Subconsultas

Objetivos 6-2

Usando uma Subconsulta para Resolver um Problema 6-3 Subconsultas 6-4

Usando uma Subconsulta 6-5

Diretrizes para o Uso de Subconsultas 6-6 Tipos de Subconsultas 6-7

Subconsultas de uma Única Linha 6-8

Executando Subconsultas de uma Única Linha 6-9 Usando Funções de Grupo em uma Subconsulta 6-10 Cláusula HAVING com Subconsultas 6-11

O Que Há de Errado com esta Instrução? 6-12 Esta Instrução Irá Funcionar? 6-13

Subconsultas de Várias Linhas 6-14

Usando o Operador ANY em Subconsultas de Várias Linhas 6-15 Usando o Operador ALL em Subconsultas de Várias Linhas 6-16 Sumário 6-17

Visão Geral do Exercício 6-18 7 Subconsultas de Várias Colunas

Objetivos 7-2

Subconsultas de Várias Colunas 7-3

Usando Subconsultas de Várias Colunas 7-4 Comparações de Coluna 7-6

Subconsulta de Comparação que Não Seja aos Pares 7-7 Subconsulta que Não Seja aos Pares 7-8

Valores Nulos em uma Subconsulta 7-9

Usando uma Subconsulta na Cláusula FROM 7-10 Sumário 7-11

8 Produzindo uma Saída Legível com o SQL*Plus Objetivos 8-2

Relatórios Interativos 8-3 Variáveis de Substituição 8-4

Usando a Variável de Substituição & 8-5 Usando o Comando SET VERIFY 8-6

Valores de Caractere e Data com Variáveis de Substituição 8-7

vii

(8)

Especificando Nomes de Coluna, Expressões e Texto no Tempo de Execução 8-8 Usando a Variável de Substituição && 8-10

Definindo as Variáveis de Usuário 8-11 O Comando ACCEPT 8-12

Usando o Comando ACCEPT 8-13 Comandos DEFINE e UNDEFINE 8-14 Usando o Comando DEFINE 8-15

Personalizando o Ambiente SQL*Plus 8-16 Variáveis do Comando SET 8-17

Salvando as Personalizações no Arquivo login.sql 8-18 Comandos de Formato do SQL*Plus 8-19

O Comando COLUMN 8-20

Usando o Comando COLUMN 8-21 Modelos de Formato COLUMN 8-22 Usando o Comando BREAK 8-23

Usando os Comandos TTITLE e BTITLE 8-24

Criando um Arquivo de Script para Executar um Relatório 8-25 Exemplo de Relatório 8-27

Sumário 8-28

Visão Geral do Exercício 8-29 9 Manipulação de Dados

Objetivos 9-2

DML (Data Manipulation Language) 9-3

Adicionando uma Nova Linha em uma Tabela 9-4 A Instrução INSERT 9-5

Inserindo Novas Linhas 9-6

Inserindo Linhas com Valores Nulos 9-7 Inserindo Valores Especiais 9-8

Inserindo Valores Espec'ificos de Data 9-9

Inserindo Valores Usando Variáveis de Substituição 9-10 Criando um Script com Prompts Personalizados 9-11 Copiando Linhas a partir de Outra Tabela 9-12 Alterando os Dados em uma Tabela 9-13 A Instrução UPDATE 9-14

Atualizando Linhas em uma Tabela 9-15

Atualizando com Subconsulta de Várias Colunas 9-16 Atualizando Linhas Baseadas em Outra Tabela 9-17 Atualizando Linhas: Erro de Restrição de Integridade 9-18 Removendo uma Linha de uma Tabela 9-19

A Instrução DELETE 9-20

(9)

Deletando Linhas de uma Tabela 9-21

Deletando Linhas Baseadas em Outra Tabela 9-22 Deletando Linhas: Erro de Restrição de Integridade 9-23 Transações de Banco de Dados 9-24

Vantagens das Instruções COMMIT e ROLLBACK 9-26 Controlando Transações 9-27

Processando Transações Implícitas 9-28

Estado dos Dados Antes do COMMIT ou ROLLBACK 9-29 Estado dos Dados Após COMMIT 9-30

Submetendo Dados a Commit 9-31 Estado dos Dados Após ROLLBACK 9-32

Fazendo Roll Back de Alterações para um Marcador 9-33 Rollback no Nível da Instrução 9-34

Consistência na Leitura 9-35

Implementação da Consistência na Leitura 9-36 Bloqueando 9-37

Sumário 9-38

Visão Geral do Exercício 9-39 10 Criando e Gerenciando Tabelas

Objetivos 10-2

Objetos do Banco de Dados 10-3 Convenções para Nomeação 10-4 A Instrução CREATE TABLE 10-5

Fazendo Referência a Tabelas de Outro Usuário 10-6 A Opção DEFAULT 10-7

Criando Tabelas 10-8

Tabelas no Banco de Dados Oracle 10-9 Consultando o Dicionário de Dados 10-10 Tipos de Dados 10-11

Criando uma Tabela Usando uma Subconsulta 10-13 A Instrução ALTER TABLE 10-15

Adicionando uma Coluna 10-16 Modificando uma Coluna 10-18 Eliminando uma Coluna 10-19 Opção SET UNUSED 10-20 Eliminando uma Tabela 10-22

Alterando o Nome de um Objeto 10-23 Truncando uma Tabela 10-24

Adicionando Comentários a uma Tabela 10-25 Sumário 10-26

ix

(10)

11 Incluindo Restrições Objetivos 11-2

O Que São Restrições? 11-3 Diretrizes sobre Restrições 11-4 Definindo Restrições 11-5 A Restrição NOT NULL 11-7 A Restrição UNIQUE KEY 11-9 A Restrição PRIMARY KEY 11-11 A Restrição FOREIGN KEY 11-13

Palavras-chave da Restrição FOREIGN KEY 11-15 A Restrição CHECK 11-16

Adicionando uma Restrição 11-17 Eliminando uma Restrição 11-19 Desativando Restrições 11-20 Ativando Restrições 11-21 Restrições em Cascata 11-22 Verificando Restrições 11-24

Verificando Colunas Associadas com Restrições 11-25 Sumário 11-26

Visão Geral do Exercício 11-27 12 Criando Views

Objetivos 12-2

Objetos de Banco de Dados 12-4 O Que É uma View? 12-5

Por Que Usar Views? 12-6

Views Simples e Views Complexas 12-7 Criando uma View 12-8

Recuperando Dados de uma View 12-11 Consultando uma View 12-12

Modificando uma View 12-13 Criando uma View Complexa 12-14

Regras para Executar Operações DML em uma View 12-15 Usando a Cláusula WITH CHECK OPTION 12-17

Negando Operações DML 12-18 Removendo uma View 12-19 Views Em Linha 12-20 Análise "Top-N” 12-21

Executando a Análise “Top-N” 12-22 Exemplo de Análise “Top-N” 12-23 Sumário 12-24

(11)

13 Outros Objetos do Banco de Dados Objetivos 13-2

Objetos do Banco de Dados 13-3 O Que É uma Seqüência? 13-4

A Instrução CREATE SEQUENCE 13-5 Criando uma Seqüência 13-7

Confirmando Seqüências 13-8

Pseudocolunas NEXTVAL e CURRVAL 13-9 Usando uma Seqüência 13-11

Modificando uma Seqüência 13-13

Diretrizes para Modificar uma Seqüência 13-14 Removendo uma Seqüência 13-15

O Que É um Índice? 13-16

Como os Índices são Criados? 13-17 Criando um Índice 13-18

Quando Criar um Índice 13-19 Quando Não Criar um Índice 13-20 Confirmando Índices 13-21

Índices Baseados em Função 13-22 Removendo um Índice 13-23

Sinônimos 13-24

Criando e Removendo Sinônimos 13-25 Sumário 13-26

Visão Geral do Exercício 13-27 14 Controlando o Acesso do Usuário

Objetivos 14-2

Controlando o Acesso do Usuário 14-3 Privilégios 14-4

Privilégios de Sistema 14-5 Criando Usuários 14-6

Privilégios de Sistema de Usuário 14-7 Concedendo Privilégios de Sistema 14-8 O Que É uma Função? 14-9

Criando e Concedendo Privilégios a uma Função 14-10 Alterando Sua Senha 14-11

Privilégios de Objeto 14-12

Concedendo Privilégios de Objeto 14-14

Usando as Palavras-chave WITH GRANT OPTION e PUBLIC 14-15 Confirmando Privilégios Concedidos 14-16

Como Revogar Privilégios de Objeto 14-17

xi

(12)

Revogando Privilégios de Objeto 14-18 Sumário 14-19

Visão Geral do Exercício 14-20 15 SQL Workshop

Visão Geral do Workshop 15-2 16 Declarando Variáveis

Objetivos 16-2 Sobre PL/SQL 16-3

Benefícios da Linguagem PL/SQL 16-4 Estrutura de Bloco PL/SQL 16-6

Tipos de Bloco 16-8

Construções de Programa 16-9 Uso de Variáveis 16-11

Tratando Variáveis em PL/SQL 16-12 Tipos de Variáveis 16-13

Declarando Variáveis PL/SQL 16-16 Regras para Nomeação 16-18

Atribuindo Valores às Variáveis 16-19

Palavras-chave e Inicialização de Variáveis 16-20 Tipos de Dados Escalares 16-22

Tipos de Dados Escalares Básicos 16-23 Declarando Variáveis Escalares 16-25 O Atributo %TYPE 16-26

Declarando Variáveis com o Atributo %TYPE 16-27 Declarando Variáveis Booleanas 16-28

Estrutura de Registro PL/SQL 16-29 Variáveis de Tipo de Dados LOB 16-30 Variáveis de Ligação 16-31

Referenciando Variáveis Não-PL/SQL 16-33 DBMS_OUTPUT.PUT_LINE 16-34

Sumário 16-35

Visão Geral do Exercício 16-37 17 Criando Instruções Executáveis

Objetivos 17-2

Diretrizes e Sintaxe de Bloco PL/SQL 17-3 Comentando Código 17-6

Funções SQL em PL/SQL 17-7 Funções PL/SQL 17-8

Conversão de Tipo de Dados 17-9

(13)

Blocos Aninhados e Escopo de Variável 17-11 Operadores em PL/SQL 17-14

Usando Variáveis de Ligação 17-16 Diretrizes de Programação 17-17

Convenções para Nomeação de Código 17-18 Endentando o Código 17-19

Determinando o Escopo da Variável 17-20 Sumário 17-21

Visão Geral do Exercício 17-22 18 Interagindo com o Oracle Server

Objetivos 18-2

Instruções SQL em PL/SQL 18-3 Instruções SELECT em PL/SQL 18-4 Recuperando Dados em PL/SQL 18-6 Manipulando Dados Usando o PL/SQL 18-8 Inserindo Dados 18-9

Atualizando Dados 18-10 Deletando Dados 18-11

Convenções para Nomeação 18-12 Instruções COMMIT e ROLLBACK 18-14 Cursor SQL 18-15

Atributos do Cursor SQL 18-16 Sumário 18-18

Visão Geral do Exercício 18-20 19 Criando Estruturas para Controle

Objetivos 19-2

Controlando o Fluxo de Execução PL/SQL 19-3 Instruções IF 19-4

Instruções IF Simples 19-5

Fluxo de Execução da Instrução IF-THEN-ELSE 19-6 Instruções IF-THEN-ELSE 19-7

Fluxo de Execução da Instrução IF-THEN-ELSIF 19-8 Instruções IF-THEN-ELSIF 19-9

Elaborando Condições Lógicas 19-10 Tabelas Lógicas 19-11

Condições Booleanas 19-12

Controle Iterativo: Instruções LOOP 19-13 Loop Básico 19-14

Loop FOR 19-16

xiii

(14)

Loop WHILE 19-19

Loops e Labels Alinhados 19-21 Sumário 19-23

20 Trabalhando com Tipos de Dados Compostos Objetivos 20-2

Tipos de Dados Compostos 20-3 Registros PL/SQL 20-4

Criando um Registro PL/SQL 20-5 Estrutura de Registro PL/SQL 20-7 O Atributo %ROWTYPE 20-8

Vantagens de Usar %ROWTYPE 20-9 O Atributo %ROWTYPE 20-10

Tabelas PL/SQL 20-11

Criando uma Tabela PL/SQL 20-12 Estrutura de Tabela PL/SQL 20-13 Criando uma Tabela PL/SQL 20-14

Usando Métodos de Tabela PL/SQL 20-15 Tabela de Registros PL/SQL 20-16

Exemplo de Tabela de Registros PL/SQL 20-17 Sumário 20-18

Visão Geral do Exercício 20-19 21 Criando Cursores Explícitos

Objetivos 21-2

Sobre os Cursores 21-3

Funções do Cursor Explícito 21-4 Controlando Cursores Explícitos 21-5 Declarando o Cursor 21-7

Abrindo o Cursor 21-9

Extraindo Dados do Cursor 21-11 Fechando o Cursor 21-13

Atributos do Cursor Explícito 21-14 Controlando Várias Extrações 21-15 O Atributo %ISOPEN 21-16

Os Atributos %NOTFOUND e %ROWCOUNT 21-17 Cursores e Registros 21-19

Loops FOR de Cursor 21-20

Loops FOR do Cursor Usando Subconsultas 21-22 Sumário 21-23

(15)

22 Conceitos de Cursor Explícito Avançados Objetivos 22-2

Cursores com Parâmetros 22-3 A Cláusula FOR UPDATE 22-5

A Cláusula WHERE CURRENT OF 22-7 Cursores com Subconsultas 22-9

Sumário 22-10

Visão Geral do Exercício 22-11 23 Tratando Exceções

Objetivos 23-2

Tratando Exceções com Código PL/SQL 23-3 Tratando Exceções 23-4

Tipos de Exceção 23-5 Capturando Exceções 23-6

Diretrizes para a Captura de Exceções 23-7

Capturando Erros Predefinidos do Oracle Server 23-8 Exceção Predefinida 23-10

Capturando Erros Não Predefinidos do Oracle Server 23-11 Erro Não Predefinido 23-12

Funções para Captura de Exceções 23-13

Capturando Exceções Definidas pelo Usuário 23-15 Exceção Definida pelo Usuário 23-16

Ambientes de Chamada 23-17 Propagando Exceções 23-18

Procedimento RAISE_APPLICATION_ERROR 23-19 Sumário 23-21

A Soluções Práticas

B Descrições da Tabela e Dados Índice

xv

(16)

(17)

Prefácio

(18)

(19)

Prefácio - 3 Perfil

Antes de Iniciar Este Curso

Antes de iniciar este curso, você deve ser capaz de usar uma GUI (Graphical User Interface, interface gráfica com o usuário). O pré-requisito necessário é estar familiarizado com conceitos e técnicas de processamento de dados.

Como Este Curso Está Organizado

Introdução ao Oracle: SQL and PL/SQL é um curso orientado por instrutor contendo palestras e exercícios práticos. Sessões de demonstração on-line e de exercício escrito reforçam os conceitos e técnicas apresentadas.

(20)

Publicações Relacionadas Publicações da Oracle

Título Número do Componente

Oracle8i Server, Release 8.1.5 A68826-01

Oracle8i Concepts, Release 8.1.5 A67781-01

Oracle8i SQL Reference Manual, Release 8.1.5 A67779-01 Oracle8i Server Application Developer’s Guide A68003-01 SQL*Plus User’s Guide and Reference, Release 8.1.5 A66736-01 SQL*Plus Quick Reference, Release 8.1.5 A66735-01 PL/SQL User’s Guide and Reference, Release 8.1.5 A67842-01

Publicações Adicionais

• Boletins sobre versão do sistema

• Guias de instalação e do usuário

• arquivos README

• artigos do International Oracle User's Group (IOUG)

• Oracle Magazine

(21)

Prefácio - 5 Convenções Tipográficas

A seguir estão duas listas de convenções tipográficas usadas especificamente dentro de texto ou de código.

Convenções Tipográficas Dentro de Texto

Convenção Objeto ou Condição Exemplo

Letras maiúsculas Comandos, Use o comando SELECT para visualizar funções, informações armazenadas na coluna nomes de coluna, LAST_NAME

nomes de tabelas, coluna da tabela EMP.

objetos PL/SQL, esquemas

Letras minúsculas, Nomes de arquivo, onde: função é o nome da função

itálico variáveis de sintaxe, a ser criada.

nomes de usuário, senhas

Inicial maiúscula Nomes de gatilho Atribua um gatilho a When-Validate-Item nomes de botão ao bloco ORD.

Escolha Cancelar.

Itálico Livros, nomes de Para obter mais informações sobre o cursos e assunto, consulte o

manuais e Oracle Server SQL Language Reference palavras ou frases Manual

enfatizadas

Não salve alterações para o banco de dados.

Aspas Títulos de módulo Este assunto é abordado na Lição 3, de lição a que é feito "Trabalhando com Objetos".

referência dentro de um curso

(22)

Convenções Tipográficas (continuação)

Convenções Tipográficas Dentro de Código

Convenção Objeto ou Termo Exemplo

Letras maiúsculas Comandos, SQL> SELECT userid

funções 2 FROM emp;

Letras minúsculas, Variáveis de sintaxe SQL> CREATE ROLE role;

itálico

Inicial maiúscula Gatilhos de forms Form module: ORD

Trigger level: S_ITEM.QUANTITY item

Trigger name: When-Validate-Item . . .

Letra minúscula Nomes de colunas, . . .

nomes de tabelas, OG_ACTIVATE_LAYER

nomes de arquivos, (OG_GET_LAYER ('prod_pie_layer')) objetos do PL/SQL . . .

SQL> SELECT last_name 2 FROM emp;

Negrito Texto que deve SQLDBA> DROP USER scott

ser incluído por 2> IDENTIFIED BY tiger;

um usuário

(23)

Mapa de

Curso

(24)

Curso de Linguagens Integradas: Linhas de Certificação

Introdução ao Oracle: SQL e PL/SQL consiste em dois módulos, SQL1 e PL/SQL Fundamentals. Esse curso é pré-requisito para a linha do DBA ou Application Developer. O SQL1 abrange a criação de estruturas de bancos de dados e o armazenamento, recuperação e manipulação de dados em um banco de dados relacional. O curso PL/SQL Fundamentals abrange a criação de blocos PL/SQL de código de aplicação.

Para aqueles que trabalharam com outros bancos de dados relacionais e têm conhecimentos de SQL, é oferecido outro módulo chamado Oracle SQL Specifics. Ele abrange as instruções SQL que não fazem parte do ANSI SQL mas são específicas do Oracle. Esse módulo combinado com o PL/SQL

Fundamentals forma o Introdução ao Oracle para Usuários Experientes em SQL.

Introdução ao Oracle: O SQL e PL/SQL e a Introdução ao Oracle para Usuários Experientes em SQL são considerados equivalentes e, após terminar um deles, você poderá passar para a linha de DBA. Para a linha do Application Developer, você deve fazer o curso Desenvolvimento de Unidades de Programa em PL/SQL. Esse curso ensina como criar funções, pacotes, acionadores e procedimentos PL/SQL.

Curso de Linguagens Integradas:

Linhas de Certificação

Introdução ao Oracle:

SQL e PL/SQL

SQL1 PL/SQL

Fundamentals

Introdução ao Oracle

para Usuários Experientes em SQL Oracle SQL

Specifics

PL/SQL Fundamentals ou

Linha do DBA Desenvolvimento de

Unidades de Programa em PL/SQL

Linha do Application Developer

(25)

Mapa de Curso - 3

Curso de Linguagens Integradas

ILT CBT Seminar

SQL for End Users Oracle SQL Specifics

Oracle8 3GL Precompiler

Workshop Oracle8 3GL

Precompiler Workshop Oracle8 Call Interface

Workshop Oracle8 Call Interface

Workshop SQL1

PL/SQL Fundamentals

SQL Statement Tuning

Develop PL/SQL Program Units Extended Data Retrieval

with SQL

Advanced PL/SQL Rules and Guidelines

Diagnostics

Basics

Database Programming Procedures, Functions,

and Packages Advanced SELECT Statements

SQL*Plus and Reporting

Retrieving & Formatting Data Creating & Managing

Database Objects Basic SELECT Statements

DML and DDL Data Retrieval Techniques

Advanced Techniques for PL/SQL Developers

PL/SQL Best Practices Oracle8: New Features in PL/SQL

Oracle Built-in Packages

Curso de Linguagens Integradas

O slide lista vários módulos e cursos disponíveis no curso de linguagens. Para a maioria desses módulos e cursos, há CBTs equivalentes.

São oferecidos quatro seminários sobre PL/SQL: Advanced Techniques for PL/SQL Developers, PL/SQL Best Practices, Oracle8: New Features in PL/SQL e Oracle Built-in Packages.

Curso ou Módulo CBT Equivalente

SQL1 Oracle SQL: Basic SELECT Statements

Oracle SQL: Data Retrieval Techniques Oracle SQL: DML and DDL

Oracle SQL Specifics Oracle SQL Specifics: Retrieving and Formatting Data Oracle SQL Specifics: Creating and Managing Database Objects

PL/SQL Fundamentals PL/SQL: Basics Extended Data Retrieval with

SQL

Oracle SQL and SQL*Plus: Advanced SELECT Statements Oracle SQL and SQL*Plus: SQL*Plus and Reporting Develop PL/SQL Program Units PL/SQL: Procedures, Functions, and Packages

PL/SQL: Database Programming SQL Statement Tuning SQL and PL/SQL Tuning: Diagnostics

SQL and PL/SQL Tuning: Rules and Guidelines

(26)

(27)

I I

Introdução

(28)

Objetivo da Lição

Nesta lição, você entenderá o RDBMS (relational database management system) e o ORDBMS (object relational database management system). Você também será apresentado aos seguintes tópicos:

• Instruções SQL específicas do Oracle

• SQL*Plus, usado para executar o SQL e para fins de formatação e elaboração de relatórios

• O PL/SQL, que é a linguagem procedural do Oracle

Objetivos

Depois de completar esta lição, você poderá fazer o seguinte:

• Discutir os aspectos teóricos e físicos de um banco de dados relacional

• Descrever a implementação Oracle do RDBMS e ORDBMS

• Descrever os novos recursos do Oracle8i

• Descrever como o SQL e o PL/SQL são usados no conjunto de produtos Oracle

• Descrever o uso e os benefícios do PL/SQL

(29)

Introdução ao Oracle: SQL e PL/SQL I-3 Ciclo de Vida de Desenvolvimento do Sistema

Do conceito à produção, você pode desenvolver um banco de dados usando o ciclo de vida de desenvolvimento do sistema, que contém vários estágios de desenvolvimento. Essa abordagem completa e sistemática para o desenvolvimento de bancos de dados transforma necessidades de informações comerciais em um banco de dados operacional.

Estratégia e Análise

• Estude e analise as necessidades comerciais. Entreviste usuários e gerentes para identificar as necessidades de informações. Incorpore as declarações de objetivos da aplicação e da empresa, além de qualquer especificação futura do sistema.

• Elabore modelos do sistema. Transfira a narrativa comercial para uma representação gráfica das regras e necessidades de informações comerciais. Confirme e refine o modelo com os analistas e especialistas.

Design

Projete o banco de dados de acordo com o modelo desenvolvido na fase de estratégia e análise.

Elaboração e Documentação

• Elabore o sistema protótipo. Crie e execute os comandos para elaborar tabelas e objetos de suporte para o banco de dados.

• Desenvolva uma documentação para o usuário, textos de ajuda e manuais de operação para suporte ao uso e à operação do sistema.

Ciclo de Vida de

Desenvolvimento do Sistema

Estratégia e Análise

Design

Elaboração e Documentação

Transição

Produção

(30)

Ciclo de Vida de Desenvolvimento do Sistema (continuação) Transição

Refine o protótipo. Mova uma aplicação para a produção com teste de aceitação do usuário, conversão de dados existentes e operações paralelas. Faça as modificações necessárias.

Produção

Forneça o sistema aos usuários. Opere o sistema de produção. Monitore o desempenho, aperfeiçoe e refine o sistema.

Observação: É possível executar as várias fases do ciclo de vida de desenvolvimento do sistema repetidamente. Este curso se concentra na fase de elaboração do ciclo de vida de desenvolvimento do sistema.

Ciclo de Vida de

Desenvolvimento do Sistema

Estratégia e Análise

Design

Elaboração e Documentação

Transição

Produção

(31)

Introdução ao Oracle: SQL e PL/SQL I-5 Armazenando Informações

Toda organização necessita de informações. Uma biblioteca mantém uma lista de membros, livros, datas de entrega e multas. Uma empresa precisa gravar informações sobre funcionários,

departamentos e salários. Essas informações são chamadas de dados.

As organizações podem armazenar dados em várias mídias e em formatos diferentes, por exemplo, um documento impresso em um arquivo físico ou dados em planilhas eletrônicas ou bancos de dados.

Um banco de dados é um conjunto organizado de informações.

Para gerenciar bancos de dados, você precisa de DBMSs (database management systems). Um DBMS é um programa que armazena, recupera e modifica dados do banco de dados a pedido. Há quatro tipos principais de bancos de dados: hierárquico, de rede, relacional e relacional de objeto, o mais recente.

Observação: O Oracle7 é um RDBMS (relational database management system) e o Oracle8 é um ORDBMS (object relational database management system).

Planilha eletrônica

Arquivo

físico Banco de

Dados SALGRADE

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

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

Armazenamento de Dados

em Diferentes Mídias

(32)

Modelo Relacional

Os princípios do modelo relacional foram definidos primeiramente pelo Dr. E.F. Codd em junho de 1970 em um estudo chamado "A Relational Model of Data for Large Shared Data Banks". Nesse estudo, o Dr. Codd propôs o modelo relacional de sistemas de bancos de dados.

Os modelos mais populares usados naquele tempo eram hierárquicos, de rede ou mesmo estruturas de dados de arquivos simples. Os RDBMSs (relational database management systems) em breve se tornaram muito populares, especialmente pela facilidade de uso e flexibilidade na estrutura. Além disso, vários fornecedores inovadores, como a Oracle, ofereciam o RDBMS com um conjunto eficiente de desenvolvimento de aplicações e produtos para usuários, formando uma solução completa.

Componentes do Modelo Relacional

• Conjuntos de objetos ou relações que armazenam os dados

• Conjunto de operadores que podem agir sobre as relações para produzir outras relações

• Integridade de dados para precisão e consistência

Para obter mais informações, consulte E.F. Codd, The Relational Model for Database Management Version 2 (Reading, Mass.: Addison-Wesley, 1990).

Conceito de Banco de Dados Relacional

• O Dr. E.F. Codd propôs o modelo relacional de sistemas de bancos de dados em 1970.

• Ele é a base para o RDBMS (relational database management system).

• O modelo relacional consiste nos seguintes itens:

– Conjunto de objetos ou relações

– Conjunto de operadores para agir sobre as relações – Integridade de dados para precisão e consistência

(33)

Introdução ao Oracle: SQL e PL/SQL I-7

Definição de Banco de Dados Relacional

Um banco de dados relacional é um conjunto de relações ou tabelas bidimensionais.

Banco de Dados

EMPNO ENAME JOB DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20

Nome da Tabela: EMP

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

Nome da Tabela: DEPT

Definição de Banco de Dados Relacional

Um banco de dados relacional usa relações ou tabelas bidimensionais para armazenar informações.

Por exemplo, você pode armazenar informações sobre todos os funcionários de uma empresa. Em um banco de dados relacional, você cria várias tabelas para armazenar informações diferentes sobre funcionários, como tabelas de funcionários, departamentos e salários.

(34)

Modelos de Dados

Modelo de sistema imaginado pelo cliente

Modelo de entidade do

modelo

do cliente Modelo de

tabela do modelo de entidade

Tabelas em discos Servidor

Modelos de Dados

Os modelos são a base do design. Os engenheiros criam um modelo de carro para estudar os detalhes antes de colocá-lo em produção. Da mesma forma, projetistas de sistemas desenvolvem modelos para explorar idéias e compreender melhor o design de um banco de dados.

Objetivo dos Modelos

Os modelos ajudam a comunicar conceitos imaginados pelas pessoas. É possível usá-los com os seguintes objetivos:

• Comunicar

• Categorizar

• Descrever

• Especificar

• Investigar

• Desenvolver

• Analisar

• Imitar

O objetivo é produzir um modelo que se adapte a vários usos, possa ser compreendido por um usuário final e contenha detalhes suficientes para que um desenvolvedor crie um sistema de banco de dados.

(35)

• Crie um diagrama de relacionamento de entidades a partir de narrativas ou especificações comerciais

• Cenário

– "...Atribua um ou mais funcionários a um departamento..."

– "...Alguns departamentos ainda não têm funcionários atribuídos a eles..."

Modelo de Relacionamento de Entidades

EMPLOYEE

#* número

* nome

o cargo

DEPARTMENT

#* número

* nome

o localização atribuído a

composto de

Modelo de Relacionamento de Entidades

Em um sistema eficiente, os dados são divididos em categorias ou entidades distintas. Um modelo de relacionamento de entidades (ER) é uma ilustração de várias entidades em uma empresa e dos relacionamentos entre elas. Um modelo de relacionamento de entidades é derivado de narrativas ou especificações comerciais e é criado durante a fase de análise do ciclo de vida de desenvolvimento do sistema. Os modelos para relacionamento de entidades separam as informações necessárias para uma empresa das atividades desempenhadas dentro dela. Embora as empresas possam alterar suas

atividades, o tipo de informações tende a permanecer constante. Portanto, as estruturas de dados também tendem a ser constantes.

Benefícios do Modelo de Relacionamento de Entidades

• Documenta as informações da organização em formato claro e preciso

• Fornece uma imagem clara do escopo das necessidades de informações

• Fornece um mapa ilustrado facilmente compreendido para o design do banco de dados

• Oferece uma estrutura eficiente para a integração de várias aplicações

(36)

Componentes-chave

• Entidade: Um item importante sobre o qual é necessário obter informações. Os exemplos são departamentos, funcionários e pedidos.

• Atributo: Um item que descreve ou qualifica uma entidade. Por exemplo, para a entidade de funcionários, os atributos são o número, o nome e o cargo do funcionário, além do número do departamento e assim por diante. Cada um desses atributos é necessário ou opcional. Esse estado é chamado opcionalidade.

• Relacionamento: Uma associação nomeada entre entidades que demonstra opcionalidade e grau. Os exemplos são funcionários e departamentos, além de pedidos e itens.

(37)

Convenções de Modelo para Relacionamento de Entidades

Entidade Soft box

Nome exclusivo, individual Letra maiúscula

Sinônimo entre parênteses

Atributo

Nome individual Letra minúscula

Obrigatório marcado com "*"

Opcional marcado com "o"

UID (Identificador Único) Primário marcado com "#"

Secundário marcado com "(#)"

EMPLOYEE

#* número

* nome

o cargo

DEPARTMENT

#* número

* nome

composto de

Entidades

Para representar uma entidade em um modelo, use as seguintes convenções:

• Soft box com qualquer dimensão

• Nome de entidade exclusivo, individual

• Nome de entidade em letras maiúsculas

• Sinônimos opcionais em letras maiúsculas entre parênteses: ( ) Atributos

Para representar um atributo em um modelo, use as seguintes convenções:

• Use nomes singulares em letras minúsculas

• Marque os atributos obrigatórios ou os valores que devem ser conhecidos com um asterisco: *

• Marque os atributos opcionais ou valores que podem ser conhecidos com a letra o Relacionamentos

Símbolo Descrição

Linha tracejada Elemento opcional que indica algo que “pode ser”

Linha contínua Elemento obrigatório que indica algo que “deve ser”

Pé-de-galinha Elemento de classificação que indica “um ou mais”

Linha simples Elemento de classificação que indica “um único”

(38)

Convenções de Modelo para Relacionamento de Entidades

Entidade Soft box

Nome exclusivo, individual Letra maiúscula

Sinônimo entre parênteses

Atributo

Nome individual Letra minúscula

Obrigatório marcado com "*"

Opcional marcado com "o"

UID (Identificador Único) Primário marcado com "#"

Secundário marcado com "(#)"

EMPLOYEE

#* número

* nome

o cargo

DEPARTMENT

#* número

* nome

composto de

Relacionamentos

Cada direção do relacionamento contém:

• Um nome, por exemplo, atribuído

• Uma opcionalidade, que indica algo que deve ser ou pode ser

• Um grau, que indica um único ou um ou mais

Observação: O termo cardinalidade é um sinônimo para o termo grau.

Cada entidade de origem {pode ser | deve ser} um nome de relacionamento {um único | um ou mais}

entidade de destino.

Observação: A convenção deve ser lida em sentido horário.

Identificadores Únicos

Um UID (identificador único) corresponde a qualquer combinação de atributos ou relacionamentos (ou os dois) que serve para diferenciar ocorrências em uma entidade. Cada ocorrência de entidade deve ser identificada com exclusividade.

• Marque cada atributo que faz parte do UID com uma tralha: #

• Marque os UIDs secundários com uma tralha entre parênteses: (#)

(39)

Terminologia de Banco de Dados Relacional

1

2 3 4

5

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7839 KING PRESIDENT 17-NOV-81 5000 10 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7900 JAMES CLERK 7698 03-DEC-81 950 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10

6

Terminologia de Banco de Dados Relacional

Um banco de dados relacional pode conter uma ou várias tabelas. Uma tabela é a estrutura de

armazenamento básica de um RDBMS. Ela armazena todos os dados necessários sobre algo do mundo real, por exemplo, funcionários, NFFs ou clientes.

O slide mostra o conteúdo da relação ou tabela EMP. Os números indicam o seguinte:

1. Uma linha simples ou tupla que representa todos os dados necessários para um funcionário específico. Cada linha de uma tabela deve ser identificada por uma chave primária, que não permite linhas duplicadas. A ordem das linhas não é importante; especifique essa ordem quando os dados forem recuperados.

2. Uma coluna ou atributo que contém o número do funcionário, que é também a chave primária.

O número do funcionário identifica um único funcionário na tabela EMP. Uma chave primária deve conter um valor.

3. Uma coluna que não é um valor de chave. Uma coluna representa um tipo de dados em uma tabela; no exemplo, o cargo de todos os funcionários. A ordem das colunas não é importante durante o armazenamento de dados; especifique essa ordem quando os dados forem

recuperados.

4. Uma coluna que contém o número do departamento, que é também uma chave estrangeira. Uma chave estrangeira é uma coluna que define como as tabelas se relacionam umas com as outras.

Uma chave estrangeira se refere a uma chave primária ou a uma chave exclusiva em outra tabela. No exemplo, DEPTNO identifica com exclusividade um departamento da tabela DEPT.

(40)

Terminologia de Banco de Dados Relacional (continuação)

5. É possível encontrar um campo na interseção entre uma linha e uma coluna. Só pode haver um valor nesse campo.

6. Um campo pode não conter nenhum valor. Nesse caso, o valor é nulo. Na tabela EMP, apenas funcionários com cargo de vendedor têm um valor no campo COMM (comissão).

Observação: Os valores nulos são abordados com mais detalhes nas lições posteriores.

(41)

Relacionando Várias Tabelas

• Cada linha de dados de uma tabela é identificada com exclusividade por uma chave primária (PK).

• Você pode relacionar logicamente dados de várias tabelas usando as chaves

estrangeiras (FK).

Nome da Tabela: EMP Nome da Tabela: DEPT

Chave primária Chave estrangeira Chave primária

EMPNO ENAME JOB DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20

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

Relacionando Várias Tabelas

Cada tabela contém dados que descrevem exatamente uma entidade. Por exemplo, a tabela EMP contém informações sobre funcionários. As categorias de dados são listadas ao longo da parte superior de cada tabela e os casos individuais são listados abaixo da tabela. Usando o formato de tabela, você pode visualizar, entender e usar informações imediatamente.

Como os dados sobre entidades diferentes são armazenados em tabelas diferentes, talvez você precise combinar duas ou mais tabelas para responder a uma pergunta específica. Por exemplo, talvez você queira saber a localização do departamento no qual um funcionário trabalha. Nesse cenário, você precisa de informações da tabela EMP (que contém dados sobre funcionários) e da tabela DEPT (que contém informações sobre departamentos). Um RDBMS permite relacionar os dados de uma tabela aos dados de outra usando as chaves estrangeiras. Uma chave estrangeira é uma coluna ou um conjunto de colunas que se refere a uma chave primária na mesma tabela ou em outra tabela.

O recurso de relacionar dados de uma tabela a dados de outra permite organizar informações em unidades gerenciáveis separadas. É possível manter logicamente os dados dos funcionários separados dos dados dos departamentos armazenando-os em uma tabela separada.