ebook BD II

INTRODUÇÃO A SISTEMAS DE BANCO DE DADOS
2012
BANCO DE DADOS
Aplicação da Structure Query Language
Este e-book visa, através de exemplos práticos, aplicar os conceitos da Structure
Query Language (SQL) na manipulação de Banco de Dados relacionais.
Ricardo R. Barcelar
http://www.ricardobarcelar.com.br
[email protected]
APRESENTAÇÃO
APRESENTAÇÃO
O
objetivo deste material é apresentar a Structure Query Language como linguagem
padrão para manipulação de bancos de dados relacionais, norteando o estudo de
banco de dados através de exemplos práticos aplicados a um Sistema Gerenciador de
Banco de Dados amplamente conhecido e utilizado, o PostgreSQL.
CELSO HENRIQUE PODEROSO DE OLIVEIRA, NAVATE e ELMASRI são referências
para os estudos traçados neste material. Dessa forma, o conteúdo apresentado certamente
poderá ser utilizado como referência na vida prática do profissional de computação que lida
diariamente com Banco de Dados.
Ricardo R. Barcelar
PROJETO FÍSICO DE BANCO DE DADOS
1
Parte
PROJETO FÍSICO DE BANCO DE DADOS
A
primeira etapa a fazer ao começa a trabalhar com banco de dados é estudar as técnicas
utilizadas para se criar um projeto de banco de dados: Modelo EntidadeRelacionamento, Modelo Lógico usando ferramenta Case, Normalização, Estratégias de
Projeto de Banco de Dados, etc.
Conhecedores dos conceitos de Codd, Chen, e outros autores que definiram o padrão
relacional para banco de dados relacionais, é então, possível avançar mais um passo:
implementar o banco de dados em uma ferramenta apropriada. Mas antes, é necessário
conhecer alguns conceitos do Projeto Físico e dos Sistemas Gerenciadores de Banco de
Dados especificamente.
O Modelo Físico de Dados é a especificação em SQL - Structure Query Language –
Linguagem de Consulta Estruturada - do esquema relacional para o Sistema Gerenciador de
Banco de Dados escolhido. Nesta fase, as estruturas de armazenamento e de recuperação de
informações, bem como os mecanismos de acesso devem ser escolhidos, visando sempre o
aprimoramento da performance dos aplicativos de Banco de Dados.
Nesta fase devem ser especificados não apenas as tabelas criadas, mas também os
índices necessários, as restrições de integridade (checks e triggers), algumas operações de
inclusão, exclusão e atualização de dados para cada tabela, bem como as consultas que a
aplicação deve realizar.
1.1 FATORES QUE INFLUENCIAM O PROJETO FÍSICO DE BANCO DE DADOS
Esta fase do projeto de Banco de Dados objetiva não só propor uma apropriada
estruturação de dados, mas fazê-lo de maneira que garanta um bom desempenho. Sendo
assim, não é possível tomar decisões significativas sobre projetos físicos e análises de
desempenho, até que conheçamos as consultas, as transações e as aplicações que devem ser
executadas no banco de dados.
Dessa forma, vamos discutir alguns fatores:
a. Analisar as consultas e transações do Banco de Dados
É importante ter uma ideia das intenções de uso do banco de dados, definindo as
consultas e transações que esperamos que sejam realizadas em alto nível, especificando o
seguinte:
- Os arquivos que serão acessados pela consulta;
3
PROJETO FÍSICO DE BANCO DE DADOS
- Os atributos nos quais quaisquer condições de seleção para a consulta estejam
especificadas;
- Os atributos nos quais quaisquer condições de junção ou condições para ligar
múltiplas tabelas ou objetos para a consulta estejam especificadas;
- Os atributos cujos valores serão trazidos através da consulta.
Para cada operação ou transação de atualização, devemos especificar o seguinte:
- Os arquivos que serão atualizados;
- O tipo de operação em cada arquivo (insert, update, delete);
- Os atributos nos quais as condições de seleção para uma exclusão ou atualização
estejam especificadas.
- Os atributos cujos valores serão alterados através de uma operação de atualização.
b. Analisar a frequência esperada de solicitação (execução) de consultas e transações
É necessário considerar as taxas de solicitação (execução), usadas de forma estatística
em situações práticas para grandes volumes de processamento.
c. Analisar as restrições de tempo de consulta e transações
Neste caso, algumas consultas e transações podem ter rigorosas restrições de
desempenho. Por exemplo, uma transação que deve ser interrompida se não for concluída em
20 segundos.
d. Analisar as frequências esperadas de operações de atualização
Um número mínimo de caminhos de acesso aos dados deve ser especificado para um
arquivo que seja frequentemente atualizado, uma vez que atualizar os próprios caminhos de
acesso desacelera as operações de atualização.
e. Analisar as restrições de Unicidade em Atributos
Caminhos de acesso aos dados devem ser especificados em todos os atributos
candidatos a chave ou chave primária. A existência de um índice facilita a pesquisa nos
arquivos de dados, pois este define um caminho de dados.
Realizadas as análises iniciais, outra decisão importante é definir qual Sistema
Gerenciador de Banco de Dados usar. É relevante levar em consideração vários fatores, como:
- O modelo físico, visto que determinará qual a carga de trabalho que será exigida pelo
Sistema Gerenciador de Banco de Dados, isso já eliminará algumas opções.
- O custo. Uma locadora, por exemplo, irá adquirir uma licença Oracle ou SQL Server?
- Plataforma de Sistema Operacional. Esta é uma decisão que não será analisada neste
momento.
4
PROJETO FÍSICO DE BANCO DE DADOS
1.2 SQL - STRUCTURE QUERY LANGUAGE - LINGUAGEM ESTRUTURADA
CONSULTA
DE
Como visto no módulo passado, é muito simples criar um script SQL através de uma
ferramenta Case. No entanto, este não será o foco desta sessão. Aqui aprenderemos a criá-lo
sem auxílio de ferramentas case. Para tanto, é necessário conhecermos a linguagem SQL.
Apesar do nome, fazer consultas a bancos de dados não é a única função de SQL. Ela
é utilizada para criar tabelas, inserir, excluir e alterar dados no banco de dados, além de
outras utilizações.
A SQL foi desenvolvida na década de 70 pela IBM em uma das primeiras tentativas de
desenvolver um banco de dados relacional. Tornou-se padrão de fato depois de 1986, quando
a ANSI (American National Standards Institute) endossou como linguagem padrão para
Banco de Dados relacionais e desde então, já sofreu três atualizações oficiais: em 1989,
1992 e 1999. Nesta última, algumas das mudanças mais significativas estão relacionadas com
a definição dos padrões para Banco de Dados Objeto-Relacionais.
Devido ao sucesso da forma de consultar e manipular dados da SQL, dentro de um
ambiente de banco de dados, a utilização da SQL foi se tornando ainda maior. Cabe ressaltar
que cada implementação de SQL possui uma adaptação da sintaxe para resolver determinados
problemas, portanto, qualquer comando mostrado pode ser usado de forma diferente em um
determinado Sistema Gerenciador de Banco de Dados.
Recomenda-se a leitura do manual do fabricante para maiores informações sobre o uso
da linguagem SQL em Sistema Gerenciador de Banco de Dados comerciais.
Atualmente, a linguagem SQL assume um papel muito importante nos sistemas de
gerenciamento de banco de dados, podendo ter muitos enfoques. Através de comandos SQL,
é possível montar consultas poderosas sem a necessidade da criação de um programa, ou
utilizar comandos SQL embutidos em programas de aplicação que acessam os dados
armazenados.
Devido ao fato de possuir várias aplicações, a linguagem SQL provê suporte a várias
funções. Que consiste em:
- DDL (Linguagem de definição de dados), onde os dados a serem armazenados são
definidos e estruturados; (Create, Alter e Drop).
- DML (Linguagem de manipulação de dados), que permite a inclusão, remoção,
seleção ou atualização de dados armazenados no banco de dados; Controle de acesso,
permitindo proteção dos dados de manipulações não autorizadas; (Select, Insert, Update,
Delete, Commit e Rollback).
- DCL (Linguagem de controle de dados) uma subclasse da DML responsável pelas
permissões de acesso ao banco; (Grant e Revoke).
Restrições de Integridade, que auxiliam no processo de definição da integridade dos
dados, protegendo contra corrupções, inconsistências e falhas do sistema de computação.
Além dessas características principais, ainda podemos citar:
- Visões, onde são especificadas as consultas disponíveis através de tabelas virtuais
(Views).
5
PROJETO FÍSICO DE BANCO DE DADOS
- Gatilhos a fim de automatizar processos no banco (Triggers).
Outra característica é a capacidade de cancelar uma série de atualizações ou gravar
depois de concluir uma série de atualizações.
1.3 CRIANDO UM BANCO DE DADOS
Esta etapa trata da Data Definition Language (DDL). O processo básico consiste
simplesmente em atribuir tipo de dado e tamanho para cada um dos atributos que foram
identificados.
Atenção:
- Cada Sistema Gerenciador de Banco de Dados adota um terminador de comando. O
mais comum é o ponto e vírgula (;)
- Quanto aos comandos apresentados, os Sistemas Gerenciadores de Banco de Dados
não são sensíveis a letras maiúsculas ou minúsculas. Contudo, o conteúdo das colunas
normalmente é sensível, portanto muito cuidado.
1.3.1 Desnormalização de dados
Em uma análise mais profunda, é muitas vezes conveniente avaliar a necessidade de
alguns campos redundantes na tabela. Esse processo é chamado de desnormalização de
dados. Ocorre que, quando idealizamos o modelo de dados, utilizamos o conceito de
processador perfeito, em que as informações são transmitidas sem custo instantaneamente.
No entanto, mesmo bancos de dados avançados possuem limitações.
Esta técnica pode ser exemplificada pelo caso dos campos calculados que são
eliminados na 3ª Forma Normal. Quando se tem um grande banco de dados que trata de Notas
Fiscais, por questões de performance costuma-se “driblar” um pouco a regra e coloca-se o
valor total da nota como um campo na tabela. Isso pode ser feito quando o banco de dados
possui mecanismo de controle de transações, em que uma eventual gravação no banco de
dados implique necessariamente a gravação na outra ponta. Esta operação deve ser feita por
meio de gatilhos (triggers) para garantir a integridade das informações.
1.3.2 Definição de dados
Antes de criar as tabelas no banco de dados, é preciso definir quais são as
características de cada um dos campos. As características que o SQL exige são o tipo do dado
e o tamanho de cada campo. As informações aqui apresentadas são utilizadas pela maioria
dos bancos de dados. Eventuais mudanças devem ser consultadas na documentação do
Sistema Gerenciador de Banco de dados adotado.
TIPO DE DADO
DESCRIÇÃO
Integer ou Int
Número positivo ou negativo inteiro. O número de bytes utilizado varia de
acordo com o banco de dados utilizado.
Smallint
Mesma função do Inteiro, mas ocupa cerca da metade do espaço
6
PROJETO FÍSICO DE BANCO DE DADOS
Numeric
Número positivo ou negativo de ponto flutuante. Deve-se informar
tamanho do campo e a quantidade de casas decimais.
Decimal
Semelhante ao Numeric, mas, em alguns bancos de dados, poderá ter
uma maior precisão após a vírgula.
Real
Número de ponto flutuante de simples precisão. A diferença básica é que
os valores serão armazenados em representação exponencial, portanto
será arredondado para o nível mais próximo de precisão.
Double Precision
Número de ponto flutuante de dupla precisão. Comporta-se como o Real,
mas permite maior aproximação de resultados.
Float
Número de ponto flutuante em que você define o nível de precisão
(número de dígitos significativos)
Bit
Armazenamento de um número fixo de bits. O número de bits deve ser
indicado, do contrário o padrão será 1.
Bit Varying
Igual ao Bit, permitindo armazenar valores maiores. Normalmente, utilizase para armazenar imagens.
Date
Permite armazenar datas
Time
Permite armazenar horários
Timestamp
Permite armazenar uma combinação de data e hora
Character ou char
Permite armazenar cadeias de caracteres (letras, símbolos e números). O
tamanho informado é fixo e indica o tamanho máximo da cadeia de
caracteres.
Character Varying
ou Varchar
Permite armazenar cadeias de caracteres, mas com tamanho variável.
Neste caso, especifica-se o tamanho máximo da coluna. Se for utilizado
menos espaço que o máximo definido, o espaço restante não será
ocupado.
Interval
Intervalo de data ou hora
1.3.3 Adaptando o modelo de dados
Com base nas informações acima, adaptaremos nosso modelo de dados. Para nossos
estudos utilizaremos o modelo lógico abaixo:
7
PROJETO FÍSICO DE BANCO DE DADOS
Figura 1- Modelo de dados
Notação dos relacionamentos
1:n – FK obrigatória. Parte da chave primária
1:n – FK não obrigatória
n:m – Muitos para muitos (cria-se a tabela associativa)
1.3.4 Criação de Tabelas
Tabelas são as estruturas mais importantes de um banco de dados. Nas tabelas estará
o conteúdo que representa cada objeto do mundo real.
As tabelas criadas no banco de dados ficam armazenadas em outras tabelas internas
do gerenciador de banco de dados denominadas Dicionário de Dados.
8
PROJETO FÍSICO DE BANCO DE DADOS
Sintaxe:
Exemplo:
CREATE TABLE nome_da_tabela
CREATE departamento
(coluna1 tipo_de_dado constraint,
(dep_id INTEGER NOT NULL,
coluna2 tipo_de_dado constraint,
dep_nome VARCHAR(100),
coluna3 tipo_de_dado constraint);
dep_atividade VARCHAR(100),
empr_id INTEGER NOT NULL);
1.3.5 Constraints – Integridade Referencial – Restrição de Integridade (RI)
Constraints são regras agregadas a colunas ou tabelas. Assim, pode-se definir um
campo como obrigatório ou não, ou aceitar apenas alguns valores predefinidos. No caso de
regras aplicadas a tabelas, tem-se a definição de chaves primárias (PK) e estrangeiras (FK).
Um bom Sistema Gerenciador de Banco de Dados deve evitar a entrada de informação
incorreta ou inconsistente em sua base de dados, garantindo, com isso, a qualidade da
informação inserida. Uma restrição de integridade (RI) é uma condição especificada no
esquema da base de dados para restringir a informação a ser armazenada.
As RI são especificadas e conferidas em dois momentos diferentes:
- Na especificação da RI: se dá na definição do esquema da base de dados pelo
usuário ou pelo administrador da base de dados (DBA);
- Na conferência da RI: é feita pelo banco de dados toda vez que uma relação é
modificada por uma aplicação sendo executada.
1.3.5.1 TIPOS DE CONSTRAINTS MAIS COMUNS
As constraints podem variar muito de um banco para outro.
a) Chave Primária: é a coluna identificadora de um registro na tabela. Para representála basta acrescentar a palavra chave PRIMARY KEY seguida do nome da coluna.
Exemplo:
...
PRIMARY KEY (cliente_id),
...
b) Chave Estrangeira: é o campo que estabelece o relacionamento entre duas tabelas.
Dessa forma, deve-se especificar na tabela que contém a chave estrangeira quais são essas
colunas e a qual tabela está relacionada.
Ao determinar este tipo de relacionamento, fica garantida a integridade das
informações. Os valores presentes na coluna definida com chave estrangeira devem ter um
correspondente na outra tabela.
9
PROJETO FÍSICO DE BANCO DE DADOS
Exemplo1:
...
FOREING KEY(coluna)
REFERENCES (tabela)
ON UPDATE ação
ON DELETE ação
Ação: Determina qual ação o banco de dados deve tomar quando for excluída ou alterada uma
linha da tabela que contém referência a esta chave. Pode ser:
- SET NULL: Altera o conteúdo da coluna para nulo, perdendo a referência, sem deixar
valores inconsistentes.
- SET DEFAULT: Altera o conteúdo da coluna pra um valor especificado na cláusula
DEFAULT, se houver.
- CASCATE: Exclui ou altera todos os registros que se relacionam com a ele.
- NO ACTION: Em caso de alteração, não modifica os valores que se relacionam a eles.
- RESTRICT: Não permite a exclusão da PK.
Exemplo2:
CREATE TABLE funcionario (fun_id
fun_admissao DATE, dept_id INTEGER,
INTEGER,
fun_nome
VARCHAR(50),
PRIMARY KEY (fun_id),
FOREIGN KEY (dept_id) REFERENCES departamento ON DELETE CASCADE ON
UPDATE NO ACTION
c) DEFAULT: Atribui um conteúdo padrão a uma coluna da tabela.
Exemplo:
...
prod_quantidade INTEGER DEFAULT 1,
...
d) NOT NULL: Indica que o conteúdo de uma coluna não poderá ser Nulo. Lembre-se,
em banco de dados SQL, colunas sem valor atribuído possuem conteúdo Nulo.
10
PROJETO FÍSICO DE BANCO DE DADOS
Exemplo:
...
cliente_nome VARCHAR(50) NOT NULL,
...
e) UNIQUE: Indica que não pode haver repetição no conteúdo da coluna. Não é a
mesma coisa que chave primária. A chave primária, além de não permitir repetição, não pode
conter valores nulos, dentre outras característica não existentes nesta restrição.
Exemplo:
...
cliente_CPF NUMERIC(11) UNIQUE,
...
f) CHECK: Definição de domínio: Um domínio é uma expressão de valores possíveis
para o conteúdo de uma coluna.
Exemplo:
...
sexo CHAR(1) CHECK (UPPER(sexo) = ‘M’ OR UPPER(sexo) = ‘F’),
…
1.3.6 Alteração da estrutura da tabela
Para alterar a estrutura de uma tabela, utilizamos o comando ALTER TABLE.
1.3.6.1 ACRESCENTAR NOVAS COLUNAS
O comando utilizado para acrescentar novas colunas é muito semelhante ao da criação
de colunas em uma tabela:
Sintaxe:
Exemplo:
ALTER TABLE nome_da_tabela ADD
ALTER TABLE departamento
coluna1 tipo_de_dado constraint,
coluna2
...
tipo_de_dado
ADD dep_ramal NUMERIC(4) UNIQUE
constraint,
1.3.6.2 ACRESCENTAR NOVAS CONSTRAINTS
11
PROJETO FÍSICO DE BANCO DE DADOS
O comando utilizado para acrescentar novas constraints é muito semelhante ao da
criação de constraints em uma tabela:
Sintaxe:
Exemplo:
ALTER TABLE nome_da_tabela
ALTER TABLE departamento
ADD (constraint)
ADD PRIMARY KEY (dep_id)
1.3.6.3 MODIFICAR COLUNAS
O comando utilizado para modificar qualquer característica de uma coluna.
Sintaxe:
Exemplo:
ALTER TABLE nome_da_tabela
ALTER TABLE departamento
ALTER COLUMN (coluna tipo_dado
constraint)
ALTER
COLUMN
VARCHAR(150);
dep_nome
TYPE
ALTER TABLE dep_nome
ALTER
NULL;
COLUMN
dep_nome
SET
NOT
Os comandos de alteração da estrutura/características de um banco de dados pode
variar de um Sistema Gerenciador de Banco de Dados para outro. Portanto, é importante, em
caso de dúvidas, consultar o manual da ferramenta. O manual do PostgreSql pode ser
encontrado em: <http://www.postgresql.org/docs/9.1/static/sql.html>
1.3.6.4 EXCLUINDO ELEMENTOS
Pelo padrão SQL, deveria ser possível excluir colunas ou constraints de uma tabela.
Alguns bancos de dados não permitem a exclusão de colunas.
Sintaxe:
Exemplo:
ALTER TABLE nome_da_tabela
ALTER TABLE departamento
DROP elemento
DROP dep_ramal;
ALTER TABLE departamento
DELETE PRIMARY KEY;
Abaixo um exemplo onde várias alterações estão sendo realizadas em um só comando:
ALTER TABLE fornecedores
ADD cgc NUMERIC(14),
DROP tipofornecedor,
12
PROJETO FÍSICO DE BANCO DE DADOS
ADD CONSTRAINT ck_email CHECK (e_mail CONTAINING ‘@’ OR e_mail IS
NULL)
1.3.7 Eliminando uma Tabela
Para eliminar uma tabela do banco de dados, utilizamos o comando DROP TABLE
seguido do nome da tabela. Alguns bancos somente permitirão esta operação se esta não
estiver relacionada à outra tabela.
Sintaxe:
Exemplo:
DROP TABLE nome_da_tabela
DROP TABLE departamento;
5. EXERCÍCIOS
Usando a Linguagem SQL crie o Schema de banco de dados para o seguinte modelo lógico:
13
ÍNDICE
2
Parte
íNDICE
Q
uando criamos a estrutura de um banco de dados temos que usar artifícios que
melhorem a performance na recuperação dos dados que lá serão armazenados.
Dentre várias técnicas existentes para melhorar a esta performance existe a criação de
índices.
O Índice é um arquivo auxiliar associado a uma Tabela. Sua função é acelerar o tempo
de acesso às linhas de uma Tabela, criando ponteiros para os dados armazenados em colunas
especificas. O Banco de dados usa o Índice de maneira semelhante ao índice remissivo de um
livro, verifica um determinado assunto no índice e depois localiza a sua posição em uma
determinada página.
1.1 CRIAÇÃO DE ÍNDICE
O índice serve para prover um acesso rápido a linhas das tabelas. Por meio dele é
possível unir uma ou mais colunas por onde o acesso é mais frequente.
Exemplo:
Temos uma tabela de pessoas e normalmente queremos fazer buscas em ordem
alfabética. O nome, não é uma boa chave primária para a tabela por ser alfanumérica, o que
deixa as pesquisas mais lentas e também pela repetição de nomes. Dessa forma, cria-se um
índice para o nome da pessoa. Assim, garante-se um acesso mais rápido aos nomes, visto que
os dados buscados estariam fora de sequência.
Os valores indexados são armazenados em um objeto do banco de dados em ordem, o
que permite ao Sistema Gerenciador de Banco de Dados pesquisar primeiro no índice, para
depois buscar na tabela. É importante ressaltar que alguns bancos de dados necessitam de
índices constantemente atualizados, pois eventualmente estes perdem a referência ao dado.
O índice quando criado com critérios bem definidos traz uma enorme vantagem, mas
por outro lado pode trazer enormes problemas, visto que todo trabalho de um Sistema
Gerenciador de Banco de Dados envolve um custo. Não é conveniente sair criando índices
para cada um dos campos da tabela sem que haja uma razão para isto.
Quando um índice é criado uma das tarefas do Sistema Gerenciador de Banco de
Dados é mantê-lo atualizado a cada nova inclusão, exclusão e alteração nos dados. Além do
mais, o índice também ocupa espaço no banco de dados, pois é criado um novo objeto que
manterá informações indexadas e armazenará a referência à linha física da tabela.
14
ÍNDICE
NOTA:
Se são criados muitos índices, pode-se prejudicar o desempenho do banco de dados
Se são criados poucos índices, pode-se prejudicar o desempenho da aplicação.
Quando criar índices?
a) Chaves Primárias: se o banco de dados não criá-las automaticamente, é necessário
fazê-lo manualmente.
b) Chave Estrangeira: é sempre recomendável criar, principalmente, quando a tabela
que faz referência é muito grande.
c) Colunas utilizadas frequentemente na cláusula WHERE ou JOIN do comando
SELECT.
Quando não criar índices?
a) Colunas com muitos valores iguais.
b) Colunas com muito conteúdo NULO (não inicializados).
c) Tabelas pequenas.
NOTA:
Sempre que uma consulta estiver muito lenta, verifique a possibilidade de criar índice
para facilitar a busca.
Para criar um índice, utilize a seguinte sintaxe:
Sintaxe:
Exemplo:
CREATE INDEX nome_index
ON nome_tabela (coluna [ASC|DESC])
CREATE INDEX idx_dep_nome
ON departamento(dep_nome)
CREATE INDEX idx_dep_varios
ON departamento(dep_nome,dep_ramal)
Para excluir um índice, utilize a seguinte sintaxe:
Sintaxe:
Exemplo:
DROP INDEX nome_index
DROP INDEX idx_dep_varios
Não é possível modificar índices. Para alterar um índice, elimine-o e, depois, recrie-o.
NOTA:
Ao eliminar uma tabela, os índices e as constraints são eliminados automaticamente.
15
ÍNDICE
1.2 ACESSO A DADOS EM UM SISTEMA GERENCIADOR DE BANCO DE DADOS
Para compreender melhor a utilização de índices é importante conhecer a estrutura de
acesso à dados em um Sistema Gerenciador de Banco de Dados.
Figura 2 - Organização de um SGBD
Neste contexto, observamos que os bancos de dados são armazenados fisicamente
como arquivos de registros, que são geralmente armazenados em discos magnéticos. Assim,
os meios de armazenamento formam uma hierarquia de armazenamento que inclui duas
categorias principais:
- Armazenamento Principal: Operados diretamente pela CPU, como a Memória
Principal e Memória Cache.
- Armazenamento Secundário: Inclui os discos magnéticos, óticos e fitas. Geralmente
possuem uma capacidade maior, porém são mais lentos e não podem ser acessados
diretamente pela CPU.
Figura 3 - Estrutura de armazenamento
16
ÍNDICE
A maioria dos bancos de dados está armazenada sob maneira permanente (ou
persistente) sob a forma de armazenamento secundário.
Geralmente, o Sistema Gerenciador de Banco de Dados possui diversas opções
disponíveis para organizar os dados, e o processo relativo ao projeto de banco de dados físico
envolve escolher entre as opções, as técnicas de organização de dados.
Os dados armazenados em disco são organizados na forma de arquivos de registros.
Cada qual é uma coleção de valores de dados que podem ser interpretados como fatos sobre
entidades, seus atributos e seus relacionamentos.
Para acessar uma informação no banco de dados considera-se o tipo de requisição e a
estrutura utilizada para organização e acesso às chamadas páginas de dados.
1.2.1 Organização dos dados em Memória Secundária
- Arquivos Heap ou pile (aglomerado): Melhor opção quando o acesso típico é uma
busca sequencial, sem ordem, recuperando todos os registros. Os registros são posicionados
no arquivo pela ordem na qual foram incluídos. São utilizados pelos índices secundários e
utilizados para coletar e armazenar registros de dados para utilização futura.
Exemplo: um simples arquivo de registros de dados.
- Arquivos Ordenados: Melhor opção se os registros devem ser recuperados em
alguma ordem, ou quando somente uma porção contígua dos registros ordenados é
necessária.
Exemplo: Arquivo de dados, possivelmente ordenado, além de vários índices.
- Arquivos Hashed: Melhor para seleções de igualdade. Uma função hash é aplicada
ao valor do campo de um registro e retorna o endereço do bloco do disco no qual o registro
está armazenado. Para a maioria dos registros, precisamos somente do acesso a um único
bloco para recuperar aquele registro. Não é adequado para acesso sequencial ordenado.
Existem outras maneiras ou possibilidades para organizar arquivos, como árvores-B e
arquivos de registro mistos, que implementam relacionamentos entre registros de diferentes
tipos fisicamente como parte da estrutura de armazenamento.
1.3 INDEXAÇÃO
Como visto, os índices são estrutura de dados que recebem como entrada uma
propriedade de registro (por exemplo, um valor de um ou mais campos) e os encontra com
essa propriedade rapidamente. Um índice permite localizar um registro sem ter que examinar
mais que uma pequena fração dos registros possíveis. Assim sendo, podemos concluir que os
campos cujos valores o índice se baseia formam a chave de pesquisa.
NOTA
Índices são estruturas de dados auxiliares cujo único propósito é tornar mais rápido o
acesso a registros baseado em certos campos, chamados campos de indexação.
17
ÍNDICE
1.3.1. Tipos de Índices
- Índices primários - baseado na chave de ordenação;
- Índices de agrupamentos (clustering) - baseado no campo de ordenação não-chave de
um arquivo;
- Índices secundários - baseado em qualquer campo não ordenado de um arquivo;
- Índices multiníveis;
- Árvores B e B+;
- Tabelas Hash;
Um dos mais antigos esquemas de índice utilizados em sistema de banco de dados é
chamado de arquivo indexado sequencialmente, que são projetados para aplicações que
requerem tanto o processamento sequencial de um arquivo inteiro quanto o acesso aleatório a
registros individuais.
Estes índices podem ser:
- Densos: É quando o número de entradas do índice é igual ao número de registros no
arquivo de dados. Cada registro no arquivo de índice contém o endereço de um registro no
arquivo de dados.
P o n teiro s
Figura 4 - Índice denso sobre um arquivo de dados sequenciais
- Esparços: Usa-se menos espaço de armazenamento que o índice denso ao custo de
um tempo um pouco maior para localizar um registro dada a sua chave. É quando o número de
entradas do índice é igual ao número de blocos do arquivo de dados.
Apenas
alguns
registros de dados são representados no arquivo de índices. Em geral, aponta para o 1º
registro de bloco.
P o n teiro s
Figura 5 - Índice esparso sobre um arquivo de dados sequenciais
18
ÍNDICE
1.3.1.1 ÍNDICE PRIMÁRIO
É o índice associado a uma chave primária. Nada mais é do que um arquivo ordenado,
cujos registros são de tamanho fixo com dois tampos. O primeiro, do mesmo tipo do campo
chave de ordenação (chave primária) e o segundo é um ponteiro para um bloco do disco ou
arquivo de dados.
Utiliza índice esparço.
Figura 6 - Índice Primário
1.3.1.2 ÍNDICE DE AGRUPAMENTO (CLUSTERING)
Se os registros de um arquivo estiverem ordenados fisicamente por um campo que não
seja chave, ou seja, que não possui um valor distinto, esse campo é chamado de campo
clustering. Semelhante ao índice primário possui dois campos de ordenação, no entanto o
primeiro campo é do mesmo tipo do campo clustering e o segundo é um ponteiro para o bloco.
Neste sentido sempre há a reserva de um bloco inteiro para cada valor de campo clustering,
melhorando operações de inclusão e exclusão. Em simples palavras é um índice associado a
um campo não chave.
Utiliza índice esparço.
19
ÍNDICE
Figura 7 - Índice de Agrupamento
1.3.1.3 ÍNDICE SECUNDÁRIO
No índice secundário o campo de indexação é um campo não ordenado do arquivo de
dados. Pode haver muitos índices secundários para um mesmo arquivo.
Dois casos:
- O campo de indexação é um campo chave (às vezes chamado de chave secundária).
- O campo de indexação não é chave.
20
ÍNDICE
É possível que mais de um registro tenha o valor da chave. Isso ocorre, por exemplo,
quando a chave de pesquisa não é a chave primária;
Um índice secundário é um índice denso, normalmente com duplicatas. O índice
consiste em pares de chaves-ponteiro. Neste caso, sempre que são permitidas chaves de
pesquisas duplicadas.
Figura 8 - Índice Secundário
Outra possibilidade para índices secundários é a entrada de índice para um bloco de
dados onde a menor chave de pesquisa é nova, isto é, a chave não apareceu em um bloco
anterior;
Se não há chave de pesquisa nova no bloco, então sua entrada de índice contém a
única chave de pesquisa encontrada nesse bloco;
Pode-se encontrar os registros de uma chave de pesquisa K examinando-se o índice
para a primeira entrada cuja chave é:
- Igual a K
- Menor que K, mas a próxima chave é maior que K
Segue-se o ponteiro da entrada. Se for encontrado pelo menos um registro com a chave
de pesquisa K, então a busca continua em blocos adicionais, até encontrar todos os registros
com a chave de pesquisa K.
Figura 9 - Índice Secundário
21
ÍNDICE
Usa o índice esparso indicando a nova chave de pesquisa mais baixa em cada bloco.
ORGANIZANDO AS IDÉIAS:
Campos ordenados
Índice primário
Índice de agrupamento
Campo chave
Campo não chave
Tipo de Índice
Primário
Agrupamento
Secundário (chave)
Secundário
(não
chave)
Campos não ordenados
Índice Secundário (chave)
Índice
Secundário
(não
chave)
Número de Entradas
Nº de blocos no arquivo de dados
Nº de valores distintos do campo de
indexação
Nº de registros no arquivo de dados
Nº de registros no arquivo de dados
Nº de valores distintos de indexação
Denso ou
Esparço
Esparço
Esparço
Denso
Denso
Esparço
1.3.1.4 ÍNDICES MULTINÍVEIS
Uma pesquisa binária é aplicada ao índice para localizar ponteiros para um bloco do
disco ou para um registro no arquivo que possui um valor de campo índice específico.
Se o arquivo de índices se torna muito grande para ser armazenado em bloco de disco,
é interessante indexá-lo em mais de um nível. A vantagem é que um índice pequeno pode ser
mantido em memória e o tempo de busca é mais baixo; por outro lado, muitos níveis de índices
podem aumentar a complexidade do sistema, sendo mais recomendado a utilização de outra
estrutura como a que veremos mais a frente: árvore-B.
Figura 10 - Índices Multiníveis
NOTA
Um índice multinível é um “índice de índice”.
22
ÍNDICE
- Primeiro nível: arquivo ordenado pela chave de indexação, valores distintos, entradas
de tamanho fixo.
- Demais níveis: índice primário sobre o índice do nível anterior e assim
sucessivamente até que no último nível o índice ocupe apenas um bloco.
- Número de acessos a bloco: um a cada nível de índice, mais um ao bloco do arquivo
de dados.
Figura 11 - Índice Primário de dois Níveis
A figura acima exemplifica um índice primário de dois níveis que se parece com a
organização ISAM (Indexed Sequential Acess Method - Método de Acesso Seqüencial
Indexado).
O problema dos índices multiníveis é que são arquivos fisicamente ordenados, portanto,
ineficientes na inserção e remoção. Para solucionar este problema podem-se adotar as
seguintes soluções:
- Deixar algum espaço em cada um dos blocos para inserção de novas entradas.
- Estruturas de dados: Árvores B e suas variações.
a) Gerenciamento de Índices nas Modificações de Dados
Os arquivos sequenciais apresentam problemas com o tempo, visto que um bloco não é
mais suficiente para armazenar o conteúdo que era antes armazenado. Para contornar esse
problema é possível adotar as seguintes soluções:
- Criar blocos de estouro (overflow). Blocos de estouro não têm entradas em um índice
esparso;
23
ÍNDICE
- Inserir novos blocos na ordem sequencial;
- Se não houver lugar para inserir informação em um bloco, alguma informação poderá
ser transferida para outro bloco. De igual forma, blocos vazios poderão ser combinados.
1.3.1.5 ÁRVORE B
Árvores B e B+ são casos especiais de estruturas de dados bastantes conhecidas do
tipo árvore. Uma árvore é formada por nós, e cada nó na árvore, exceto um nó especial
denominado raiz, possui um nó pai e diversos nós filhos. Um nó que não possui filhos é
chamado de nó folha.
Um modo de implementar uma árvore é possuir tantos ponteiros em cada nó quantos
forem os nós filhos daquele nó. E para percorrer todos os nós, da árvore e da sub-árvore é
necessário fazer uso da recursividade.
Figura 12 - Árvore de Grau 3
a) Árvore de Pesquisa
Uma árvore de pesquisa é um tipo especial de árvore que se utilizada para guiar a
pesquisa por um registro, dado o valor de um dos campos do registro.
A árvore de busca é projetada com dois objetivos:
- Manter a árvore balanceada; e
- Evitar o desperdício de espaço dentro de um nó, à custa de maior complexidade nos
algoritmos de inserção e remoção.
Regras:
- Os nós da sub-árvore esquerda contém valores menores que a raiz;
- Os nós da sub-árvore direita contém valores maiores ou iguais a raiz;
- As sub-árvores são árvores binárias de pesquisa.
24
ÍNDICE
Figura 13 - Distribuição nos nós
b) Árvore B
Em computação, Árvore B ou B-Tree é uma estrutura de dados árvores que são muito
utilizadas em banco de dados e sistema de arquivos.
Para inserir ou remover variáveis de um nó, o nó não poderá ultrapassar sua ordem e
nem ser menor que sua ordem dividida por dois. Árvores B não precisam ser rebalanceadas
como são frequentemente as árvores de busca binária com Árvore AVL. Árvores B têm
vantagens substanciais em relação a outros tipos de implementações quanto ao tempo de
acesso e pesquisa aos nós.
Uma árvore B de ordem "m" (máximo de filhos para cada nó) é uma árvore que atende
as seguintes propriedades:
- Cada nó tem no máximo "m" filhos
- Cada nó (exceto a raiz e as folhas) tem pelo menos "m/2" filhos
- A raiz tem pelo menos dois filhos se a mesma não for uma folha
- Todas as folhas aparecem no mesmo nível e não carregam informação
- Um nó não-folha com "k" filhos deve ter k-1 chaves
As vantagens da árvore B são:
- Melhor desempenho por ter um número menor de nós do que uma árvore binária. Por
exemplo: Menos nós significa menor altura que resulta em menos acessos ao disco.
- Por garantir poucos ponteiros entre os nós, há uma economia de espaço.
- Maior rapidez em buscas pela utilização de chaves primárias.
- Sua estrutura é dinâmica, ajustando automaticamente o balanceamento da árvore, a
cada inclusão/exclusão.
- Permite um tempo de acesso de dados menor, em uma busca aleatória, por causa de
suas ramificações.
Algoritmos
INSERÇÃO
1. Primeiro pesquise a chave, para ter a certeza de que esta não existe na árvore.
2. Busque a posição onde esta será inserida. Teste para ver se o nó está cheio.
3. Se nó estiver vazio, insira o valor dentro dele, senão execute uma subdivisão do nó da
seguinte forma:
3.1. Verifique se o nó-pai está vazio, se sim execute
3.1.1. Passe o elemento do meio do nó para seu pai.
3.1.2. Divida o nó em dois nós iguais.
25
ÍNDICE
3.2. Se o nó pai estiver cheio, repita as duas linhas acima recursivamente. (Caso todos os
nós-pai estiverem cheios, inclusive a raiz, deve ser criada uma nova raiz aumentando assim a
altura da árvore.
3.3. Somente depois de satisfeita todas as divisões necessárias, insira nova chave.
EXCLUSÃO
1. Primeiro pesquise a chave para ter a certeza de que esta existe na árvore.
2. Se existir, verifique se está em folha, e faça a exclusão.
3. Se existir e não estiver em folha, substitua esta chave pela menor chave do filho a direita.
3.1. Se o número de chave no nó, for maior do que (Ordem/2 - 1), então termine a rotina.
3.2. Senão redistribua as chaves entre os nós vizinhos.
BUSCA
1. Indique a chave que será procurada.
2. Pesquise desde a raiz até encontrá-la, e então retorne o nó e a posição desta.
3. Se a chave não for encontrada, continue o laço até encontrar um nil das folhas.
Exemplo:
Figura 14 - Operações na árvore
Para melhor compreensão do conceito de Árvore B, visite o link http://www.cse.ohiostate.edu/~bondhugu/acads/234-tree/index.shtml e simule a inserção, exclusão e busca de
valores na árvore.
1.3.1.5 HASHING
São particularmente adequados para pesquisas por igualdade. A idéia principal é usar
uma função de hashing. Esta função mapeia um valor da chave de pesquisa (atributo de uma
tabela) em um registro ou balde (bucket) de registros.
26
ÍNDICE
Figura 15 - Hashing
A balde é uma unidade de armazenamento que contém um ou mais registros (um balde
é tipicamente um bloco do disco). Numa organização de ficheiro em hashing, obtemos o
balde de um registro a partir do valor da sua chave de pesquisa. Mais concretamente, o balde é
obtido através do cálculo da função de hash para aquela chave de pesquisa.
A função de hash h é uma função do conjunto de todos os valores da chave K para o
conjunto dos endereços de todos os baldes B. A função de hash é usada para localizar
registros para as operações de acesso, inserção, bem como eliminação. Observe os exemplos
de busca, inserção e exclusão.
A desvantagem do hashing estático pode conduzir a longas cadeias de transbordo.
Busca
Inserção
Exclusão
27
ÍNDICE
Longas cadeias de transbordo podem desenvolver-se e degradar o desempenho.
a) Hashing Estático
O hashing estático escolhe a função hash com base no tamanho do arquivo atual, ou
com base no tamanho antecipado do arquivo com base em um ponto no futuro. Esta técnica
reorganiza periodicamente a estrutura de hash.
b) Hashing Dinâmico
Diferente do hashing estático a função hash é modificada dinamicamente para
acomodar o crescimento ou encolhimento do banco de dados, usando o hashing extensível.
c) Hashing Extensível
Divide e une os buckets enquanto o banco de dados cresce e encurta. A Eficiência do
espaço é mantida e a função hash gera valores por intervalos relativamente grandes. Outra
característica é que os buckets são criados por demanda.
28
DATA MANIPULATION LANGUAGE - DML
3
Parte
DATA MANIPULATION LANGUAGE - DML
U
ma vez criada a estrutura física do banco de dados é necessário popular as tabelas.
Para isso utilizam-se os comandos da DML (Data Manipulation Language). Nesta seara
encontramos comandos como Insert, Update, Delete, Commit e Rollback.
DML(Linguagem de Manipulação de Dados):
Permite a inclusão, remoção, seleção ou atualização de dados armazenados no banco
de dados; Controle de acesso, permitindo proteção dos dados de manipulações não
autorizadas;
3.1 INSERT
Comando responsável por adicionar um ou mais registros na tabela de Banco de
Dados. Os campos que forem omitidos recebem valores NULOS (“NULL”).
Sintaxe:
INSERT [TRANSACTION transaction] INTO <object> [(col [, col …])]
{VALUES (<val> [, <val> …]) | <select_expr>};
<object> = tablename | viewname
<val> = {:variable | <constant> | <expr>
| <function> | udf ([<val> [, <val> …]])
| NULL | USER | RDB$DB_KEY | ?
}
Exemplo:
INSERT INTO clientes (id, nome) VALUES (1, ‘Nome do Cliente’);
NOTE BEM:
 A lista de colunas é opcional no comando Insert;
 Caso não seja definida, assume-se a sequência da criação da tabela;
 Para maior clareza do código e evitar erros em caso de reestruturação da tabela
