TECNOLOGIA DE BASE DE DADOS

Prévia do material em texto

1 
Base de Dados II 
CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Curso: Ciências da Computação 
vicente@inforalpha.com 
2 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Tecnologia de Bases de Dados 
 
SGBD 
 
Conceitos e Arquiteturas 
3 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
•O que é um SGBD ? 
• Modelos de dados dos SGBDs 
• SGBDs para informação de gestão 
• SGBDs para outras aplicações 
• Arquitectura de SGBDs 
• Componentes de um SGBD 
Sumário 
4 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
• Raghu Ramakrishnan, Database Management Systems 
Elementos de Estudo 
5 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Colecção de dados, integrados, de grandes dimensões. 
Modela uma empresa, organização, “universo” 
 
Entidades (alunos, professores, disciplinas) 
Associações (Professor lecciona Disciplina) 
Base de Dados 
6 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Pacote de software concebido com o objectivo de armazenar 
e gerir bases de dados. 
Sistema de Gestão de Bases de Dados - SGBD 
7 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
• O Sistema de Gestão de Base de Dados é o conjunto de software destinado 
a gerir todo o sistema de armazenamento e manipulação de dados, 
fazendo o interface entre o nível aplicacional e a base de dados 
propriamente dita; 
 
• Esconde do nível aplicacional os detalhes do armazenamento físico dos 
dados e permite às aplicações um elevado grau de abstracção. 
Sistema de Gestão de Bases de Dados - SGBD 
8 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
•Controlo da redundância 
• Restrição de acessos 
• Armazenamento persistente (dados e programas) 
• Inferências 
• Múltiplas interfaces (para tipos diversos de utilizadores) 
• Representação de relações complexas 
• Forçar constrangimentos de integridade 
• Backup e recuperação de faltas 
Para que serve um SGBD? 
9 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
• Auto-descrição da informação (catálogo) 
• Isolamento entre programas e dados (abstracção dos dados) 
• Suporte de vistas múltiplas 
• Partilha de dados e processamento transaccional 
Programação com Ficheiros vs. SGBD 
10 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
• Em geral, numa organização, SIM! 
 
• A médio/longo prazo a não adopção do SGBD tem custos superiores: 
 
– A informação tende a crescer indefinidamente e a sua composição evolui. 
– O número de utilizadores cresce 
– A dependência da organização em relação à disponibilidade da informação 
 é cada vez mais crítica. 
Vale a pena usar um SGBD? 
11 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
• Overhead demasiado elevado: 
 
– overhead do investimento em hw, sw, formação 
– SGBD demasiado geral no tratamento de dados 
– no processamento da segurança, controlo de concorrência, recuperação de 
 faltas e controlo de integridade 
 
• Base de dados/aplicações simples/imutáveis 
• Requisitos de tempo-real incontornáveis 
• Ausência de acesso concorrente 
Quando não usar um SGBD? 
12 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
• Da Computação à Informação 
 – aplicações “científicas” vs. a web 
 
• Dados aumentam em diversidade e volume 
 – Bibliotecas Digitais, vídeo interactivo, genoma humano 
 – o número de aplicações está em explosão! 
 
• SGBD aplica conceitos que cobrem a quase totalidade das bases da informática 
 – Sistemas Operativos, Linguagens, Teoria, IA, Multimédia, Lógica 
Porquê aprender BDs? 
13 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
• Um modelo de dados (data model) é uma colecção de conceitos para 
 descrever dados. 
• Um esquema é uma descrição de uma colecção específica de dados, usando 
 um dado modelo de dados. 
• O modelo de dados relacional é o mais usado presentemente. 
 – Conceito fundamental é a relação, uma tabela com linhas e colunas. 
 – Toda a relação tem um esquema, que descreve quais as colunas ou campos. 
Modelo de Dados? 
O American National Standards Institute (ANSI) através do Standards Planning 
and Requirements Committee (SPARC) estabeleceu um padrão para o 
desenvolvimento de tecnologias de base de dados, definindo uma arquitectura 
de 3 níveis independentes: Interno, Conceptual e Externo 
14 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
• Modelo Conceptual (externo) 
• Modelo Lógico (conceptual) 
• Modelo Interno (físico) 
 
 (físico - estruturas de dados armazenadas em disco) 
Modelos de um Sistema de Informação 
15 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Arquitectura ANSI/SPARC 
Dados 
Interno 
Conceptual 
Externo 
SGBD 
Aplicações 
16 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Nível Interno 
• Refere-se ao armazenamento físico dos dados, organização de ficheiros, métodos de acesso e 
organização das estruturas físicas; 
• Deve ser organizado para permitir um melhor desempenho nas operações que 
previsivelmente se realizem com maior frequência 
Nível Conceptual 
• É também designado por esquema conceptual; 
• Refere-se ao modelo conceptual dos dados, independente dos utilizadores e das aplicações; 
• Constitui a estrutura da base de dados; 
• É o nível que permite esconder os detalhes do armazenamento físico dos dados, do nível 
aplicacional 
Nível Externo 
• Refere-se à independência programa/dados; 
• Como cada utilizador não necessita de trabalhar com a totalidade do esquema conceptual, o 
SGBD permite definir para cada um, uma view, que determina a janela de dados com que 
necessita de trabalhar; Este conceito aplica-se também às aplicações 
Arquitectura ANSI/SPARC 
17 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Arquitetura ANSI/SPARC 
18 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
SGBD 
em Detalhe 
19 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
SGBD em Detalhe 
20 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Meios de Armazenamento 
21 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Meios de Armazenamento 
22 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Meios de Armazenamento 
23 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Meios de Armazenamento 
24 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Meios de Armazenamento 
25 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Meios de Armazenamento 
26 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Utilizadores do SGBD 
27 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Data Base Administrator - DBA 
28 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Data Base Administrator - DBA 
29 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Data Base Administrator - DBA 
30 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Data Base Administrator - DBA 
31 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Aplicação (Desenvolvida no SGBD) 
32CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Aplicação (Desenvolvida no SGBD) 
33 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Utilizador ad hoc 
34 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Utilizador ad hoc 
35 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Programador 
36 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Programador 
37 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Oracle, Progress, DB2, MySQL, 
PostgreSQL, Interbase, SQL Server. 
Paradox*, Access*, dbase* 
* Desktop Database Management Systems 
Exemplos: 
38 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
• 1ª Geração: 
 – Modelo Hierárquico, Modelo Rede 
• 2ª Geração: 
 – Modelo Relacional 
