Neste artigo não vou descrever o que é uma
transaction e nem uma
store procedure, a intenção aqui é dar um exemplo de como utilizar os dois em conjunto.
Para este artigo vamos precisar utilizar o
MySQL versão 5 ou superior e tabelas do tipo
innodb. Vamos criar o seguinte exemplo para o uso de store procedure com transaction. Imagine um cadastro simples onde teremos que entrar as seguintes informações:
Supondo que o sistema pode ter um alto volume de dados, seria melhor colocar o login e senha em uma tabela e os outros dados em outra.
CREATE TABLE usuario (
usuarioid MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
nome VARCHAR(60) NOT NULL,
idade TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE login (
loginid MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
usuarioid MEDIUMINT UNSIGNED NOT NULL,
login VARCHAR(15) NOT NULL,
senha CHAR(8) NOT NULL,
UNIQUE INDEX FK_USUARIO(usuarioid),
UNIQUE INDEX (login),
PRIMARY KEY (loginid),
FOREIGN KEY (usuarioid) REFERENCES usuario(usuarioid)
) ENGINE=INNODB;
Agora vamos criar a procedure de cadastro:
DELIMITER $$
DROP PROCEDURE IF EXISTS `cadastro_usuario`$$
CREATE PROCEDURE `cadastro_usuario`(
IN in_nome VARCHAR(60),
IN in_idade TINYINT UNSIGNED,
IN in_login VARCHAR(15),
IN in_senha CHAR(8),
OUT erro VARCHAR(255)
)
BLOCO1:BEGIN
DECLARE excecao SMALLINT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET excecao = 1; /*caso exista algum erro, a variável excecao terá o valor 1*/
START TRANSACTION; /*inicio a transaction*/
/*inserindo na tabela usuario*/
INSERT INTO usuario (nome, idade) VALUES (in_nome, in_idade);
/*pegando o valor do id inserido e armazena em uma variável*/
SELECT DISTINCT LAST_INSERT_ID() INTO @usuarioid FROM usuario; /*sem o distinct serve, o id se repetirá pelo número total de registros na tabela.*/
/*verificando se houve erro*/
IF excecao = 1 THEN
SET erro = 'erro ao inserir na tabela usuario'; /*armazeno o valor na variável OUT erro*/
ROLLBACK; /* dou um rollback, com isso todas as minhas operações são perdidas*/
LEAVE BLOCO1; /*encerro a procedure*/
END IF;
/*caso não tenha erro, chamo o outro insert*/
INSERT INTO login (usuarioid, login, senha) VALUES (@usuarioid, in_login, in_senha);
/*verifico se inseriu com sucesso*/
IF excecao = 1 THEN
SET erro = 'erro ao inserir login';
ROLLBACK;
LEAVE BLOCO1;
ELSE
erro = 'cadastro efetuado com sucesso';
COMMIT; /*aqui eu salvo as minhas inserções e disponibilizo para os demais*/
END IF;
END$$
DELIMITER ;
Para chamar a procedure:
call cadastro_usuario('nome do usuario', 18, 'login_usuario', 'senha', @erro);
para saber o retorno basta chamar "select @erro;".
Com isso saberemos se foi inserido com sucesso ou não.
Bem, isso foi um exemplo simplificado, poderíamos dentro da procedure por exemplo validar se o valor da variável de login veio vazia ou se era menor que 5 caracteres. Poderíamos ao invés de chamar o comando insert, chamar uma outra procedure que faria a inserção, isso vai depender muito da sua necessidade.
Espero ter ajudado com essa simples apresentação sobre store procedures e transaction.
Qualquer dúvida, podem entrar em contato comigo.
Fernando F. Andrade
DBA MySQL
Email: fernandofandrade@gmail.com