recomenda-se utilizar a lista de colunas.
 Valores alfanuméricos devem ser especificados entre aspas simples ‘exemplo’;
 Valores numéricos não devem conter esse separador.
29
DATA MANIPULATION LANGUAGE - DML
 Valores do tipo data devem ser tratados de acordo com as especificações do SGBD;
 Ao incluir uma linha no Banco de Dados, o gerenciador checará as restrições de
integridade (Constraints). Assim chaves primárias, estrangeiras, domínios, etc são checadas no
momento da inclusão.
 Caso alguma restrição for violada, a linha não será incluída e uma mensagem será
emitida pelo SGBD.
 As colunas que não tiverem valores atribuídos terão conteúdo NULL ou default, caso
não haja a restrição NOT NULL.
 Para inserir um valor nulo deve informar NULL no lugar correspondente a coluna.
3.1.1 Inserindo Várias Linhas
Para incluir diversas linhas em uma tabela, utiliza-se o comando INSERT em conjunto
com o comando SELECT. Isso irá copiar as linhas de uma tabela para outra; A lista de colunas
do SELECT deve corresponder a totalidade de colunas da tabela.
Exemplo:
INSERT INTO
CURRENT_DATE;
vendas_old
SELECT
*
FROM
vendas
WHERE
data_venda
=
Outra forma é usando o comando INSERT por várias vezes consecutivas acompanhado
do finalizador ponto-e-virgula . Ou também após o comando VALUES colocando os dados
entre parênteses separados por vírgula.
Exemplo:
INSERT INTO vendas_old (prod_id, prov_valor) VALUES (1, 2.00),
(2, 3.00), (3, 5.00), (4, 10.00);
3.1.2 Laboratório
INSERT INTO autor (aut_id, aut_nome) VALUES (1, ‘Renato Russo’);
INSERT INTO autor (aut_id, aut_nome) VALUES (2, ‘Ton Jobim’);
INSERT INTO musica (mus_id, mus_nome, mus_duracao) VALUES (1,
‘sera’, ‘00:02:28’);
INSERT INTO musica (mus_id, mus_nome, mus_duracao) VALUES (2, ‘Ainda
é Cedo’, ‘00:03:55’);
INSERT INTO gravadora (grav_id, grav_nome, grav_tel, grav_contato)
VALUES (1, ‘EMI’, ‘1122334455’, ‘José Vitor’);
INSERT INTO cd(cd_id, cd_nome, cd_preco, dt_lancamento, grav_id)
VALUES (1, ‘Mais do Mesmo’, 20.50, ’21.12.1998’, 1);
3.1.3 Exercícios
a) Popule as tabelas do banco de dados conforme representado abaixo:
30
DATA MANIPULATION LANGUAGE - DML
ATENÇÃO:
Observe a sequência de inserção dos dados.
3.2 UPDATE
Comando responsável alteração de ou um mais registros na tabela de Banco de Dados.
Sintaxe:
UPDATE [TRANSACTION transaction] {table | view}
SET col = <val> [, col = <val> …]
[WHERE <search_condition> | WHERE CURRENT OF cursor];
Exemplo:
UPDATE cliente SET data_inclusao = CURRENT_DATE;
3.2.1 Where
Comando responsável por especificar qual registro da tabela será alterado quando
usado em conjunto com a cláusula UPDATE.
31
DATA MANIPULATION LANGUAGE - DML
NOTE BEM:
 O conteúdo a ser atualizado deve respeitar o tipo de dado da coluna.
 A condição expressa no comando servirá para definir quais linhas devem ser