• 3ª Geração: 
 – Modelos Centrados em Objectos 
 – Modelo Relacional por Objectos 
39 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Transações 
• SGBDs são em geral multi-user 
 – processam simultaneamente operações disparadas por 
 vários utilizadores 
 • deseja-se alta disponibilidade e tempo de resposta pequeno 
 
 – execução intercalada de conjuntos de operações 
 • exemplo: enquanto um processo i faz I/O, outro processo j é selecionado 
 para execução 
• SGBD 
 – sistema de processamento de operações de acesso ao BD 
• Surge assim o conceito de transacção 
 pois todas as tarefas de gestão da base de dados vivem à custa deste conceito 
40 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Transação 
• Unidade lógica de processamento em um SGBD 
 
• Composta de uma ou mais operações 
 – seus limites podem ser determinados em SQL 
 
• De forma abstrata e simplificada, uma transação pode 
 ser encarada como um conjunto de operações de 
 leitura e escrita de dados 
41 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
• Atomicidade - as operações que constituem uma 
transacção formam um conjunto indivisível (atómico): 
– Ou terminam com sucesso - COMMIT 
– Ou são todas desfeitas – ROLLBACK 
 
• Integridade - caso envolva actualização de dados, 
uma transacção deverá levar a Base de Dados de um 
estado de integridade para outro estado de 
integridade 
Conjunto bem definido de operações sobre a Base de 
Dados, com as seguintes características: 
Propriedades de umaTransação 
42 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Cod_Func = 1234 
Nome = Luis Mota 
Salário = 120 
Cod_Func = 1234 
Nome = Luis Mota 
Salário = 130 
Mudança de Estado Sucedida 
Cod_Func = 1234 
Nome = Luis Mota 
Salário = 120 
Cod_Func = 1234 
Nome = Luis Mota 
Salário = 110 
Mudança de Estado não Sucedida 
Propriedades de umaTransação 
43 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
• Isolamento - se várias transacções ocorrerem 
em simultâneo, não devem as mesmas interferir 
entre si, tendo cada uma a ilusão de ser a única 
a ser executada; 
• Persistência - todos os efeitos de uma transacão 
bem sucedida tornam-se persistentes e visíveis 
para as outras transacções 
Propriedades de umaTransação 
44 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
• Uma transação é sempre monitorada pelo 
SGBD quanto ao seu estado 
– que operações já fez? concluiu suas 
operações? deve abortar? 
• Estados de uma transação 
– Ativa, Em processo de efetivação, Efetivada, 
Em processo de aborto, Concluída 
 
Respeita um Grafo de Transição de Estados 
Estados de uma Transação 
45 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Transição de Estados de uma 
Transação 
46 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Transição de Estados de uma 
Transação 
47 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Transição de Estados de uma 
Transação 
48 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Transição de Estados de uma 
Transação 
49 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Transição de Estados de uma 
Transação 
50 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Transição de Estados de uma 
Transação 
51 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Flat Transaction (tudo ou nada) 
 
Mais simples e mais comuns, consistem em delimitar o conjunto de operações que as 
constituem (via nível aplicacional) Ex: 
 
BEGIN TRANSATION 
 …. 
 <OPERAÇÕES SOBRE A BD> 
 … 
END TRANSACTION 
 /* Faz o COMMIT */ 
 
 
BEGIN TRANSACTION 
 Para cada cliente 
 Ler TOTAL_COMPRAS 
 Ler PREMIOS 
 PREMIOS=PREMIOS+0,1 *TOT_COMPRAS 
 Escrever PREMIOS 
END TRANSACTION 
Tipos de Transação 
52 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
SAVEPOINTS TRANSACTION 
 
BEGIN TRANSACTION 
 … 
 <OPERAÇÕES SOBRE A BD> 
 … 
 SAVEPOINT <X> 
 … 
 <OPERAÇÕES SOBRE A BD> 
 … 
 SAVEPOINT <Y> 
 … 
 <OPERAÇÕES SOBRE A BD> 
 … 
END TRANSACTION /* FAZ COMMIT*/ 
 
Torna-se assim mais fácil 
reiniciar (rollforward) ou 
desfazer (rollback) a partir de 
um SAVEPOINT 
Tipos de Transação 
53 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
CHAINED TRANSACTIONS 
 
BEGIN TRANSACTION 
 … 
 <OPERAÇÕES SOBRE A BD> 
 … 
 COMMITPOINT 
 … 
 <OPERAÇÕES SOBRE A BD> 
 … 
 COMMITPOINT 
 … 
 <OPERAÇÕES SOBRE A BD> 
 … 
END TRANSACTION /* FAZ COMMIT*/ 
Torna-se mais fácil reiniciar 
(rollforward) ou desfazer 
(rollback) a partir de um 
COMMITPOINT, reduzindo-se a 
amplitude das operações de 
rollforwars e rollback. 
Tipos de Transação 
54 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
NESTED TRANSACTIONS 
 
• São as mais flexíveis e definem uma hierarquia de 
subtransacções, que podem ser flat ou nested transactions; 
• Cada subtransacção pode desfazer-se ou finalizar; 
• O commit de cada subtransacção só é efectivo após a 
transacção-pai ter finalizado; 
• Os resultados de uma subtransacção só são visíveis para a 
sua transacção-pai; 
• Quando uma subtransacção é desfeita, todas as suas 
subtransacções são desfeitas também. 
Tipos de Transação 
55 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
BEGIN TRANSACTION 
 
… 
 
START SUBTRANSACTION 
 
… 
 
STARTSUBTRANSACTION 
 
… 
 
STARTSUBTRANSACTION 
 
… 
 
END TRANSACTION 
BEGIN TRANSACTION 
… 
END TRANSACTION 
BEGIN TRANSACTION 
… 
END TRANSACTION 
BEGIN TRANSACTION 
… 
END TRANSACTION 
Esquema 
representativo de 
NESTED 
TRANSACTIONS 
BEGIN TRANSACTION 
… 
START SUBTRANSACTION 
… 
STARTSUBTRANSACTION 
… 
END TRANSACTION 
BEGIN TRANSACTION 
… 
START SUBTRANSACTION 
… 
END TRANSACTION 
BEGIN TRANSACTION 
… 
END TRANSACTION 
56 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Linguagens de Bases de Dados 
A interacção do nível aplicacional com o SGBD faz-se à custa de linguagens específicas. 
 
