SQL

Banco de Dados I
2007
Módulo III: Linguagens de Consulta
(Aulas 3, 4, 5 e 6)
Clodis Boscarioli
Agenda:
O módulo de Consultas de um SGBD;
SQL – Structured Query Language;
Introdução;
Operadores e cláusulas de manipulação;
Funções agregadas;
Operadores e cláusulas de modificação;
Visão;
Asserções e gatilhos;
Exemplos.
Visão Geral
de um SGBD
Relembrando!
Processador
de consultas
Usuários
navegantes
Programadores Usuários
Administrade aplicações sofisticados dores de BD
Interface com Programas de
aplicações
aplicações
Consultas
(queries)
Pré-compilador
de comandos
DML
Programas de
aplicações em
código objeto
Usuários
Esquema de
Banco de Dados
Compilador
DML
Interpretador
DDL
Componentes de execução
de consultas
SGBD
Gerenciador
de memória
Gerenciador
de transações
Gerenciador
de buffer
Gerenciador
de arquivos
Armazenamento
em disco
Índices
Arquivos de
dados
Dados
estatísticos
Dicionário
de dados
BD
Linguagens de Sistemas de BDs
1.
Linguagem de Definição de Dados
Um esquema de dados é especificado por uma conjunto
de definições expressas por uma linguagem especial
chamada Linguagem de definição de dados (do
inglês Data-Definition Language – DDL).
O resultado da compilação dos parâmetros/comandos
DDL geram os dicionário de dados (arquivo de
metadados).
Linguagens de Sistemas de BDs
2. Linguagem de Manipulação de Dados
Manipulação de dados = recuperação de informações
do banco de dados e inserção, remoção e
alteração de dados no banco de dados.
A linguagem de manipulação de dados (do inglês datamanipulation language – DML) é a linguagem que
viabiliza o acesso e a manipulação do dados.
Podem ser:
Procedurais: o usuário especifica procedimentos para recuperar
os dados que necessita.
Não-procedurais: o usuário descreve os dados que necessita.
Processamento de Consultas
Compilador DML: traduz comandos DML em instruções de baixo
nível, entendidos pelo componente de execução de consultas. Além
disso, otimiza a solicitação do usuário.
Pre-compilador para comandos DML inseridos em programas
de aplicação: convertem comandos DML em chamadas de
procedimentos normais da linguagem hospedeira. Interage com o
compilador DML e modo a gerar o código apropriado.
Interpretador DDL: interpreta os comandos DDL e os registra no
dicionário de dados.
Componentes para tratamento de consultas: executa instruções
de baixo nível gerada pelo compilador DML.
SQL - Introdução
A SQL (Structured Query Language - Linguagem de Consulta Estruturada)
É suportada por todos os SGBDs relacionais comerciais. Estes “deveriam”
suportar a SQL padrão. Alguns fornecedores incluem outros comandos
próprios.
A vantagem do SQL padrão é não ter problemas com migração de SGBD para
SGBD.
SQL combina construtores da álgebra relacional e do cálculo relacional de tupla
e proporciona uma interface declarativa e amigável.
Inicialmente chamada de SEQUEL (Structured English Query Language), foi
projetada e implementada pela IBM.
Num esforço conjunto da ANSI (American Nacional Standars Institute) e da ISO
(International Standards Organization) criou-se a primeira versão padrão da
SQL, a SQL-86, substituída posteriormente pela SQL-92 e depois pela SQL-99.
abordada nesse Curso!
O atual projeto da SQL é o padrão 200n.
pode ser considerada uma das maiores razões para o sucesso dos sistemas de
banco de dados relacionais.
SQL - Introdução
Possui comandos para definição de dados, consultas e atualizações,
abrangendo uma DDL e uma DML.
Além disso, define mecanismos para criação de visões, especificações de
segurança, autorizações, definições de restrições e controle de transações.
Também possui regras para embutir os comandos SQL em linguagens de
programação genérica como Java, COBOL, ou C/C++.
O padrão SQL-99 foi divido em uma especificação de núcleo mais pacotes
opcionais. O núcleo deve ser implementado por todos os vendedores de
SGBD’s relacionais compatíveis com este padrão. Os pacotes podem ser
implementados como módulos opcionais, que podem ser adquiridos
independentemente para aplicações específicas de um BD, como data
mining, dados espaciais, dados temporais, entre outros.
Definição de Dados e Tipos de Dados SQL
Termos usados: tabela, linha e coluna.
Principal comando para definição de dados: CREATE
Pode ser usado para criar esquemas, tabelas, domínio, asserções,
gatilhos, visões.
Esquema: agrupa tabelas e outros construtores que
pertencem à mesma aplicação de um banco de dados.
Um esquema SQL é identificado por um nome de esquema
e inclui uma identificação de autorização, que indica o
usuário ou a conta a qual o esquema pertence, bem como
os descritores de cada elemento do esquema.
Os elementos do esquema incluem tabelas, restrições,
visões, domínios.
Exemplo:
Create schema Empresa authorization JSmith
Um catálogo é uma coleção de esquemas em um ambiente
SQL (um SGBD).
Definição de Dados e Tipos de Dados SQL
CREATE TABLE:
Usado
para especificar uma nova relação (tabela),
dando-lhe um nome e especificando seus atributos e
restrições iniciais.
Forma geral
create table empregado.
De
maneira geral, o esquema SQL, no qual as
relações são declaradas, está implicitamente definido
dentro do ambiente no qual os comandos CREATE
TABLE serão executados.
Alternativamente pode-se anexar de maneira
explícita o nome do esquema ao nome da relação.
Forma geral
create table empresa.empregado
create table empregado
( Fnome
Varchar(15)
Minicial
Char,
Lnome
Varchar(15)
SSN
Char(9),
DataNasc
Date,
Endereço
Varchar(30),
Sexo
Char,
Salario
Decimal(10,2),
SuperSSN
Char(9),
DNO
Int
Primary Key (SSN),
Foreign Key (DNO) references Departamento(DNum));
create table departamento
( Dnome
Varchar(15)
Dnumero
Int
GerSSN
Char(9)
GerDataInicio Date,
Primary Key (DNum),
Unique (Dnome),
Foreign Key (GerSSN) references empregado(SSN));
create domain tipo_ssn as char(9)
not null,
not null,
not null,
not null,
not null,
not null,
not null,
Restrições básicas
Not null: valores nulos não são permitidos no atributo.
Default <value>: valor padrão para um atributo.
Check: restringe os valores permitidos para um atributo.
Suponha que os números dos departamentos estejam restritos aos
números inteiros entre 1 e 20.
... Dnum int not null check (Dnum > 0 and Dnum < 21);
create domain D_num as integer check (D_num > 0 and D_num < 21)
Primary Key: especifica um ou mais atributos que definem a chave
primária da relação.
Unique: define chaves alternativas.
Restrições básicas
Foreign Key: define os atributos de chaves estrangeiras e de onde
(qual tabela e atributo) elas vieram.
Set Null, Cascade e Set Default para on delete ou on update:
ações que devem ser tomadas quando da exclusão ao ateração do
atributo de chave estrangeira em sua tabela original).
create table empregado
( ...,
Dno Int not null Default 1,
constraint emppk
primary key (SSN),
constraint empdeptfk
foreing key (Dno) references
departamento(Dnumero) on delete set default on update cascade);
Restrições básicas
Restrições de tuplas
Exemplo:
poder-se-ia adicionar a cláusula check ao
final da declaração create table da tabela
Departamento para ter certeza de que a data da
nomeação do seu gerente é posterior à data de
criação do departamento.
check (dep_cria_data < Ger_data_inicio)
Comandos para alterações de esquemas
Drop: elimina elementos de esquema nomeados, como
tabelas, domínios ou restrições, ou até mesmo o
esquema propriamente dito.
drop schema empresa cascade
drop schema empresa restrict
drop table dependente cascade
Comandos para alterações de esquemas
Alter: as definições de uma tabela básica (em meio estável)
ou de outros elementos do esquema que possuírem
denominação poderão ser alteradas pelo comando ALTER.
alter table empresa.empregado add funcao varchar(12);
alter table empresa.empregado drop endereco cascade;
alter table empresa.empregado alter gersn drop default;
alter table empresa.empregado alter gersn set default “334455”;
alter table empresa.empregado drop constraint empfk cascade;
SQL - Estrutura Básica
•A estrutura básica de uma expressão SQL consiste em
três cláusulas: select, from e where, da seguinte forma:
select A1, A2, ..., An
from r1, r2, ... , rn
where P
onde, cada Ai representa um atributo e cada ri
uma relação e P é um predicado a ser satisfeito.
SQL - Estrutura Básica
A cláusula select corresponde à operação de projeção da álgebra
relacional. Usada para relacionar os atributos desejados no
resultado de uma consulta.
-
A cláusula from corresponde à operação de produto cartesiano
da álgebra relacional. Associa as relações que serão pesquisadas
durante a evolução de uma expressão.
-
A cláusula where corresponde à seleção do predicado da álgebra
relacional. Consiste em um predicado envolvendo atributos da
relação que aparece na cláusula from.
-
Exemplificando...
Considere o Esquema:
empregado (pnome, mnome, unome, ssn, datanasc, endereco,
sexo, salario, superssn, dno)
departamento (dnome, dnumero, gerssn, gerdatainicio)
depto_localizações (dnumero, dlocalização)
projeto (pjnome, pnumero, plocalizaçao, dnum)
trabalha_em (essn, pno, horas)
dependente (essn, nome_dependente, sexo, datanasc,
parentesco)
Encontre o aniversário e o endereço dos
empregados cujo nome seja ‘John B. Smith’.
select datanasc, endereco
from empregado
where pnome = “John” and mnome = “B” and
unome = “Smith”;
Obs: o comando select não elimina tuplas
repetidas!
Encontre o nome e o endereço de todos os
empregados que trabalham no departamento
‘Pesquisa’.
select pnome, unome, endereco
from empregado, departamento
where dnome = “Pesquisa” and Dnumero = DNO;
Obs: na cláusula from estão as tabelas que serão
combinadas (“juntadas”) com um produto
cartesiano.
Para cada projeto localizado em ‘Stanford’,
relacione o número do projeto, o número do
departamento responsável e o último nome do
gerente do departamento, seu endereço e sua
data de aniversário.
select pnumero, dnum, unome, endereco,
datanasc
from projeto, departamento, empregado
where dnum = dnumero and gerssn = ssn and
plocalizacao = ‘Stanford’;
Operação Rename
Em SQL proporciona um mecanismo para renomear tanto atributos quanto
relações, usando a cláusula as, como segue:
select Atributo as Novo_nome
from relação;
Exemplo:
Selecione todos os SSN de empregados
select ssn as seguro_social
from empregado;
Selecione todas as combinações de um SSN de
empregado e um Dnome de departamento
select ssn, dnome as Departamento
from empregado, departamento;
Variáveis Tuplas
A cláusula as é particularmente útil na definição de
variável tupla, como é feito no cálculo relacional.
Uma variável tupla em SQL precisa estar associada a
uma relação.
Variáveis tuplas são definidas na cláusula from por
meio do uso da cláusula as.
as
Para cada empregado, recupere o primeiro e o último
nome do empregado e o primeiro e o último nome de
seu superior imediato.
select e.pnome, e.unome, s.nome, s.unome
from empregado as E, empregado as S
where E.superssn = S.ssn;
Alternativa (na cláusula FROM):
Empregado as E(PN, MN, UM, SSN, DT, END, SEX, SAL,
SSSN, DNO)
Recupere todos os valores dos atributos de empregado
que trabalham no departamento de número 5
select *
from empregado
where DNO = 5;
Recupere o salário de todos os empregados
select all salario
from empregado;
Recupere todos os diferentes valores de salários
select distinct salario
from empregado;
Faça uma lista com todos os números de projetos nos
quais esteja envolvido algum empregado cujo último
nome seja ‘Smith’; ou como empregado, ou como gerente
do departamento que controla o projeto.
(select distinct pnumero
from projeto, departamento, empregado
where Dnum = Dnumero and GerSSN = ssn and
unome = ‘Smith’)
union
(select distinct pnumero
from trabalha_em, projeto, empregado
where pnumero = Pno and essn = SSN and
unome = ‘Smith’);
Obs: Diferença entre conjuntos except
Interseção de conjuntos intersect
Operações em Strings
As operações de strings mais usadas são as checagens
para verificação de coincidências de pares, usando o
operador like. Identificamos esses pares por meio do uso
de dois caracteres especiais:
( %) porcentagem: o caracter % compara qualquer substring;
( _ ) sublinhado: o caracter _ compara qualquer caracter.
Recupere todos os empregados cujos endereços sejam
em Houston,Texas.
select pnome, mnome, unome
from empregado
where endereço like ‘%Houston,TX%’;
Recupere todos os empregados que nasceram na
década de 50.
select pnome, mnome, unome
from empregado
where datanasc like ‘_ _ _ _ _ _ _ _ 5 _’;
Suponha que se deseja ver qual será o resultado
de aumentar em 10% o salário de todos os
empregados que trabalham com o projeto
‘ProdutoX’.
select pnome, unome, 1.1*salário as
aumento_sal
from empregado, trabalha_em, projeto
where ssn = essn and pno = pnumero and
pnome = ‘ProdutoX’;
Recupere todos os empregados do
departamento 5 que ganham entre 30 mil e 40
mil reais.
select *
from empregado
where (salario between 30000 and 40000)
and dno = 5;
Ordenação e Apresentação de Tuplas
• Para listar uma relação em ordem ascendente usa-se:
order by nome do campo asc
• Para listar uma relação em ordem descendente o
comando é:
order by nome do campo desc
• Por default a relação dos itens é efetuada em ordem
ascendente.
Recupere a lista dos empregados e os respectivos
projetos nos quais eles trabalham, ordenada por
departamento e , dentro de cada departamento, também
por ordem alfabética do último nome, e depois, pelo
primeiro nome do empregado.
select dnome, unome, pnome, pjnome
from departamento, empregado, trabalha_em,
projeto
where dnumero = dno and ssn = essn and pno =
pnumero
order by dnome, unome, pnome;
Obs: order by dnome desc, unome asc, pnome asc
Recupere os nomes de todos os empregados
que não têm supervisor.
select pnome, unome
from empregado
where superssn is null;
Faça uma lista com todos os números de projetos nos
quais esteja envolvido algum empregado cujo último
nome seja “Smith”; ou como empregado, ou como
gerente do departamento que controle o projeto.
select distinct pnumero
from projeto
where pnumero in (select pnumero
from projeto, departamento, empregado
where dnum = dnumero and gerssn = ssn
and unome = ‘Smith’)
or
pnumero in (select pno
from trabalha_em, empregado
where essn = ssn and unome = ‘Smith’);
Selecione o número do seguro social de todos os
empregados que trabalham com a mesma combinação
(projeto, horas) em algum dos projetos em que o
empregado ‘John Smith’ (ssn = ‘123456789’) trabalhe.
select distinct essn
from trabalha_em
where (pno, horas) in (select pno, horas
from trabalha_em
where ssn = ‘123456789’)
Encontre os nomes dos empregados cujos salários são
maiores que os salários de todos os empregados do
departamento 5.
select unome, pnome
from empregado
where salario > all (select salario
from empregado
where dno = 5);
Obs: alternativas
= any (ou = some)
Uso de operadores (<, >, >=, <=, <>)
Combinações: >all, <all
Recupere o nome de cada um dos empregados que
tenham um dependente cujo primeiro nome e sexo
sejam o mesmo do empregado em questão.
select pnome, unome
from empregado
where ssn in (select essn
from dependente
where pnome= dependente_nome
and empregado.sexo = dependente.sexo);
Recupere o nome de cada um dos empregados que
tenham um dependente cujo primeiro nome e sexo
sejam o mesmo do empregado em questão.
select pnome, unome
from empregado
where exists (select *
from dependente
where ssn = essn and
empregado.sexo = sexo
and pnome = dependente_nome);
Recupere o nome dos empregados que não possuam
nenhum dependente.
select pnome, unome
from empregado
where not exists (select *
from dependente
where ssn = essn);
Relacione os nomes de gerentes que possuam ao
menos um dependente.
select pnome, unome
from empregado
where exists (select *
from dependente
where ssn = essn)
and
exists (select *
from departamento
where ssn = gerssn);
Encontre todos os empregados que trabalham em
todos os projetos controlados pelo departamento 5.
select pnome, unome
from empregado
where not exists
( (select pnumero from projeto where dnum = 5)
except
(select pno from trabalha_em where ssn = essn));
Recupere os números dos seguros sociais de todos os
empregados que trabalham nos projetos 1, 2 ou 3.
select distinct essn
from trabalha_em
where pno in (1, 2, 3);
Recupere o nome e o endereço de todos os
empregados que trabalham no departamento
‘Pesquisa’.
select pnome, unome, endereco
from (empregado join departamento on dno = dnumero)
where dnome = ‘Pesquisa’;
select pnome, unome, endereco
from (empregado natural join (departamento as
dept(dnome, dno, gerssn, gerdatain)))
where dnome = ‘Pesquisa’;
Para cada empregado, recupere o primeiro e o último
nome do empregado e o primeiro e o último nome de
seu superior imediato. Caso o empregado não tenha um
superior imediato, inclua apenas o primeiro e o ultimo
nome dele.
select e.unome as nome_empregado s.unome as
nome_supervisor
from (empregado as e left outer join empregado as s on
s.superssn = s.ssn);
Obs: ainda tem-se o right outer join, full outer join
Funções Agregadas
SQL provê algumas funções de sumarização de
dados.
Exemplos:
Encontre
a soma dos salários, o maior salário, o
menor salário e a média salarial de todos os
empregados.
select sum (salario), max (salario), min (salario), avg (salario)
from empregado;
Encontre a soma dos salários de todos os empregados
do departamento ‘Pesquisa’, bem como o maior salário,
o menor salário e a média salarial desse departamento.
select sum (salario), max (salario), min (salario), avg (salario)
from (empregado join departamento on dno = dnumero)
where dnome = ‘Pesquisa’;
Recupere o número total de empregados da
empresa
select count(*)
from empregado
Recupere o número de empregados do
departamento ‘Pesquisa’.
select count(*)
from empregado, departamento
where dno = dnumero and dnome = ‘pesquisa’
Recupere o nome de todos os empregados que
possuem dois ou mais dependentes.
select unome, pnome
from empregado
where (select count(*)
from dependente
where ssn = essn) >= 2
Conte o número de valores diferentes de salário
contidos no banco de dados
select count (distinct salario)
from empregado
Para cada departamento, recupere o número de
empregados que nele trabalham e a média de
seus salários
select dno, count (*), avg (salario)
from empregado
group by dno;
Para cada projeto, recupere seu número, seu nome e o
número de empregados que nele trabalham.
select pnumero, pjnome, count (*)
from projeto, trabalha_em
where pnumero = pno
group by pnumero, pjnome
Para cada projeto em que trabalhem mais de dois
empregados, recupere o número do projeto, seu nome e
o número de empregados.
select pnumero, pjnome, count (*)
from projeto, trabalha_em
where pnumero = pno
group by pnumero, pjnome
having count (*) > 2;
Para cada departamento que tenha mais de
cinco empregados, recupere o número do
departamento e o número dos empregados que
recebem mais de 40 mil.
select dnumero, count (*)
from departamento, empregado
where dnumero = dno and salario > 40000 and dno in
(select dno
from empregado
group by dno
having count (*) > 5)
group by dnumero;
SQL - Inserção, exclusão e Atualização
Adicione uma nova tupla à relação empregado.
insert into empregado
values (‘Richard’, ‘K’, ‘Marini’, ‘4830672’, ’13-06-1953’, ’98,
Oak Forest, TX’, ’M’, 37000, ‘3468345’, ‘4’);
Insira uma tupla, de um novo empregado, para o qual
sabe-se apenas os atributos pnome, unome, dno e ssn.
insert into empregado(pnome, unome, dno, ssn)
values (‘Richard’, ‘Marini’, 4, ‘4830672’);
Crie uma tabela que contenha o total de empregados e
o total de salários e cada departamento.
create table depts_info
(dept_nome varchar(15),
no_de_emps
integer,
total_sal
integer);
insert into depts_info (dept_nome, no_de_emps, total_sal)
select dnome, count (*), sum (salario)
from (departamento join empregado on dnumero = dno)
group by dnome;
Exemplos de uso do comando delete:
delete from empregado
where unome = ‘Brown’;
delete from empregado
where ssn = ‘1234530’;
delete from empregado
where dno in (select dnumero
from departamento
where dnome = ‘Pesquisa’);
delete from empregado
Exemplos de uso do comando update:
update projeto
set plocalização = ‘Bellaire’, dnum = 5
where pnumero = 10;
update empregado
set salario = salario * 1,1
where dno in (select dnumero
from departamento
where dnome = ‘Pesquisa’);
Visões
Na terminologia SQL, uma visão é uma tabela única,
derivada de outra tabela, que pode ser uma tabela
básica ou uma visão previamente definida.
Uma visão não existe de forma física, ela é considerada
uma tabela virtual, em contraste com as tabelas
básicas, cujas tuplas são realmente armazenadas no
BD.
Isso limita as operações de atualização possíveis para
as visões, embora não imponha nenhuma limitação para
as consultas.
Visões
Supõe-se que uma visão esteja sempre atualizada; se modificarmos
as tuplas das tabelas básicas sobre as quais a visão foi construída,
a visão deverá, automaticamente, refletir essas alterações.
Conseqüentemente, a visão não é realizada no instante de sua
definição, mas quando especificarmos uma consulta sobre ela.
É responsabilidade do SGBD, e não do usuário, ter a certeza de
que uma visão está atualizada.
Se não precisarmos mais de uma visão poderemos usar o comando
drop view:
drop view trabalha_em1;
Visão
Materialização de visões: criar fisicamente uma
tabela temporária a partir da primeira consulta a
essa visão e mantê-la, considerando que
poderão seguir-se outras consultas.
Nesse caso, deve-se ter uma estratégia eficiente
para a atualização da tabela da visão sempre
que as tabelas básicas forem atualizadas, de
modo a garantir que a visão esteja sempre
atualizada.
Problemas de atualizações sobre Visões
Suponha que seja emitido um comando de atualização do
atributo pjnome de ‘ProdutoX’ para ‘ProdutoY’, para o
empregado ‘John Smith’.
update trabalha_em1
set pjnome = ‘ProdutoY’
where unome = ‘Smith’ and pnome ‘John’ and pjnome =
‘ProdutoX’;
Essa consulta pode ser mapeada em diversas atualizações
das relações básicas para chegar ao efeito desejado
pela atualização da visão.
...
Problemas de atualizações sobre Visões
(a)
update trabalha_em
set pno = (select pnumero
from projeto
where pjnome = ‘produtoY’)
where essn in (select ssn
from empregado
where unome = ‘Smith’ and pnome = ‘John’)
and
(select pnumero
from projeto
where pjnome = ‘ProdutoX’ );
(b)
update projeto set pnome = ‘ProdutoY’
where pjnome = ‘ProdutoX’;
Obs: (a) e (b) podem produzir resultados diferentes... Provavelmente, o resultado de (a)
será o mais adequado.
Visões
Em resumo:
Uma visão de uma única tabela é atualizável se a visão contiver, entre
seus atributos, a chave primária, bem como todos os atributos com
restrição not null que não contiverem valores default especificados.
As visões definidas a partir de diversas tabelas (utilizando-se de
junções), em geral, não são atualizáveis.
As visões definidas usando-se as funções de agrupamento e
agregadas não são atualizáveis.
Em SQL, a cláusula with check option precisa ser adicionada ao
final da definição da visão se ela puder ser atualizada. Isso permite
que o sistema cheque a capacidade de atualização da visão e
planeje a estratégia de execução das atualizações das visões.
Asserções
Permite a determinação de restrições genéricas sobre o banco de
dados, via asserções declarativas, usando a declaração create
assertion da DDL.
Em uma asserção é dado um nome à restrição especificada por
meio de uma condição semelhante à cláusula WHERE de um
consulta SQL.
Exemplo:
“O salário de um empregado não pode ser maior que o salário do
gerente do departamento em que ele trabalha.”
Asserções
create assertion limite_salário
check (not exists
(select *
from empregado as E,
empregado as S, departamento D
where e.dno = d.numero and
d.ssnger = s.ssn and e.salario >
s.salario));
Asserções
A restrição limite_salário é seguida da palavra-chave check, que é seguida
da condição entre parênteses que precisa ser verdadeira em todos os
estados do banco de dados para que a asserção seja satisfeita.
O nome da restrição pode ser usado posteriormente para sua referência,
modificação ou eliminação.
O SGBD é responsável por garantir que a condição não seja violada.
Sempre que alguma tupla no banco de dados fizer com que a condição
ASSERTION evolua para FALSE, a restrição será violada.
A técnica básica para a formulação de asserções é especificar uma
consulta que selecione as tuplas que violem a condição desejada. Por
meio da inclusão dessa consulta em uma cláusula NOT EXISTS, a
asserção especificará que o resultado dessa consulta deverá ser vazio.
Logo, a asserção será violada se o resultado da consulta não for vazio.
As asserções devem ser utilizadas somente nos casos em que não for
possível usar check em atributos, nos domínios e nas tuplas.
Triggrers (Gatilhos)
Regras que especificam ações disparadas
automaticamente por meio de certos eventos
(modificações no banco de dados);
Considerados um importante avanço para os
sistemas de banco de dados.
Embora haja definição padrão, cada SGBD
define a sintaxe para os gatilhos. No
PostgreSQL, por exemplo, os gatilhos executam
procedimentos ou “funções sem retorno”. Já no
Oracle, a especificação é bastante parecida com
a SQL-99.
Funções e Gatilhos no PostgreSQL
CREATE OR REPLACE FUNCTION idade(char)
RETURNS integer AS '
DECLARE
idade integer;
BEGIN
select (current_date - datanasc)/365 into idade from aluno where
nome = $1;
RETURN idade;
END;
' LANGUAGE plpgsql;
Funções e Gatilhos no PostgreSQL
CREATE OR REPLACE FUNCTION media(char)
RETURNS numeric AS '
DECLARE
med numeric;
BEGIN
select avg(quantidade) into med from fornecimento
where cod_fornec = $1;
RETURN med;
END;
' LANGUAGE plpgsql;
Funções e Gatilhos no PostgreSQL
CREATE OR REPLACE FUNCTION projetos.aumenta(numeric)
returns numeric
AS $BODY$
BEGIN
update projetos.fornecimento set quantidade = quantidade + $1;
return null;
END; $BODY$
LANGUAGE 'plpgsql';
SELECT projetos.aumenta(2000);
Funções e Gatilhos no PostgreSQL
CREATE OR REPLACE FUNCTION backupfor() RETURNS trigger AS
$$
BEGIN
insert into bakfornec (SELECT * FROM FORNECEDOR);
RETURN NULL;
END;
O gatilho indicado é responsável por
executar a função que faz uma cópia das linhas
$$
da tabela fornecedor para uma tabela auxiliar,
LANGUAGE plpgsql;
toda vez que uma exclusão é executada.
CREATE TRIGGER tbackup
AFTER DELETE ON fornecedor
FOR EACH ROW EXECUTE PROCEDURE backupfor();
Modelo generalizado para BD Ativos e
Gatilhos Oracle
Modelo: evento-condição-ação ou modelo ECA. Uma regra no
modelo ECA tem três componentes:
O evento (ou eventos) que ativa (ativam) a regra: esses eventos
normalmente são operações de atualização de banco de dados
aplicadas explicitamente. Porém, no modelo geral, eles poderiam
também ser eventos temporais* ou outros tipos de eventos externos;
A condição que determina se a ação da regra deve ser executada:
uma vez ocorrido o evento, uma condição opcional pode ser avaliada.
Se nenhuma condição for especificada, a ação será disparada pelo
evento. Se uma condição for especificada, primeiro ela será avaliada, e
somente se seu resultado for verdadeiro a ação da regra será
executada.
A ação a ser executada que normalmente é uma sucessão de
declarações SQL, mas também poderia ser uma transação de banco de
dados ou um programa externo que será executado automaticamente.
* Exemplo: dê este comando diariamente as 5:30.
Exemplos
Considere o seguinte esquema simplificado:
Empregado (nome, ssn, salario, *dno, *supervisor_ssn)
Considere que é permitido o valor null para dno,
indicando que um empregado pode estar
temporariamente sem departamento.
Departamento (dnome, dno, total_sal, *gerente_ssn)
Note que o atributo total_sal é derivado, e seu valor
corresponde à soma de salários dos empregados
locados naquele departamento em particular. A
manutenção do valor correto de tal atributo pode ser
tarefa de uma regra ativa.
Projetando a regra ativa
Primeiro é necessário determinar os eventos
que possam causar uma mudança no valor de
total_sal, que podem ser do seguinte tipo:
1.
2.
3.
4.
Inserindo uma ou mais tuplas de novos empregados;
Mudando o salário de um ou mais empregados existentes;
Realocando um ou mais empregados entre os
departamentos;
Excluindo uma ou mais tuplas de empregados.
Projetando a regra ativa
Evento 1: será necessário apenas recalcular total_sal
se o empregado novo for imediatamente nomeado para
um departamento - isto é, se o valor do atributo de dno
para a tupla do novo empregado não for null.
Conseqüentemente, essa será a condição a ser
checada.
Uma condição semelhante poderia ser atribuída ao
evento 2 e 4: determinar se o empregado cujo salário é
alterado (ou que está sendo excluído) está alocado no
departamento.
Para o evento 3, sempre será executada uma ação que
mantém o valor correto de total_sal, assim, nenhuma
condição é necessária (a ação sempre será executada).
Projetando a regra ativa
A ação para os eventos 1, 2 e 4 é atualizar o
valor de total_sal automaticamente para que o
departamento do empregado reflita as recentes
inserções, atualizações e remoções de salário
dos empregados .
No caso do evento 3, é necessária uma ação
dupla: uma para atualizar o total_sal do
departamento antigo e outra para atualizar o
total_sal do novo departamento.
Sintaxe (Oracle – PL/SQL)
<trigger> ::= create trigger <nome gatilho>
(after | before) <eventos
ativadores> on <nome tabela>
[ for each row ]
[ when <condição> ]
<ações disparadas>
<eventos ativadores> ::= <evento ativador> {or <evento ativador>}
<evento ativador> ::= insert | delete | update [of <nome coluna> {,
<nome coluna>}]
<ação disparada> ::= <bloco PL/SQL>
A especificação
R1 : create trigger totalsal1
after insert on empregado
for each row
when (new.dno is not null)
update departamento
set total_sal = total_sal + new.salario
where dno = new.dno;
New: palavra chave usada para se referir à tupla
que foi inserida ou atualizada.
A especificação
R2 : create trigger totalsal2
after update of salario on empregado
for each row
when (new.dno is not null)
update departamento
set total_sal = total_sal + new.salario –
old.salario
where dno = new.dno;
Old: palavra chave usada para se referir à tupla excluída
ou à tupla antes de sua atualização.
A especificação
R3 : create trigger totalsal3
after update of dno on empregado
for each row
begin
update departamento
set total_sal = total_sal + new.salario
where dno = new.dno;
update departamento
set total_sal = total_sal - old.salario
where dno = old.dno;
end;
A especificação
R4 : create trigger totalsal4
after delete on empregado
for each row
when (old.dno is not null)
update departamento
set total_sal = total_sal – old.salario
where dno = old.dno;
A especificação
R5 : create trigger inform_supervisor1
before insert or update of ssn, supervisor_ssn on
empregado
for each row
when
(new.salario > (select salario from empregado
where ssn = new.supervisor_ssn))
inform_supervisor(new.supervisor_ssn, new.ssn)
Onde inform_supervisor é um procedimento externo
previamente declarado.
PostgreSQL:Outras funcionalidades SQL
Como já dito, cada SGBD pode implementar outras
funcionalidades SQL, para além da SQL-padrão. No
PostgreSQL, por exemplo, estão definidas algumas
funções matemáticas, de manipulação de strings, data
e hora e endereçamento de rede.
Referências Bibliográficas
Sistemas de Banco de Dados. (Cap. 5) Abraham Silberchatz, Henry F.
Korth e S. Sudarshan. 3ª Edição. Makron Books, 1999.
Sistemas de Banco de Dados. (Cap. 8) Ramez Elsmari, 4ª Edição.
Shamkant B. Navathe. Pearson Addison Wesley, 2005.
Introdução a Banco de Dados (Apostila). (Cap. 8) Osvaldo Kotaro
Takai, Isabel Cristina Italiano, João Eduardo Ferreira. DCC-IME-USP,
2005.
A First Course in Database Systems. (Cap. 5) Jeffrey D. Ullman,
Jennifer Widom. Prentice Hall, 1997.
PostgreSQL: Funcionalidades Básicas. Monica T. T. Pereira.
Minicurso CONGED 2007.
Notas de Aula sobre Gatilhos no Oracle, Prof. Sarajane Marques Peres.