atualizadas.
 Caso a cláusula WHERE contenha a chave primária somente uma linha da será
atualizada.
 Todas as restrições de integridade (Constraints) serão avaliadas pelo SGBD.
3.2.2 Laboratório
Se quisermos alterar o preço de um CD, podemos utilizar o seguinte comando filtrando
na cláusula WHERE a chave primária da tabela CD:
Exemplo:
UPDATE cd SET cd_preco = 21,00 WHERE cd_id = 1;
Se o aumento for para todos os CDs de uma determinada gravadora, utilizaremos como
filtro da cláusula WHERE grav_id:
Exemplo:
UPDATE cd SET cd_preco = 22,00 WHERE grav_id = 1;
Imagine que ocorra um aumento de preço generalizado para todos os CDs em 5%.
Assim multiplica-se o próprio preço do CD por 1,05.
Exemplo:
UPDATE cd SET cd_preco = cd_preco * 1,05;
Outros Exemplos:
UPDATE cd SET cd_preco = 15 WHERE cd_id = 1;
UPDATE autor SET aut_nome = ‘B. Manilow’ WHERE aut_id = 2;
UPDATE cd SET cd_preco = cd_preco * 1,10 WHERE grav_id = 3;
UPDATE gravadora SET grav_nome = ‘E.M.I’, grav_tel = ‘2144332211’
WHERE grav_id = 1;
3.3 DELETE
Comando responsável pela exclusão de ou um mais registros na tabela de Banco de
Dados.
Sintaxe:
DELETE [TRANSACTION transaciona] FROM table
{[WHERE <search_condition>] | WHERE CURRENT OF cursor};
32
DATA MANIPULATION LANGUAGE - DML
Exemplo:
DELETE FROM VENDAS WHERE DATA_VENDA <= ‘01-01-1999’;
3.3.1 Where
Comando responsável por especificar qual registro da tabela será excluído quando
usado em conjunto com a cláusula DELETE.
NOTE BEM:
 O comando DELETE pode, da mesma forma que o comando UPDATE, afetar uma ou
mais linhas de uma tabela. Desta forma o escopo da exclusão será definido pela cláusula
WHERE.
 Se for especificado na cláusula WHERE a chave primária, somente uma linha será
excluída.
 Antes de uma exclusão, o gerenciador sempre fará uma checagem na integridade
referencial.
3.4 CONTROLE BÁSICO DE TRANSAÇÕES
Uma vez realizadas as operações de inclusão, alteração e exclusão, a modificação
realizada não estará ainda no banco de dados. Ela é visível apenas na seção atual.
Para que os comandos de Insert, Update e Delete sejam enviados para o banco é
necessário utilizar os comandos COMMIT ou descartados pelo comando ROLLBACK.
3.5 LABORATÓRIO
Neste primeiro exemplo, apenas o autor com código 1 será excluído (desde que não
haja relacionamentos com outras tabelas):
Exemplo:
DELETE FROM autor WHERE aut_id = 1;
Neste caso todos os CD’s da gravadora 2 serão excluídos:
Exemplo:
DELETE FROM cd WHERE grav_id = 2;
33
PESQUISA BÁSICA EM TABELAS
4
Parte
PESQUISA BÁSICA EM TABELAS
A
gora que já sabemos criar, incluir, alterar e excluir informações nas tabelas, veja como
podemos extrair informações do banco de dados. Para tanto o comando utilizado é o
SELECT. Junto dele há uma imensa possibilidade de comandos como veremos a
seguir.
4.1 SELECT
É o comando utilizado para realizar buscas/pesquisas no banco de dados. Atrás dele há
uma extensão de possibilidades que vão desde a simples extração do conteúdo de todas as
linhas e colunas de uma tabela até a união de diversas tabelas, cálculos, agrupamentos,
ordenações e filtragem de linhas e colunas.
Sintaxe:
SELECT [DISTINCT | ALL] {* | Coluna [, coluna, ...]}
FROM tabela
Onde:
DISTINCT: Não mostra eventuais valores repetidos;
ALL: Mostra todos os valores (Propriedade Default do comando select);
*: Mostra todas as colunas da tabela;
NOTE BEM:
Para execução dos exemplos utilizaremos estrutura criada na parte 3 – DML.
Exemplos:
O exemplo mais simples de uma consulta é extrair todas as informações de uma tabela:
SELECT * FROM cd;
O exemplo a seguir demonstra como filtrar apenas algumas colunas da tabela:
SELECT cd_id, cd_nome FROM cd;
SELECT grav_id, grav_nome, grav_tel FROM gravadora;
34
PESQUISA BÁSICA EM TABELAS
4.1.1 Ordenando o resultado
Em muitas situações a ordem mostrada nem sempre é a que esperamos. Para que os
dados sejam mostrados da maneira que melhor nos atende usa-se a cláusula ORDER BY
seguida pela coluna que desejamos que seja ordenada:
Exemplo:
SELECT aut_id, aut_nome FROM autor
ORDER BY aut_nome;
SELECT cd_id, cd_nome FROM cd
ORDER BY cd_id;
Se forem especificadas mais de uma coluna a serem ordenadas, o gerenciador primeiro
ordenará pela primeira coluna e em seguida pelas demais:
Exemplo:
SELECT grav_id, cd_nome FROM cd
ORDER BY grav_id, cd_nome;
4.1.2. Filtrando linhas
Para filtrar linhas em uma pesquisa, utilizamos a cláusula WHERE. Assim, é definida
uma expressão lógica (condição) que será validada e mostrará apenas as linhas que
atenderem ao critério estabelecido.
Sintaxe:
SELECT [DISTINCT | ALL] {* | Coluna [, coluna, ...]}
FROM tabela
WHERE condição
Para um resultado satisfatório devemos saber exatamente como construir condições
que satisfaçam às nossas necessidades de busca para atingir nossos objetivos. Sempre que a
condição especificada for verdadeira o resultado será mostrado. Para tanto é necessário
utilizar-se de alguns operadores como veremos a seguir.
4.1.2.1 OPERADORES RELACIONAIS
Estes operadores devem ser usados na definição das condições. Podemos testar
igualdade, diferença, maior, menor, maior ou igual, menor ou igual. Os operadores devem ser
colocados entre os argumentos que estão sendo comparados.
OPERADOR
SIGNIFICADO
EXEMPLO
=
Igual
aut_id = 2
<
Menos que
cd_preco < 10
<=
Menor ou igual a
cd_preco <= 10
35
PESQUISA BÁSICA EM TABELAS
>
Maior que
cd_preco > 10
>=
Maior ou igual a
cd_preco <= 10
Diferente
aut_id != 2 ou aut_id <> 2
!= ou <>
Exemplo:
SELECT cd_nome, cd_preco FROM cd
WHERE cd_preco > 12;
NOTE BEM:
 Da mesma forma que podemos comparar uma coluna com um valor, podemos
comparar com outra coluna.
 Sempre quando fazemos esse tipo de comparação, devemos obedecer ao tipo de dado
que estamos comparando.
4.1.2.2 OPERADORES LÓGICOS
Muitas vezes, apenas uma condição não é suficiente para determinarmos o critério de
busca. Sempre que isso ocorrer, podemos utilizar operadores lógicos.
OPERADOR
SIGNIFICADO
EXEMPLO
AND
e
Condição-1 AND Condição-2
OR
ou
Condição-1 OR Condição-2
NOT ou !
não/negação
NOT Condição
AND
Indica que as duas condições devem ser verdadeiras para que seja mostrada a linha.
Exemplo:
SELECT cd_nome, cd_preco, grav_id FROM cd
WHERE cd_preco > 10 AND grav_id = 2;
OR
Utilizamos o operador OR sempre que quisermos que o resultado final seja verdadeiro.
Exemplo:
SELECT cd_nome, cd_preco, grav_id FROM cd
WHERE cd_preco > 11 OR grav_id = 2
NOTE BEM
 Não há limitação no uso e na combinação de condições usando OR e AND.
 É conveniente utilizar parênteses para determinar o que se quer comparar.