Basicamente, podem-se identificar dois tipos de linguagens: 
Data Definition Language – DDL 
Data Manipulation Language – DML 
57 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
 Data Manipulation Language–DML (Linguagens de Manipulação de Dados) 
 
 Linguagem disponibilizadaao nível aplicacional para a manipulação dos 
 dados. Operações possíveis: 
 inserção, remoção, alteração e consulta de dados. 
 A DML é vulgarmente, confundida com as linguagens de interrogação (query languages). 
 Data Definition Language – DDL (Linguagens de Definição de Dados) 
 
 Não é uma linguagem de programação pois não possui qualquer instrução 
 específica de processamento. 
 
 É uma notação para descrever a estrutura dos dados a armazenar 
 ⇒ é uma linguagem de definição dos metadados. 
 O resultado da execução das instruções LDD é armazenado no dicionário de 
 dados. 
Linguagens de Bases de Dados 
58 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
SQL - Revisão 
59 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
é um conjunto de instruções com as quais os programas e utilizadores acedem as 
informações das bases de dados relacionais, como é o caso do Oracle. 
 
 As aplicações permitem aos usuários manipulação nos dados sem o efetivo uso 
de instruções SQL, no entanto essas aplicações seguramente usam SQL para 
executar as requisições dos usuários. 
Structured Query Language (SQL) 
60 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Para extrairmos e consultarmos dados das tabelas de um banco relacional, usamos a 
instrução SELECT, que faz uma pesquisa nas estruturas relacionais da base de dados e 
retorna valores na forma de linhas e colunas. Uma consulta SQL pode também ser 
nominada simplesmente como query. 
Implementando consultas SQL simples 
SELECT 
 
 {*|coluna1 [apelido], coluna2 [apelido]} 
 
FROM tabela 
Construção Básica 
Diretrizes: 
• SQL não fazem distinção entre maiúsculas e minúsculas; 
• SQL podem estar em uma ou mais linhas; 
• As palavras reservadas não podem ser abreviadas; 
• Normalmente as clausulas são colocadas em linhas diferentes (boa prática); 
• Guias e identações são usadas para aumentar a legibilidade. 
61 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Quanto às colunas temos duas alternativas a primeira é selecionar todas as colunas em 
uma única consulta SQL, a outra mais elegante é selecionar apenas as colunas 
interessantes, cada coluna pode ter um alias associado. Alias são muito úteis para 
cálculos matemáticos. 
 
 
 
 
Colunas 
62 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Expressões Aritméticas (Operadores aritméticos) 
É possível criar expressões com colunas do tipo NUMBER e DATE usando 
operadores aritméticos, na seguinte procedência ( *, /, +, - ). 
 
 
 
Para adicionarmos 1(um) dia ao valor de um coluna do tipo DATE devemos 
utilizar o operador + da seguinte forma valor_data+1, e para adicionar uma hora a 
seguinte sintaxe valor_data+(1/24) 
 
 
Obs.: Um valor nulo não é o mesmo que zero ou um espaço em branco. **QUALQUER VALOR OPERACIONADO COM NULO 
RESULTA NULO**. Este problema é remediado pela função NVL 
63 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Operador de Concatenação 
No Oracle o operador || executa contatenação de dois strings de caracteres, e poderá 
ser usado nas cláusulas SELECT, WHER e ORDER BY. Utilizada na cláusula SELECT de uma 
consulta SQL, resultará em um campo do tipo caracter. 
 
 
 
64 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Restringindo e ordenando dados 
É possível restringir as linhas retornadas da consulta SQL utilizando a cláusula WHERE. 
As linhas da tabela só estarão no retorno da consulta SQL se a condição da cláusula 
WHERE aplicada à linha for atendida com sucesso. A cláusula WHERE é seguida de 
uma expressão condicional. O Oracle aplica condição às linhas da(s) tabela(s) de 
cláusula FROM. Cada aplicação de linha gera um valor boolean. As linhas que geram 
valores TRUE formarão o dataset de retorno 
 
 
 
Cláusula WHERE 
65 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Operadores de comparação comparam dois valores ou expressões e 
retornando um resultado de valor Boolean. A tabela ilustra os operadores de 
comparação mais recorrentes. Geralmente os operadores são usados na cláusula 
WHERE. 
 
 
 
Operadores de comparação 
66 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Operadores lógicos são usados para combinar ou alterar o resultado de uma ou mais 
comparações . O produto desta operação será um valor booleano e no escopo deste 
curso será utilizado para determinar quais linhas estarão no resultado da consulta. 
 
 
 
Operadores Lógicos 
67 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Múltiplos operadores podem formar uma expressão válida. Os operadores com maior 
precedência serão avaliados antes dos operadores de menor precedência, seguindo a 
tabela 
 
 
 
Precedência 
68 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
A ordem das linhas retornadas em um resultado de consulta é indefinida. 
A cláusula ORDER BY pode ser utilizada para classificar as linhas. A cláusula ORDER 
BY deve ser colocada após a cláusula WHERE. Por definição o resultado será exibido 
de forma crescente e através da palavra-chave DESC a ordem é invertida. O 
resultado pode também ser ordenado por várias colunas 
 
 
Order By 
69 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Funções de uma linha 
Em instruções SQL as funções de uma linha são usadas principalmente para manipular 
os valores que serão apresentados. Estas aceitam um ou mais argumentos e retornam 
um ou único valor para cada linha do dataset gerado pela consulta. Um argumento 
pode ser um dos seguintes itens, constantes, valor variável, nome de coluna, expressão. 
70 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Funções de Caracter 
Quanto aos recursos de funções de uma linha 
• Actua em cada linha retornada da consulta. 
• Retorna um resultado por linha. 
• Podem receber zero, um ou mais argumentos. 
• Podem ser usados em cláusulas SELECT, WHERE, ORDER BY 
71 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Obs.: Funções de Linha podem ser usadas em instruções DML´s 
Funções de Caracter 
72 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Funções Numéricas 
Tabela Dual 
Em todas as bases de dados Oracle existe uma 
tabela chamada DUAL, aparentemente 
irrelevante. No entento, ela é útil quando se 
deseja retornar um valor pontual, sendo 
principalmente usada para a execução de 
instruções SQL que não necessitam de tabela 
base. 
73 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Funções de Data 
Para manipular valores do tipo DATE em um formato diferente do padrão 
estabelecido pelo ambiente de trabalho, se faz necessário o uso da função 
TO_CHAR com os elementos de Format Model corretos. 
 
