Olá!

Hoje vou mostrar como se faz para criar stored procedures e funções no MySQL. Stored procedures e funções são processos que você cria e ficam armazenados noMySQL, onde você pode executá-los depois. A vantagem é que o tempo de execução para consultar dados e mostrar na tela do usuário, ou executar comandos mais complexos, ficam muito mais rápidos do que se você fosse fazer via PHP, por exemplo.

Imagina se você tem que fazer uma consulta, e de acordo com cada resultado desta consulta você tem que inserir ou atualizar uma outra tabela. Ou então imagine que você tenha que fazer uma consulta e executar alguns cálculos com os dados de alguns campos. Isso dá para fazer com PHP, mas você torna o processo muito mais lento, pois para cada resultado, o php abre uma nova consulta para executar a instrução devida. No exemplo que mostrarei, vou comparar uma stored procedure com um script php, que fazem exatamente a mesma coisa.

Com uma stored procedure, você faz uma chamada só ao servidor MySQL e ele se encarrega de fazer sozinho. Stored Procedure são ótimas para se executar scripts mais complexos no banco de dados.

Com uma função, em uma única consulta você já traz os resultados prontinhos para o php somente mostrar na tela. é como se o resultado da função fosse um dado já armazenado mo MySQL.

Diferença entre stored procedures e funções

Stored procedures não retornam dados. Elas executam scripts no MySQL, que podem ser desde alterações da estrutura de tabelas, até migração de dados de uma tabela para outra, ou executar ações de acordo com os resultados de uma consulta.

Funções obrigatoriamente devem retornar dados, sejam INT, CHAR, VARCHAR, BOOLEAN etc. São ótimas para fazer comparação de dados e retornar valores, executar cálculos, consultar outras tabelas diferentes de acordo com os parâmetros passados etc.

Enfim, dá para fazer muita coisa com o MySQL através de Stored procedures e funções, que otimizam o código e o torna muito mais profissional.

Bom, vamos aos exemplos.

Criando uma Stored Procedure

Vamos supor que você queira uma tabela para armazenar quais sessões de uma aula e quantas vezes um aluno as acessou. Você armazena em uma tabela a nota do aluno para cada vez que ele faz os exercícios de cada aula.

Então temos as tabelas aula_sessao e aula_nota (além das tabelas aluno e aula, claro)

Você precisa atualizar a tabela de sessão dos exercícios para cada aula que o usuário fez, dizendo o número de vezes que o usuário fez os exercícios daquela aula.

Claro que você pode criar um Trigger para executar isso automaticamente, mas imagina se você começou a monitorar os dados de sessão da aula um tempo depois que o site já estava no ar. Você vai ter que recuperar os dados de algum lugar para a contagem não começar do zero. E para agravar ainda mais a situação, você resolveu atualizar estes dados depois que o sistema de monitoramento já estava no ar, ou seja, já possuia dados gravados. Então você tem que inserir novos registros se ainda não existirem, ou atualizar os registros se já existirem.

delimiter ;
-- Na linha abaixo eu apago a procedure caso ela exista
drop procedure if exists insereDados;

delimiter |
-- Na linha abaixo eu crio a procedure
create procedure insereDados()
 begin

-- Abaixo eu declaro as variáveis.
 DECLARE done INT DEFAULT 0;
 declare aulaId INT;
 declare alunoId INT;
 declare vezes INT;
 declare existe CHAR(1);

-- Abaixo, eu declaro um cursor que será a minha consulta de referência que atualizará a tabela aula_sessao
-- Nela eu pego o id da aula, o id do aluno, a quantidade de vezes que o aluno fez os exercícios para cada aula,
-- e verifico em uma subquery se o aluno já possui o registro daquela aula em aula_sessao

 DECLARE curs CURSOR FOR (

select
a.idaula,
a.idaluno,
count(*),
(select if(count(*)=0,'N','S') from aula_sessao where idaluno = a.idaluno and idaula = a.idaula)
from
aula_nota a
group by
a.idaluno,a.idaula

);

-- Na linha abaixo, altero o valor da variável done para um, caso nenhum resultado seja encontrado no cursor;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

-- Ativo o cursor
 OPEN curs;

-- Começo o loop para cada resultado encontrado no cursor
 REPEAT

-- Atribuo os dados de cada campo, na ordem do select do cursor, dentro das variáveis abaixo
 FETCH curs INTO aulaId, alunoId, vezes, existe;

-- se done é falso (done = 0) executa o que está dentro do if
    if not done then
      -- se o resultado do cursor diz que não existe o dado em aula_sessao, vou inserir o registro
if existe = 'N' then
insert into aula_sessao (idaula,idaluno,sessao_exercicios) values (aulaId,usuarioId,vezes);
      -- Se já existe um registro relacionando o aluno à tabela aula_sessao, vou atualizar o registro
elseif existe = 'S' then
update aula_sessao set sessao_exercicios = vezes where idaula = aulaId and idaluno = alunoId;
end if;
    end if;
 -- até que a variavel done receba o valor TRUE (quando não existirem resultados disponíveis)
 UNTIL done END REPEAT;
