ATPS Programação em Banco Dados - Etapa 2

Prévia do material em texto

FACULDADE ANHANGUERA – UNIDADE CAMPINAS I
TECNOLOGIA EM ANÁLISE E DESENVOLVIMENTO DE SISTEMAS
PROGRAMAÇÃO EM BANCO DE DADOS – ETAPA 02
ALUNO RA
MÁRCIO DA SILVA BENAGES 7505571270
QUEMUEL SANTOS DE AQUINO 8309737322
ATIVIDADE PRÁTICA SUPERVISIONADA (ATPS)
TUTOR: PROF. RICARDO AUGUSTO DA SILVA
CAMPINAS, 03 DE SETEMBRO DE 2014.
SUMÁRIO
Introdução .............................................................................................................3
Pesquisa Sobre Utilização de Triggers e Procedures …...........................................3
Comandos para Criação das Procedures …............................................................9
Elaborar os Comandos DDL …...............................................................................7
Relatório …...…......................................................................................................8
Referências Bibliográficas …................................................................................11
2
INTRODUÇÃO
Nesta Etapa estaremos apresentando o resultado de estudos sobre utilização de triggers e
procedures no banco de dados MySQL.
Como resultado destes, estaremos também automatizando ações com base em eventos
ocorridos nas tabelas, tais como inclusão e exclusão de registros.
PESQUISA SOBRE UTILIZAÇÃO DE TRIGGERS E PROCEDURES
Abaixo estaremos falando sobre os conceitos de bancos de dados, seu gerenciamento,
consultas, modelos de dados, entre vários outros tópicos importantes que coletamos durante
nossa pesquisa, iniciando pelas Triggers.
- Conhecendo as Triggers:
É muito comum, em aplicações que utilizam bancos de dados, que ações sejam disparadas em
resposta ou como consequência de outras, realizando operações de cálculo, validações e, em
geral, surtindo alterações na base de dados.
Em muitos casos, os programadores optam por executarem tais ações a partir da própria
aplicação, executando várias instruções SQL em sequência para obter o resultado esperado.
De fato essa é uma solução que pode até ser tida como mais segura, por certos pontos de vista,
mas tende a tornar ainda mais “pesada” a execução de certas tarefas, requisitando mais
recursos da máquina cliente.
A solução ou pelo menos uma forma alternativa a essa está na utilização de Triggers no banco
de dados, automatizando certas ações com base em eventos ocorridos.
Triggers (“gatilhos” em português) são objetos do banco de dados que, relacionados a certa
tabela, permitem a realização de processamentos em consequência de uma determinada ação
como, por exemplo, a inserção de um registro.
Os triggers podem ser executados ANTES ou DEPOIS das operações de INSERT, UPDATE e
DELETE de registros.
3
Os principais pontos positivos sobre os triggers são:
- Poupa recursos da máquina do cliente;
- Facilita a manutenção, sem que seja necessário alterar o código fonte da aplicação.
Como pontos negativos, podemos levar em consideração:
- Alguém que tenha acesso não autorizado ao banco de dados poderá visualizar e alterar o
processamento realizado pelos gatilhos;
- Requer maior conhecimento de manipulação do banco de dados (SQL) para realizar as
operações internamente;
Para criarmos uma Trigger, seguimos a sintaxe dos comandos abaixo descritos no MySQL:
CREATE TRIGGER nome momento evento
ON tabela
FOR EACH ROW
BEGIN
/*corpo do código*/
END
Podemos definir os parâmetros da seguinte forma:
- nome:
Nome do gatilho. Segue as mesmas regras de nomeação dos demais objetos do banco.
- momento:
Quando o gatilho será executado. Os valores válidos são BEFORE (antes) e AFTER (depois).
- evento:
Evento que vai disparar o gatilho. Os valores possíveis são INSERT, UPDATE e DELETE.
- tabela:
Nome da tabela a qual o gatilho está associado.
4
OBS.: Podemos ressaltar que os comandos LOAD DATA e REPLACE também disparam os
eventos de inserção e exclusão de registros, com isso, os gatilhos também são executados.
Não é possível criar mais de um trigger para o mesmo evento e momento de execução na
mesma tabela. Por exemplo, não se pode criar dois gatilhos AFTER INSERT na mesma
tabela.
Para exemplificar e tornar mais clara a utilização dos gatilhos, faremos uma simulação que
exemplifica algo similar ao que está em nossa ATPS.
Digamos que um mercado que, ao realizar vendas, precisa que o estoque dos produtos seja
automaticamente reduzido. A devolução do estoque deve também ser automática no caso de
remoção de produtos da venda.
Ao inserir e remover registro da tabela ItemVenda, o estoque do produto referenciado deve ser
alterado na tabela ItemVenda. Para isso, serão criados dois triggers: um AFTER INSERT para
dar baixa no estoque e um AFTER DELETE para fazer a devolução da quantidade do
produto.
Como se trata de um ambiente hipotético, faremos uma simulação de uma trigger usando a
tabela ItemVenda de nossa ATPS (conforme código abaixo):
DELIMITER $$
CREATE TRIGGER Tgr_ItemVenda_Insert AFTER INSERT
ON ItemVenda
FOR EACH ROW
BEGIN
UPDATE ItemVenda SET qtde = qtde - NEW.qtde
WHERE iditemvenda = NEW.ItemVenda;
END$$
 