Para o nosso curso será utilizado o Format Model ‘DD-MON-YYYY’ 
74 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Funções de Conversão 
TO_CHAR(X*,’format_model’+) 
Onde X é um valor do tipo DATE ou NUMBER e ‘format_model’ é uma 
string que descreve o formato de como o argumento X será apresentado. Existem 
formatações específicas para o tipo DATE e outras para o tipo NUMBER. 
75 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Funções de Conversão 
76 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
TO_DATE(‘string’,’formatação’) 
Onde ‘string’ é a informação que se deseja transformar paravalor do tipo 
DATE e ’formatação’ é o FORMAT MODEL que indica com como o Oracle deverá 
reconhecer a string apresentada no primeiro parâmetro 
Funções de Conversão 
77 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Mostrando dados de várias tabelas 
Existem momentos em que faz necessário o uso de dados a partir de mais de uma 
tabela, neste caso usaremos condições especiais chamadas de JOIN’s. As linhas de uma 
tabela podem ser relacionadas às linhas de outra tabela de acordo com os valores 
comuns existentes nas colunas correspondentes, que em geral são colunas de chave 
primária e estrangeira. 
 
Diretrizes para Joins: 
• Ao se escrever uma instrução SELECT que combine mais de uma tabela, é interessante deixar 
claro a que tabela o campo pertence, posicionando o nome da tabela antes do nome do campo. 
• Não é obrigatório o posicionamento o nome da tabela antes do nome do campo, porém, se 
uma mesma coluna pertence a mais de uma tabela, deve-se prefixar a coluna com o nome da 
tabela. 
• Para combinar n tabelas se fez necessário no mínimo n-1 condições de JOIN 
78 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
É possível também adicionar condições de filtros às condições de JOIN para restringe 
mais ainda as linhas obtidas. No exemplo abaixo, serão apresentados o nome e o 
departamento do funcionário “Matos”. 
79 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Quando um JOIN for completamente inválido ou omitido, o resultado da consulta 
SQL será um produto cartesiano no qual serão exibidas todas combinações de linhas 
de todas as tabelas envolvidas na consulta. O Produto cartesiano tende a gerar um 
grande número de linhas e seu resultado raramente é útil. Apresentamos o produto 
cartesiano aqui por finalidades didáticas. 
 
 
Produto Cartesiano 
80 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Alias de Tabela 
Para qualificar as colunas é possível utilizar alias de tabela ao invés do nome da 
tabela. Assim como os alias de coluna dão outro nome à coluna, os alias de tabela 
tem a mesma função. Os alias de tabela são definidos na cláusula FROM. O nome da 
tabela é especificado totalmente seguido do seu alias. 
 
 
Na instrução SQL do exemplo 5.4 foram definidos os alias “e” para a tabela 
employees e alias “d” para a tabela departments. 
81 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Diretrizes para Joins: 
• Os alias de tabela não ultrapassam 30 posições; 
• Um alias de tabela poderá substituir o nome da tabela em todas as cláusulas do SQL. 
• Os alias devem ser sugestivos. Não utilizem algo com T1, T2, T3,... 
• Palavras reservadas não podem ser utilizadas como alias nenhum. Algo como DESC alusivo a 
descrição 
82 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Funções de grupo 
De modo diferente das funções de uma única linha, as funções de grupo operam em 
conjunto de linhas para fornecer um resultado por grupo. Esses conjuntos podem ser 
uma tabela inteira ou a mesma dividida em grupos menores. 
83 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
84 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
É através da cláusula GROUP BY que dividimos as linhas de uma tabela em grupo 
menores. Em seguida, poderá ser aplicado a esses grupos formados as funções de grupo, 
gerando assim informações sumárias para cada grupo. Primeiramente deve ser 
determinada a identificação do grupo. A identificação do grupo pode ser uma coluna, 
várias colunas, uma expressão usando colunas ou várias expressões usando colunas. O 
Oracle considerará no grupo todas as linhas que atenderem a cláusula WHERE caso esta 
exista, e então será aplicada a função de grupo ao grupo caso exista. 
 
 
Diretrizes para Joins: 
Usando a cláusula WHERE, linhas serão eliminadas antes de serem organizadas em grupo. 
Não é permitido o uso de alias na cláusula GROUP BY . 
Quando se deseja um campo esteja no retorno do SQL este deverá estar na cláusula GROUP BY. 
É possível criar agrupamentos de mais de um campo. 
Funções de grupo não devem ser utilizadas na cláusula WHERE e sim na cláusula HAVING. 
85 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Para se excluir um grupo inteiro criado pela cláusula GROUP BY, deveremos usar a 
cláusula HAVING, que executa um trabalho parecido com a cláusula WHERE que 
elimina as linhas, este, no entanto, elimina grupos. 
86 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Subconsultas 
Uma subconsulta é uma instrução SELECT incorporada a outra instrução SELECT. O uso 
de subconsultas torna possível a construção de sofisticadas instruções e são úteis 
quando precisamos selecionar linhas de uma tabela com uma condição que dependa 
dos dados na própria tabela. Também podem ser chamadas de subqueries ou 
consulta interna. 
87 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
88 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Operador IN 
Operador ANY 
Operador ALL 
89 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Manipulando dados (DML) 
INSERT 
90 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
A instrução INSERT serve para adicionar linhas em uma determinada tabela. Como 
você pode observar a lista de campos da tabela não é obrigatória, no entanto, se 
você optar por supri-la deverá ter em mente a sua estrutura da tabela, pois deverá 
fornecê-los na mesma ordem. 
 
 
 
91 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
UPDATE 
92 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
A instrução UPDATE altera valores de campos de uma tabela, de acordo com uma 
condição fornecida, se esta condição for suprida, toda a tabela será actualizada. As 
regras que governam a restrição de linhas nas consultas são também aplicáveis nas 
instruções UPDATE 
 
 
93 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
DELETE 
94 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
A instrução DELETE exclui um ou mais registros de acordo com a condição fornecida, 
similarmente ao UPDATE se esta condição for suprida todos os dados de sua tabela 
serão apagados!!!. As regras que governam a restrição de linhas nas consultas são 
também aplicáveis nas instruções DELETE 
 
 
 
95 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Controle de transação 
O conjunto de inserções, alterações e exclusões efetivadas pelas instruções SQL 
pertencentes a uma transação pode ser confirmadas (COMMIT) ou desconsideradas 
(ROLLBACK). Uma transação se inicia com o primeiro comando DML executado. A 
transação termina quando é confirmada ou desconsiderada. 
96 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
savepoint 
97 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
• Estudo de Caso: Supermercado Simples 
• SQL Básico: DDL e DML 
• Comandos Especiais 
98 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Supermercado 
Cod_Pessoa (PK) 
Nome 
Endereco 
Fone 
Tipo 
Cod_Pessoa (PK e FK) 
Cod_Cargo (FK) 
Salario 
Cod_Venda (PK e FK) 
Cod_Prod (FK) 
Qtd 
Sub_Total 
Pessoa Funcionario 
Cod_Cargo (PK) 
Descricao 
Cargo 
Cod_Produto (PK) 
Descricao 
Preco 
Produto Cod_Venda (PK) 
Cod_Cliente (FK) 
Data 
Total 
Venda VendaxProd 
Cod_Pessoa(PK e FK) 
Pontos 
Cliente 
99 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
DDL (Data Definition Language) 
• Comando Create 
 
