iMasters | Por uma internet mais criativa e dinâmica

• MySQL

• Imprimir
• Indicar
• Digg it
• Del.icio.us
• Twitter
• 17 comentários

Olá pessoal!

Gostaria, antes de mais nada, agradecer ao pessoal do iMasters pelo convite e salientar aqui que me sinto muito feliz com mais esse advento.

Neste artigo (usarei um servidor SUPERION da SunSix, rodando Ubuntu 6.06 LTS com MySQL 5.0.37 Community Version), iniciaremos uma viagem interessante sobre todo o mundo dos procedimentos armazenados ou stored routines, cujo conceito principal é que são "programas armazenados no servidor, pré-compilados, chamados de forma explícita para executar alguma lógica de manipulação de dados, podendo retornar ou não algum valor".

Mal começamos e já temos o conceito de stored procedure ou stored routines. No caso do MySQL, os procedimentos armazenados estão disponíveis exatamente desde a versão 5.0, que foi um marco na evolução do SGBD OpenSource mais utilizado no mundo.

Necessariamente, você precisará ter instalado na sua máquina o MySQL 5++ e o MySQL Client (este é disponibilizado no momento da instalação do server) . Utilizarei também o MySQL Query Browser para tornar nossa experiência mais interessante com o MySQL e sair um pouco da linha de comando, mas anda grade parte será utilizando o mysql client.

Antes de entrarmos na sintaxe, ainda temos que registrar aqui que os procedimentos armazenados, quando criados e compilados, são inseridos em uma tabela chamada ROUTINES no banco de dados INFORMATION_SCHEMA, que é o dicionário de dados do MySQL. Para listarmos todos os stored routines (Stored Procedure e Functions), basta emitirmos o seguinte comando no mysql client:

mysql> SELECT * FROM INFORMATION_SCHEMA.ROUTINES;

Perceba que listamos todos os procedimentos armazenados (Stored Procedure e Functions), de todos os bancos de dados. Saliento que estamos listando somente Stored Procedure e Functions, pois, somente estas rotinas são gravadas na tabela ROUTINES do bancos de dados INFORMATION_SCHEMA. Triggers também são um tipo de procedimento armazenado, mas estão separadas em outra tabela do dicionário, chamada TRIGGERS.

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS;

A sintaxe geral para criação de Stored Procedure é a seguinte:

CREATE PROCEDURE proc_name([parameters, ...])
[characteristics]
[BEGIN]

      corpo_da_rotina;

[END]

Explicando...

proc_name: seu procedimento armazenado deve ter um nome, para quando for chamado, podermos então usá-lo;

tipo_param: existem 3 tipos de parâmetros em uma Stored Procedure no MySQL:

• IN => este é um parâmetro de entrada, ou seja, um parâmetro cujo seu valor será utilizado no interior do procedimento para produzir algum resultado;
• OUT => esté parâmetro retorna algo de dentro do procedimento para o lado externo, colocando os valores manipulados disponíveis na memória ou no conjunto de resultados;
• INOUT => faz os dois trabalhos ao mesmo tempo!

parameters: nessa parte do procedimento, informaremos os parâmetros da seguinte forma: [IN | OUT | INOUT] nome_parametro tipo_dado.

characteristics: as características do procedimento pode apresentar. Como não utilizaremos inicialmente tais características, vamos nos ater a sintaxe principal. Questões de segurança, se é determinística ou não, qual a linguagem que estamos utilizando e se nosso procedimento modificará dados na banco de dados, são algumas das características que poderemos definir neste item que abordaremos com mais detalhe na parte dois do artigo.

corpo_da_rotina: onde são definidos os comandos SQL que farão alguma manipulação e/ou defenderão alguma lógica, podendo retornar ou não algum resultado.

PRIMEIRO EXEMPLO:

Nesse primeiro exemplo, implementaremos um Stored Procedure bem simples, que nos devolverá um "OLÁ"! Abra um terminal do seu Linux ou mesmo um prompt do seu Windows e entre no mysql client digitando:

shell> mysql -u nome_usuario -psenha,

Usarei neste artigo o banco de dados chamado test, que já vem criado desde a instalação do MySQL. Caso não conste no seu, use um banco de dados de sua preferência.

