MATB09 - Laboratório de Banco de Dados

MATB09 - Laboratório de
Banco de Dados
Stored Procedure e Triggers
Profª. Daniela Barreiro Claro e Denildo Machado (Monitor)
Stored Procedure(SP)
•
São procedimentos armazenados de modo
persistente
Stored Procedure(SP)
•
É usado para armazenar "parte da aplicação"
no banco de dados
SP(Por que usar?)
•
•
Utilizado para realizar validação de dados,
"livrando" a aplicação desta tarefa(ex:
controle de acesso e validação de
informações)
Os SPs podem consolidar e centralizar a
lógica que foi originalmente implementada
na aplicação.
SP(Por que usar?)
•
•
•
Processamento extenso ou complexo que
requer a execução de várias instruções SQL
pode ser direcionado aos procedimentos
armazenados, e todas as aplicações
chamarem os procedimentos
Reduz a duplicidade dos esforços e melhora
a modularidade
Reduz o tráfego de dados entre o cliente e o
servidor
SP(como criar?)
•
Forma geral de criação
CREATE PROCEDURE <nome do
procedimento> (<parametros>)
<declaracao de variaveis>
<corpo do procedimento>
SP(como criar?)
•
Exemplo no MySQL
CREATE PROCEDURE proc_name([parameters, ...])
[characteristics]
[BEGIN]
corpo_da_rotina;
[END]
SP(exemplo no MySQL)
DELIMITER //
CREATE PROCEDURE mySp_correntistaUpdate(v_id INT,
v_nome VARCHAR(60), v_cpf VARCHAR(20))
BEGIN
IF (((v_id > 0) && (v_id != '') ) && (v_nome != '')
&& (v_cpf != '')) THEN
UPDATE tbl_correntista SET correntista_nome =v_nome,
correntista_cpf =v_cpf
WHERE correntista_id =v_id;
ELSE
SELECT 'Os novos NOME e CPF devem ser informados!'
AS Msg;
END IF;
END;
//
DELIMITER ;
SP(e depois de criado?)
•
A forma geral de chamada de SP é:
CALL procedure(...)
ou
EXECUTE procedure( ... )
•
No nosso exemplo:
CALL mySp_correntistaUpdate(1, ‘Denildo
Machado’, ’123.123.111-11′);
SP(observações)
•
•
•
•
•
SPs podem chamar outras SPs
SPs pode retornar um conjunto de resultados
Resultados vindos de SPs podem ser utilizados
por outros SPs(através de cursores)
Os SPs podem receber variáveis​​, retornar
resultados ou modificar as variáveis ​e devolvêlos,
SPs podem utilizar estruturas de controle de
fluxo como IF, WHILE, LOOP, REPEAT
Triggers(Gatilhos)
•
São regras(SPs especiais) que especificam
ações disparadas automaticamente por meio
de certos eventos(Update, Insert ou Delete).
•
Podem ser executados sobre tabelas ou views
•
Segue o modelo ECA
o
Evento-Condição-Ação
Triggers(modelo ECA)
•
Evento
o
o
•
São os eventos que ativam as regras.
São operações de atualização em banco de dados
aplicadas explicitamente
Condição
o
o
o
É 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 a condição for avaliada o seu resultado deve ser
verdadeiro para que a ação da regra seja disparada
Triggers (modelo ECA)
•
Ação
É a ação a ser executada
o É uma série de declarações SQL, mas
também pode ser uma transação ou uma
SP
o
Triggers(Quando usar?)
• Construção de tabelas de log
• Construção de tabelas de auditoria
• Automatizar tarefas de inserção(ex: empregado)
• Manter a integridade da aplicação(ex: Saque Bc)
• Impor regras de negócio(ex: obrigar que toda
•
nota fiscal tenha ao menos um produto)
Executar as regras de negócio (por exemplo,
notificar um gerente que os dados da conta de
um usuário foi alterada)
Triggers(Quando usar?)
•
Replicar os dados (ex: armazenar um
registro de cada mudança, para ser enviado a
outro banco de dados mais tarde)
Triggers(no MySQL)
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_bodytrigger_time: { BEFORE | AFTER
}trigger_event: { INSERT | UPDATE | DELETE }
Triggers (Exemplo no
MySQL)
DELIMITER $$CREATE TRIGGER `Mytrigger`AFTER INSERT ON
Table_CurrentFOR EACH ROWBEGIN
UPDATE Table_Record
SET `Value` = NEW.`Value`
WHERE `Name` = NEW.`Name`
AND `Value` < NEW.`Value`;
END $$
DELIMITER;
Links úteis
Stored Procedures
http://en.wikipedia.org/wiki/Stored_procedure
http://imasters.com.br/artigo/223/sql-server/criacao-e-uso-de-storedprocedures/
http://imasters.com.br/artigo/7556/mysql/stored-procedures-no-mysql/
Triggers
http://www.macoratti.net/sql_trig.htm
http://en.wikipedia.org/wiki/Database_trigger
http://pt.wikipedia.org/wiki/Gatilho_(banco_de_dados)
http://www.devmedia.com.br/introducao-a-triggers/1695