Create Table <nome> ( 
 <campo> <tipo>(<tamanho>) [NOT NULL], 
 ... 
 [CONSTRAINT "PK_<tableName>“] primary key (<campo1>,[<campo2>,...,<campon>]), 
 [CONSTRAINT “FK_<tableName>“][foreign key] (<campo>) REFERENCES <table>(campo) 
); 
 
 
 
100 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
DDL 
• Exemplo: 
Create table pessoa ( 
 cod_pessoa number(5) NOT NULL, 
 nome varchar2(150) NOT NULL, 
 endereco varchar2(150) , 
 fone varchar2(11) , 
 tipo char(1) NOT NULL, 
 CONSTRAINT “PK_PESSOA” primary key (cod_pessoa) 
); 
101 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
DDL 
• Exemplo: 
Create table funcionario ( 
 cod_pessoa number(5) NOT NULL, 
 cod_cargo number(5) NOT NULL, 
 salario number(5,2) , 
 CONSTRAINT “PK_FUNC” primary key (cod_pessoa), 
 CONSTRAINT “FK_PESSOA” foreign key (cod_pessoa) 
references pessoa(cod_pessoa), 
 CONSTRAINT “FK_CARGO” foreign key (cod_cargo) 
references cargo(cod_cargo) 
); 
102 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
DDL 
• Comando Alter 
– Modificando uma coluna 
 
Alter Table <nome> MODIFY ( 
 <campo> <tipo>(<tamanho>) [NOT NULL] 
); 
 
• Ex: 
Alter Table pessoa MODIFY ( 
 nome varchar2(200) NOT NULL 
); 
 
103 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
DDL 
• Comando Alter 
– Adicionando uma coluna 
 
Alter table <nome> add ( 
 <campo> <tipo>(<tamanho>) [NOT NULL] 
); 
 
• Ex: 
Alter table funcionario add ( 
 data date NOT NULL 
); 
 
 
104 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
DDL 
• Comando Alter 
– Eliminando uma coluna 
 
Alter Table <nome> DROP ( 
 <campo> 
); 
 
• Ex: 
Alter Table funcionario DROP ( 
 data 
); 
 
 
 
105 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
DDL 
• Comando Drop 
– Excluindo uma tabela 
 
DROP TABLE [<esquema>.]<tabela> [CASCADE CONSTRAINTS]; 
 
• Ex: 
 DROP TABLE pessoa [CASCADE CONSTRAINTS]; 
 
 
106 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
DDL 
• Visões 
– Tabelas virtuais que não ocupam espaço físico 
– create view <nome> [<atributos>] as select 
<consultas> 
 
/*Criar uma visão dos funcionários que ganham mais de 
1000 USD */ 
 
Create view func_1000 as select * from funcionario 
where salario > 1000 
107 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
DML (Data Manipulation Language) 
• Comando Insert 
 
Insert into [<esquema.>]<tabela> [(campo1,...,campon)] 
values (valor1,...,valorn); 
 
Ex.: 
Insert into cargo (cod_cargo,descricao) values (1,‟Caixa‟); 
 
Insert into pessoa (cod_pessoa,nome,endereco,fone,tipo) 
values (1,‟Bruno‟,‟meu_endereco‟,‟99999999‟,‟F‟); 
 
Insert into funcionario (cod_pessoa,cod_cargo,salario) 
values (1,1,‟250.33‟); 
 
 
 
 
108 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
DML 
• Comando Update 
 
update [<esquema.>]<tabela> set campo1 = 
valor1,...,campon = valorn WHERE condição 
 
Ex.: 
Update pessoa set endereco = „novo_endereco‟, fone = 
null 
where cod_pessoa = 1; 
 
Update funcionario set salario = „650.60‟ where 
cod_pessoa = 1 
 
109 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
DML 
• Comando Delete 
 
Delete from [<esquema.>]<tabela> WHERE 
condição 
 
Ex.: 
Delete from funcionario where cod_pessoa = 1; 
 
Delete from pessoa where cod_pessoa = 1; 
 
 
110 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
DML 
• Comando Select 
 
Select {* | <campo1,...,campon>} from <tabela> [where condição] 
Ex.: 
/* Listando todos os atributos de todas as pessoas */ 
Select * from pessoa; 
 
/* Listando nome e endereco de todas as pessoas */ 
Select nome,endereco from pessoa; 
 
/* Listando nome e cargo de todos os funcionários */ 
Select pessoa.nome, cargo.descricao from pessoa, cargo, 
funcionario where pessoa.cod_pessoa = funcionario.cod_pessoa 
and funcionario.cod_cargo=cargo.cod_cargo; 
 
 
 
111 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
DML 
• Comando Select 
– Consultas encadeadas 
/* Listar o cliente que possui mais pontos*/ 
Select pessoa.nome 
from pessoa, cliente 
where cliente.pontos = (select MAX(cliente.pontos) 
from cliente) and pessoa.cod_pessoa = 
cliente.cod_pessoa; 
 
 
 
 
112 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
DML 
• Comando Select 
– Cláusula Distinct 
/* Listando todos os cargos que possuem ao menos um 
funcionário*/ 
Select distinct cargo.descricao from funcionario, 
cargo where funcionario.cod_cargo = cargo.cod_cargo; 
 
 
 
 
113 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Comandos Especiais 
• DESCRIBE: Exibe a estrutura de uma tabela. 
Ex.: DESC <tabela>; 
 
• COMMIT: Grava uma transação no banco de dados. 
Ex.: COMMIT; 
 
• ROLLBACK: Recupera o banco de dados para a última posição que estava 
após o último comando commit ser executado. 
Ex.: ROLLBACK; 
114 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Exercícios Propostos 
• Terminar a criação do restante das tabelas 
• Selecionar o cliente que mais gastou ontem 
• Selecionar o cargo e o salário do funcionário que recebe menos 
• Selecionar quantos clientes com mais de 200 pontos gastaram no supermercado, hoje, 
mais de 500 USD 
• Listando os cargos e a quantidade de funcionários em cada cargo agrupados por cargo 
• Listando os cargos e a quantidade de funcionários em cada cargo agrupados por cargo, 
porém só para aqueles cargos que possuem mais de dois funcionários atrelados a ele. 
• Listando o nome dos clientes em ordem alfabética 
• Listando os salários em ordem decrescente. 
• Mostrar o valor do maior salário dos funcionários 
• Mostrar qual o a média de pontos dos clientes. 
• Mostrar quantos clientes possuem mais de 1000 pontos 
 
 
 
115 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
• Terminar a criação do restante das tabelas 
• Selecionar o cliente que mais gastou ontem 
• Selecionar o cargo e o salário do funcionário 
que recebe menos 
• Selecionar quantos clientes com mais de 200 
pontos gastaram no supermercado, hoje, mais 
de 500 USD 
• Listando os cargos e a quantidade de 
funcionários em cada cargo agrupados por 
cargo 
• Listando os cargos e a quantidade de 
funcionários em cada cargo agrupados por 
cargo, porém só para aqueles cargos que 
possuem mais de dois funcionários atrelados 
a ele. 
• Listando o nome dos clientes em ordem 
alfabética 
• Listando os salários em ordem decrescente. 
• Mostrar o valor do maior salário dos 
funcionários 
• Mostrar qual o a média de pontos dos 
clientes. 
• Mostrar quantos clientes possuem mais de 
1000 pontos 
 
Cod_Pessoa (PK) 
Nome 
Endereco 
Fone 
Tipo 
Cod_Pessoa (PK e FK) 
Cod_Cargo (FK) 
Salario 
Cod_Venda (PK e FK) 
Cod_Prod (FK) 
Qtd 
Sub_Total 
Pessoa 
Funcionario 
Cod_Cargo (PK) 
DescricaoCargo 
Cod_Produto (PK) 
Descricao 
Preco 
Produto 
Cod_Venda (PK) 
Cod_Cliente (FK) 
Data 
Total 
Venda VendaxProd 
Cod_Pessoa (PK e FK) 
Pontos 
Cliente 
116 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
PL/SQL 
DECLARE 
 -- declarações 
BEGIN 
 -- instruções 
END; 
117 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Programar em Oracle 
Com PL/SQL 
Procedural Language Extension to SQL 
118 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
PL/SQL(Procedural Language/SQL) é a linguagem procedural desenvolvida pela 
Oracle que é utilizada para montar os blocos PL/SQL. 
 
Um bloco PL/SQL consiste de um conjunto de instruções SQL (SELECT, INSERT, 
UPDATE, DELETE) ou comandos PL/SQL, e desempenha uma função lógica única, 
afim de resolver um problema específico ou executar um conjunto de tarefas 
afins. 
 
O Bloco PL/SQL também pode ser referenciado com Unidade de Programa 
PL/SQL 
 
Os blocos PL/SQL são qualificados em bloco anônimo e Stored Procedure 
119 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
O bloco anônimo 
• Não tem nome 
• Não está armazenado no SGDB 
• Geralmente está armazenada na aplicação. 
Stored SubProgramas 
• Utiliza a estrutura do bloco anônimo com base. 
• Estão armazenados no SGDB, 
• A eles é atribuído um nome que poderá ser utilizado nas aplicações ou 
por outros objetos do banco de dados 
blocos PL/SQL 
120 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
A estrutura de um bloco PL/SQL é constituida de três seções: 
 
a) SEÇÃO DE DECLAÇÃO (DECLARE) - Nesta seção são definidos os objetos 
PL/SQL como variáveis, constantes, cursores e exceções definidas pelo usuário 
que poderão ser utilizadas dentro do bloco. 
 