5
CREATE TRIGGER Tgr_ItemVenda_Delete AFTER DELETE
ON ItemVenda
FOR EACH ROW
BEGIN
UPDATE ItemVenda SET qtde = qtde + OLD.qtde
WHERE iditemvenda = OLD.ItemVenda;
END$$
DELIMITER;
No primeiro gatilho, foi utilizado o registro NEW para obter as informações da linha que está
sendo inserida na tabela. O mesmo é feito no segundo gatilho, onde se obtém os dados que
estão sendo apagados da tabela através do registro OLD.
Em ambientes reais, triggers podem ser utilizados para operações mais complexas, por
exemplo, antes de vender um item, verificar se há estoque disponível e só então permitir a
saída do produto.
- Conhecendo as Stored Procedures:
O Stored Procedure é uma coleção de comandos em SQL que tem por finalidade encapsular
tarefas repetitivas, aceitando parâmetros de entrada e retornando 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.
Geralmente o uso desta ferramenta é simple e facilita bastante o trabalho do Programador!
Abaixo vamos mostrar um exemplo simples que poderá facilitar e muito a vida de qualquer
programador.
O exemplo que darei abaixo, pode ser usado em sistemas diversos, desde que seja
corretamente customizado.
6
Criando as Tabelas:
Crie a tabela abaixo em sua base de dados, conforme o exemplo abaixo:
CREATE TABLE `cidade` (
 `id_cidade` int(11) NOT NULL auto_increment,
 `id_pais` int(11) default NULL,
 `nome` varchar(80) default NULL,
 `populacao` longtext,
 PRIMARY KEY (`id_cidade`)
) TYPE=MyISAM;
DROP TABLE IF EXISTS `pais`;
CREATE TABLE `pais` (
 `id_pais` int(11) NOT NULL auto_increment,
 `nome` varchar(80) NOT NULL default '0',
 `idioma` varchar(50) default NULL,
 PRIMARY KEY (`id_pais`)
) TYPE=MyISAM;
Após criar as tabelas, vamos a um exemplo básico de stored procedure:
DELIMITER $$
DROP PROCEDURE IF EXISTS `listar_paises` $$
CREATE PROCEDURE `listar_paises`(IN _id INT)
BEGIN
IF(_id IS NULL) THEN
SELECT * FROM pais;
ELSE
SELECT * FROM pais where id_pais = _id;
END IF;
END $$
DELIMITER ;
7
Vamos entender como funciona?
DELIMITER $$ – Servirá para informar onde começa e termina o sistema.
DROP PROCEDURE – Para excluir uma procedure, o procedimento é simples: Basta utilizar
a cláusula “DROP PROCEDURE”, conforme o exemplo acima.
CREATE PROCEDURE – Essa é a parte onde solicitarei ao Sistema para criar uma
Procedure, disponibilizando entre o BEGIN (queinformará onde inicia) e END (que
informará onde termina) os parâmetros.
IMPORTANTE: Para usar as funcionalidades de Stored Procedure do MySQL, você deverá
incluir a linha a seguir no “PHP.INI”:
“extension=php_mysqli.dll”.
Obs.: A DLL também deverá estar habilitada.
Em Servidores web com o CPanel, geralmente já vem configurado! Se for outra estrutura é
necessário configurar manualmente.
- Para evitar erros, trabalhe na estrutura LAMP, pois nela você terá suporte ao PHP 5 e
MySQL 5. Em outras versões poderá dar erro.
Bem… este foi apenas um pontapé inicial para iniciarmos os trabalhos com o Stored
Procedure.
8
COMANDOS PARA CRIAÇÃO DAS PROCEDURES
Márcio, estarei focando nessa parte. Pretendo te entregar ok até a quarta-feira.
FIZ ATÉ AQUI
REFERÊNCIAS BIBLIOGRÁFICAS
- ELMASRI, Ramez; NAVATHE, Shamkant (orgs.). Sistemas de Banco de Dados. 4a ed. Rio
de Janeiro: Pearson - Addison Wesley, 2005.
- HEUSER, Carlos Alberto. Projeto de Banco de Dados. 6a ed. Porto Alegre: Bookman, 2009.
- DATE, C. J. Introdução a Sistemas de Bancos de Dados. 8a ed. Rio de Janeiro: Campus -
Elsevier, 2004.
9
	SUMÁRIO