Algumas ferramentas fornecidas pelo mecanismo de banco de dados MySQL são os procedimentos armazenados, funções e gatilho, que são usados para realizar transações ou operações, como inserir ou modificar registros.
Stored procedures são pequenos programas desenvolvidos em código SQL. Um procedimento armazenado é um conjunto de comandos SQL que são armazenados junto com o banco de dados.
A vantagem de um stored procedure é que podemos criá-lo em qualquer editor de texto e até mesmo no servidor, é executado pelo mecanismo de banco de dados e não é acessível aos usuários, mas apenas ao administrador.
Um procedimento armazenado envia seus resultados a um aplicativo para que os exiba na tela evitando sobrecarregar o servidor, no tutorial:
- Procedimentos armazenados MYSQL - Criação, consultas e inserções de dados
Eu tinha explicado como criá-los, aqui adicionaremos funções e gatilhos. Veremos um exemplo em um banco de dados de um imóvel que chamaremos de locação e então criaremos as tabelas.
- Estrutura da tabela para a tabela `real estate` CREATE TABLE IF NOT EXISTS` real estate` (` id` int (11) NOT NULL, `userid` int (11) DEFAULT NULL,` idtype property` int (6) DEFAULT '0 ', `preço` decimal (10,2) PADRÃO' 0,00 ',` comissão` decimal (10,0) NÃO NULO, texto `descrição`, data` highdate` PADRÃO' 0000-00-00 ', `id da província` int (10) DEFAULT NULL, `idlocalidad` int (10) DEFAULT NULL,` address` varchar (150) DEFAULT NULL, `floor and apartment` varchar (100) DEFAULT NULL, texto` between_streets`, `idoperation` int (100 ) DEFAULT NULL, `destaque` char (3) DEFAULT 'no',` image1` varchar (255) DEFAULT NULL, `image2` varchar (255) DEFAULT NULL,` image3` varchar (255) DEFAULT NULL, `image4` varchar (255) DEFAULT NULL, `old` varchar (100) DEFAULT NULL,` mt2covered` int (11) DEFAULT NULL, `lot_surface` int (11) DEFAULT NULL, enum` ativado` ('sim', 'não') NÃO NULL DEFAULT 'si') ENGINE = MyISAM AUTO_INCREMENT = 196 DEFAULT CHARSET = latin1; - Índices da tabela `imobiliária` ALTER TABLE` imobiliária` ADD PRIMARY KEY (` id`);
Agora desenvolveremos um procedimento armazenado para cada transação para consultar, inserir, modificar e excluir um registro.
Podemos usar o Phpmyadmin ou um gerenciador como o Heidisql que é gratuito e funciona em Windows ou Linux com Wine.
Criamos um procedimento armazenado para consultar a tabela de imóveis:
DELIMITER // CRIAR PROCEDIMENTO pa_listainmuebles () BEGIN SELECT * FROM properties; END // DELIMITER;O MYSQL entende que uma instrução termina com um ponto e vírgula. O Declaração DELIMITER mude o caractere final para qualquer outro caractere, por convenção // é usado para indicar o fim do procedimento armazenado para que o MySQL não finalize o procedimento armazenado ao encontrar o primeiro ponto e vírgula.
Podemos ir para o Guia Rotinas para ver cada transação que criamos e a partir daí podemos modificar, executar, exportar ou deletar o código.
Para executar um procedimento de armazenamento, usamos o Comando CALL desde a Aba SQL ou também de uma linguagem de programação como .NET ou Java. Em seguida, invocamos o procedimento armazenado criado com o comando.
CALL pa_listainmuebles ();
A seguir criaremos um stored procedure para inserir uma propriedade, para isso precisaremos de parâmetros do tipo IN, ou seja, atribuiremos dados e variáveis de entrada ao stored procedure para fazer uma transação, neste caso salvá-las no banco de dados.
DELIMITER // CRIAR PROCEDIMENTO pa_nuevoinmueble (IN id INT, IN userid INT, IN price DECIMAL, IN Commission DECIMAL) BEGIN INSERT INTO property` (`id`,` userid`, `price`,` comissão`) VALORES (id, userid ), preço, comissão) END // DELIMITER;
PROLONGAR
Em seguida, podemos executar o procedimento armazenado invocando e atribuindo os parâmetros.
LIGUE para `pa_newinmueble` ('12 ',' 15 ',' 10,00 ',' 0,05 ')Também podemos inserir dados executando a rotina do Phpmyadmin.
PROLONGAR
A seguir iremos criar o stored procedure para editar uma propriedade do editor Phpmyadmin, neste caso iremos apenas modificar o preço.
Podemos criar funções a partir do campo Definidor onde podemos atribuir um usuário definido no servidor Mysql, neste caso o usuário root do host localhost, para que ele possa acessar o procedimento armazenado.
Se quisermos fazer isso a partir do código SQL, devemos executar os seguintes comandos:
CREATE DEFINER = `root` @` localhost` PROCEDURE `pa_editarinmueble` (IN` new property` DECIMAL (10,2), IN` id da propriedade` INT (11)) BEGIN UPDATE propriedade SET preço = nova propriedade WHERE id = id da propriedade; FIMVocê o executa e pronto.
Usando Trigger ou Triggers no Mysql
Um Trigger ou Trigger no MySQL é um conjunto de instruções SQL que dependem de um procedimento armazenado e são usados para serem executados automaticamente quando um determinado evento ocorre em nosso banco de dados. Esses eventos são disparados por transações ou instruções como INSERT, UPDATE e DELETE.
Um exemplo é quando uma alteração é salva em um registro, fazemos automaticamente um backup ou gravamos um arquivo de auditoria para saber quais dados foram alterados, quando e quem os alterou. Eles podem ser usados para qualquer manipulação que afete os dados, para dar suporte ou gerar novas informações.
Criaremos a tabela de auditoria imobiliária abaixo:
CREATE TABLE `audit` (` user` VARCHAR (200) NULL DEFAULT NULL, `description` TEXT NULL,` date` DATETIME NULL DEFAULT NULL) COLLATE = 'latin1_swedish_ci' ENGINE = InnoDBVamos criar uma trigger que salva uma mensagem em auditoria caso alguém altere o preço de um imóvel.
CREATE DEFINER = `root` @` localhost` TRIGGER `real estate_after_update` APÓS A ATUALIZAÇÃO ON` imobiliária` PARA CADA LINHA INSERIR EM auditoria (usuário, descrição, data) VALORES (usuário (), CONCAT ('Preço imobiliário alterado', NEW.id, '(', OLD.price, ') por (', NEW.price, ')'), AGORA ())Este gatilho é executado automaticamente após ocorrer uma atualização de preço, podemos incluir mais campos se desejarmos, com OLD especificamos o campo com o valor antes da modificação e com NEW especificamos o novo valor inserido, com NOW () especificamos a data e a hora atual.
Criamos uma trigger que terá o After Update nas propriedades como um evento, ou seja, após ocorrer uma atualização na tabela de propriedades, nesse caso adicionaremos o usuário que fez a modificação, o novo preço e o preço anterior.
Eu realizo uma atualização em uma propriedade:
LIGUE para `pa_editarinmueble` ('80000', '170')Em seguida, vamos para a mesa de auditoria e podemos ver a mudança:
Também podemos ver os resultados em um relatório na visualização de impressão do Phpmyadmin. Podemos ver como foram salvos os dados que identificam a propriedade, a alteração realizada e o usuário que a efetuou, também temos a data e hora em que a alteração foi realizada.
A seguir, veremos outra aplicação possível, se um imóvel for alugado, então isso muda automaticamente seu status para não ativo ou o tornaremos indisponível.
Para isso devemos ter uma tabela simples onde guardar qual imóvel está alugado, por exemplo prático não levaremos muito rigor nos dados.
CREATE TABLE `rental` (` id` INT (10) NOT NULL, `property id` INT (10) NOT NULL,` inquilino id` INT (11) NOT NULL, PRIMARY KEY (`id`)) COLLATE = 'latin1_swedish_ci 'ENGINE = InnoDB;Em seguida, criaremos o procedimento armazenado para inserir um novo registro na tabela de aluguéis.
CREATE DEFINER = `root` @` localhost` PROCEDURE `pa_newrental` (IN` id da propriedade` INT, IN` id do locatário` INT) IDIOMA SQL NÃO DETERMINISTICO CONTÉM SQL SQL DEFINER DE SEGURANÇA COMENTÁRIO '' INSERT INTO` aluguéis` (`id da propriedade `,` inquilino id`) VALORES (inquilino id, inquilino id)
E então o gatilho para modificar as propriedades foi ativado:
CREATE DEFINER = `root` @` localhost` TRIGGER `rental_after_insert` APÓS INSERT ON` rental` PARA CADA LINHA ATUALIZAÇÃO imobiliária SET ativado = 'no' where id = NEW. PropertyidEm seguida, invocamos o procedimento armazenado onde atribuímos o id da propriedade e o id do cliente ou inquilino que alugo.
LIGUE para novo aluguel (170,11)Em seguida, vamos para a tabela de imóveis e devemos ver que o campo ativado muda de estado SE estiver ativo para NÃO, está ativo.
Vimos as vantagens de usar gatilho com procedimentos armazenados no MySQL para:
- Auditar e registrar eventos ou atividades de alteração de dados em uma tabela.
- Altere o estado de um campo ativando ou negando permissões e ações em uma tabela
- Também permite preservar a consistência dos dados executando ações de acordo com os eventos que afetam uma ou mais tabelas.