b) SEÇÃO DE EXECUÇÕES (BEGIN..END;) - Nesta seção contemplará a sequência 
de comandos PL/SQL e instruções SQL do bloco. 
 
c) SEÇÃO DE TRATAMENTO DE ERRO (EXCEPTION) - Nesta seção serão tratados 
os erros definidos e levantados pelo próprio bloco e os erros gerados pela 
execução do bloco . 
estrutura de um bloco PL/SQL 
121 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
DECLARE 
 -- declarações 
 
BEGIN 
-- instruções e comnados 
 
EXCEPTION 
-- tratamentos de erro 
 
END; 
122 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Diretrizes: 
 
• Apenas a secção de execução é obrigatória. 
• As palavras chaves, DECLARE, BEGIN, EXCEOPTION não são seguidas por 
ponto-evírgula, mas END e todas as outras instruções PL/SQL requerem 
ponto-e-vírgula. 
• Não existe bloco sem algum comando válido. 
• Pode existir aninhamento de bloco, no entanto, esta funcionalidade é restrita 
à secção de Execução e à SeCção de Tratamento de Erro. 
• As lnhas da seção de execução devem ser finalizadas com ; (ponto-e-vígula) 
123 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Os comentários em PL/SQL são de dois tipos 
a) Uma Linha: utiliza-se o delimitador --. A partir de dois hífens tudo o que for 
escrito até o final da linha é considerado comentário. 
 
b) Múltiplas linhas: utiliza-se o delimitador /* para abrir e */ para fechar. 
Tudo e todas as linhas que estiverem entre os dois delimitadores serão 
ignorados na execuçãor. 
Comentários 
1 BEGIN 
2 -- comentando apenas uma linha 
3 COMANDO1; 
4 /* comentando 
5 várias 
6 linhas */ 
7 COMANDO2; 
8 COMANDO3; -- o resto será ignorado 
9 END; 
10 / 
124 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Para utilizar variáveis e constantes no seu programa, você deve 
declará-las anteriormente. É na seção DECLARE que são declaradas 
as variáveis e constantes. 
Declarações (seção de declaração) 
DECLARE 
 nVIVenda NUMBER(16,2); 
 cNmVendedor VARCHAR2(40); 
 dDtVenda DATE:=SYSDATE; 
 mMultiplic CONSTANT NUMBER:=100; --constante 
 BEGIN 
 7 NULL; 
END; 
 / 
125 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Tipos de dados 
126 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Tipos de dados e declaração de variáveis 
127 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
O comando %TYPE dá-nos a possibilidade de associarmos ao tipo de uma 
variável o tipo de uma coluna de uma tabela, desta forma, automaticamente a 
variável assumirá o tipo de dado da coluna. 
 
