EXEMPLOS - MySQL STORED PROCEDURE(SP) - PROCEDIMENTO ARMAZENADO NO MySQL

Um Procedimento Armazenado (Stored Procedure, em inglês) é um conjunto de instruções SQL(Structured Query Language) com um nome atribuído, que é armazenado em um sistema de gerenciamento de banco de dados relacional (SGBDR), para que possa ser reutilizado e compartilhado por vários programas. Outro aspecto interessante, é que quando o procedimento é executado pela primeira vez, todas as informações sobre ele são armazenadas no cache do sistema, o que o torna muito mais rápido para ser executado posteriormente, o que é uma vantagem, principalmente considerando que a maior parte do tempo de resposta de um sistema é devido aos acessos ao banco de dados. Lembre-se, pra gente chamar o stored Procedure usamos a instrução CALL.

ANOTE AÍ!

• O cache é um tipo de arquivo temporário que reúne informações que são usadas com frequência para facilitar o acesso pelo sistema de forma mais acelerada.


PARÂMETROS DO STORED PROCEDURE

Normalmente, quando a gente desenvolve um stored procedure usamos parâmetros. No MySQL, um parâmetro adquire um dos seguintes termos: IN , OUT e INOUT.

• IN - significa que o parâmetro passa um valor para um procedimento. O procedimento pode modificar o valor, mas a modificação não é visível para o chamador quando o procedimento retorna. Por padrão, cada parâmetro é um parâmetro IN. Para especificar o contrário, use a palavra-chave OUT ou INOUT antes do nome do parâmetro.
• OUT - Significa que o stored procedure pode alterar o parâmetro e devolve-lo ao programa de chamada.
• INOUT - é a combinação do modo IN e OUT. Em poucas palavras, um parâmetro INOUT é inicializado pelo chamador, pode ser modificado pelo procedimento e qualquer alteração feita pelo procedimento fica visível para o chamador quando o procedimento retorna.

Observação: Especificar um parâmetro como IN, OUT ou INOUT é válido apenas para  PROCEDURES. Para a FUNCTION, os parâmetros são sempre considerados como parâmetros IN.

1º EXEMPLO

Criar uma tabela com o nome faculdades e implementar o conceito de stored procedure para fazer a inserção de registros na tabela criada.

Solução

A seguir, temos um exemplo de uma tabela simples de nome faculdades que foi criada utilizando a IDE MySQL Workbench.

create table faculdades(
id_faculdade int primary key,
nome varchar(65) not null,
email varchar(60),
localizacao varchar(80) not null,
municipio varchar(45) not null,
estado varchar(45) not null
);

Agora sim, vamos preencher a tabela faculdades

insert into faculdades(id_faculdade,nome,email,localizacao,municipio,estado)
values(11,'FATEC BAURU','f196acad@cps.sp.gov.br','Rua Manoel Bento da Cruz, 3-30 - Centro','Bauru', 'São Paulo');

insert into faculdades(id_faculdade,nome,email,localizacao,municipio,estado)
values(26,'ESCOLA DE ENGENHARIA DE LINS','unilins@unilins.edu.br','Avenida Nicolau Zarvos, 1925 - Jardim Aeroporto','Lins', 'São Paulo');

Pra gente visualizar os dados inseridos, vamos fazer um select * from, para trazer todos os registros da tabela faculdades.

SELECT * FROM faculdades;

Após a execução do comando, teremos o resultado, conforme exibido na imagem a seguir:

Estes, são os comandos relacionados ao Stored Procedure:

• CREATE PROCEDURE - Este comando é usado para criar a procedure
• ALTER PROCEDURE - Este comando pode ser usado para renomear um stored procedure ou para alterar a sua estrutura.
• DROP PROCEDURE- Este comando é usado para deletar um stored procedure
• SHOW CREATE PROCEDURE - Este comando é uma extensão do MySQL. De forma similar a SHOW CREATE TABLE, ele retorna as informações exatas que podem ser usadas para recriar a rotina chamada.

Galera, a gente cria um stored procedure com o comando CREATE PROCEDURE [nome da procedure]

Agora, vamos criar uma procedure para trazer todos os registros da tabela faculdades.

Observação: o delimitador é o caractere ou string de caracteres que você usará para informar ao cliente mysql que você terminou de digitar uma instrução SQL. Em poucas palavras, o delimiter serve para indicar o final do conjunto de instruções. Por muito tempo, o delimitador sempre foi um ponto e vírgula. Isso causa um grande problema porque em um procedimento armazenado, pode-se ter muitas declarações, e cada uma deve terminar com um ponto e vírgula.

DELIMITER //
  CREATE PROCEDURE faculdades_select()
  BEGIN
       SELECT * FROM faculdades;
  END //
  DELIMITER ;
Onde:

• faculdades_select() é o nome do stored procedure. O nome pode ser de sua preferência.

Pronto, procedure feita . Agora, é só executar esse código e o banco o compilará e o deixará armazenado, só esperando você o chamar.

Pra ficar mais interessante, vamos chamar o procedimento armazenado, utilizando o comando call.

CALL faculdades_select();

Com o script abaixo, vamos criar um procedimento armazenado para inserir dados na nossa tabela faculdades.

