mysql, php

Como criar stored procedures e funções no MySQL

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!

21 Comments

  1. Alexandre
    Sobre: “Como criar stored procedures e funções no MySQL”
    Uma dúvida, ao invés de usa um case para gerar as opções de níveis, como seria se esses níveis se a função precisasse desses níveis de uma tabela?
    +-+—————-+
    |id|descricao |
    +-+—————–+
    | 0|comum |
    | 1|colaborador |
    | 2|moderador |
    | 3|administrador|
    +-+—————–+
    • Alexandre,
      desculpe-me, mas não consegui entender o que você quis dizer. Você pode ser mais claro?
  2. Alessandro
    Há menos de 1 ano comecei a programar algumas aplicações com php e javascript com banco de dados mySql. Porém, realizo todas as minhas consultas através de instruções sql no próprio código fonte do php.
    A minha pergunta, apesar de que talvez seja muito tola, é:
    Onde e como declaro/amarzeno stored procedures? (isto é, no próprio código php? através de algum script no servidor do bd?)

    Obrigado pela ajuda!

    • Ao criar uma stored procedure, ela é armazenada no próprio servidor MySQL, asssim como triggers e funções. O MySQL possui um banco padrão onde ele armazena os metadados, chamado information_schema.

      Dessa forma, você pode acessar as funções e procedures fazendo a seguinte consulta:

      select * from information_schema.routines;

      E os triggers com a consulta select * from information_schema.triggers;

      ao executar o script deste tutorial, automaticamente a procedure/função fica armazenada, e para executar basta executar a sql call procedure_name(); ou select function_name();

      Stored procedures e funções são muito mais rápidas de serem executadas do que scripts SQL/PHP, poupando memória e processamento do servidor, otimizando o site. Obviamente, para dar saída na tela, você precisa de usar um script server side, como o php. Repare também que as procedures não retornam dados, enquanto que as funções sim.

      Espero ter ajudado.

      Att,

  3. @Alexandre
    Se o que você quer é trazer dados de uma outra tabela, basta dar um join com esta tabela onde está armazenado o dado. E a sql ficaria assim:

    select
    u.idusuario,
    u.nome,
    u.email,
    n.nivel_str
    from
    usuario u inner join nivel n on n.id = u.idnivel
    where u.idusuario = 1

    Lembrando que eu usei um caso simples para exemplificar o uso de uma função. Obviamente, esta solução é melhor para manutenção do que o uso de uma função.

    Att,

  4. roberta
    Bom dia Wellington!
    Eu conseguir fazer a procedure usando o MySQL Query Browser, porém preciso faze -lá usando phpmyadmin. No phpmyadmin aparece que mensagem que o comando foi executado com sucesso, mas não constrói.
    Vc sabe o que pode está acontecendo.
    • Olá Roberta! me desculpe pela demora em responder.

      bom, para saber se a stored procedure foi criada, execute o seguinte comando:

      select * from information_schema.ROUTINES;

      A procedure deve estar lá. Depois, para executar, execute

      call nome_da_procedure();

      Apaguei seus outros comentários, a seu pedido.

      Att,

  5. roberta
    Olá Wellington!
    Obrigada pela resposta.
    Eu executei os comandos, que vc ensinou. Aparece a procedure criada, mas quando eu executo o comando
    call nome_da_procedure();
    Aparece a seguinte mensagem: #1312 – PROCEDURE bdoc2010.sp_prob_setor_mun can’t return a result set in the given context.
    Porém usando o comando no MySQL Query Browser , ele retorna o resultado.

    Estou usando a seguinte estrutura para criar a procedure :

    DELIMITER $$

    DROP PROCEDURE IF EXISTS `sp_teste` $$
    CREATE PROCEDURE `sp_teste` ()
    BEGIN

    END $$

    DELIMITER ;

    Vc sabe o que pode está acontecendo?

    Muito obrigada pela ajuda!!

    • Oi Roberta.

      Bom, parece que a procedure está retornando dados… Uma procedure não deve retornar dados, a não ser para fins de debug da mesma, e via linha de comando. Uma procedure deve ser executada somente para fazer operações no banco, sem retornar nada. Se você pretende retornar dados, crie uma função.

      De qualquer forma, me envie a sql utilizada para criação da procedure, de preferência por email, para eu dar uma olhada…

  6. roberta
    Oi Wellington!
    Qual é o seu email?
    obrigada!!
  7. Cláudia
    Wellington
    Tenho uma Stored Procedure criada no MySQL e gostaria de chamá-la no PHP, preciso passar um parâmetro para o BD e não acho um exemplo (que funcione).

    O erro: Warning: mysqli_prepare() expects parameter 1 to be mysqli, null given in /home/ligaprov/public_html/resultados_jogos_atualizar2.php

    Por favor, dê uma olhada no código abaixo.

  8. Muito legal seu post Wellington.
    Ajudou muito. Estou tendo que trabalhar com um sistema php e sql server e agora com mysql.
    Valeu
  9. Jonas Rodrigues
    Olá Wellington!
    Gostei muito do post, analisei toda estrutura da procedure e criei uma para o que eu preciso. Tenho uma procedure que faz inserção em 3 tabelas e vendo seu post vi que posso criar as atualizações para essas tabelas na mesma procedure, porem senti falta nesse post de como chamar a procedure no php passando os paramentros para a mesma (minha dificuldade).
    Você poderia dar um exemplo de como eu faria isso?

    Se você achar interessante, posso postar a minha procedure.

    Desde já, muito obrigado!!!

    • você pode tentar assim:

      $sql = “call nomeDaProcedure(” . $param .”)”;

      mysql_query( $sql );

      Abraço!

  10. Paulo Gomes
    Olá eu criei uma procedure no banco de dados mysql que insere dados numa tabela chamada tb_venda, como eu faço para chamar essa tabela de um script php?
    A minha idéia foi criar a procedure no bd, e chamar essa procedure no php passando os parametros e a função se encarregar de fazer o insert no banco.
    Desde já eu agradeço a atenção.

    Paulo.

  11. Olá amigo,

    seu artigo foi o melhor que encontrei agora, parabéns!!!

    Estou com uma dúvida, meu desejo é verificar uma tabela, A, se tiver conteúdo então verificar uma tabela, B, e se não tiver conteúdo nela ainda então adiciona.
    Algo assim:

    Select * from A where item=X;
    Select * from B where itema=A.id;
    if naotemdados then
    Insert into B (registro,nome,etc) values (A.id,A.nome,A.etc)
    end if

    Tentei fazer isso mas não estou conseguindo trabalhar 2 cursores

  12. Danilo
    Muitissimo obrigado! Me ajudou pra caramba!! Abraço.
  13. Esse post está mesmo ótimo! É necessário devoção para criar bons posts como esse, obrigado pela dedicação! Eu adoro ler seus textos, continue assim!
  14. Luciano
    Wellington Ribeiro, queria ver se vc pode me ajudar, eu queria com que o banco de dados SQL hospedado, verifica-se uma data. Exemplo, preciso que o cliente sem entar no site, receba um alerta via sms no celular avisando que o prazo X tem 1 dia para acontecer. Em miudos o cliente entra com dados e em um campo tem uma data, o mysql todo dia a meia noite, executa e verifica a data no banco de dados, se ela for a data atual-1 em via o sms. avisando que o prazo é amanha. sacou.
  15. @Luciano Olá Luciano. Desculpe pela demora em responder.
    Para fazer o que você quer, é simples:

    select * from usuarios where data_cadastro regexp(curdate() + interval 1 day);

    Assim, vai selecionar todos os usuários que tem a data igual ao proximo dia, ignorando a hora.
    Para isso funcionar, a data cadastrada deve estar no padrão de datas do mysql (YYYY-mm-dd H:i:s).

Deixe uma resposta

O seu endereço de email não será publicado Campos obrigatórios são marcados *

*

Você pode usar estas tags e atributos de HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>