O comando %ROWTYPE criará uma estrutura de registro idêntica à estrutura 
de uma tabela. 
Tipos de dados e declaração de variáveis 
DECLARE 
 Nome_variável nome_tabela.nome_coluna%TYPE; -- variável 
 Nome_registro nome_tabela%ROWTYPE; -- registro 
BEGIN 
 -- instruções e comnados 
END; 
128 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
%TYPE e %ROWTYPE facilitam a manutenção do código 
129 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Na programação PL/SQL não existe nenhuma funcionalidade de entrada 
ou saída. Para remediar isso, usaremos no aplicativo SQL*Plus o 
Supplied Package DBMS_OUTPUT que fornecerá apenas a capacidade 
de dar saídas para mensagens na tela. Isso é feito por meio de dois 
passos: 
I/O - Pacote DBMS_OUTPUT 
1.Permitir a saída no SQL*Plus com o comando set serveroutput 
 SET SERVEROUTPUT {ON | OFF} 
2.Dentro do programa PL/SQL, utilize o procedure 
DBMS_OUTPUT.PUT_LINE. Essa procedure adicionará o 
argumento informado ao buffer de saída. 
130 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Com esses passos completos, a saída é impressa na tela do SQL*Plus depois 
do bloco ser completamente executado. Durante a execução, o buffer é 
preenchido pelas chamadas de DBMS_OUTPUT.PUT_LINE. 
 
O SQL*Plus não recupera o conteúdo do buffer e não o imprime até que o 
controle retorne para o SQL*Plus, depois que o bloco terminou a execução. 
 
 
SQL> SET SERVEROUTPUT ON 
SQL> BEGIN 
 2 DBMS_OUTPUT.PUT_LINE(‘Olá mundo PL/SQL’); 
 3 END; 
4 / 
Olá mundo PL/SQL 
PL/SQL procedure successfully completed. 
SQL> 
 
131 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Assinalar Valores 
Pode-se assinalar valores a uma variável de duas formas. A primeira forma utiliza o 
operador := (sinal de dois pontos seguido do sinal de igual). Assim a variável 
posicionada à esquerda do operador receberá o valor da expressão posicionada à 
direita. 
1 DECLARE 
2 nSalario NUMBER; 
3 nSalarioAtual NUMBER; 
4 aRegionName regions.region_name%TYPE; 
5 dHoje DATE; 
6 nAnoBi BOOLEAN:=TRUE; 
7 BEGIN 
8 nSalario := 400; 
9 nSalarioAtual := F_SALARY(103) * 0.10; 
10 aRegionName := 'ASIA'; 
11 dHoje := SYSDATE; 
12 END; 
13 / 
132 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
A Segunda forma de assinalar valor a uma variável é através de um resultado de 
SELECT que será transferido assinalado à variável. 
Um SELECT que assinala valor a uma variável obrigatoriamente deverá retornar uma 
e somente uma linha, caso contrário, um erro de execução será disparado, 
NO_DATA_FOUND se não for retornada nenhuma linha e TOO_MANY_ROWS se 
mais de uma linha for retornada 
Assinalar Valores 
133 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
1 DECLARE 
2 bonus10 NUMBER; 
3 bonus20 NUMBER; 
4 emp_id NUMBER:=206; 
5 BEGIN 
6 SELECT salary * 0.10 
7 INTO bonus10 
8 FROM employees 
9 WHERE employee_id = emp_id; 
10 
11 SELECT salary * 0.10, salary * 0.20 
12 INTO bonus10, bonus20 
13 FROM employees 
14 WHERE employee_id =emp_id; 
15 
16 DBMS_OUTPUT.PUT_LINE('SALARIO COM 10% DE BONUS :'|| bonus10); 
17 DBMS_OUTPUT.PUT_LINE('SALARIO COM 20% DE BONUS :'|| bonus20); 
18 END; 
19 / 
134 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Controle de Fluxo 
Este conjunto de comandos permite testar uma condição e, 
dependendo se a condição é falsa ou verdadeira, será tomada uma 
determinada direção de fluxo. O controle de fluco se dá em três 
formas: IF-THEN, IF-THEN-ELSE,IF-THEN-ELSIF. 
135 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
1 DECLARE 
2 v_first_name employees.first_name%TYPE; 
3 v_salary employees.salary%TYPE; 
4 BEGIN 
5 SELECT first_name, salary 
6 INTO v_first_name, v_salary 
7 FROM employees 
8 WHERE employee_id = 142; 
9 
10 IF v_salary > 3000 THEN 
11 DBMS_OUTPUT.put_line ('Salario acima de U$D 3,000'); 
12 DBMS_OUTPUT.put_line ('Teste IF-THEN'); 
13 END IF; 
14 END; 
15 / 
IF-THEN 
É a forma mais simples. Testa a condição especificada após o IF e, caso seja verdadeira, executa o 
comando além do THEN. Caso não seja, executa as acções após o END IF (note que devem ser 
escritos separados). 
136 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
IF-THEN-ELSE 
Aqui, acrescenta-se a palavra-chave ELSE para determinar o que 
deve ser feito caso a condição seja falsa. Dessa forma, o fluxo 
seguirá para os comandos após o THEN caso a condição seja 
verdadeira, e após o ELSE caso seja falsa. 
137 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
1 DECLARE 
2 v_first_name employees.first_name%TYPE; 
3 v_commission_pct employees.commission_pct%TYPE; 
4 BEGIN 
5 SELECT first_name, commission_pct 
6 INTO v_first_name, v_commission_pct 
7 FROM employees 
8 WHERE employee_id = 174; 
9 
10 IF v_commission_pct IS NULL THEN 
11 DBMS_OUTPUT.put_line ('Sem comissão'); 
12 DBMS_OUTPUT.put_line ('outra acção'); 
13 ELSE 
14 DBMS_OUTPUT.put_line('Comissão de '||v_commission_pct*100||'%'); 
15 DBMS_OUTPUT.put_line(' outra acção '); 
16 END IF; 
17 END; 
18 / 
IF-THEN-ELSE 
138 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
IF-THEN-ELSIF 
Quando se deseja testar diversas condições utilizando um mesmo IF, utilizase ELSIF. 
Assim, pode-se após cada ELSIF, testar nova condição que, caso seja verdadeira, 
executará as respectivas acções 
1 DECLARE 
2 Vencimentos NUMBER; 
3 BEGIN 
4 vencimentos := F_SALARY(101); -- deduções 
5 IF vencimentos <= 10000 THEN 
6 DBMS_OUTPUT.PUT_LINE ('Primeira faixa'); 
7 ELSIF vencimentos > 10000 AND vencimentos <= 15000 THEN 
8 DBMS_OUTPUT.PUT_LINE ('Segunda faixa'); 
9 ELSIF vencimentos > 15000 AND vencimentos <= 20000 THEN 
10 DBMS_OUTPUT.PUT_LINE ('Terceira faixa'); 
11 ELSE 
12 DBMS_OUTPUT.PUT_LINE('Ultima faixa'); 
13 END IF; 
14 END; 
15 / 
IF (está_na_hora) THEN 
 soa_alarme; 
