Nesse artigo vou estar falando um pouco sobre Stored Procedures e demonstrar um pequeno exemplo de sua utilização no MySQL/MariaDB.

Ambiente utilizado:

Computador: Notebook Gateway
Sistema operacional: OpenSuse 11.2
Banco de dados: MariaDB 5.1.42

O que é Stored Procedure?
Stored Procedures nada mais são do que um conjunto de instruções SQL que são executadas dentro do banco de dados.
É como escrever um programa dentro do próprio banco de dados para executar tudo lá dentro.

Sintaxe geral:
CREATE PROCEDURE (tipo_param param_1 data_type, …)
[BEGIN]
   corpo_da_rotina;
[END]

Ao passar os parâmetros para uma procedure você pode definir o seu tipo:
IN => parâmetro de entrada.
OUT => parâmetro de retorno.
INOUT => parâmetro de entrada e de retorno.
Caso no parâmetro não seja informado o seu tipo, o padrão adotado é IN.

Exemplo prático:
Criaremos uma procedure que irá “popular” uma tabela denominada Cliente com o nome e email.

1. No terminal, conecte-se ao MariaDB.
   mysql -u root -p

2. Criação do banco de dados:
   CREATE DATABASE mysqllabs;

3. Criação da tabela CLIENTE:

CREATE  TABLE IF NOT EXISTS mysqllabs.cliente (
  id INT NOT NULL AUTO_INCREMENT ,
  nome VARCHAR(45) NOT NULL ,
  email VARCHAR(80) NOT NULL ,
  PRIMARY KEY (id) )
ENGINE = Maria;

O campo ID é um auto incremento e possui mais dois atributos: nome e email.
A tabela foi criada usando a STORAGE ENGINE Maria. Caso não conheça Storage Engines dê uma olhada nesse artigo escrito pelo Alexandre Almeida
(http://www.mysqllabs.com/joomla/index.php?option=com_content&view=article&id=55:storageengine1&catid=1:latest-news&Itemid=100).

4. Criaremos uma procedure chamada INS_CLIENTE que terá como parâmetros de entrada o NOME e o EMAIL. Os dados serão inseridos na tabela CLIENTE.

delimiter //

CREATE PROCEDURE mysqllabs.ins_cliente(in pnome VARCHAR(45)
                                      ,in pemail VARCHAR(80) )
BEGIN
    
    INSERT INTO cliente(nome, email)
    VALUES(pnome, pemail);
END
//

Agora vocês me perguntam. O que significa o DELIMITER antes de criar a procedure?
A mudança de delimitador de linha serve para que o MariaDB não interprete o ‘;’ nos comandos que listamos dentro de um gatilho, procedimento, etc…Se deixássemos o ;(ponto e vírgula) no final do INSERT o nosso comando seria encerrado incorretamente.

5. Para executar a procedure basta utilizar o comando CALL.

DELIMITER ;
call mysqllabs.ins_cliente(‘julio’, ‘julio@mysqllabs.com’);

6. Para checar se inserção foi efetuada na tabela, execute o comando:
select * from mysqllabs.cliente;


Depois de criar a stored procedure no MySQL como que faço para consultar?
Depois de criados e compilados, as stored procedures são inseridas em uma tabela chamada ROUTINES no banco de dados INFORMATION_SCHEMA, que é o dicionário de dados do MySQL.
Para listarmos, basta emitirmos o seguinte comando:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES;

Se preferir, pode-se utilizar o comando: SHOW PROCEDURE STATUS;
Ele retorna características da rotina, tais como nome, tipo, quem criou, datas de modificação e criação.

Outra alternativa é utilizar o comando: SHOW PROCEDURE STATUS LIKE ‘%ins%’;
Sendo que no LIKE informamos o nome da procedure ou um pedaço de sua descrição para pesquisa, no nosso caso informei a string INS.

Espero que tenham gostado e até a próxima semana!