Programação em Bancos de
Dados Relacionais
[email protected]
Programando em SGBDs
Relacionais
• Nem toda tarefa é factível em SQL
• SQL – Linguagem de Consulta, não de
Programação
• SQL não implementa, de fato, uma Máquina
de Turing completa!
• Linguagens de programação (C, Pascal,
Cobol, Fortran, Lisp, Prolog): Máquinas de
Turing!
Exemplo
• Suponha uma tabela Projeto, criada da
seguinte forma:
Create table Projeto (
Cod int primary key,
Titulo Varchar(20) not null unique,
CodProjPai int references Projeto)
• Um projeto pode ser parte de outro projeto
(um projeto maior)
• Um projeto pode ser subdividido em outros
Desafio:
• Crie uma consulta em SQL que, dado o
projeto de código 14, mostre todos os seus
descendentes.
• Ou então os seus ascendentes.
• !
• ?
• Impossível! SQL não faz tudo o que uma
Linguagem de Programação faz!
Como resolver?
• Os SGBDs Relacionais acrescentam
linguagens de programação à SQL
• É possível fazer-se estruturas programáticas
(procedimentos) nos SGBDRs
• Isso serve para resolver a incompletude de
SQL bem como implementar parte das
regras de negócio!
Regras de negócio
• Cliente/Servidor:
– Processa no cliente a interface e (parte das)
regras de negócio
– Processa no servidor (parte das) regras de
negócio e acesso a dados
• Isso torna o cliente muito caro!
– Robustez para suportar o processamento
– Alto custo de reinstalação/upgrade dos sistemas
A solução
• Cliente executando apenas interface (cliente
magro)
– Fácil de ser reinstalado
– Menor capacidade de processamento
• Regras de negócio num servidor
centralizado
• Dados idem!
Possíveis caminhos
• Um servidor de aplicações
– Regras de negócio implementadas em
componentes
– Compartilhadas pelos clientes
– Atualizações e manutenções centralizadas
– Disponíveis em serviços como Web Services
Possíveis caminhos
• Procedimentos armazenados
– Regras de negócio implementadas em
componentes
– Compartilhadas pelos clientes
– Atualizações e manutenções centralizadas
– Disponíveis no próprio SGBD!
– Procedimentos armazenados
Qual a melhor?
• Servidor de aplicações:
– Maior independência de plataforma
– Dificuldade de integração Dados/Programas
• Procedimentos Armazenados
– Total dependência de plataforma (SGBD)
– Facilidade de integrar Dados e Programas
(linguagens nativas)
Os caminhos
• De um modo geral opta-se pelo servidor de
aplicações
– A integração pode ser facilitada por ferramentas
– O valor agregado pela Independência de
plataforma é muito alto!
• Mas há motivos para escolher
Procedimentos Armazenados!
Porque Procedimentos
Armazenados
• Fácil desenvolvimento
• Cultura de uso de um SGBD
• Implementação de partes do processamento
– Não há obrigação de escolher entre
Procedimentos Armazenados e Servidores de
Aplicação
– Normalmente encontra-se uma solução híbrida!
Procedimentos Armazenados
• O que é
– Uma estrutura de programa acionável a partir
do cliente ou do próprio servidor
– Pode receber parâmetros
– Executa alguma tarefa no SGBD (Transações)
– Busca algum dado no SGBD
O primeiro procedimento
armazenado
• Um modelo em UML
• Um Diagrama de Colaboração
• Uma chamada do tipo:
CadAluno( )
Secretária
Janela
Cadastro
Alunos
Novo(Aluno)
Alunos
Como implementa
• O método CadAluno ( )
• Cliente: só interface
Como Implementa
•
•
•
•
•
O Método Novo( )
SGBD
Os parâmetros serão os dados dos alunos!
Chamado pela Janela Cadastro de Alunos!
Duas soluções:
a) Insert into alunos (...)
b) Exec NovoAluno(...) /* Transact Sql */
•
Qual a melhor?
Acoplamento
• O quanto um componente A do programa
interfere num componente B?
– Componente: Um método, uma classe, uma
tabela, uma janela, um arquivo, etc.
– Interferir: Mudar algo em A torna necessário
alterar também B? E o contrário?
Acoplamento
• À medida de o quanto alterações de um
componente impactam em outro denominase Acoplamento!
• Obs:
– Não é necessário quantificar o acoplamento!
– Deve-se buscar acoplamentos os mais baixos
possíveis!
No nosso exemplo
• Onde há mais acoplamento?
• Suposição: Mudou o nome de uma coluna
na tabela Alunos
– Em a): Todos os locais onde a chamada é feita
precisam ser modificados
– Em b): Somente a implementação do
procedimento NovoAluno precisa ser
modificada – o cliente não “vê” nada!
Como fazer um procedimento
Armazenado
• Comando DDL
• Ex: (Transact SQL)
Create procedure NomeProc <Parametros> as
<Comandos>
• Em qualquer SGBDR é possível criar
procedimentos
• Os conceitos são os mesmos!
Exemplo de Procedimento
Create procedure AlteraPreco as
@CodProd int, @NovoPreco as
numeric as
Update Estoque
Set Preco = @NovoPreco
Where Cod = @CodProd;
Pode-se definir uma transação
Create procedure Transfere @ct_origem char(6),
@ct_dest as char(6), @valor as numeric as
Begin transaction
update contas set saldo = saldo - @valor
where num = @ct_origem;
update contas set saldo = saldo + @valor
where num = @ct_dest;
Commit;
Pegando um dado p/ variável
local
Create procedure Transfere @ct_origem
char(6), @ct_dest as char(6), @valor as
numeric as
Declare @saldo as numeric;
Select @saldo = saldo
From contas
Where Num = @ct_origem;
...
Usando comandos de fluxo: if
Create procedure Transfere @ct_origem char(6),
@ct_dest as char(6), @valor as numeric as
Declare @saldo as numeric;
Select @saldo = saldo
From contas
Where Num = @ct_origem;
...
if @saldo < 0 /* não é permitido *
...
Pode-se encerrar transações com
RollBack
Create procedure Transfere @ct_origem char(6), @ct_dest
as char(6), @valor as numeric as
Begin transaction
Declare @saldo as numeric;
update contas set saldo = saldo - @valor
where num = @ct_origem;
update contas set saldo = saldo + @valor
where num = @ct_dest;
Select @saldo = saldo
From contas
Where Num = @ct_origem;
if @saldo < 0 /* não é permitido */
rollback;
else
Commit;
Procedimentos podem retornar
dados
• Outro exemplo
MediaAluno( )
Secretária
Janela
Cadastro
Alunos
C = PegaMedia(Aluno)
Alunos
Como chamar?
• Num ambiente externo (cliente)
Exec PegaMedia ‘19921414’
– Retorna dados que devem ser tratados
(Datasets, Recordsets, etc.)
• No mesmo ambiente
Exec @Media=PegaMedia ‘19921414’
– Semelhante ao uso de select com variáveis
locais
Triggers (Gatilhos)
• Há situações em que se pode adaptar um
procedimento armazenado a um evento
• Certas regras de integridade não podem ser
implementadas na criação da tabela
• Ex:
– Como impor uma restrição de integridade com
cardinalidade 1 para 30?
Situando o exemplo
• Numa universidade, só podem haver no
máximo 30 alunos numa turma
• Tabela Turma (CodDisc$, MatAluno$)
• Pode-se fazer um trigger que conte a
quantidade de alunos matriculados
– Se for inferior ao limite, tudo ok
– Se for superior ao limite, operação cancelada
(rollback)
Eventos
• Triggers estão associados a eventos
transacionais
– Insert: o trigger executa associado a uma
operação de inclusão numa tabela
– Update: o trigger executa associado a uma
operação de alteração numa tabela
– Delete: o trigger executa associado a uma
operação de exclusão numa tabela
Como criar um Trigger
• Comando DDL
• Ex: (Transact SQL)
Create trigger NomeTrigger on Tabela for
[insert, update, delete] as
<comandos>
• Em qualquer SGBDR é possível criar
procedimentos
• Os conceitos são os mesmos!
Nosso exemplo
Create trigger tg_turmas_iu on turmas for insert,
update
As
Declare @quant as int;
Select @quant = count(*)
from turmas t, inserted i
Where t.CodDisc = i.CodDisc;
If @quant > 30 /* Limite máximo */
Begin
raiserror(‘Turma com limite máximo’,16,1);
rollback;
End;
• Raiserror: retorna uma mensagem de erro
(Transact Sql)
Inserted
• Tabela Virtual
• Para manipular os dados que estão sendo
incluídos na tabela
• Possui as mesmas colunas da tabela
Deleted
• Tabela Virtual
• Para manipular os dados que estão sendo
excluídos da tabela
• Possui as mesmas colunas da tabela
Dados Novos e Velhos
• Inserted são os dados “novos” (entrando)
• Disponíveis nos triggers associados a
– Insert
– Update
• Deleted são os dados “velhos” (saindo)
• Disponíveis nos triggers associados a
– Delete
– Update
Cursores
• Mais um exemplo
Alunos
*(Para cada aluno A de
computação) FazMatricula(A)
Alunos
Como implementar?
• FazMatricula é um procedimento
armazenado
– Recebe como parâmetro a matrícula de um
aluno (identificador de um aluno)
• Para implementar a chamada aos múltiplos
valores:
• Cursores
O que é um cursor
•
•
•
•
Uma lista de dados
Resultantes de um comando select
Podem ser processados separadamente
Podem ser atribuídos a uma (ou mais)
variáveis
• Precisam ser declarados, abertos, fechados e
desalocados!
Como se usa
Declare NomeCursor cursor for select ...
– Declara um cursor
Open NomeCursor
– Abre o cursor (executa a consulta)
Fetch NomeCursor into <variáveis>
– Traz um dado do cursor para variáveis (é
necessário compatibilidade de tipos)
Como se usa
Close NomeCursor
– Fecha o cursor
Deallocate NomeCursor
– Desaloca o cursor (libera a área)
• @@Fetch_Status: uma variável de ambiente cujo
valor depende do resultado do último Fetch
– Zero se não retornou dados
– Diferente de zero se retornou
• @@Fetch_Status é Transact SQL
• Nos outros SGBDs há soluções similares
Usando um cursor
...
Declare cs_alunos cursor for select matricula
from alunos;
Declare @matr as char(11);
Begin transaction /* uma transação muito longa */
Fetch cs_alunos into @matr;
While @@Fetch_Status <> 0 /* comando de fluxo */
begin
exec FazMatricula @matr;
Fetch cs_alunos into @matr;
end;
Commit;
Close cs_alunos;
Deallocate cs_alunos;
...
Observações especiais
• Cursores podem ter mais de um dado por
linha
Declare cs_turmas cursor for select matricula,
coddisc from turmas;
...
Fetch cs_turmas into @aluno, @disc;
• Pode-se e usar ao mesmo tempo quantos
cursores forem necessários
Programação em SGBDs
Relacionais
FIM!
“Dizes-me com quem andas e eu te direi se vou contigo”
Barão de Itararé