END IF; 
_________________________________ 
 
IF (está_na_hora) THEN 
 soa_alarme; 
ELSE 
 está_atento; 
END IF; 
_________________________________ 
 
IF (hora > 20) THEN 
 cumprimento := ‘Boa noite’; 
ELSIF (hora > 12) THEN 
 cumprimento := ‘Boa tarde’; 
ELSE 
 cumprimento := ‘Bom dia’; 
END IF; 
139 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Outros exemplos 
Mesmo utilizando diversos ELSIF’s pode-se 
acrescentar um ELSE no final para o caso de 
nenhuma das condições anteriores serem 
satisfeitas. Mais uma vez, somente após o 
END IF e a cada linha de ação é que se deve 
colocar o ponto-e-vírgula. 
140 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
O LOOP permite que você realize repetições de determinadas 
ações. Na programação PL/SQL encontramos 3(três) tipo: LOOP 
Simples, WHILE-LOOP, FOR-LOOP. 
Controle de Repetição 
141 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
LOOP Simples 
Com este comando você pode realizar repetições de uma seqüência de comandos. O 
comando LOOP indica o inicio da área de repetição, enquanto que o END LOOP indica 
que o fluxo deve retornar do LOOP. 
1 BEGIN 
2 LOOP 
3 DBMS_OUTPUT.PUT_LINE ('Primeira ação do laço'); 
4 DBMS_OUTPUT.PUT_LINE ('Segunca ação do laço'); 
5 END LOOP; 
6 END; 
7 / 
Neste exemplo, como não está definida nenhuma condição de paragem do laço, você já deve 
ter concluído que este LOOP não terá fim. Logo, para resolver este problema é necessário 
utilizar o comando EXIT ou EXIT WHEN. O EXIT causa uma saída incondicional do LOOP, e o 
EXIT WHEN permite testar uma condição e, apenas se ela for verdadeira, provocará a saida 
do LOOP. 
142 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
1 DECLARE 
2 x NUMBER; 
3 BEGIN 
4 X := 1; 
5 LOOP 
6 DBMS_OUTPUT.PUT_LINE('O valor de x eh '||X); 
7 X := X + 1; 
8 EXIT WHEN X>=5; 
9 END LOOP; 
10 END; 
11 / 
EXIT ou EXIT WHEN 
143 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
WHILE-LOOP 
Este comando permite testar uma condição antes de iniciar a seqüência de acções de 
repetição. Ao final de LOOP, é testada a condição novamente e, caso verdadeira, continua 
a seqüência de acções dentro do LOOP ou sai, caso seja falsa, executando o que estiver 
após o END LOOP. 
1 DECLARE 
2 x NUMBER; 
3 BEGIN 
4 X := 1; 
5 WHILE X < 5 LOOP 
6 X := X + 1; 
7 END LOOP; 
8 END; 
9 / 
144 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
FOR - LOOP 
Utilize este comando sempre que você souber preveamente o número de vezes que um 
LOOP deve ser executado. A cada comando FOR-LOOP existe uma variável controladora 
que em cada interação assumirá todos os valores interiros (variando de 1 em 1) 
contidos entre o limite inicial e o limite final. 
FOR contador IN [REVERSE] inicio.. Fim LOOP 
 comando1; 
 comando2; 
END LOOP; 
contador Variável que terá seu valor incrementado. 
 
[REVERSE] Indica que se deve diminuir ao invés de aumentar o contador. O 
 valor de inicio deve ser maior que o fim, pois o valor será 
 decrescido a cada repetição 
 
inicio intervalo inicial de repetição 
 
Fim intervalor final da repetição 
145 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
1 DECLARE 
2 Y NUMBER := 1; 
3 BEGIN 
4 FOR X IN 1..5 LOOP 
5 Y := Y + X ; 
6 DBMS_OUTPUT.PUT_LINE (x); 
7 END LOOP; 
8 END; 
9 / 
FOR - LOOP 
Não necessidade de declarar a variável controladora, isso é feito implicitamente 
pelo comando FOR-LOOP. Podemos utilizar a variável controladora como uma 
variável normal, no entanto, não podemos assinalar valores à variável 
controladora. O escopo de visibilidade na variável controladora é apenas dentro 
do laço. Caso o número de vezes que deva ser repetida a seqüência de ações 
seja fruto de um cálculo, você poderá substituir tanto o intervalo superior 
quanto o superior por variáveis, mas não se esqueça que esses valores devem 
ser sempre números inteiros. 
FOR … IN … LOOP … END LOOP 
 
FOR X IN 1 .. 2 LOOP 
 NULL; 
END LOOP; 
X é so visível 
dentro do 
ciclo For 
146 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
Se o salário de um funcionário for menor do que U$D 500, ele 
deverá ter um aumento de 10% 
Cod_Pessoa (PK) 
Nome 
Endereco 
Fone 
Tipo Cod_Pessoa (PK e FK) 
Cod_Cargo (FK) 
Salario 
Cod_Venda (PK e FK) 
Cod_Prod (FK) 
Qtd 
Sub_Total 
Pessoa 
Funcionario 
Cod_Cargo (PK) 
Descricao 
Cargo 
Cod_Produto(PK) 
Descricao 
Preco 
Produto 
Cod_Venda (PK) 
Cod_Cliente (FK) 
Data 
Total 
Venda VendaxProd 
Cod_Pessoa (PK e FK) 
Pontos 
Cliente 
Exercício 
147 CC-FCUAN - 2010 Dr. Vicente 
Base de Dados II 
 
DECLARE 
 salfuncionario.salario%type; 
BEGIN 
 select salario 
 into sal 
 from funcionario 
 where cod_pessoa = 1 
 
 FOR UPDATE OF salario; 
 IF sal < 500 THEN 
 sal := sal * 1.1; 
 update funcionario set salario = sal 
 where od_pessoa = 1; 
 END IF; 
 COMMIT; 
END; 
148 CC-FCUAN - 2010 Dr. Vicente 
joaovicentelopes@hotmail.com 
Base de Dados II 
Curso: Ciências de Computação 
Base de Dados II