MySQL, Stored Procedures

Olá leitor, agora que já vimos os comandos básicos do SQL no MySQL vamos ver algumas coisas um pouco mais avançadas que podem deixar suas aplicações muito mais fáceis de dar manutenção, além também de mantermos as boas práticas de programação.

Introdução aos Stored Procedures

Quando estamos desenvolvendo nossas aplicações que acessam banco de dados, é muito comum que acabemos executando rotinas de manipulação de dados, que tendem com o passar do tempo, ficar cada vez mais complexas. 

Dependendo da rotina que queremos executar, pode acarretar em um consumo de recursos da aplicação relativamente alto, no caso de aplicações web, isso acaba se tornando mais visível, pois estas aplicações consomem uma quantidade maior de informações para poderem trafegar pela rede.

Para  contornar ou pelo menos atenuar esse consumo excessivo de recursos, é retirado parte da responsabilidade da aplicação e transferida para o banco de dados.

Certo agora você pode estar se perguntando, como executar várias rotinas diretamente no banco de dados a partir de uma única instrução? A resposta é,  utilizando as famosas Stored Procedures (ou Procedimentos Armazenados, em português).

Stored procedures são rotinas definidas no banco de dados, identificadas por um nome pelo qual podem ser invocadas. Um procedimento pode executar uma série de instruções, receber parâmetros e retornar valores.

Utilizando uma Stored Procedure

Vamos agora ver um exemplo de uso de uma Stored Procedure e a execução do mesmo exemplo sem a utilização da Stored Procedure.

  • O cliente faz a compra de alguns itens em uma loja virtual, esses itens são inseridos no pedido;
  • O pedido então e registrado e fica com o status “PENDENTE” até ser confirmado;
  • O funcionário da loja confirma o pedido, registrando o movimento no livro caixa.

Enquanto o pedido não é confirmado, nada é registrado no livro do caixa, logo é necessário ter uma rotina  que deve executar as seguintes ações:

  • Alterar o status do pedido;
  • Alterar o status dos itens do pedido;
  • Registrar o valor do pedido no caixa.

Temos então três instruções que precisamos realizar. Veja na figura 1 como ficam essas instruções:

Figura 1: Execução da rotina sem Stored Procedure

No entanto, podemos deixar isso mais simples e em vez de chamar essas três instruções separadas podemos chamá-las de uma só vez e executar todas as operações de update/insert/delete, que a partir daí, ficariam por conta do servidor. A representação deste modelo e mostrada na figura 2.


Figura 2: Execução da rotina usando Stored Procedure

As principais vantagens de utilizar as procedures são:

  • Simplificação da execução de instruções SQL pela aplicação;
  • Transferência de parte da responsabilidade de processamento para o servidor;
  • Facilidade na manutenção, reduzindo a quantidade de alterações na aplicação.

Alguns pontos negativos são:

  • Necessidade de maior conhecimento da sintaxe do banco de dados para escrita de rotinas em SQL;
  • As rotinas ficam mais facilmente acessíveis. Alguém que tenha acesso ao banco poderá visualizar e alterar o código.

Criando e invocando Stored Procedures no MySQL

Bom chega de teoria, vamos colocar a mão na massa agora e criar a nossa primeira procedure, iniciando pela sintaxe utilizada para a criação desse tipo de objeto, que podemos ver no exemplo abaixo. Para o exemplo utilizaremos as tabelas criadas no post MySQL, afinal o que é?

DELIMITER // 
 CREATE PROCEDURE selecionar_leitor()
   BEGIN
   SELECT *  FROM LEITOR;
   END //
 DELIMITER ; 

No código acima onde temos o “selecionar_leitor”, deve-se informar o nome que identificará o seu procedimento.  Por boas práticas o nome deste procedimento não deve iniciar com caracteres especiais e a separação das palavras deve-se utilizar “ _ “.

Podemos também passar parâmetros, essa parte é opcional, e deve ser informada só quando houver a necessidade de passar alguma informação para o procedimento. Caso seja necessário passar algum parâmetro deve-se utilizar a sintaxe como no exemplo abaixo.

(MODO nome TIPO, MODO nome TIPO, MODO nome TIPO)

Do comando acima temos a seguinte sintaxe:  

  • MODO: Indica a forma como o parâmetro será tratado no procedimento, se será um dado de entrada ou saída;
  • Nome: É o nome que daremos as variáveis que iremos passar;
  • TIPO: É o tipo de dado que vamos passar no parâmetro informado (int, varchar, decimal, etc.).

Sintaxe para chamar um Stored Procedure

Para chamarmos nossa procedure que criamos acima, basta utilizar o comando abaixo, essa procedure deverá retornar todos os registros da tabela LEITOR.

CALL selecionar_leitor ();

Chamando a Stored Procedure com parâmetros

Veja agora a criação de uma procedure com parâmetros, ela fará uma consulta na tabela LEITOR também, porém o parâmetro que vamos passar limitará a quantidade de registro que ela retornará, veja abaixo o código para sua criação:

DELIMITER // 
 CREATE PROCEDURE selecionar_leitor_limite(IN quantidade INT)
   BEGIN
   SELECT * FROM LEITOR
   limit quantidade;
   END //
 DELIMITER ; 

Para chamar esta procedure devemos executar este comando:

CALL selecionar_leitor_limite(1); 

Usando parâmetro de saída

DELIMITER // 
 CREATE PROCEDURE Verificar_Quantidade_leitores(OUT total INT)
   BEGIN
   SELECT COUNT(*) INTO total FROM LEITOR;  
   END //
 DELIMITER ;

A função desse procedimento é retornar à quantidade de registros da tabela LEITOR, passando esse valor para a variável de saída “total”. Para isso foi utilizada a palavra reservada INTO.

Para chamá-lo, usamos um símbolo de arroba (@) seguido do nome da variável que receberá o valor de saída. Para recuperamos o valor basta executar os comandos abaixo:

CALL Verificar_Quantidade_leitores(@total);
SELECT @total;

Neste artigo vimos alguns exemplos básicos do uso das Stored Procedure, seus pontos positivos e negativos e como chamá-las utilizando o comando CALL. Bom por hoje é só, muito obrigado e até o próximo artigo.

Compartilhe!