Caso prefira, podemos usar também o MySQL Query Browser (você poderá baixá-lo em http://dev.mysql... dentro de um pacote chamado GUI Tools). No Windows basta instalar e no Linux basta descompactar o pacote TAR e executar.

Se você optou pelo MySQL Client, nesse momento estamos nesse ponto:

MySQL Client

Se você tiver optado por trabalhar com o MySQL Query Browser, estamos nesse ponto:

MySQL Query Browser

Pronto! O Query Browser já nos deu quase tudo pronto para escrevermos somente os parâmetros que teremos no nosso procedimento e o corpo da rotina. Notem que é utilizado o operador DELIMITER para mudar o delimitador de comandos, que por padrão é o ";". Mudamos o DELIMITADOR para podermos usar o ";" no meio do procedimento. Caso não efetuemos essa troca, o procedimento será enviado pela metade e um erro será enviado ao terminal, por erro na sintaxe.

DELIMITADOR no MySQL, em outras situações, por padrão também é chamado de terminador. Para verificar qual é o delimitador da sessão corrente emita o comando \s, que é a forma curta do comando STATUS.

Delimiter

Bem, procedimento compilado basta executarmos o procedimento com o seguinte comando:

CALL OLA();

OBS.: Após a versão 5.1.18, não é mais necessário os parênteses caso o procedimento não receba parâmetros.

A resposta do procedimento será "OLÁ!" como definimos.

Resposta

Bom, agora que já temos uma noção básica de como é implementado um procedimento armazenado no MySQL, já podemos partir para aplicações do mundo real, tais como, manipular inserções de dados, exclusões de registros e atualizações de linhas de uma ou mais tabelas. Tudo isso nos levará a aportar lá na frente no conceito de transações, onde desenvolveremos um procedimento para simulação de transferência de valor entre contas bancárias de mesma agência.

Como seria então, um procedimento para inserir dados em uma tabela do banco de dados?

Bom, antes de prosseguirmos, criaremos uma tabela, de nome tbl_correntista, que terá os campos correntista_id do tipo INT, correntista_nome do tipo VARCHAR(60) e correntista_cpf do tipo VARCHAR(20).

CREATE TABLE tbl_correntista (
  correntista_id int auto_increment primary key,
  correntista_nome varchar(60) not null unique,
  correntista_cpf varchar(20) not null,
  dt_cadastro timestamp default current_timestamp,
) Engine =InnoDB;

Pronto! Após executarmos o script acima nossa tabela estará criada e já poderemos dar carga através de um procedimento armazenado, onde também utilizaremos em meio a este, estruturas condicionais, IF-THEN-ELSE!

Com a nossa tabela criada, criaremos nosso procedimento para efetuar o INSERT dos dados, ou seja, um procedimento para dar carga na tabela.

DELIMITER //
CREATE PROCEDURE mySp_correntistaInsert(v_nome VARCHAR(60), v_cpf VARCHAR(20))
BEGIN
IF ((v_nome != '') && (v_cpf != '')) THEN
INSERT INTO tbl_correntista (correntista_nome, correntista_cpf)
VALUES (v_nome, v_cpf);
ELSE
SELECT 'NOME e CPF devem ser fornecidos para o cadastro!' AS Msg;
END IF; 
END;
//

Após compilarmos o procedimento, já poderemos chamá-lo através da declaração CALL, como se segue:

CALL mySp_correntistaInsert('Wagner Bianchi', '023.456.789-10');

...notem que utilizamos em meio ao nosso procedimento de inserção, a estrutura condicional para consistir o valor das variáveis. Caso os valores de ambas sejam vazios, a mensagem será disparada, como segue abaixo:

Mensagem

Após executarmos a linha de inserção com os valores, teremos um registro na tabela, da seguinte forma:

TabelaTabela

Temos mais dois procedimentos, fáceis, para complementar essa primeira parte do artigo. Faremos a seguir, um procedimento, com o mesmo formato para atualizarmos o registro da nossa tabela "tbl_correntista", que até o momento encontra-se com um registro. Serão três agora, o identificador do registro - v_id - , o novo nome do correntista - v_nome - e o novo cpf - v_cpf -.

Vamos lá!

DELIMITER //
CREATE PROCEDURE mySp_correntistaUpdate(v_id INT, v_nome VARCHAR(60), v_cpf VARCHAR(20))
BEGIN 
IF (((v_id > 0) && (v_id != '') ) && (v_nome != '') && (v_cpf != '')) THEN
UPDATE tbl_correntista SET correntista_nome =v_nome,
correntista_cpf =v_cpf
WHERE correntista_id =v_id;
ELSE
SELECT 'O novos NOME e CPF devem ser informados!' AS Msg;
END IF; 
END;
//

Já podemos, após compilarmos o procedimento de UPDATE, atualizarmos nosso registro na tabela de correntistas. Depois de compilado com sucesso, já podemos chamar nosso procedimento de atualização e passar os parâmetros para atualizar o registro que temos na tabela.

CALL mySp_correntistaUpdate(1, 'Wagner MySQL Bianchi', '123.123.111-11');

TabelaTabela

Note que este procedimento poderá facilmente ser adaptado em qualquer sistema que receba o identificador do registro a ser atualizado em uma tabela qualquer de um banco de dados. Sistemas web passam parâmetros com facilidade vi POST ou GET, de forma que poderão ser entregues como parâmetro ao procedimento.

Para finalizarmos, faremos um procedimento para excluir registros, que é o mais trivial de todos, basta mais uma vez enviarmos o identificador do registro como parâmetro e efetuarmos a exclusão após a conferência como estamos fazendo nos outros procedimentos.

Note que, em um sistema, você poderá implementar um único procedimento para exclusão de registros, que receberá alguns parâmetros como o identificador, o nome da tabela e o nome da coluna, mas, nesse momento, nos atentaremos para o simples, nos próximos artigos sofisticaremos um pouco mais nossos procedimentos.

DELIMITER //
CREATE PROCEDURE mySp_correntistaDelete(v_id INT)
BEGIN
IF ((v_id > 0) && (v_id != '')) THEN
DELETE FROM tbl_correntista WHERE correntista_id =v_id;
ELSE 
SELECT 'O identifiador do registro não foi informado!' AS Msg;
END IF; 
END;
//

Agora já podemos excluir o registro que inserimos e atualizamos! Segue a sintaxe para isso:

CALL mySp_correntistaDelete(1);

Bom, espero que este artigo, ainda na primeria fase, com aplicações de procedimentos muito básicos, possam trazer uma iniciação em stored procedure no MySQL a todos que buscam aprender a manejar este recurso que é de grande proveito, principalmente para sistemas que necessitam de modularidade e otimização quanto à performance.

Por outro lado, você poderá ficar preso ao MySQL (que não é mal negócio) ou qualquer outro SGBD que você vá usar para ser back-end, utilizando os procedimentos armazenados.

Na parte 02, continuação deste artigo, avançaremos bem nos conceitos e já falaremos de transações com vários comandos dentro de uma Stored Procedures ou Stored Routines, trabalhando com criação de variáveis, iterações e savepoints, iniciando nossa aplicação de transferência de valores entre contas bancárias de mesma agência, que também necessitará uma pequena introdução nos conceitos de commit e rollback. Ou seja, transação.

Em breve os artigos serão escritos utilizando o MySQL 6.0. Muitos outros recursos, aguardem!

Um abração a todos e Happy MySQL'In!

Todos os artigos de Wagner Bianchi

17 comentários publicados

• 1. Muito Bommm!!!

  Quarta-feira, 12/12/2007, por marco antonio soares
  

  Legal demais cara, estou aprendendo a usar Stored Procedures agora e seu artigo foi de grande valia para mim.....
  

  Responder comentário

• 2. Bommmm

  Terça-feira, 18/12/2007, por Ivan Olicio
  

  Muito bom a artigo
  é que eu sempre estou falando para os meus amigos, que um Procedures, só tem vantagens,eu já uso elas a muito tempo mais com Banco Firebird, e a ganho de desempenho é muito grande usando Procedures e fica mais facil a manuteção do seu banco com elas. Mais o que ta fando pro Mysql, não sei ainda não achei um SGBD bom igual o Ib Expert do Firebird para manipulação de Procuderes e varias outroas vantagens......

  Responder comentário

• 3. Vantagens

  Terça-feira, 18/12/2007, por Ivan Olicio
  

  No meu caso os meus bancos de dados tem em media 4gigas eu tenho tabelhas com mais de 15 milhoes de registro, e quanho em desempenho com Procedures.
  
  Procedures não é perda de tempo e sim ganho de tempo valiosos......

  Responder comentário

• 4. Stored Procedures com MySQL

  Sexta-feira, 21/12/2007, por emerson lopes
  

  artigo de MySQL

  Responder comentário

• 5. Desculpa

  Domingo, 30/12/2007, por Ivan Olicio
  

  Me desculpe, pois acho que é um grande Artigo com MySql que faz o pessoal a usar Stored Procedures. Pois vejo pouco pessoas de MySql usar no que outras Bancos já se uma mais. E outro Detalhes a mesma linguagem da Strored Precedures é bem ou case iguais com todos os bancos somente tirando alguns comandos amais que tem cada tipo de banco. Mais desculpe a minha ignorância. E meus Parabens Wagner Bianchi pelo Artigo de Stored Procedures com MySql

  Responder comentário

• 6. Correção

  Terça-feira, 01/01/2008, por Christiano Carvalho
  

  Existe um pequeno erro na criação da tabela 'tbl_correntista' (basta retirar a última vírgula que está após a palavra timestamp). Fora isso, o artigo está muito bem explicado e didático. Parabéns, aguardo a continuação... :)

  Responder comentário

