Store Procedures com Transaction no MySQL

facundo

Esse artigo traz um exemplo simples e comentado de como usar procedures com transaction no MySQL. Aqui usamos o MySQL 5 com tabelas do tipo innodb.

[ Hits: 25.540 ]

Por: Fernando Ferreira de Andrade em 26/09/2007

0 0

Denuncie Favoritos Indicar Impressora

Introdução

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:

login
senha
nome
idade

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

Páginas do artigo

1. Introdução

Outros artigos deste autor

Como tornar o Linux mais comercial em pequenas e médias empresas

Leitura recomendada

Instalando o MySQL no Slackware

Configurando Apache + MySQL + PHP no Slackware

OcoMon 2.0RC6 no OpenSUSE 11.2 com PHP5 + MySQL5 + Apache2 com correção de acentuação

SQL Dicas & Truques (versão 0.1)

SQL Dicas & Truques (parte 2)

Comentários

[1] Comentário enviado por hugoeustaquio em 26/09/2007 - 10:07h

Acho que o termo mais correto seria 'stored procedure', que significa 'procedimento armazenado'.... Meio estranho também a afirmação de que separar os dados do usuário da senha será melhor para um grande volume de dados, acho que a maior vantagem das chaves estrangeiras é a integridade dos dados. Fica aqui o toque para quem se interessa em usar stored procedures: Tente inserir somente as regras de negócio intimamente ligadas ao banco, e cuidado com os projetos que não são feitos 'sob medida', pois acontece muito de você chegar em um cliente e ele dizer que já possui um servidor utilizando um determinado banco de dados, e fica difícil adaptar o sistema se ele tiver muita stored procedure. O artigo foi bom, principalmente para quem precisa de um exemplo. Parabéns ao Bacardí...

0 0

[2] Comentário enviado por villas em 18/06/2009 - 23:20h

bicho eu sou newbie em SP mas no seu primeiro script não tem algo errado na linha:
PRIMARY KEY (id) - não seria assim : PRIMARY KEY (usuarioid) ???

Outra coisa que deu erro - será que fiz algo errado?
/* SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'cadastro efetuado com sucesso'; COMMIT; */

0 0

[3] Comentário enviado por villas em 18/06/2009 - 23:50h

Opa achei o erro e fica pra quem quiser acompanhar e favor corrigir o script publicado.

1) faltou o SET antes de-> erro = 'cadastro efetuado com sucesso';
ELSE
erro = 'cadastro efetuado com sucesso';
COMMIT; /*aqui eu salvo as minhas inserções e disponibilizo para os demais*/
END IF;

ficaria assim a linha: SET erro = 'cadastro efetuado com sucesso';

2) Na ultima linha faltou o sinal delimitador E REMOVER O PONTO-E-VIRGULA:

DELIMITER $$

Abraço

0 0

[4] Comentário enviado por villas em 19/06/2009 - 00:13h

O que ainda não entendi é como via php eu faço esse chamado:
chamar "select @erro;".

0 0

[5] Comentário enviado por villas em 19/06/2009 - 00:29h

Corrigindo tudo funcionou belezinha ... obrigado pela dica.

Apenas esse erro que não sei como ( gostaria que fosse no php) mostrar o erro como por exemplo entradas duplicadas que já testei repetir com os mesmo dados e não insere.

Valew

0 0

[6] Comentário enviado por vicentedeandrade em 22/11/2010 - 12:08h

Ola facundo, no SQL Server Management Studio tem um lugar (nome_do_banco/Programação/Procedimentos Armazenados) q lista as Stored Procedures.
Tem algo semelhante no PhpMyAdmin ou MySQL?

Obrigado.

0 0