DELIMITER //
 CREATE PROCEDURE faculdades_insert(idx int,nomex varchar(65),emailx varchar(60),localizacaox varchar(80),municipiox varchar(45),estadox varchar(45))
 BEGIN
  insert into faculdades(id_faculdade,nome,email,localizacao,municipio,estado)
values(idx,nomex,emailx,localizacaox,municipiox,estadox);
END //
DELIMITER ;
CALL faculdades_insert(10,'HUICODE ACADEMY','huicodesocial@gmail.com','Rua dos Bandeirantes 3-33','Bauru','São Paulo');

Pra demonstrar que deu tudo certo, ou seja, que uma nova faculdade foi inserida no sistema, vamos fazer um select * from, para trazer todos os registros da tabela faculdades.

SELECT * FROM faculdades;

2º EXEMPLO

Utilizando a tabela do exercício anterior, crie um procedimento armazenado que trás o nome da faculdade quando o usuário digita o código identificador da faculdade.

Solução

DELIMITER //
CREATE   PROCEDURE pesquisar_faculdade( IN Idsearch INT )    
    BEGIN
SELECT nome, email, localizacao,municipio,estado
FROM faculdades
WHERE id_faculdade=Idsearch;     
    END//
DELIMITER ;

Pra gente chamar o procedimento armazenado com argumento, fazemos o seguinte:

CALL pesquisar_faculdade(10);

3º EXEMPLO

Utilizando a tabela do exercício anterior, crie um stored procedure com a finalidade de recuperar os campos: nome e email da tabela faculdades.

Solução

DELIMITER //
  CREATE PROCEDURE  pesquisar_faculdade(in parametro1 INT)
  BEGIN
declare id_facul int(11);
     declare nome_facul varchar(65);
     declare email_facul varchar(60);
  SELECT nome,email into nome_facul,email_facul from faculdades where parametro1=id_faculdade;
    select nome_facul,email_facul;
END //
DELIMITER ;
CALL  pesquisar_faculdade(11);

Se a gente inserir um id_faculdade que não consta no banco de dados, o retorno será conforme a figura abaixo.

CALL  pesquisar_faculdade(1);

4º EXEMPLO

Utilizando a tabela do exercício anterior, crie um stored procedure para testar os parâmetros(validar os campos) id_faculdade e nome, da tabela faculdades. O procedimento armazenado deve emitir uma mensagem para o usuário, caso ele insira um id_faculdade ou nome da faculdade já existente.

Solução

DELIMITER //
  CREATE PROCEDURE faculdades_verificar(in pid INT, in pnome varchar(65))
  BEGIN
    declare id_facul int(11);
    declare nome_facul varchar(65);
    declare email_facul varchar(45);
    declare localizacao_facul varchar(120);
    declare municipio_facul varchar(45);
    declare estado_facul varchar(45);
    declare qtde int;

if pid>=1 and pid<=99 then
      if length(pnome)>=1 and length(pnome)<=65 then
     select count(*) into qtde from faculdades where id_faculdade=pid;
if qtde=0 then
   select count(*) into qtde from faculdades where upper(nome)=upper(pnome);
             if qtde=0 then
    insert into faculdades(id_faculdade,nome,email,localizacao,municipio,estado)
                 values(id_facul, nome_facul, email_facul, localizacao_facul, municipio_facul, estado_facul);
      else
select'O valor do campo nome já existe.' ;
  end if;  
            else
                select 'O valor do campo id já existe';
          end if;
       else
            select 'O valor do campo nome está fora da faixa de valores aceitáveis';
   end if;
   else
     select 'O valor do campo id está fora da faixa de valores aceitáveis';
end if;
END //
DELIMITER;

CALL faculdades_verificar(10,'fatec Jundiaí');

CALL faculdades_verificar(1,'fatec bauru');

Explicações

• O DELIMITER serve para indicar o final do conjunto de instruções. Por muito tempo, o delimitador sempre foi um ponto e vírgula. Isso causa um problema porque, em um procedimento armazenado, pode-se ter muitas declarações, e cada uma deve terminar com um ponto e vírgula.
• Para evitar problemas com o valor do campo id é necessário utilizar o AUTO_INCREMENT que serve para que um valor de um campo seja automaticamente inserido toda a vez que se cria um novo registro, como um ID nas chaves primárias.

Se você quiser excluir um procedimento do banco de dados, faça o seguinte: DROP PROCEDURE nome_da_procedure;

Por exemplo, se eu quisesse excluir o procedimento faculdades_verificar, faria o seguinte: DROP PROCEDURE faculdades_verificar; -- Neste caso, a gente está excluindo o procedimento faculdades_verificar do banco de dados.

Lembra do SHOW CREATE PROCEDURE? Esta sentença retorna as informações detalhadas de um procedimento armazenado no MySQL que pode ser usada até para recriar a rotina de um stored procedure.

Exemplo: SHOW CREATE PROCEDURE

Após a execução do comando SHOW CREATE PROCEDURE faculdades_insert; teremos o resultado, conforme exibido na imagem a seguir:

Clicando em CREATE DEFINER, podemos observar as informações relacionadas a procedure faculdades_insert que a gente criou, conforme exibido na imagem a seguir:

FOI ÚTIL? Faça um comentário