• 7. Correto!

  Sexta-feira, 11/01/2008, por Wagner Bianchi
  

  Existe uma vírgula a mais ao final do último campo no comando de criação da tabela. Bem observado Cristiano.
  
  Um forte abraço à todos os amigos e sou muito grato aos comentários.

  Responder comentário

• 8. SP no Mysql Versão 5.0.27

  Segunda-feira, 30/06/2008, por Tiago Fernando Sarri
  

  ola amigo, belo artigo?
  so tenho um problema, na versão 5.0.27 não da certo este esquema para criar SP, vc tem alguma dica nesta versão?
  Obrigado

  • Wagner Bianchi - Quarta-feira, 21/01/2009

    

    Stored Procedures estão disponíveis no MySQL desde a versão 5.0, qual é o problema que está se passando?
    
    Um abraço!

  Responder comentário

• 9. SP no Mysql Versão 5.0.27

  Quarta-feira, 20/08/2008, por Wagner Bianchi
  

  Após criar um Stored Procedure simples:
  
  mysql> CREATE PROCEDURE test.sp100() SELECT NOW();
  Query OK, 0 rows affected (0.00 sec)
  
  ...qual é o erro apresentado?

  Responder comentário

• 10. Comigo não funcionou.

  Terça-feira, 20/01/2009, por Flavio Antonio
  

  Gostei muito do artigo. Mas comigo não funcionou.
  Uso o MySQL Query Browser. Mysql 5.0. Apresenta o seguinte erro quando executo:
  
  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 'END$$
  DELIMITER' at line 1
  
  
  DELIMITER $$
  DROP PROCEDURE IF EXISTS 'mysqlteste'.'ola'$$
  CREATE PROCEDURE 'mysqlteste'.'ola'()
  BEGIN
  SELECT 'OLA';
  END$$
  DELIMITER;
  
  
  
  

  • Wagner Bianchi - Terça-feira, 20/01/2009

    

    Tenta assim:
    
    DELIMITER $$
    CREATE PROCEDURE ola()
    BEGIN
    SELECT 'OLA';
    END$$
    DELIMITER $$
    
    Seleciona o banco de dados primeiro e depois executa o comando mais interno. Acabei de executar aqui no Query Browser mesmo e deu tudo certinho... ;-)
    
    Abração!!

  Responder comentário