36
PESQUISA BÁSICA EM TABELAS
Exemplo:
SELECT cd_nome, grav_id, cd_preco FROM cd
WHERE (grav_id = 2 OR grav_id = 3) AND (cd_preco >= 17.50)
NOT ou !
É utilizado para inverter o resultado de uma expressão lógica, negando o resultado da
condição. Caso a condição seja verdadeira, será retornado falso e vice-versa.
Exemplo:
SELECT cd_nome, cd_preco FROM cd
WHERE NOT (cd_preco > 15);
4.1.2.3 OPERADORES ESPECIAIS
Existem alguns operadores que são utilizados para determinar melhor as linhas que
queremos filtrar. São eles: IS NULL, IS NOT NULL, BETWEEN, LIKE e IN.
IS NULL
Sabemos que nem todas as colunas têm valores inicializados. Logo esse comando é
utilizado para saber os campos que não foram inicializados:
Exemplo:
SELECT * FROM gravadora
WHERE grav_tel IS NULL
IS NOT NULL
Compara a negação do comando anterior. Somente aqueles que tiverem conteúdo
serão mostrados:
Exemplo:
SELECT * FROM gravadora
WHERE grav_tel IS NOT NULL
BETWEEN
Esse operador serve para determinar um intervalo de busca. Quando desejarmos um
intervalo entre números, datas, etc, utilizaremos o BETWEEN para simplificar a forma de
escrevermos o comando. Normalmente é utilizado em conjunto com o AND.
Exemplo:
SELECT cd_nome, cd_dt_lancamento FROM cd
WHERE cd.cd_dt_lancamento BETWEEN '1979-01-01' AND '2000-12-31';
37
PESQUISA BÁSICA EM TABELAS
LIKE
Com esse operador podemos comparar cadeias de caracteres utilizando padrões de
comparação para um ou mais caracteres. O caractere percentual (%) substitui zero, um ou
mais caracteres e sublinha (_) substitui um caractere.
EXPRESSÃO
APLICAÇÃO
LIKE ‘A%’
Todas as palavras que iniciem com a letra A
LIKE ‘%A’
Todas as palavras que terminem com a letra A
LIKE ‘%A%’
Todas as palavras que tenham a letra A em qualquer posição
LIKE ‘A_’
String de dois caracteres que tenha a primeira letra A
LIKE ‘_A’
String de dois caracteres que tenha o último caractere letra A
LIKE ‘_A_’
String de três caracteres cuja segunda letra seja A
LIKE ‘%A_’
Todas as palavras que tenham a letra A na penúltima posição
LIKE ‘_A%’
Todas as palavras que tenha a letra A na segunda posição
Exemplos:
SELECT * FROM autor
WHERE aut_nome LIKE 'R%';
SELECT * FROM gravadora
WHERE grav_nome LIKE '_o%';
Um problema que pode surgir quando queremos fazer buscas utilizando os caracteres
de substituição é tê-los na cadeia de caracteres que está sendo pesquisada. Neste caso
devemos usar um caractere especial denominado ESCAPE.
Exemplo:
SELECT * FROM cd
WHERE cd_nome LIKE '%\_%'ESCAPE '\';
IN
Permite comparar o valor de uma coluna com um conjunto de valores. Utilizamos para
substituir uma série de comparações seguidas da cláusula OR.
Exemplo:
SELECT * FROM autor
WHERE aut_id IN (1, 3);
Sua maior utilização é em subquerys (será visto posteriormente).
38
PESQUISA BÁSICA EM TABELAS
4.2. EXERCÍCIOS
a) Liste todos os campos e linhas da tabela GRAVADORA;
b) Liste todas as linhas dos campos CD_ID, CD_NOME, CD_PRECO da tabela CD;
c) Liste todas as linhas dos campos AUT_ID, AUT_NOME da tabela AUTOR em ordem
alfabética;
d) Repita o comando anterior em ordem alfabética decrescente;
e) Liste todos os CDs da gravadora 3;
f)
Liste as colunas CD_NOME, CD_PRECO dos CD’s cujos preços de venda sejam
inferiores a 20,00 e sejam da GRAVADORA 3;
g) Liste as colunas da tabela gravadora cujo GRAV_CONTATO seja nulo;
h) Repita o comando anterior desta vez listando GRAV_CONTADO não nulo;
i)
Liste os CD’s cujos CD_PRECO esteja entre 15,00 e 30,00;
j)
Liste todos os CD’s cuja CD_DT_LANCAMENTO seja posterior ao ano 01/01/2000;
k) Liste as MUSICAS cujo nome comece com A da tabela MUSICA;
l)
Liste os CD’s cuja segunda letra do CD_NOME seja a letra E;
m) Liste os CD’s que possuam a letra O em qualquer posição do CD_NOME;
n) Liste os CD’s que possuam CD_PRECO inferior a 30,00 em ordem decrescente de
CD_DT_LANCAMENTO;
o) Liste as músicas cuja MUS_ID seja 1, 3, 5;
39
CÁLCULOS E FUNÇÕES USUAIS
5
Parte
CÁLCULOS E FUNÇÕES USUAIS
U
m recurso bastante útil e importante de SQL é a possibilidade de se realizar cálculos e
totalizações de valores unitários. Podemos realizar cálculos quando realizamos buscas
no banco de dados simplesmente aplicando um dos operadores aritméticos a coluna.
Exemplo:
Um aumento de 5% em todos os CD’s:
SELECT cd_id, cd_nome, cd_preco, preco_venda * 1.05
FROM cd;
Em alguns Sistemas Gerenciadores de Banco de Dados existem outras funções além
das aritméticas, como a função POWER do Oracle e Sysbase que permite calcular
exponenciação.
5.1 OPERADORES
Estes são os operadores utilizados no SQL:
OPERADOR
SIGNIFICADO
+
Soma
-
Subtração
*
Multiplicação
/
Divisão
A precedência é igual a da matemática, ou seja, a multiplicação e a divisão têm prioridade
sobre a soma e subtração.
Para alterar a prioridade deve-se utilizar parênteses (Operação).
Exemplo:
15 / 5 * 3, Resultado = 9
15 / (5 * 3), Resultado = 1
SELECT cd_id, cd_nome, cd_preco - (cd_preco * 0.10)
FROM cd
40
CÁLCULOS E FUNÇÕES USUAIS
5.1.1. CHARACTER_LENGTH / LENGTH
Retorna o número de caracteres contidos em uma cadeia de caracteres. Esta função não
existe em todos os SGBD, como acontece no Firebird.
Exemplo:
SELECT CHARACTER_LENGTH (‘Renato Russo’);
SELECT CHARACTER_LENGTH (cd_nome)
FROM cd
WHERE cd_id = 10;
5.1.2. Alfanuméricos
Em SQL é possível concatenar alfanuméricos e existem algumas funções para manipulá-los.
Para concatenar caracteres, utiliza-se dois pipes ( || ).
Exemplo:
SELECT grav_id || ' - ' || grav_contato
FROM gravadora;
5.1.3. UPPER e LOWER
Ao realizar buscas alfanuméricas no banco de dados notamos que os parâmetros são case
sensitive’s. Assim sendo, é possível usar os comandos UPPER E LOWER para transformar a
cadeia de caracteres para maiúsculo e/ou minúsculo.
Exemplo:
SELECT * FROM autor
WHERE UPPER(aut_nome) = ‘RENATO RUSSO’;
SELECT UPPER(aut_nome) FROM autor;
5.1.4. SUBSTRING
Essa função é requisito para SQL-92 ao nível intermediário. Retorna uma parte da cadeia de
caracteres.
Exemplo:
SELECT SUBSTRING(aut_nome from 1 FOR 3), aut_nome
FROM autor;
5.1.5. Manipulação de Datas
Em colunas do tipo data podemos realizar uma série de cálculos e operações cronológicas,
como calcular o número de dias entre duas datas, somar, subtrair dias, meses etc.
O padrão especifica 04 (quatro) tipos de dados relacionados data e hora:
41
CÁLCULOS E FUNÇÕES USUAIS
Tipo
Date
Time
Timestamp
Interval
Descrição
Apenas data
Apenas Hora
Data/Hora
Intervalo entre dois tipos
O padrão SQL definiu algumas funções acrescentando CURRENT_:
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP
Exemplos:
SELECT * FROM cd
WHERE cd_dt_lancamento = CURRENT_DATE;
Para saber a quantidade de dias que já se passaram depois do lançamento do CD usaríamos o
comando:
SELECT CURRENT_DATE, cd_dt_lancamento, (CURRENT_DATE –
cd_dt_lancamento) FROM cd
Adicionar dias em uma data:
SELECT cd_dt_lancamento + 7 FROM cd;
SELECT cd_dt_lancamento + INTERVAL ‘7 DAY’
FROM cd;
5.1.6. EXTRACT
Essa função extrai e retorna um valor de um campo do tipo data. É possível extrair apenas o
dia, o mês, o ano, a hora, etc.
Exemplo:
SELECT cd_dt_lancamento, EXTRACT(MONTH FROM cd_dt_lancamento)
FROM cd;
SELECT cd_dt_lancamento, EXTRACT(DAY FROM cd_dt_lancamento)
FROM cd;
Caso seja necessário realizar a concatenação de dados do tipo DATE é necessário utilizar o
comando CAST para converter o tipo do dado para um tipo STRING.
SELECT cd_dt_lancamento, CAST(EXTRACT(MONTH FROM
cd_dt_lancamento) AS VARCHAR) ||'/'||
CAST(EXTRACT(DAY FROM cd_dt_lancamento) AS VARCHAR) FROM cd;
42
CÁLCULOS E FUNÇÕES USUAIS
5.2. EXERCÍCIOS
a. Monte uma pesquisa que mostre a data atual.
b. Escreva uma busca que mostre CD_NOME, CD_PRECO e CD_PRECO com 20% de
aumento.
c. Escreva uma busca igual à anterior, porém acrescente uma coluna mostrando a diferença
entre o CD_PRECO e CD_PRECO com 20% de aumento.
d. Escreva uma busca que mostre GRAV_NOME, GRAV_CONTATO em uma única coluna
separados por um hífen.
e. Escreva uma busca que mostre todos os autores que tenham a letra A no nome.
f. Escreva uma busca que mostre a primeira letra da MUS_NOME e a MUS_DURACAO da
tabela música.
g. Escreva uma busca que mostre o CD_NOME e o número de dias entre a data atual e a data
de lançamento.
h. Escreva uma busca semelhante à anterior que mostre uma coluna com 15 dias após a
CD_DT_LANCAMENTO.
i. Escreva uma busca que retorne o ano de lançamento do CD_ID = 1, 2 e 3;
j. Escreva uma consulta que retorne os 05 primeiro caracteres de MUS_NOME e outra coluna
com MUS_NOME.
43
PESQUISA EM MÚLTIPLAS TABELAS
6
Parte
PESQUISA EM MÚLTIPLAS TABELAS
N
os capítulos anteriores as pesquisas foram realizadas em apenas uma tabela. No
entanto, observamos no modelo criado, assim como acontece sempre, que uma tabela
está relacionada à outra, sendo necessário extrair informações de duas ou mais tabelas
relacionadas ao mesmo tempo. Esta tarefa recebe o nome de união ou junção de tabelas.
Isso se dá por meio das chaves primárias e chaves estrangeiras, que são no modelo
físico as colunas que as tabelas têm em comum.
Para os exemplos utilizados nesta aula é fundamental fazer uso do Modelo Lógico para
melhor visualizar os relacionamentos.
Figura 16 - Modelo Lógico de Dados
44
PESQUISA EM MÚLTIPLAS TABELAS
6.1. ALIAS
Ao realizar pesquisa em diversas tabelas são referenciadas colunas pertencentes às
tabelas da relação. Dessa forma, é necessário distinguir a qual tabela pertence à dada coluna.
Podemos fazer isso de duas formas:
a) Colocando o nome da tabela na frente do nome da coluna.
Sintaxe:
SELECT tabela.coluna
FROM tabela;
b) Utilizando um alias ou apelido para a tabela e colocando-o na frente da coluna.
Sintaxe:
SELECT t.coluna
FROM tabela t;
É ainda possível usar alias em nome de colunas, fazendo uso da cláusula AS;
Sintaxe:
SELECT coluna AS nome_desejado
FROM tabela;
6.2. UNIÃO DE TABELAS
Para realizar a união de tabelas basta acrescentar após a cláusula FROM do comando
SELECT as tabelas que queremos unir utilizando após a cláusula WHERE a condição de união
entre elas (Chave Primária e Chave Estrangeira).
Sintaxe:
SELECT [tabela1.]coluna [, [tabela2.]coluna, ...]
FROM tabela1, tabela2 [, ...]
WHERE tabela1.PK = tabela2.FK
Uma prática recomendável é utilizar o mesmo nome nos campos de chave primária e
chave estrangeira, pois dessa forma facilita a identificação dos campos ao realizar a união das
tabelas.
É possível colocar diversas tabelas na cláusula FROM. Não devemos esquecer, porém,
que é necessário especificar as chaves primárias e chaves estrangeiras de cada
relacionamento.
Em caso de dúvidas, consulte o Modelo de Dados para estabelecer quais são as
colunas comuns entre as tabelas. Daí a importância de se ter um bom projeto de Banco de
Dados claro e preciso.
A cláusula WHERE deve conter todo canal de relacionamento entre as tabelas listadas
na cláusula FROM sob pena de ocorrer o Produto Cartesiano.
45
PESQUISA EM MÚLTIPLAS TABELAS
6.3. PRODUTO CARTESIANO
Ocorrerá um produto cartesiano sempre que:
- A condição de união entre as tabelas for omitida (não houver cláusula WHERE);
- Condição de união entre as tabelas for inválida (cláusula WHERE incorreta);
- Todas as linhas da primeira tabela estiverem unidas a todas as linhas da segunda
tabela.
- Nesta situação, as linhas da primeira tabela serão combinadas com as linhas da
segunda, demonstrando um resultado na maior parte das vezes indesejado.
- Esse procedimento é muito utilizado para efetuar testes de performance no banco
devido a grande quantidade dedados.
Exemplo:
SELECT cd.cd_id, cd.cd_nome, gravadora.grav_nome
FROM gravadora, cd
Ao invés de se mostrar todos os dados, deseja-se visualizar os CD’s e suas respectivas
gravadoras. Emprega-se então, a União entre tabelas.
Exemplo:
SELECT cd.cd_id, cd.cd_nome, gravadora.grav_nome
FROM gravadora, cd
WHERE cd.grav_id = gravadora.grav_id
Usando alias:
SELECT c.cd_id, c.cd_nome, g.grav_nome
FROM gravadora g, cd c
WHERE c.grav_id = g.grav_id
6.4. UNIÃO REGULAR (INNER JOIN OU EQUI-JOIN)
Denomina-se união regular as uniões que têm a cláusula WHERE unindo chave
primária e chave estrangeira como visto anteriormente.
O padrão determina uma sintaxe alternativa para esse comando. Quando a chave
primária e a chave estrangeira têm o mesmo nome em ambas as tabelas é possível
simplificar o comando usando o comando NATURAL JOIN.
Exemplo:
SELECT cd.cd_id, cd.cd_nome, gravadora.grav_nome
FROM cd
NATURAL JOIN gravadora;
Outras duas maneiras definidas no padrão SQL é determinar qual(is) coluna(s) utilizar
na união usando a cláusula USING e caso o nome das colunas não sejam iguais, determinar
quais são as colunas com a cláusula ON. Ambas produzindo o mesmo efeito.
46
PESQUISA EM MÚLTIPLAS TABELAS
Exemplo:
SELECT cd.cd_id, cd.cd_nome, gravadora.grav_nome
FROM cd JOIN gravadora USING (grav_id);
SELECT cd.cd_id, cd.cd_nome, gravadora.grav_nome
FROM cd JOIN gravadora ON gravadora.grav_id = cd.grav_id;
6.5. UNIÃO DE MAIS DE DUAS TABELAS
Frequentemente é necessário unir mais de duas tabelas para obter uma informação
consistente.
Suponha que queira saber o nome da música, a faixa e o nome do CD em que está a
música. No modelo de dados observamos a necessidade de se consultar três tabelas no Banco
de Dados.
Exemplo:
SELECT c.cd_nome, f.fai_numero, m.mus_nome
FROM cd c, faixa f, musica m
WHERE c.cd_id = f.cd_id AND
m.mus_id = f.mus_id
O código abaixo produziria o mesmo efeito:
SELECT c.cd_nome, f.fai_numero, m.mus_nome
FROM faixa f
INNER JOIN cd c ON c.cd_id = f.cd_id
INNER JOIN musica m ON m.mus_id = f.mus_id
Observe que é possível realizar a união de tantas tabelas quanto forem necessárias.
SELECT a.aut_nome, g.grav_nome
FROM autor a, cd c, gravadora g, faixa f, musica_autor ma, musica
m
WHERE a.aut_id = ma.aut_id AND
m.mus_id = ma.mus_id AND
m.mus_id = f.mus_id AND
c.cd_id = f.mus_id AND
g.grav_id = c.grav_id
6.6. UNIÕES EXTERNAS (OUTER-JOIN)
Observa-se na união entre tabelas que quando uma linha não satisfaz a condição de
união entre as tabelas, ela não será mostrada no resultado da busca. Isto acontece porque o
banco de dados, não podendo estabelecer a relação entre as colunas que estão sendo unidas
na busca, coloca NULL onde o dado não existe.
47
PESQUISA EM MÚLTIPLAS TABELAS
Exemplo:
SELECT cd.cd_id, cd.cd_nome, gravadora.grav_nome
FROM gravadora, cd
WHERE cd.grav_id = gravadora.grav_id
No resultado observamos que alguns dados de gravadora são omitidos.
Define-se União Externa como aquela que inclui linhas no resultado da busca mesmo
que não haja relação entre as duas tabelas.
Para que os dados omitidos sejam mostrados utilizaremos o comando SELECT
utilizando União Externa.
6.6.1. União Externa à Esquerda (Left Outer-Join)
Como o nome diz, a união pela esquerda incluirá todas as linhas da primeira tabela na
expressão, ou seja a tabela da esquerda será a tabela base para a união.
Observe esse comando e depois inverta a posição das tabelas GRAVADORA e CD.
Exemplo:
SELECT cd.cd_id, cd.cd_nome, gravadora.grav_nome
FROM gravadora
LEFT OUTER JOIN cd ON (gravadora.grav_id = cd.grav_id)
Neste caso, todas as gravadoras são mostradas independente de haver ou não dados
relacionados na tabela CD.
6.6.2. União Externa à Direita (Right Outer-join)
Ao contrário da anterior, a união é realizada pela direita, pois incluirá todas as linhas da
tabela da direita na expressão da união, ou seja a tabela da direita será a tabela base para a
união.
Exemplo:
SELECT cd.cd_id, cd.cd_nome, gravadora.grav_nome
FROM gravadora
RIGHT OUTER JOIN cd ON (gravadora.grav_id = cd.grav_id)
NOTA:
Se a tabela da referência (direita ou esquerda) for a que contém a chave estrangeira,
ocorrerá uma união regular entre as tabelas.
6.6.3. União Externa Total (Full Outer-join)
Realiza uma união independente da coluna opcional estar à direita ou à esquerda.
Neste caso, são geradas linhas que não existem em uma ou em ambas as tabelas.
48
PESQUISA EM MÚLTIPLAS TABELAS
Exemplo:
SELECT cd.cd_id, cd.cd_nome, gravadora.grav_nome
FROM gravadora
FULL OUTER JOIN cd ON (gravadora.grav_id = cd.grav_id)
Neste caso a consulta age como uma união externa a direita ou a esquerda por não
haver campos nulos na coluna de chave estrangeira de CD.
6.7. EXERCÍCIOS
a. Faça uma busca que mostre todos os CD’s e o nome de suas respectivas
gravadoras.
b. Faça uma pesquisa que mostre o produto cartesiano das tabelas MUSICA e CD.
c. Realize uma consulta se utilizando da sintaxe JOIN que mostre todas MUSICAS e o
nome de seus respectivos CD’s.
d. Crie uma pesquisa que retorne como resultado todas as gravadoras e seus
respectivos CD’s mesmo que estes não possuam CD’s associados.
e. Crie uma pesquisa que mostre o AUTOR e suas MUSICAS ordenadas por AUTOR.
f. Acrescente a pesquisa anterior o nome do CD que foi gravado a música.
g. Crie uma pesquisa que mostre em quais gravadoras os autores tem realizado suas
gravações.
49
FUNÇÕES DE GRUPO E AGRUPAMENTO
7
Parte
FUNÇÕES DE GRUPO E AGRUPAMENTO
A
té agora trabalhamos com funções que tratavam apenas de uma linha. Contudo, em
diversas situações é necessário trabalhar com várias linhas da consulta. Dessa forma, é
necessário conhecer funções especiais denominadas funções de grupo e agrupamento.
7.1. FUNÇÕES DE GRUPO
Funções de grupo operam conjuntos de linhas visando a fornecer um resultado para o grupo.
Estes grupos podem ser constituídos desde toda a tabela até subgrupos da tabela. Existem
diversas funções de grupo que são implementadas pelo padrão SQL.
As principais funções são:
FUNÇÃO
COUNT
SUM
AVG
MIN
MAX
STDDEV
VARIANCE
DESCRIÇÃO
Retorna número de linhas afetadas pelo comando.
Retorna o somatório do valor das colunas especificadas
Retorna a média aritmética dos valores das colunas
Retorna o menor valor da coluna de um grupo de linhas
Retorna o maior valor da coluna de um grupo de linhas
Retorna o desvio-padrão da coluna
Retorna a variância da coluna
7.1.1. COUNT
Diferente das outras funções de grupo, o COUNT retorna o número de linhas que
atende a uma determinada condição.
Podemos utilizá-lo com um asterisco entre parênteses, para indicar que queremos saber
a quantidade total de linhas independente de haver linhas com colunas nulas ou não.
Exemplos:
SELECT COUNT(*)
FROM gravadora;
SELECT COUNT(grav_contato)
FROM gravadora;
Efeito semelhante pode ser conseguido com o seguinte comando:
50
FUNÇÕES DE GRUPO E AGRUPAMENTO
Exemplos:
SELECT COUNT(grav_contato)
FROM gravadora
WHERE grav_contato IS NOT NULL;
Diferente de:
SELECT COUNT(*)
FROM gravadora
WHERE grav_contato IS NULL;
Outra maneira é utilizando o comando DISTINCT. Observe a diferença entre os dois
comandos:
Exemplos:
SELECT COUNT(DISTINCT aut_id)
FROM musica_autor;
SELECT COUNT(aut_id)
FROM musica_autor;
7.1.2. SUM
Retorna o valor total de uma determinada coluna em um determinado grupo de linhas.
Assim se quisermos saber o total do preço de venda dos CD’s, utilizamos o comando:
Exemplo:
SELECT SUM(cd_preco)
FROM cd;
É possível realizar cálculos baseados na somatória ou mesmo incluir outras colunas e
operações no comando.
Exemplo:
SELECT SUM(cd_preco) * 1.2
FROM cd;
7.1.3. AVG
Extrai a média aritmética de um determinado grupo de linhas.
Para saber o preço médio dos CD’s da loja execute o seguinte comando:
Exemplo:
SELECT AVG(cd_preco)
FROM cd;
51
FUNÇÕES DE GRUPO E AGRUPAMENTO
7.1.4. MIN
Retorna o menor valor de uma coluna em um grupo de linhas. Podemos utilizá-la em
colunas do tipo data ou alfanuméricas.
Para saber o preço de venda do CD mais barato execute o seguinte comando:
Exemplos:
SELECT MIN(cd_preco)
FROM cd;
SELECT MIN(cd_dt_lancamento)
FROM cd;
SELECT MIN(cd_nome)
FROM cd;
7.1.5. MAX
Retorna o maior valor de uma coluna em um grupo de linhas. Podemos utilizá-la em
colunas do tipo data ou alfanuméricas.
Para saber o preço de venda do CD mais caro execute o seguinte comando:
Exemplos:
SELECT MAX(cd_preco)
FROM cd;
SELECT MAX(cd_dt_lancamento)
FROM cd;
SELECT MAX(cd_nome)
FROM cd;
7.1.6. STDDEV (Não funciona em alguns bancos de dados.)
Retorna o desvio padrão de uma determinada coluna.
Para saber o desvio padrão dos preços de venda dos CD’s da loja execute o seguinte
comando:
Exemplo:
SELECT STDDEV(cd_preco)
FROM cd;
7.1.7. VARIANCE (Não funciona em alguns bancos de dados.)
Retorna a variância de uma determinada coluna.
52
FUNÇÕES DE GRUPO E AGRUPAMENTO
Para saber a variância do preço de venda dos CD’s da loja execute o seguinte
comando:
Exemplo:
SELECT VARIANCE(cd_preco)
FROM cd;
7.2. AGRUPANDO RESULTADOS
É possível agrupar valores com base em determinadas colunas. Desta forma,
estaremos trabalhando um pequeno grupo de dados. Para isso usamos a cláusula GROUP BY.
7.2.1. GROUP BY
O comando GROUP BY deve vir antes da cláusula ORDER BY e depois do WHERE (se
houver necessidade de utilizá-lo). É utilizado com uma função de grupo (COUNT, SUM, AVG,
MIN ou MAX).
Para saber quantas músicas há em cada CD:
Exemplo:
SELECT cd_id, COUNT(*)
FROM faixa
GROUP BY cd_id;
Para o preço médio de venda de cada CD agrupado por gravadora:
Exemplo:
SELECT grav_id, AVG(cd_preco)
FROM cd
GROUP BY grav_id;
É possível realizar mais de uma função de grupo dentro de um mesmo SELECT. Neste
exemplo, além do preço de venda, temos a quantidade de CD’s de cada gravadora:
Exemplo:
SELECT grav_id, AVG(cd_preco), COUNT(*)
FROM cd
GROUP BY grav_id;
7.3. AGRUPAMENTOS COM MAIS DE UMA TABELA
Podemos unir mais de uma tabela com as regras já citadas.
53
FUNÇÕES DE GRUPO E AGRUPAMENTO
Exemplo:
SELECT c.grav_id, g.grav_nome, AVG(c.cd_preco)
FROM cd c, gravadora g
WHERE c. grav_id = g.grav_id
GROUP BY c.grav_id, g.grav_nome
NOTA
É necessário colocar todas as colunas que fazem parte do comando SELECT na cláusula
GROUP BY.
Caso se deseje ordenar o resultado, basta acrescentar o comando ORDER BY no final do
comando após o comando GROUP BY:
Exemplo:
SELECT c.grav_id, g.grav_nome, AVG(c.cd_preco)
FROM cd c, gravadora g
WHERE c. grav_id = g.grav_id
GROUP BY c.grav_id, g.grav_nome
ORDER BY g.grav_nome desc
7.4. RESTRINGINDO O RESULTADO
Em algumas situações os dados agrupados podem ser restringidos ou filtrados. Até
agora vimos que toda tabela era afetada pelo comando GROUP BY. Contudo, nem sempre
isso é desejável.
Para isso podemos utilizar a cláusula WHERE em conjunto com GROUP BY ou utilizar
a cláusula HAVING.
Retornar o total de autores agrupados por MUS_ID sendo este menor que 3:
Exemplo:
SELECT mus_id, COUNT(*)
FROM musica_autor
WHERE mus_id < 3
GROUP BY mus_id;
ou
SELECT mus_id, COUNT(*)
FROM musica_autor
GROUP BY mus_id
HAVING mus_id < 3;
NOTE BEM
Usando o filtro da cláusula WHERE as linhas são filtradas antes do agrupamento;
Usando HAVING, as linhas são filtradas depois do agrupamento.
A única restrição é que a cláusula HAVING só pode usar as colunas que fazem parte do
GROUP BY. Para o WHERE isso não acontece.
Nunca use na cláusula WHERE uma função de grupo para filtrar os grupos. Dessa forma, o
comando abaixo é inválido:
54
FUNÇÕES DE GRUPO E AGRUPAMENTO
Exemplos:
SELECT GRAV_ID, AVG(cd_preco) FROM cd
WHERE AVG(cd_preco) > 12
GROUP BY grav_id
A forma correta seria:
SELECT grav_id, AVG(cd_preco) FROM cd
GROUP BY grav_id
HAVING AVG(cd_preco) > 12
7.5. EXERCÍCIOS
a. Verifique o maior e menor valor entre os preços de venda dos CD’s.
b. Verifique a diferença entre o maior e menor valor dos preços de venda dos CD’s.
c. Verifique a quantidade de dias compreendidos entre a data mais atual e a data mais
antiga das datas de lançamento dos CD’s;
d. Mostre através de uma consulta o tempo total do CD 1 (cd_id = 1);
e. Mostre através de uma consulta a média de duração das músicas cujo nome comece
com a letra A;
f. Faça uma consulta que retorne a quantidade de CD’s cadastrados.
g. Faça uma consulta que retorne a o nome do CD e a média de duração das músicas
em cada CD, desde que esta média não seja superior a 4.
h. Faça uma consulta que retorne os autores que são autores de mais de uma música.
55
SUBCONSULTAS
8
Parte
SUBCONSULTAS – SUBQUERIES
N
as sessões anteriores exploramos largamente o comando SELECT utilizando-o em
conjunto com uma gama de funções. Ainda neste contexto, é possível incluir
subconsultas dentro das consultas principais.
8.1. SUBQUERY
As subqueries fazem parte do padrão SQL-86 logo, todos os bancos de dados
relacionais que utilizam SQL devem permitir essa utilização.
Sintaxe:
SELECT colunas
FROM tabela
WHERE expressão operador (SELECT colunas FROM tabela
WHERE ...)
Há três tipos de subquery:
a) Subquery de uma linha: o retorno do SELECT interno será uma única linha.
b) Subquery de múltiplas linhas: o retorno do SELECT interno será mais de uma
linha.
c) Subquery de múltiplas colunas: o retorno do SELECT interno será conterá mais de
uma linha e coluna.
8.1.1. Subquery de uma linha
Neste caso, primeiro é realizada a busca em uma tabela e, com base no resultado,
pesquisada a outra tabela.
Exemplo:
SELECT cd_nome, cd_preco FROM cd
WHERE cd_preco > (SELECT AVG(cd_preco) FROM cd)
Acima é utilizada uma subquery para verificar a média de preço dos CD’s e, com base
nesse resultado, é extraído o resultado da busca do primeiro SELECT.
56
SUBCONSULTAS
Exemplo:
SELECT grav_id, cd_nome, cd_preco FROM cd c
WHERE cd_preco > (SELECT AVG(cd_preco) FROM cd
WHERE grav_id = c.grav_id)
Acima são buscados apenas os CD’s que tenham preço de venda superior a média da
própria gravadora.
Muito cuidado ao usar essa construção, porque ele tende a consumir muito recurso do
banco de dados, pois a cada linha do primeiro SELECT será extraído a média no segundo
SELECT.
Assim é necessário saber qual a gravadora do primeiro SELECT, para poder calcular o
segundo SELECT.
NOTA
- Coloque as subqueries entre parênteses.
- Coloque a subquery à direita do operador.
- Não use a cláusula ORDER BY em uma subquery. Se for necessário ordenar faça-o
no SELECT principal.
- Use operadores de grupo apenas em buscas que potencialmente retornem mais de
uma linha.
- Use operadores de linha apenas em buscas que retornem uma única linha.
Exemplo:
SELECT grav_id, cd_nome, cd_preco FROM cd
WHERE grav_id = (SELECT grav_id FROM cd WHERE cd_id = 2) AND
cd_preco > (SELECT cd_preco FROM cd WHERE cd_id = 5);
8.1.1.1. SUBQUERY EM CLÁUSULA HAVING
A utilização é semelhante a cláusula WHERE. A subquery será executada primeiro e o
resultado da busca servirá de base para filtrar as linhas do GROUP BY.
Exemplos:
SELECT grav_id, MIN(cd_preco) FROM cd
GROUP BY grav_id
HAVING MIN(cd_preco) > (SELECT cd_preco FROM cd
WHERE cd_id = 2)
SELECT grav_id, MAX(cd_preco) FROM cd c
GROUP BY grav_id
HAVING MAX(cd_preco) > (SELECT AVG(cd_preco) FROM cd
WHERE grav_id = c.grav_id)
57
SUBCONSULTAS
8.1.1.2. COMANDO EXISTS
Verifica o número de linhas retornadas pela subquery. Caso contenha uma ou mais
linhas, então o resultado será mostrado.
Exemplo:
SELECT grav_id, grav_nome FROM gravadora
WHERE EXISTS (SELECT * FROM cd
WHERE cd.grav_id = gravadora.grav_id)
Note que quando se está utilizando esse operador, não importa o que o comando
SELECT interno irá buscar. Interessa apenas se ele retorna ou não linhas, Por esse motivo é
usado o * em vez de uma coluna em especial.
8.1.2. Subquery de múltiplas linhas
Neste caso o SELECT interno retorna mais de uma linha. Não é possível utilizar
operadores simples como igualdade, diferença, maior ou menor.
Utilizam-se operadores de grupo para realizar a comparação, como:
- ANY
- ALL
- IN
8.1.2.1. IN
Imagine que queremos saber quais CD’s têm o preço igual ao menor preço de cada
gravadora. Inicialmente devemos saber qual o menor preço de cada gravadora.
SELECT MIN(cd_preco) FROM cd
GROUP BY grav_id
Para saber quais são os CD’s escreveríamos o seguinte comando:
SELECT cd_id, cd_nome, cd_preco FROM cd
WHERE cd_preco IN (19, 23, 11)
Para fazer isso em um único comando, escreveríamos o comando da seguinte forma:
SELECT cd_id, cd_nome, cd_preco FROM cd
WHERE cd_preco IN (SELECT MIN(cd_preco) FROM cd
GROUP BY grav_id)
58
SUBCONSULTAS
8.1.2.2. ANY (Compara com qualquer valor)
Esse operador permite comparar operadores simples (=,<,>,!=) com um grupo de linhas.
Imagine que desejamos saber quais CD’s têm preço inferior a qualquer outro da gravadora com
código 2, mas que não sejam da gravadora 2.
Inicialmente é preciso saber qual é o preço de venda de cada CD que não seja da
gravadora 2:
SELECT cd_id, cd_nome, cd_preco FROM cd
WHERE grav_id != 2
Depois precisamos saber quais os preços dos CD’s da gravadora 2 com o comando da
seguinte forma:
SELECT cd_preco FROM cd
WHERE grav_id = 2
Agora realizamos a pesquisa na tabela de CD para comparar os preços inferiores a
esses:
SELECT cd_id, cd_nome, cd_preco FROM cd
WHERE cd_preco < ANY (SELECT cd_preco FROM cd
WHERE grav_id = 2)
AND grav_id != 2
8.1.2.3. ALL
Esse operador é usado em combinação com operadores simples (<,>) para que os
valores retornados de todas as linhas do SELECT interno sejam comparados com o SELECT
externo.
Imagine que queiramos saber quais CD’s têm o preço de venda menor que a média de
preço de venda de todas as gravadoras.
Inicialmente é necessário saber a média de preços por gravadora:
SELECT AVG(cd_preco) FROM cd
GROUP BY grav_id;
Depois verificamos quais são os CD’s com preço inferior a todos anteriores.
SELECT cd_id, cd_nome, cd_preco FROM cd
WHERE cd_preco < ALL (SELECT AVG(cd_preco) FROM cd
GROUP BY grav_id)
59
SUBCONSULTAS
8.1.3. Subquery de múltiplas colunas
A técnica consiste em colocar as colunas unidas na cláusula WHERE do SELECT
externo e realizar a busca no SELECT interno dessas colunas unidas da mesma forma. Essa
técnica tende a ser muito lenta, mas pode ser feita em qualquer banco de dados.
Temos vários CD’s indicados no cadastro e queremos conhecer os dados do CD dentre
os menores CD’s indicados de cada gravadora. Para isso temos que saber o menor CD
indicado de cada gravadora. Utilizamos o || para unir as colunas.
Exemplo:
SELECT grav_id || cast(MIN(cd_indicado) as varchar)
FROM cd
GROUP BY grav_id
Agora realizamos a busca dos dados que atendem essas características:
SELECT cd_id, cd_nome, grav_id, cd_indicado
FROM cd
WHERE (grav_id || cast (cd_indicado as varchar)) IN
(SELECT grav_id || cast(MIN(cd_indicado) as varchar) FROM cd
GROUP BY grav_id)
Outra técnica consiste em colocar as colunas de busca entre parênteses no SELECT
externo, entretanto não está disponível em alguns bancos de dados.
Exemplo:
SELECT cd_id, cd_nome, grav_id, cd_indicado FROM cd
WHERE (grav_id, cd_indicado) IN
(SELECT grav_id, MIN(cd_indicado) FROM cd
GROUP BY grav_id)
NOTA:
Quando um SELECT interno contiver valores nulos, não deve ser utilizado NOT IN
como operador de comparação. Isso porque qualquer comparação com nulo retorna nulo.
8.1.4. Subquery na Cláusula FROM
Podemos utilizar uma subquery na cláusula FROM de um comando SELECT.
Essa estrutura não está disponível para todos os bancos de dados, mas faz parte do
padrão SQL. Sua utilização é muito parecida com as visões (Views).
Imagine que queremos extrair o nome do CD, seu preço e o preço médio da gravadora,
e o preço médio da gravadora é extraído por uma subquery na própria cláusula FROM:
60
SUBCONSULTAS
Exemplo:
SELECT a.cd_nome, a.cd_preco, b.preco_medio
FROM cd a, (SELECT grav_id, AVG(cd_preco) as preco_medio FROM cd
GROUP BY grav_id) b
WHERE a.grav_id = b.grav_id
AND a.cd_preco > b.preco_medio
Veja que foi substituída a segunda tabela por um comando SELECT e que a tabela
recebeu o apelido de b.
8.2. EXERCÍCIOS
a) Faça uma lista de CD’s usando subquery que foram gravados pela gravadora 2.
b) Faça uma lista que mostre os CD’s que custam mais que a média de preço dos CD’s
gravados pela gravadora 1.
c) Faça uma consulta usando a cláusula HAVING que retorne o CD mais barato que a
média de preço dos CD’s.
d) Faça uma lista que mostre os CD’s cujas músicas tem duração maior que 00:02:00.
e) Faça uma lista que mostre quais CD’s tem preços superiores a qualquer CD da
gravadora 1 excluindo os da própria gravadora 1.
61
VISÕES/VIEW
9
Parte
VISÕES/VIEW
A
nteriormente aprendemos a criar consultas em bancos de dados com a linguagem SQL
usando uma série de funções que permitem a extração dos dados de diversas formas.
Nesta sessão veremos a utilização de visões (Views), também chamadas de tabelas
virtuais em sistemas de bancos de dados.
As views são um modo especial de enxergar dados de uma ou várias tabelas. É um
objeto de banco de dados criado a partir de um comando SELECT.
É importante saber que ela é armazenada no dicionário de dados e possui a mesma
estrutura de uma tabela.
As views permitem restringir o acesso aos dados, por exemplo, utilizando o comando
SELECT podemos filtrar linhas e coluna que não devam ser mostradas a todos os usuários.
As buscas complexas tornam-se simples, pois ao DBA é possível criar consultas
complexas em forma de visões e ao usuário realizar as buscas necessárias nessa visão.
Outro aspecto é a independência de dados: as visões podem ser criadas visando
atender necessidades genéricas e não apenas às relacionadas a pessoa ou programas.
Podemos buscar dados em diversas tabelas.
A eliminação de códigos é outra razão: normalmente os códigos não devem ser
mostrados aos usuários, pois são utilizados apenas para estabelecer relacionamento entre as
tabelas. Dessa forma facilita a compreensão dos dados pelo usuário.
9.1. CLASSIFICAÇÃO
As visões podem ser classificadas de duas formas:
a) Simples:
- Os dados são extraídos de uma única tabela;
- Não contém funções.
- Não possuem dados agrupados.
- Podem utilizar comandos DML para manipular os dados.
b) Complexas:
- Os dados são extraídos de várias tabelas.
- Podem conter funções.
- Podem conter dados agrupados.
- Não podem utilizar comandos DML para manipulação de dados, exceto no Oracle,
pois possui recursos específicos para isso.
62
VISÕES/VIEW
9.2. CRIAÇÃO DA VISÃO
Para criar uma visão, utilizamos o comando CREATE VIEW:
Sintaxe:
CREATE VIEW nome
AS query/subquery
Exemplo:
CREATE VIEW vCD AS
SELECT cd_id, cd_nome, cd_preco FROM cd;
Como visto anteriormente esta é uma visão simples.
Como uma visão é sempre baseada em um comando SELECT, podemos criar visões
extremamente complexas.
Exemplos:
CREATE VIEW vPRECO_CD AS
SELECT grav_id, cd_nome, cd_preco FROM cd
WHERE grav_id = (SELECT grav_id FROM cd
WHERE cd_preco > 10.00)
CREATE VIEW vCD_FAIXA AS
SELECT cd_id, item_faixa FROM item_cd
WHERE mus_id IN (SELECT mus_id FROM musica
WHERE UPPER(mus_nome) LIKE ‘%A’)
CREATE VIEW VGRAV_MAX AS
SELECT grav_id, cd_preco FROM cd a
WHERE cd_preco > (SELECT AVG(cd_preco) FROM cd
WHERE grav_id = a.grav_id)
9.3. BUSCANDO CONTEÚDO DE VISÕES
Utilizamos o comando SELECT para buscar o conteúdo de visões exatamente como
fazemos com qualquer tabela.
Exemplo:
SELECT * FROM vCD;
SELECT * FROM vCD_FAIXA;
Podemos, ainda, incluir qualquer outra cláusula do comando SELECT, mesmo em
pesquisa realizadas em visões.
63
VISÕES/VIEW
Exemplo:
SELECT * FROM VPRECO_CD
WHERE cd_preco > 8;
9.4. UTILIZAÇÃO DE COMANDOS DML EM VISÕES
Somente em visões simples é possível utilizar comandos DDL. O padrão SQL
determina as condições em que uma visão pode ser atualizada:
- Deve ser criada em uma única tabela;
- Deve conter apenas um SELECT;
- Se foi criada com base em outra visão, a primeira visão dever ser passível de
atualização;
- O comando SELECT não pode conter colunas calculadas;
- Não deve utilizar GROUP BY;
- Não deve conter a cláusula DISTINCT;
- Pode conter uma subquery, desde que o SELECT interno tenha como base a mesma
tabela do SELECT externo;
- Inclusões só podem ser feitas caso a visão contenha a chave primária da tabela base.
- Exclusões e alterações em visões sem a chave primária da tabela base são
permitidas, porém desaconselháveis, pois não se sabe ao certo o resultado que pode aparecer.
Isso é chamado de Materialização de Views.
9.5. EXCLUINDO UMA VISÃO
Para excluir uma visão, utilizamos o comando DROP VIEW;
Ao excluir uma visão não estamos excluindo os dados (linhas e colunas), visto que é
apenas uma tabela virtual.
Sintaxe:
DROP VIEW nome;
Exemplo:
DROP VIEW vCD;
9.6. EXERCÍCIOS
a) Crie uma visão que mostre uma lista de CD’s que foram gravados pela gravadora 2.
b) Crie uma visão que mostre os CD’s e suas respectivas gravadoras.
c) Crie uma visão que mostre as músicas e seus respectivos autores.
d) Construa uma visão que mostre as músicas sua duração e a que CD pertence.
e) Construa uma visão que mostre os autores e em quais gravadoras possuem CD
gravado.
64
PROCEDIMENTOS ARMAZENADOS/STORED PROCEDURES
Parte
PROCEDIMENTOS ARMAZENADOS
10
m muitas situações será necessário armazenar procedimentos escritos com a finalidade
de se utilizar recursos como loop, estruturas de decisão, etc. Para isso, utiliza-se os
procedimentos armazenados o qual definimos como um programa escrito em uma
linguagem própria que é armazenado como parte do banco de dados.
Em outras palavras Procedimento Armazenado ou Stored Procedure é uma
coleção de comandos em SQL disponíveis em Banco de dados. Encapsula tarefas repetitivas,
aceita parâmetros de entrada e retorna um valor de status (para indicar aceitação ou falha na
execução). O procedimento armazenado pode reduzir o tráfego na rede, melhorar a
performance, criar mecanismos de segurança, etc. (Wikipédia, a enciclopédia livre, 2009).
E
10.1. VANTAGENS DO USO DE STORED PROCEDURES
- Redução de tráfico de rede.
- Aumenta a performance da aplicação, particularmente em uma WAN ou em uma
conexão de baixa velocidade.
- Utilizando stored procedure, elimina-se o processo de “parsed”, ou seja, da query ter
que ser analisada gramaticalmente e submetido ao otimizador para formulação de um plano de
execução.
- São analisadas, otimizadas e armazenadas em uma forma executável no momento em
que são adicionadas ao banco de dados.
- É possível executar operações muito mais complexas que uma simples query.
- Pode ser usada por aplicações distintas.
- Facilita a manutenção, pois é possível alterá-la sem ter que recompilar a aplicação.
- Proporciona mais segurança ao banco de dados, acessando tabelas que o usuário
não tem acesso.
10.2. QUANDO USAR STORED PROCEDURES
- Sempre que a metodologia de desenvolvimento do software permitir.
- Não existem desvantagens em usar procedimentos armazenados, entretanto existem
duas limitações:
- Deve ser possível passar qualquer informação variável para a stored procedure
(parâmetros).
- Operações complexas podem ser limitadas.
65
PROCEDIMENTOS ARMAZENADOS/STORED PROCEDURES
10.3. STORED PROCEDURES NO POSTGRESQL
Como visto, Stored Procedures são programas desenvolvidos em determinada
linguagem de script e armazenados no servidor, local onde serão processados. Também são
conhecidos como funções, este o motivo pelo qual as Stored Procedures no PostgreSQL são
referenciadas como Function.
O PostgreSQL conta com três formas diferentes de criar funções:
a) Funções em Linguagem SQL: São funções que utilizam a sintaxe SQL e se
caracterizam por não possuírem estruturas de condição (if, else, case), estruturas de repetição
(while, do while, for), não permitirem a criação de variáveis e utilizam sempre algum dos
seguintes comandos SQL: SELECT, INSERT, DELETE ou UPDATE.
b) Funções de Linguagens Procedurais: Ao contrário das funções SQL, aqui é
permitido o uso de estruturas de condição e repetição e o uso de variáveis. As funções em
linguagens procedurais caracterizam-se também por não possuírem apenas uma possibilidade
de linguagem, mas várias. Normalmente a mais utilizada é conhecida como PL/PgSQL,
linguagem semelhante ao conhecido PL/SQL da Oracle. Existem outras linguagens como, por
exemplo, o PL/Perl, PL/Python e PL/Tcl, que possuem sintaxe igual ou semelhante às
linguagens que lhes deram origem.
c) Funções em Linguagens Externas ou de Rotinas Complexas: São funções
normalmente escritas em C++ que trazem consigo a vantagem de utilizarem uma linguagem
com diversos recursos, na qual pode-se implementar algoritmos com grande complexidade.
Tais funções são empacotadas e registradas no Sistema Gerenciador de Banco de Dados para
seu uso futuro.
Existem ainda outras linguagens como PL/Ruby, PL/sh e PL/Java, no entanto, estas são
definidas por projetos independentes. Dentre elas destacam-se as funções criadas com
PL/Java devido a sua crescente demanda entre usuários do PostgreSQL.
Para criar uma função utilizando SQL no PostgreSQL utiliza-se o comando CREATE
FUNCTION:
Sintaxe:
CREATE [OR REPLACE] FUNCTION nome ([tipo_do_parametro1],[...])
RETURNS tipo_retornado AS '
Implementação_da_função;
'
LANGUAGE 'SQL';
- CREATE FUNCTION é o comando que define a criação de uma função, [OR
REPLACE] informa que se acaso existir uma função com este nome, a atual função deverá
sobrescrever a antiga.
- RETURNS tipo_retornado informa o tipo de dado que será retornado ao término da
função. Tais tipos de retornos são os convencionais como o INTEGER, FLOAT, VARCHAR,
etc. As funções em SQL também permitem o retorno de múltiplos valores e para isso informase como retorno SETOF. Na implementação haverá as linhas de programação para a
implementação da stored procedure.
- LANGUAGE indica para a função em qual linguagem ela está sendo implementada.
66
PROCEDIMENTOS ARMAZENADOS/STORED PROCEDURES
Na passagem de parâmetros à função não se utiliza o nome nas variáveis declaradas
nos parênteses da assinatura da função. Utiliza-se o tipo da variável de parâmetro separado
por vírgula. Para acessar o valor dos parâmetros, utiliza-se o $ mais o número da posição que
ocupa nos parâmetros, seguindo a ordem da esquerda para a direita.
Exemplo:
CREATE FUNCTION soma(INTEGER, INTEGER)
RETURNS INTEGER AS '
SELECT $1 + $2;
'
LANGUAGE 'SQL';
O fato de que as funções utilizando SQL sempre retornam valor faz com que seja
sempre necessário que a última linha de comando da função utilize o comando SELECT.
Exemplo:
CREATE FUNCTION cubo(INTEGER)
RETURNS FLOAT AS '
SELECT $1 ^ 3;
'
LANGUAGE 'SQL';
Também é possível criar funções que fazem interação entre uma determinada consulta
e parâmetros utilizados na função. Na função abaixo se obtém o total de CD’s gravados por
determinada gravadora, passando como parâmetro o id da gravadora (grav_id):
Exemplos:
CREATE FUNCTION cdGravados(INTEGER)
RETURNS INTEGER AS '
SELECT COUNT(cd_id) FROM cd WHERE grav_id = $1; '
LANGUAGE 'SQL';
CREATE OR REPLACE FUNCTION cdGravados2(in INT, out f1 INT, out f2
TEXT, out f3 NUMERIC)
AS $$ SELECT CAST(cd_id as integer), CAST(cd_nome AS text),
CAST(cd_preco AS numeric)
FROM cd WHERE GRAV_id = $1 $$
LANGUAGE ‘SQL’;
NOTA:
Dependendo da configuração escolhida na instalação do PostgreSQL será necessário
ajustar o tipo do retorno fazendo uso da função CAST.
Exemplo:
CAST(COUNT(cd_id) as INTEGER)
67
PROCEDIMENTOS ARMAZENADOS/STORED PROCEDURES
Para usar a função utiliza-se a cláusula SELECT.
Exemplo:
SELECT cdGravados(4);
Como mencionado, também é possível retornar várias linhas de uma consulta em uma
função. Para isso, utilizamos o tipo de retorno SETOF. No próximo exemplo é criada uma
função em que retorna todos os CD’s mais caros que determinado valor passado por
parâmetro.
Exemplo:
CREATE OR REPLACE FUNCTION precoMaior(NUMERIC(10,2))
RETURNS SETOF cd AS'
SELECT * FROM cd WHERE cd_preco > $1;
'
LANGUAGE 'SQL';
Quando as funções possuem o seu retorno referenciado em uma tabela ou uma View,
ou seja, quando a função retorna um resultset, devemos utilizar a função da seguinte maneira:
Exemplo:
SELECT * FROM precoMaior(5.2);
A exclusão de uma função se faz através do comando DROP, como visto na DDL.
Sintaxe:
DROP FUNCTION nome_da_funcao();
Quando a função, na sua assinatura contiver parâmetros não será possível sua
exclusão através do comando DROP FUNCTION nome_da_funcao(), ou seja, para excluir uma
função é necessário passar toda a sua assinatura.
Exemplo:
DROP FUNCTION precoMaior(NUMERIC(10,2));
Ainda existe o fato que no momento da exclusão poder excluir a função passando mais
um parâmetro, como no exemplo a seguir:
Exemplo:
DROP FUNCTION cd_grav (psql INTEGER) RESTRICT;
DROP FUNCTION cd_grav (psql INTEGER) CASCADE;
Passando o RESTRICT como parâmetro, a exclusão da função será recusada caso
existam dependências de objetos em torno da função (como por exemplo, Triggers e
operadores). Com o CASCADE esses objetos serão excluídos juntamente com a função.
68
PROCEDIMENTOS ARMAZENADOS/STORED PROCEDURES
Nota:
Se a função for removida e recriada, a nova função não é mais a mesma entidade que
era antes. Ficarão inválidas as regras, visões, gatilhos, etc. existentes que fazem referência à
antiga função. Use o comando CREATE OR REPLACE FUNCTION para mudar a definição de
uma função, sem invalidar os objetos que fazem referência à função.
10.4.1. PL/pgSQL
A PL/pgSQL é uma linguagem estrutural estendida da SQL que tem por objetivo auxiliar
as tarefas de programação no PostgreSQL. Ela incorpora à SQL características procedurais,
como os benefícios e facilidades de controle de fluxo de programas que as melhores
linguagens possuem como loops estruturados (for, while) e controle de decisão (if, then, else).
Dessa forma, programar em PL/pgSQL significa ter a disposição um ambiente procedural
totalmente desenvolvido para aplicações de bancos de dados, beneficiando-se do controle
transacional inerente das aplicações deste tipo.
10.4.2. Elementos da Linguagem
A criação de Stored Procedures inclui todas as construções de uma linguagem de
programação estruturada, como:
- Comentários: -- Este é um comentário
- Bloco de comandos (BEGIN/END): É possível usar construções como IF-THEN-ELSE
e loops WHILE que somente podem conter um comando.
- Comandos de atribuição: var1 := var2 * var3
- Operador de concatenação: || (duas barras verticais)
Além desses elementos é possível usar as expressões condicionais:
- BETWEEN
- LIKE IN
- EXISTS
- ANY
- ALL
- IS NULL
- IS NOT NULL
10.4.3. Estrutura da Linguagem
A PL/pgSQL é estruturada em blocos, assim, todo o texto de definição de uma função
precisa estar em um bloco.
Sintaxe:
[<<label>>]
[DECLARE
declarações]
BEGIN
conteúdo 1;
conteúdo 2;
conteúdo n;
69
PROCEDIMENTOS ARMAZENADOS/STORED PROCEDURES
END;
- DECLARE é a área para declaração de variáveis. As variáveis declaradas nessa
sessão são inicializadas recebendo seus valores default sempre que existe uma entrada nesse
bloco. Como na maior parte das linguagens existentes, na PL/pgSQL, variáveis com o mesmo
nome possuem o seu escopo definido para o bloco imediatamente posterior à sua declaração.
Exemplo:
CREATE OR REPLACE FUNCTION mostra_valor(valor INT)
RETURNS INTEGER AS
$$ DECLARE
valor INTEGER := $1;
BEGIN
RAISE NOTICE 'O valor da variável aqui é %', Valor;
RETURN valor;
END; $$
LANGUAGE 'PLPGSQL';
Executando a função:
SELECT mostra_valor(12);
Como visto é possível usar as mesmas estruturas de controle disponíveis nas
linguagens de programação:
Exemplo:
CREATE OR REPLACE FUNCTION formatarCPF(cpf VARCHAR(11))
RETURNS VARCHAR(14) AS $$
BEGIN
IF char_length(cpf) != 11 THEN
RAISE NOTICE 'Formato inválido: %', $1;
RETURN 'ERRO';
END IF;
RETURN SUBSTRING(cpf FROM 1 FOR 3) || '.' || SUBSTRING(cpf
FROM 4 FOR 3) || '.' || SUBSTRING(cpf FROM 7 FOR 3) || '-' ||
SUBSTRING(cpf FROM 10 FOR 2);
END;
$$
LANGUAGE PLPGSQL;
SELECT formatarCPF('11111111111');
Além de todos os recursos advindos da estrutura de programação é permitido fazer
interação com a estrutura do banco de dados nas operações da DML como no exemplo abaixo:
70
PROCEDIMENTOS ARMAZENADOS/STORED PROCEDURES
Exemplo:
CREATE
OR
REPLACE
FUNCTION
alteraPreco(id
INTEGER,
preco
NUMERIC(10,4))
RETURNS VARCHAR(50) AS $$
DECLARE
valorMax NUMERIC(10,4) := 100;
valorMin NUMERIC(10,4) := 1;
BEGIN
IF $2 >= valorMin and $2 <= valorMax THEN
UPDATE CD SET cd_preco = $2 WHERE cd_id = $1;
RETURN 'Preço alterado com sucesso';
ELSE
RETURN 'O valor inserido está fora da faixa delimitada
para preços de CDs';
END IF;
END;
$$
LANGUAGE PLPGSQL;
SELECT alteraPreco(1, 16.5);
SELECT alteraPreco(2, 120);
As versões 8.3 e posteriores do PostgreSQL permitem o retorno de conjuntos de linhas
e colunas de uma função através da funcionalidade RETURN QUERY. O RETURN QUERY
aceita o tipo RECORD como valor de retorno tornando fácil retornar resultados de consultas
genéricas.
Exemplo:
CREATE OR REPLACE FUNCTION cd_grav (psql integer)
RETURNS SETOF RECORD AS $$
BEGIN
RETURN QUERY SELECT cd_nome, grav_nome FROM cd, gravadora WHERE
cd.grav_id = gravadora.grav_id AND gravadora.grav_id = $1; -- Consulta
RETURN; -- Retorna as linhas
END;
$$ LANGUAGE plpgsql;
A diferença desta forma de programar está na chamada da função. Ao utilizar valor de
retorno do tipo RECORD, deve ser indicada a lista de campos a serem retornados e seus tipos.
Esta pode ser vista como uma limitação, embora na prática também seja uma forma de se
garantir a confiabilidade dos resultados retornados na execução de comandos SQL dentro de
funções pela validação dos valores retornados.
Exemplo:
SELECT * FROM cd_grav(2) AS ( c1 VARCHAR(50), c2 VARCHAR(50) );
71
PROCEDIMENTOS ARMAZENADOS/STORED PROCEDURES
Esses são alguns recursos possíveis com Stored Procedures/Functions no PostgreSQL.
Contudo, é importante saber que existe um gama de outros recursos possíveis fazendo uso do
PgSQL, assim como acontece com outros bancos de dados, como o Oracle.
10.4. EXERCÍCIOS
a) Crie uma Stored Procedure no PostgreSQL que faça inserções na tabela de música.
b) Crie uma Stored Procedure no PostgreSQL que retorne quais música foram gravadas
em determinado CD passando como parâmetro o id do CD (cd_id).
72
TRIGGERS (GATILHOS OU AUTOMATISMOS)
Parte
11
TRIGGERS (GATILHOS OU AUTOMATISMOS)
D
esenvolver uma aplicação para gerenciar os dados significa criar uma aplicação que
faça o controle sobre todo ambiente desde a interface, passando pela manutenção dos
dados e as regras de negócios do sistema.
Assim, é o sistema que deve controlar e tomar as decisões sobre o que fazer em
determinadas situações. Em um programa para controlar os produtos de uma empresa, quando
a quantidade de um produto atingir certa quantidade o sistema deverá avisar o
operador/usuário para providenciar a reposição do mesmo.
Ao trabalhar com base de dados Cliente/Servidor como SQL Server, Oracle, Informix,
PostgreSQL dentre outras, é possível usar um recurso muito poderoso chamado Trigger.
Triggers são rotinas armazenadas no banco de dados e utilizadas quando um comando
Insert, Update ou Delete é executado em uma tabela ou até mesmo uma visão (view).
Trigger significa gatilho e é disparada quando os comandos da DML são disparados.
São executadas automaticamente sem a interferência do usuário, ou seja, são procedimentos
armazenados que são acionados por algum evento e em determinado momento.
As principais aplicações das triggers estão relacionadas às validações, restrições de
acesso, rotinas de segurança e consistência de dados.
Triggers são iguais a stored procedures com as seguintes exceções:
- São chamadas automaticamente;
- Não tem parâmetros;
- Não retornam valores.
A criação de uma trigger envolve duas etapas:
- Um comando SQL que vai disparar o Trigger (INSERT, DELETE, UPDATE)
- A ação que a trigger vai executar (Geralmente um bloco de códigos SQL)
O gatilho fica associado com uma tabela e executa uma função especifica. Na maioria
dos bancos de dados estes eventos podem ser inserções (INSERT), atualizações (UPDATE) e
exclusões (DELETE), e podem ser executadas em dois momentos:
- Antes da execução do evento (BEFORE);
- Depois da execução do evento (AFTER).
Uma trigger é executada automaticamente mediante seis eventos da DML:
- BEFORE UPDATE (Antes da atualização);
- AFTER UPDATE (Após a atualização);
- BEFORE INSERT (Antes da inserção);
- AFTER INSERT (Após a inserção);
73
TRIGGERS (GATILHOS OU AUTOMATISMOS)
- BEFORE DELETE (Antes da exclusão);
- AFTER DELETE (Após a exclusão);
11.1. BEFORE OU UPDATE
Uma trigger deve ser disparada antes de o registro ser atualizado caso queira alterar o
valor de uma ou mais colunas antes que a linha seja atualizada, ou queira bloquear a alteração
da linha gerando uma exceção, como por exemplo: usar uma trigger BEFORE UPDATE para
evitar que o usuário apague o registro de um cliente que tenha comprado nos últimos dois
anos.
Triggers do tipo AFTER quando se deseja garantir que a atualização que disparou a
trigger seja completada com sucesso antes de executar outras ações, como por exemplo:
inserir uma linha em uma tabela de salario_historico sempre que o salário de um funcionário for
alterado.
11.2. USANDO OLD E NEW
No corpo de uma trigger é possível usar tanto os valores antigos ou novos de qualquer
registro. Para isso basta utilizar os comandos OLD e NEW.
Exemplo:
old.aut_nome
new.aut_nome
Com esses comandos é possível criar registros de histórico, calcular percentual de
alteração de valores, etc.
11.3. TRIGGERS NO POSTGRESQL
Um diferencial das triggers no PostgreSQL é que elas são sempre associadas a
funções de triggers e em outros Bancos de Dados elas são criadas no corpo da trigger.
11.3.1. Tipos de Triggers
O PostgreSQL possui dois tipos de triggers:
- Triggers-por-linha
- Triggers-por-instrução
A trigger-por-linha é disparada uma vez para cada registro afetado pela instrução que
disparou a trigger.
A trigger-por-instrução é disparada somente uma vez quando a instrução é
executada.
11.3.2. Funções de Trigger e Linguagens Procedurais
74
TRIGGERS (GATILHOS OU AUTOMATISMOS)
Funções de triggers são funções que não recebem nenhum parâmetro e retornam o tipo
trigger. Essas funções recebem uma estrutura chamada TriggerData, e esta é passada
internamente para a função pelo PostgreSQL.
O PostgreSQL disponibiliza duas variáveis importantes para serem usadas em conjunto
com as triggers-por-linha: NEW e OLD.
A variável NEW, no caso do INSERT, armazena o registro que está sendo inserido. No
caso do UPDATE, armazena a nova versão do registro depois da atualização.
A variável OLD, no caso do DELETE, armazena o registro que está sendo excluído. No
caso do UPDATE, armazena a antiga versão do registro depois da atualização.
As funções de triggers devem ser escritas em alguma linguagem procedural disponível
no banco de dados1.
Cada linguagem, que suporta triggers, possui o seu próprio método para tornar os
dados de entrada da trigger disponíveis para a função. Estes dados de entrada incluem o tipo
de evento da trigger, assim como as opções informadas na criação da trigger. Para uma trigger
no nível de linha, os dados de entrada também incluem as linhas NEW para as triggers de
INSERT e UPDATE, e a linha OLD para os triggers de UPDATE e DELETE.
Sintaxe:
CREATE TRIGGER nome { BEFORE | AFTER } { evento [OR ...] }
ON tabela FOR EACH { ROW | STATEMENT }
EXECUTE PROCEDURE função ( argumentos )
Onde:
Nome: O nome da Trigger.
Tabela: O nome da tabela na qual a trigger estará vinculada.
Evento: INSERT, DELETE ou UPDATE.
Função: Pode-se usar, neste caso, uma stored procedure para execução de
determinada tarefa.
row|statement: especifica se a trigger deve ser disparada uma vez para cada linha
afetada pelo evento ou apenas uma vez por comando SQL. Se não for especificado nenhum
dos dois, o padrão é FOR EACH STATEMENT.
Exemplo:
Para este primeiro exemplo é necessário alterar nosso banco de dados:
ALTER TABLE autor
ADD aut_cpf CHAR(14);
Próximo passo é criar a função que faz a inserção de determinado valor nesta tabela:
1
Essas linguagens podem ser várias, como Ruby, Perl, Python, entre outras. Atualmente existem quatro linguagens
procedurais disponíveis na distribuição padrão do PostgreSQL: PL/pgSQL, PL/Tcl, PL/Perl e PL/Python. Mas é
possível que o usuário defina outras linguagens. Para instalar novas linguagens no PostgreSQL, consulte a
documentação oficial do PostgreSQL, que pode ser obtida no seguinte endereço: http://pgdocptbr.sourceforge.net/.
75
TRIGGERS (GATILHOS OU AUTOMATISMOS)
Exemplo:
CREATE OR REPLACE FUNCTION ftFormataCPF()
RETURNS trigger AS
$$
BEGIN
IF char_length(new.aut_cpf) = 11 THEN
new.aut_cpf := SUBSTRING(new.aut_cpf FROM 1 FOR 3) ||
'.'
||
SUBSTRING(new.aut_cpf
FROM
4
FOR
3)
||
'.'
||
SUBSTRING(new.aut_cpf FROM 7 FOR 3) || '-' || SUBSTRING(new.aut_cpf
FROM 10 FOR 2);
RETURN new;
END IF;
END;
$$
LANGUAGE plpgsql;
Por fim, criando a trigger:
Exemplo:
CREATE TRIGGER tFormataCpf BEFORE INSERT
ON autor FOR EACH ROW
EXECUTE PROCEDURE ftFormataCPF ();
Verificando seu funcionamento:
INSERT INTO autor (aut_id, aut_nome, aut_cpf) VALUES (102, 'teste
function2', '00000000002');
11.3.3. Parâmetros Especiais
Dentre as funções que foram apresentadas para triggers é possível utilizar uma grande
quantidade de argumentos com a finalidade de criar controles e métodos de auditoria nos
bancos de dados PostgreSQL. Todas elas são facilmente encontradas no manual do
PostgreSQL.
Desses, um dos argumentos mais importantes é o TG_OP que permite identificar qual
operação está sendo realizada: INSERT, UPDATE e DELETE. Muito útil para criar mecanismos
de auditoria nos dados contidos em determinada tabela.
Acompanhe atentamente o exemplo abaixo:
Exemplo:
Primeiramente vamos criar uma tabela de histórico:
CREATE TABLE historico_cd (
hcd_id INTEGER,
hcd_preco_novo NUMERIC(14,2),
hcd_preco_antigo NUMERIC(14,2),
76
TRIGGERS (GATILHOS OU AUTOMATISMOS)
hcd_data_hora TIMESTAMP,
hcd_operacao VARCHAR(50) );
Depois criamos a função:
CREATE OR REPLACE FUNCTION auditaCD()
RETURNS trigger AS
$$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO historico_cd (hcd_id, hcd_preco_novo,
hcd_preco_antigo,
hcd_data_hora,
hcd_operacao)
VALUES
(NEW.cd_id,
NEW.cd_preco, NULL, CURRENT_TIMESTAMP, ‘INSERT’);
RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO historico_cd (hcd_id, hcd_preco_novo,
hcd_preco_antigo,
hcd_data_hora,
hcd_operacao)
VALUES
(OLD.cd_id,
NEW.cd_preco, OLD.cd_preco, CURRENT_TIMESTAMP, ‘UPDATE’);
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
INSERT INTO historico_cd (hcd_id, hcd_preco_novo,
hcd_preco_antigo,
hcd_data_hora,
hcd_operacao)
VALUES
(OLD.cd_id,
NULL, OLD.cd_preco, CURRENT_TIMESTAMP, ‘DELETE’);
RETURN NEW;
END IF;
END;
$$
LANGUAGE plpgsql;
Por fim, criamos a Trigger:
CREATE TRIGGER tg_auditaCD AFTER INSERT OR UPDATE OR DELETE
ON cd
FOR EACH ROW EXECUTE PROCEDURE auditaCD();
Verificando seu funcionamento:
INSERT
INTO
cd
(cd_id,
grav_id,
cd_nome,
cd_preco,
cd_dt_lancamento) VALUES (100, 1, 'teste trigger1', 10.00, '2009-1115');
UPDATE cd SET cd_preco = 20.00 WHERE cd_id = 100;
DELETE FROM cd WHERE cd_id = 100;
11.3.4. Triggers Recursivas
77
TRIGGERS (GATILHOS OU AUTOMATISMOS)
Se uma função de trigger executar comandos SQL, estes comandos podem disparar
triggers novamente. Isto é conhecido como cascatear triggers. Não existe limitação direta do
número de níveis de cascateamento. É possível que o cascateamento cause chamadas
recursivas da mesma trigger; por exemplo, um trigger para INSERT pode executar um
comando que insere uma linha adicional na mesma tabela, fazendo com que a trigger para
INSERT seja disparada novamente. É responsabilidade de o programador evitar recursões
infinitas nestes casos.
11.3.5. Alterando uma Trigger
É possível alterar o nome de trigger usando a sintaxe abaixo:
Sintaxe:
ALTER TRIGGER nome ON tabela RENAME TO novo_nome;
11.3.6. Excluindo uma Trigger
Para excluir uma trigiger basta executar o comando abaixo:
Sintaxe:
DROP TRIGGER nome ON tabela [ CASCADE | RESTRICT ]
Onde:
Nome: é o nome da trigger a ser removida.
Tabela é o nome da tabela para a qual a trigger está definida.
[ CASCADE | RESTRICT ]: indica que ao remover a trigger vamos remover também
todos os objetos que dependem dela (CASCADE) ou recusaremos sua exclusão (RESTRICT).
11.3.7. Desabilitando/Habilitando uma Trigger
Para desabilitar/habilitar uma trigger execute o comando abaixo:
Sintaxe:
ALTER TABLE nome_tabela [DISABLE|ENABLE] TRIGGER nome_trigger;
Para desabilitar todas as triggers da tabela, execute o seguinte comando:
Sintaxe:
ALTER TABLE nome_tabela [DISABLE|ENABLE] TRIGGER ALL;
11.4. EXERCÍCIO
a) Crie uma trigger responsável por verificar, no momento da inserção na tabela de CD,
se o preço do CD é maior de 1,00 e inferior a 100,00.
78
SEGURANÇA DE BANCO DE DADOS
Parte
12
SEGURANÇA DE BANCO DE DADOS
P
odemos dizer que segurança em banco de dados garante que os usuários tenham
permissão para fazer o que realmente precisam fazer. Para tanto, os Sistemas
Gerenciadores de Banco de Dados precisam de certas limitações, na qual os usuários
não poderão violar. Estas especificações são criadas pelo DBA (Administrador de Banco de
Dados).
Todas as decisões acerca dos direitos que devem ser concedidos a determinado
usuário são decisões políticas e não técnicas. Assim sendo, tais competências fogem a alçada
dos Sistemas Gerenciadores de Banco de Dados.
12.1. VISÃO DE SEGURANÇA
O Sistema Gerenciador de Banco de Dados deve fornecer ao usuário uma
representação conceitual dos dados (visão), sem fornecer muitos detalhes de como as
informações estão armazenadas.
Muitas verificações de autorização podem aplicar-se ao tempo de execução, entretanto
a abordagem de segurança de que se baseia uma visão é um tanto inábil, pois um usuário
pode precisar de direitos diferentes sobre subconjuntos diferentes da mesma tabela.
Existem muitos aspectos relativos aos problemas de segurança, dentre eles os
aspectos legais, sociais e éticos. O que deve ser analisado é se quem faz a solicitação da
informação tem direito legal sobre ela.
Outro aspecto a se analisar são os controles físicos. Considera-se a sala a sala do
servidor um lugar inviolável, pois lá estão todas as informações.
Para que o sistema seja capaz de decidir que limitações se aplicam a determinada
solicitação é necessário reconhecer a fonte daquela solicitação, isto é, deve ser capaz de
reconhecer de que usuário específico partiu aquela demanda. Neste sentido é necessário
atribuir mecanismos de autenticação para os responsáveis pela solicitação sejam identificados,
como um meio de garantir a auditoria sobre os feitos daquele usuário nos dados armazenados.
12.2. INSTRUÇÕES GRANT E REVOKE
O mecanismo de visão possibilita dividir o banco de dados em partes conceituais de
modo que a informação sensível fique oculta aos usuários não autorizados.
Por meio das instruções GRANT e REVOKE é possível atribuir restrições aos usuários
de acordo com um perfil previamente estabelecido. Esses representam a principal interface que
79
SEGURANÇA DE BANCO DE DADOS
concede ao usuário capacidade de desempenhar qualquer operação através de comandos
SQL.
12.3. OUTROS ASPECTOS DE SEGURANÇA
As visões e o mecanismo de GRANT e REVOKE não são os únicos meios de se
implementar segurança em banco de dados. Na verdade, não existe nenhum ponto no Sistema
Gerenciador de Banco de Dados que proporcione um conjunto amplo de controles de
segurança. Dessa forma, um suposto infiltrador suficientemente determinado é capaz de
passar por todos os controles já mencionados.
Para situações em que os dados sejam sensíveis, torna-se necessário um arquivo ou
banco de dados especial, onde o sistema registra automaticamente todas as operações
desempenhadas por determinado usuário. A isso chamamos de Log.
Outro nível de segurança pode ser proporcionado pela codificação de dados. A idéia
básica consiste no armazenamento físico dos dados no disco e antes de serem transmitidos
são codificados (Criptografados).
12.4. CRIAÇÃO DE USUÁRIOS NO POSTGRESQL
Para possibilitar a utilização da base de dados por usuários distintos, cada um
trabalhando em uma sessão diferente é necessário realizar a criação de um usuário no
Sistema Gerenciador de Banco de Dados atribuindo-lhe um nome para autenticação e senha.
A este usuário criado poderão ser concedidas permissões diversas assegurando-lhe acesso
aos dados, bem como concedendo e restringindo operações sobre as informações.
O comando usando no PostgreSQL para criar usuário é o CREATE USER. No entanto,
nas versões mais modernas é possível fazê-lo diretamente através do comando CREATE
ROLE, possibilitando a utilização de mais recursos.
Sintaxe:
CREATE USER|ROLE nome [ [ WITH ] opção [ ... ] ]
Onde, opção pode ser:
SYSID id_do_usuário
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| IN GROUP nome_do_grupo [, ...]
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'senha'
| VALID UNTIL 'data_e_hora'
Parâmetros:
- Nome: O nome do usuário.
- id_do_usuário: A cláusula SYSID pode ser utilizada para escolher o identificador de
usuário do PostgreSQL do novo usuário. Normalmente não é necessário, mas pode ser útil se
for necessário recriar o dono de um objeto que ficou órfão. Se não for especificado, será
utilizado por padrão o maior identificador de usuário atribuído acrescido de um (com mínimo de
100).
80
SEGURANÇA DE BANCO DE DADOS
- CREATEDB NOCREATEDB: Estas cláusulas definem a permissão para o usuário criar
banco de dados. Se for especificado CREATEDB, o usuário sendo definido terá permissão
para criar seus próprios bancos de dados. Se for especificado NOCREATEDB, nega-se ao
usuário a permissão para criar banco de dados. Se nenhuma destas cláusulas for especificada,
o padrão é NOCREATEDB.
- CREATEUSER NOCREATEUSER : Estas cláusulas determinam se o usuário pode ou
não criar novos usuários. CREATEUSER também torna o usuário um superusuário, o qual
pode passar por cima de todas as restrições de acesso. Se nenhuma destas cláusulas for
especificada, o padrão é NOCREATEUSER.
- nome_do_grupo: O nome de um grupo existente onde o usuário será incluído como
um novo membro. Podem ser especificados nomes de vários grupos.
- Senha: Define a senha do usuário. Se não se pretende utilizar autenticação por senha
esta opção pode ser omitida, mas o usuário não poderá mais se conectar se for decidido mudar
para autenticação por senha. A senha poderá ser definida ou mudada posteriormente através
do comando ALTER USER.
- ENCRYPTED UNENCRYPTED: Estas cláusulas controlam se a senha será
armazenada criptografada, ou não, nos catálogos do sistema; Se não for especificada
nenhuma destas cláusulas, o comportamento padrão será determinado pelo parâmetro de
configuração password_encryption. Se a cadeia de caracteres da senha já estiver criptografada
no formato MD5, então a cadeia de caracteres será armazenada como está,
independentemente de ser especificado ENCRYPTED ou UNENCRYPTED (porque o sistema
não pode descriptografar a cadeia de caracteres criptografada contendo a senha). Esta
funcionalidade permite a restauração de senhas criptografadas efetuadas por uma operação de
dump/restore. Deve ser observado que os clientes antigos podem não possuir suporte para o
mecanismo de autenticação MD5, necessário para trabalhar com as senhas que são
armazenadas criptografadas.
- data_e_hora: A cláusula VALID UNTIL define uma data e hora após a qual a senha do
usuário não é mais válida. Se esta cláusula for omitida, a conta será válida para sempre.
A cláusula VALID UNTIL define uma data de expiração para a senha apenas, e não para a
conta do usuário per se. Em particular, a obediência à data de expiração não é imposta ao se
conectar utilizando um método de autenticação não baseado em senha.
Deve ser usado o comando ALTER USER para mudar os atributos de um usuário, e
DROP USER para remover um usuário. Deve se usado ALTER GROUP para adicionar ou
remover usuários de grupos de forma semelhante como acontece com a criação do usuário.
O PostgreSQL inclui o programa createuser que possui a mesma funcionalidade do
CREATE USER (na verdade, chama este comando), mas pode ser executado a partir da linha
de comando.
81
SEGURANÇA DE BANCO DE DADOS
Exemplos:
Criar um usuário sem senha:
CREATE USER Jonas;
CREATE ROLE Jonas;
Criar um usuário com senha:
CREATE ROLE manuel WITH PASSWORD 'jw8s0F4';
Criar um usuário com uma senha válida até o fim de 2009. Após o primeiro segundo de
2010 a senha não será mais válida:
CREATE ROLE miriam WITH PASSWORD 'jw8s0F4' VALID UNTIL '2010-0101';
Criar uma conta onde o usuário pode criar bancos de dados:
CREATE ROLE manuel WITH PASSWORD 'jw8s0F4' CREATEDB;
Criar um usuário com poderes de superusuário:
CREATE ROLE administrador WITH
SUPERUSER
‘xxxxx’;
ENCRYPTED
PASSWORD
Para alterar determinado usuário, basta utilizar o comando ALTER ROLE de maneira
semelhante como ocorre com o comando CREATE ROLE:
Exemplo:
Conceder permissão para o usuário criar outro usuários e bancos dedados:
ALTER ROLE miriam CREATEROLE CREATEDB;
Para excluir usuário do banco de dados utiliza-se o comando DROP:
DROP ROLE Manoel;
Para saber quais usuário estão cadastrados no PostgreSQL basta fazer uma consulta
na tabela do sistema pg_user:
SELECT username FROM pg_user;
12.5. CRIANDO GRUPOS DE USUÁRIOS
No PostgreSQL é possível criar grupos de usuários. Lembrando que todas as
permissões atribuídas ao grupo serão repassadas aos usuários pertencentes a ele.
Sintaxe:
CREATE GROUP nomedogrupo;
Exemplos:
CREATE GROUP adm;
82
SEGURANÇA DE BANCO DE DADOS
Adicionar e remover usuário de um grupo:
Exemplo:
ALTER GROUP adm ADD USER miriam, administrador;
ALTER GROUP adm DROP USER miriam;
Para excluir um grupo, basta usar o comando padrão para exclusão de objetos no
banco de dados, DROP. Lembrando que o comando DROP remove apenas o grupo e não os
usuários do grupo.
Exemplo:
DROP GROUP adm;
Para listar todos os grupos criados é necessário consultar a tabelas do sistema
pg_group:
SELECT gronome FROM pg_group;
12.6. CONCEDENDO PRIVILÉGIOS AOS USUÁRIOS
Nesta etapa é importante lembrar que o superusuário tem o direito de fazer o que bem
entender nos bancos de dados existentes. Outro aspecto é relativo ao proprietário de um objeto
no banco de dados. Sendo ele o dono do objeto ele também tem todas as permissões para
fazer o que bem entender com esse objeto.
São vários os privilégios que um usuário pode receber, dentre eles:
- SELECT
- INSERT
- UPDATE
- DELETE
- RULE
- REFERENCES
- TRIGGER
- CREATE
- TEMPORARY
- EXECUTE
- USAGE
- LOGIN
- CONNECT
A concessão de privilégios se dá através do comando GRANT e sua revogação através
do comando REVOKE.
Sintaxe:
GRANT privilégio ON objeto TO usuário;
REVOKE privilégio ON objeto FROM usuário;
Exemplos:
GRANT SELECT, INSERT, UPDATE ON cliente TO miriam;
Para atribuir e retirar permissão ao grupo:
GRANT SELECT, DELETE ON cliente TO adm;
REVOKE UPDATE ON cliente FROM miriam;
Para remover os privilégios de todos os usuários:
REVOKE ALL ON cliente FROM PUBLIC;
83
SEGURANÇA DE BANCO DE DADOS
Os privilégios especiais do dono da tabela são sempre inerentes à condição de ser o
dono, não podendo ser concedidos ou revogados. No entanto, o dono do objeto pode revogar
seus próprios privilégios, tornando-se um usuário comum como os demais no que se refere ao
objeto na qual é proprietário.
Em geral, somente o dono e o superusuário podem conceder ou revogar privilégios para
um objeto.
12.7. OBSERVAÇÕES IMPORTANTES
O usuário do sistema (superusuário) deve ser um usuário criado exclusivamente para o
PostgreSQL. Nunca devemos torná-lo dono de um executável.
Os nomes de usuário são globais para todos os bancos de dados.
84
TRANSAÇÕES EM BANCO DE DADOS
Parte
13
TRANSAÇÕES EM BANCO DE DADOS
O
termo transação refere-se a uma coleção de operações que formam uma única
unidade de trabalho lógica. Por exemplo, a transferência de dinheiro de uma conta
para outra é uma transação consistindo de duas atualizações, uma para cada conta.
Uma transação é uma unidade de execução do programa que acessa e possivelmente
atualiza vários itens de dados. Para garantir a integridade dos dados, é necessário que o
SGBD mantenha as seguintes propriedades das transações: atomicidade, consistência,
isolamento e durabilidade.
- Atomicidade: uma transação é uma unidade atômica de processamento; ou ela será
executada em sua totalidade ou não será de modo nenhum.
- Consistência: uma transação deve ser preservadora de consistência se sua execução
completa fizer o banco de dados passar de um estado consistente para outro também
consistente.
- Isolamento: uma transação deve ser executada como se estivesse isolada das
demais. Isto é, a execução de uma transação não deve sofrer interferência de quaisquer outras
transações concorrentes.
- Durabilidade: as mudanças aplicadas ao banco de dados por uma transação
efetivada devem persistir no banco de dados. Essas mudanças não devem ser perdidas em
razão de uma falha.
Essas propriedades normalmente são conhecidas como propriedades ACID. Esse
acrônimo é derivado da primeira letra de cada uma das quatro propriedades.
Quando se trabalham com transações, é necessário que se faça pelo menos duas
ressalvas. A primeira é que em certas situações é interessante se agregar vários comandos
como sendo integrantes de uma mesma transação, como, por exemplo, em uma transferência
bancária que envolve a retirada de dinheiro de uma conta e o acréscimo em outra como se
fosse apenas uma única operação lógica. A segunda ressalva é que em outras situações se faz
necessário sacrificar ou flexibilizar as características ACID em virtude da necessidade de maior
desempenho.
13.1. ESTADOS DE UMA TRANSAÇÃO
Na ausência de falhas, todas as transações são completadas com sucesso. Porém,
uma transação nem sempre pode completar sua execução com sucesso. Caso isso ocorra,
essa transação é considerada abortada.
85
TRANSAÇÕES EM BANCO DE DADOS
Se houver necessidade de garantir a propriedade de atomicidade, uma transação
abortada não pode ter efeito sobre o estado do banco de dados. Assim, qualquer mudança que
a transação abortada tenha feito no banco de dados deve ser desfeita. Quando as mudanças
causadas por uma transação abortada tiverem sido desfeitas, dizemos que a transação foi
revertida (rolled back). Se uma transação foi executada com sucesso, diz-se que foi confirmada
(committed). Assim, é possível estabelecer vários estados para as transações:
- Active: corresponde ao estado inicial. A transação fica neste estado enquanto está
sendo executada.
- Partially Committed: Após a última instrução ter sido executada.
- Failed: Depois de se descobrir que a execução normal não pode continuar.
- Aborted: quando a transação foi revertida e a base de dados voltou ao estado anterior
a transação
- Committed: como dito antes, após uma conclusão da transação com êxito.
Figure 17 – Estado das Transações
13.2. CONTROLE DE CONCORRÊNCIA
Após uma abordagem inicial sobre transações, é fácil verificar que estas fazem sentido
num âmbito concorrente. O controle de concorrência é uma das coisas mais importantes em
bases de dados grandes. Tomemos novamente como exemplo o caso de um banco, em que
várias pessoas podem acessar aos mesmos dados simultaneamente, fazendo várias
operações iguais ou diferentes. Caso não haja um controlo de concorrência forte, poderiam
existir problemas sérios.
86
TRANSAÇÕES EM BANCO DE DADOS
Para lidar com este problema, foram desenvolvidas técnicas específicas para lidar com
esta concorrência:
- Protocolos Baseados em Locks: Estes protocolos baseiam-se, tal como o nome
indica, na utilização de locks. Através destas propriedades, podemos garantir que o acesso a
determinados dados são feitos de acordo com estes locks, mas basicamente a idéia é que se
uma transação está acessando determinados dados, mais nenhuma outra transação pode
acessar estes (modo exclusivo). Através de propriedades de locks e unlocks conseguimos
estabelecer estas regras. Note que estes locks podem ser do tipo exclusivo, em que se uma
transação obtém um exclusive-mode lock mais nenhuma transação poderá ler ou escrever
sobre esses dados, ou também podem ser do tipo shared, em que se uma transação obtém o
shared-mode lock qualquer outra transação poderá ler os mesmos dados, mas nunca escrever
neles. Um protocolo deste gênero muito conhecido é o Two-Phase Locking Protocol, que
consiste em duas fases distintas, Growing Phase, onde são obtidos os locks sem nunca os
libertar, e a shrinking phase, onde uma transação liberta os locks, não os podendo obter mais.
- Protocolos Baseados em Time-Stamps: Para cada transação no sistema, é atribuído
um time-stamp, antes de esta começar a execução. Há então duas formas de funcionamento: a
atribuição do time-stamp pode ser com a utilização do clock do sistema ou utilizando um
contador lógico, que é incrementado sempre que uma nova transação entra no sistema.
Através destes time-stamps conseguem-se fazer um controle de concorrência.
- Protocolos Multi-Versões: De forma a maximizar ainda mais a concorrência, este tipo
de protocolo cria várias cópias do mesmo item. Assim, cada write (Q) cria uma nova versão de
Q e quando Q é chamado para leitura, o sistema elege a versão do Q mais apropriada,
garantida a serialização. É neste âmbito que surgem então os protocolos Multiversion
Timestamp Ordering e o conhecido Two-Phase Locking.
Existem muitos outros protocolos, no entanto esses se destacam como mais utilizados.
13.3. GESTÃO DE CONCORRÊNCIA NO POSTGRESQL
O PostGreSQL implementa uma política de auto-commit. Desta forma, cada instrução é
tratada como uma transação. Um read é uma transação, um write é outra, etc… Há então duas
formas de contornar esta situação: uma é simplesmente desligar o auto-commit, através da
instrução \SET AUTOCOMMIT OFF. Assim, é tudo considerado uma transação até que a
instrução commit seja executada. A outra solução passa por indicar explicitamente o início e o
fim de uma transação usando o comando BEGIN no início da transação e o comando
COMMIT; no final desta. Caso se pretenda anular a transação, ao invés do comando COMMIT
é possível introduzir o comando ROLLBACK.
13.3.1. Garantia de Isolamento
O PostgreSQL, diferente de muitos outros Sistemas Gerenciadores de Banco de Dados,
mantém os dados coerentes através da utilização de modelos multiversão (Multiversion
Concurrency Control, MVCC). Assim, cada transação vê uma versão da base de dados
(snapshot) tal como era em algum tempo atrás, independentemente do estado atual dos dados,
evitando assim o problema de uma transação poder ver os dados incoerentes. Protocolos em
87
TRANSAÇÕES EM BANCO DE DADOS
nível de locks de tabela e tupla também são possíveis no PostgreSQL para aplicações que não
se adaptem bem ao funcionamento do modelo MVCC, sendo que é necessário estabelecer
manualmente o nível de granularidade desejado. Contudo, um uso cuidado no MVCC é melhor
que a utilização de locks.
13.3.2. Isolamento nas Transações
O SQL standard define quatro níveis de isolamento de transações de acordo com três
fenômenos que não devem acontecer entre transações concorrentes, sendo os fenômenos os
seguintes: dirty reading, nonrepeatable read e phantom read.
- Dirty reading: uma transação lê dados que foram modificados por uma outra
transação concorrente que ainda não realizou o comando commit;
- Nonrepeatable read: uma transação re-lê dados e descobre que os estes foram
modificados por uma outra transação;
- Phantom read: uma transação executa novamente uma pergunta e descobre que os
valores que satisfazem a pergunta são diferentes da anterior, devido a um commit de uma
outra transação.
Os quatro níveis de isolamento das transações SQL são as seguintes:
Tabela 1- Nível de isolamento das transações SQL
Isolation level
Read uncommitted
Read committed
Repeatable read
Serializable
Dirty Read
Possible
Not Possible
Not Possible
Not Possible
Nonrepeatable Read
Possible
Possible
Not Possible
Not Possible
Phantom Read
Possible
Possible
Possible
Not Possible
O PostgreSQL suporta o Read Commited e o Serializable. Como o padrão SQL tem
mais dois elementos, o Read Uncommitted é tratado como um Repetable Read que é tratado
como Serializable. O Read Commited é o grau de isolamento padrão do sistema em questão.
13.3.3. Níveis de Granularidade (Locks)
O PostgreSQL permite a utilização de locks com vários níveis de granularidade (explicit
locking), em nível de tabela, em nível de tuplas e, em versões mais recentes, os chamados
advisory locks. Para criar locks em nível de tabela, a instrução utilizada é a seguinte:
LOCK [ TABLE ] [ ONLY ] name [, ...] [ IN lockmode MODE ] [ NOWAIT ]
Sendo que lock mode é um dos seguintes:
ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE |
SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE
88
TRANSAÇÕES EM BANCO DE DADOS
13.3.4. Consistência de Dados no PostgreSql
Como no sistema PostgreSQL um leitor nunca realiza locks sobre os dados, dados lido
numa transação podem ser escritos por cima por uma outra corrente transação, ou seja, os
dados lidos podem não ser sempre os mais atuais. Para resolver este problema surge um
pouco à necessidade de se utilizarem os locks, SELECT FOR UPDATE, SELECT FOR
SHARE, ou fazendo locks sobre toda a tabela, como forma de impossibilitar a modificação dos
dados em questão.
Porém, a utilização excessiva de locks pode limitar, e muito, a concorrência de uma
base de dados, que, é bastante proveitosa em inúmeras situações.
13.3.5. Atomicidade e Durabilidade no PostgreSql
De acordo com as propriedades anteriores, é justo dizer que as transações funcionam
como um todo. Ou a transação é executada na sua totalidade, ou nada dela é executada. Caso
haja falha, a base de dados é restaurada, através de mecanismos de rollback, que serão
abordados no seguinte tópico. O mesmo acontece com a durabilidade. Caso a transação se
concretize os dados são guardados em memória estável e o utilizador é notificado do sucesso
da operação. Em caso de falha, os dados não são guardados e o utilizador é notificado da falha
que ocorreu.
13.3.6. Mecanismos de Rollback e Recuperação no PostgreSql
Por vezes, em transações complexas e grandes, há necessidade de estabelecer save
points. Estas são pontos de restauração do sistema, considerado um ponto especial na
transação que permite a execução de todos os comandos após ter existido um rollback,
restaurando o estado da transação para o estado atual no save point. Para criar um save point,
basta introduzir o seguinte comando:
SAVEPOINT savepoint_name
Desta forma é então possível estabelecer um save point. Para se retornar ao estado
atual num save point, é necessário invocar comandos de rollback, que podem seguir as
seguintes definições:
ROLLBACK [ WORK | TRANSACTION ]
ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name
A primeira opção restaura o sistema todo, ou seja, volta para o início da transação,
anulando todas as operações efetuadas. A segunda efetua um rollback para um determinado
save point. A seguir um exemplo concreto bem como os resultados de operações com save
points e rollbacks:
89
TRANSAÇÕES EM BANCO DE DADOS
Exemplo:
CREATE TABLE teste (id INTEGER, PRIMARY KEY (id));
COMMIT;
INSERT INTO teste VALUES (1);
COMMIT;
INSERT INTO teste VALUES (2);
SAVEPOINT y;
DELETE FROM teste;
SELECT * FROM teste; //nada é mostrado
ROLLBACK TO y;
SELECT * FROM teste; //são mostrados os valores 1 e 2
ROLLBACK;
SELECT * FROM teste; // é mostrado o valor 1
90
REFERÊNCIAS
REFERÊNCIAS
ELMASRI, Ramez; Sistema de Banco de Dados - Fundamentos e Aplicações - 4ª Edição,
Pearson Education, 2005.
OLIVEIRA, Celso Henrique Poderoso; SQL – Curso Prático; Novatec, 2002.
MOMJIAN, Bruce; PostgreSQL: Introduction and Concepts. E-Book; Addison-Wesley, 2001.
PostgreSQL – Manual (With user comments) do PostgreSQL 8.3. Disponível em
<http://www.postgresql.org/files/documentation/pdf/8.3/postgresql-8.3-A4.pdf>
PostgreSQL – Manual (The SQL Language) do PostgreSQL 9.1. Disponível em
<http://www.postgresql.org/docs/9.1/static/sql.html>
91