-- Fecho o cursor e finalizo a stored procedure
 CLOSE curs;

 end
|

delimiter ;

Para executar a procedure, basta chamá-la assim:
CALL insereDados();

Se você quiser apagá-la depois de executada, bas dar o comando:
drop procedure if exists insereDados;

A mesma coisa no PHP (porém muito mais lento)

O código PHP abaixo faz exatamente a mesma coisa que a Stored Procedure que criamos:

<?php

$sql = "
 select
 a.idaula,
 a.idaluno,
 count(*) as vezes,
 if(s.id is null,'N','S') as existe
 from
 aula_nota a
 left join aula_sessao s on s.idaula = a.idaula and s.idaluno = a.idaluno
 group by
 a.idaluno,a.idaula
";
$res = mysql_query( $sql );

while( $r = mysql_fetch_array( $res ) )
{
 if($r['existe']=="S")
 {
 $sql = "update aula_sessao set sessao_exercicios = ".$r['vezes']." where idaula = ".$r['idaula']." and idusuario = ".$r['idaluno'];
 }
 else
 {
 $sql = "insert into aula_sessao (idaula,idaluno,sessao_exercicios) values (".$r['idaula'].",".$r['idaluno'].",".$r['vezes'].")";
 }
 mysql_query( $sql );
}

?>

A desvantagem é que a cada vez que você dá o comando mysql_query(), uma nova conexão é aberta com o banco. Então, se o resultado da primeira query tem muitas linhas, o tempo de execução será muito grande, o uso de processador será absurdo, você poderá perder dados durante o tempo de execução do script, além de poder derrubar o servidor MySQL (acontece muito na plataforma windows ou de acordo com as configurações do servidor).

Usando a Stored Procedure, além da velocidade de execução ser absurdamente maior, você mantém a consistência de dados, pois o MySQL sabe se algum dado foi inserido durante a execução do script e o atualiza também, se for o caso.

Criando uma função no MySQL

Vou mostrar um exemplo simples para ilustrar.

Vamos supor que você tem uma tabela de usuários que te traz o nivel de acesso de cada um deles. Vamos supor que temos os seguintes níveis de acessos:

0: comum
1: colaborador
2: moderador
3: administrador

Você quer apresentar esta informação para o usuário. Para fazer isso somente uma vez, basta na sql você fazer:

select
idusuario,
nome,
    email,
    (case nivel
       when 0 then 'comum'
       when 1 then 'colaborador'
       when 2 then 'moderador'
       when 3 the 'administrador'
    end) as nivelString
from
    usuario
where idusuario = 1

Você pode simplesmente criar uma função para pegar a string que diz o nivel de acesso do usuário, com a vantagem de poder usá-la em várias sqls, sem ter a necessidade de fazer o CASE toda vez que você precisar obter esse dado. Além disso, a manutenção fica muito mais simples se você quiser, por exemplo, acrescentar mais um nível de acesso. Basta alterar a função.

Então vamos criar:

delimiter ;

drop function if exists getNivelString;

delimiter |

create function getNivelString(nivel INT)
 returns varchar(45)
 begin
   declare nivelString varchar(45);
   case nivel
     when 0 then nivelString = 'comum'
     when 1 then nivelString = 'colaborador'
     when 2 then nivelString = 'moderador'
     when 3 then nivelString = 'administrador'
     else nivelString = 'comum'
   end;
   return nivelString;
end
|

delimiter ;

Agora,  a consulta pode ficar assim:

select
idusuario,
nome,
    email,
    getNivelString(idnivel) as nivelString
from
    usuario
where idusuario = 1

Lembrando que este é um exemplo bem simples diante da infinidade de coisas que se pode fazer.

Agora, o php para mostar os dados fica bem simples:

$sql = "
select
idusuario,
nome,
    email,
    getNivelString(idnivel) as nivelString
from
   usuario
where idusuario = 1
";
$campo = mysql_fetch_array( mysql_query( $sql ) );

echo "
    Olá {$campo['nome']}! Você é um usuário {$campo['nivelString']}.
";

A mesma coisa em PHP

<?php
function getNivelString($idnivel)
{
    switch( $idnivel )
    {
       case 0: $string = 'comum'; break;
       case 1: $string = 'colaborador'; break;
       case 2: $string = 'moderador'; break;
       case 3: $string = 'administrador'; break;
       default $string = 'comum'
    }
    return $string;
}

$sql = "select idusuario, nome, email, idnivel from usuario where idusuario = 1";
$campo = mysql_fetch_array( mysql_query( $sql ) );

echo "
    Olá {$campo['nome']}! Você é um usuário " . getNivelString( $campo['idnivel'] ) . ".
";
?>

Os exemplos dados aqui são apenas para demostrar como criar e usar funções e stored procedures no MySQL. Os exemplos são simples, mas você pode adaptá-los às suas necessidades.

Espero que este artigo tenha sido útil para você. Demorei um bom tempo para escrevê-lo

Deixe um comentário!

Até a próxima!