• 11. Erro de sintaxe

  Quarta-feira, 21/01/2009, por Flavio Antonio
  

  Wagner, tentei de todas as formas. Aparece sempre o mesmo erro. Segue o script.
  DELIMITER $$
  
  DROP PROCEDURE IF EXISTS 'mysqlteste'.'selecionacidade'$$
  CREATE PROCEDURE 'mysqlteste'.'selecionacidade'()
  BEGIN
  SELECT * from cidade;
  END$$
  DELIMITER $$
  Aparece sempre o mesmo erro
  
  Script line: 1 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 'DELIMITER $$
  
  DROP PROCEDURE IF EXISTS 'mysqlteste'.'selecionacidade'$$
  CREAT' at line 1
  Script line: 7 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 'END$$
  
  DELIMITER $$' at line 1
  

  • Wagner Bianchi - Quarta-feira, 21/01/2009

    

    Flávio,
    
    O artigo foi fundamentado com embasamento no manual on-line do MySQL, sendo que também os exemplos criados dentro do artigo foram colocados de maniera bem clara e da forma que está, não há dúvidas sobre o funcionamento.
    
    Sugiro que você reveja os estudos relendo o artigo e o manual on-line, para fazer funcionar o MySQL Query Browser, quanto à criação de SP's. Utilizo muito esta ferramenta e também o terminal para manipular os procedimentos no servidor de bancos de dados MySQL.
    
    Caso ainda tenha dúvidas, o pessoal do fórum poderá lhe ajudar mais efetivamente.
    
    Um grande abraço.

  Responder comentário

