Store Procedures com Transaction no MySQL

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.545 ]

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


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 Workbench - com solução de possível problemas de dependência

MySQL Workbench no Slackware 14.0

SQL Dicas & Truques (parte 2)

Gerenciando banco de dados com MySQL (parte 2)

Amarok: migrando de SQLite para MySQL

  
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í...

[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; */

[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

[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;".

[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

[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.


Contribuir com comentário




Patrocínio

Site hospedado pelo provedor RedeHost.
Linux banner

Destaques

Artigos

Dicas

Tópicos

Top 10 do mês

Scripts