• 12. Comentários

  Quarta-feira, 18/02/2009, por Leandro Silva
  

  Pessoal, muito bom esse artigo.
  Alguém sabe como adicionar comentários dentro de uma stored procedure?

  • Wagner Bianchi - Quarta-feira, 18/02/2009

    

    Utilize os tipos de comentários permitidos no MySQL:
    
    Comentários de linha:
    
    // comment
    # comment
    
    Comentário de bloco:
    
    /*
    
    Comentário de bloco
    
    */
    
    Happy MySQL'ing

  • Wagner Bianchi - Quinta-feira, 26/02/2009

    

    Errei quando escrevi acima sobre o comentário de linha ser o //. Troque o // por -- .
    
    Grande abraço!!

  Responder comentário

Sobre o autor

Wagner Bianchi é Tecnólogo em Gerenciamento de Banco de Dados pela Faculdade Infórium de Tecnologia, cursando o MBA em Administração de Empresas pela Fundação Getúlio Vargas, Consultor em Desenvolvimento de Sistemas/Banco de Dados, Analista de Sistemas em projetos OpenSource e especialista em SGBD MySQL. Atua também como Analista de Sistemas em Projetos da UFMG/ICEX, onde participa do desenvolvimento de produtos voltados para a área de CMS e Gestão da Informação. Certificado MySQL 5.0 Developer (CMDEV I e II), Certificado MySQL 5.0 Database Administrator I (CMDBA I) e MCDBA.

Indique para um amigo

Tags relacionadas

microsoft 3g apple seguranca mercado internet desenvolvimento twitter mobile php crescimento iphone web android publicidade yahoo celular brasil youtube lançamento linux ti tecnologia criação windows google

TI SHOP Produtos iMasters

• Lançamento: CD-ROM Treinamento Aplicado de SQL - Lançamento! Treinamento Aplicado de SQL - Aprenda a trabalhar com SQL com bancos de dados Oracle e SQL Server. São mais de 100 tópicos explicados por Mauro Pichilliani, um articulistas mais lidos do iMasters. Aproveite! Apenas R$ 69,90 no TI SHOP.
• Lançamento: Livro iMasters "O Encontro de 2 Mundos"- Este livro conta com 56 crônicas de profissionais mais admirados e influentes do mercado brasileiro de Internet. Aproveite o preço especial para leitores do iMasters. Apenas R$ 40,00 e envio imediato!
• DVD Curso Completo de Photoshop - Do conceito à finalização Lançamento! Curso Completo de Photoshop, em DVD, com mais de 230 aulas dividas em 4 módulos: conceito, básico, avançado e finalização. Apenas R$ 69,50 no TI SHOP - Frete com 50% de desconto
• DVD Javascript Starter - Curso Completo Com mais de 9 horas de vídeo-aulas, é um curso completo sobre Javascript. Ideal para quem deseja aprender a linguagem. Apenas R$ 64,90 no TI SHOP - Frete com 50% de desconto!