O que é uma Stored Procedure?

É uma colação de comandos SQL, que encapsula uma série de tarefas repetitivas, relativas ao acesso a banco, aceita parâmetros de entrada e retorna um valor de status ou conjunto de registros.

Por que usar um Stored Procedure?

As Stored Procedures ajudam a reduzir o tráfego na rede, a melhorar o desempenho de consultas, a criar mecanismos de segurança e simplificar o código da aplicação, já que não haverá a necessidade de manter consultas SQL de várias linhas misturadas a toda lógica da sua aplicação.

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;

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

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

corpo_da_rotina;[END]

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, não serão utilizadas inicialmente.

DELIMITER //DROP PROCEDURE IF EXISTS listar_paises //CREATE PROCEDURE listar_paises(IN id INT)BEGIN

IF(id = ‘ ’) THENSELECT * FROM pais;

ELSESELECT * FROM pais where id_pais = id;

END IF;END //

DELIMITER ;

Vamos a explicação.

DELIMITER // – Serve para marcar onde começa e onde termina a procedure, neste caso eu escolhi “//”

Mysql> status /* para observar as configurações padrão (DELIMITER)

DROP PROCEDURE IF EXISTS `listar_paises` - nesta linha eu informo ao SGBD que se a procedure já existir eu a estarei sobrescrevendo.

CREATE PROCEDURE - irá cria a procedure, note que há um parâmetros para esta procedures. Os parâmetros podem ser IN, apenas de entrada, OUT, apenas de saída e INOUT, entrada e saída. Os tipos de dados dos parâmetros são os mesmo tipos de dados do SGBDs ( INT, VARCHAR(45), TEXT, BLOB...)

Entre os comandos BEGIN e END é que serão colocados os comandos executados pela procedures.

Primeiramente eu verifico se foi passado o parâmetro id para a procedure.

IF(_id IS NULL) THEN

Caso tenha sido passado eu busco um país com aquele id, em caso contrário serão retornados todos os registros da tabela.

Chamando a procedure, que irá trazer todos os registros da tabela

call listar_paises( null );

Chamando a procedure, que irá trazer somente o registro do país com o ID = 1;

call listar_paises( 1 );

Nesta matéria eu mostrarei a vocês como alterar os dados no banco, seja com inserção de novos registros, atualização ou exclusão de registros pré-existentes.

Vamos a primeira procedure que trata da exclusão de um registro.

DELIMITER $$DROP PROCEDURE IF EXISTS `excluir_cidades` $$CREATE PROCEDURE `excluir_cidades`(IN _id INT)BEGIN

DELETE FROM cidade WHERE id_cidade = _id;SELECT ROW_COUNT();

END $$DELIMITER ;

A novidade nesta procedure é a adição do comando

SELECT ROW_COUNT();

Este comando retorna a quantidade de linhas que foram afetadas pelo comando anterior. Assim podemos ter um controle maior sobre a operação realizada, ou apenas para nos manter informados do que ocorreu de fato.

Por exemplo, se a procedure me retornar o Valor 1, significa que eu consegui excluir o registro, caso retorne 0 a exclusão pode ter falhado.

Exercício:

Crie uma procedure para atualizar dados na tabela “cidade”.

DELIMITER $$DROP PROCEDURE IF EXISTS `atualizar_cidades` $$CREATE PROCEDURE `atualizar_cidades`(IN _id INT,IN _nome VARCHAR(45),IN _populacao BIGINT,IN pais INT )

BEGINUPDATE cidade SET nome = _nome, populacao = _populacao ,

pais = _pais WHERE id_cidade = _id;SELECT ROW_COUNT();

END $$DELIMITER ;

Exercício 2:Crie uma procedure para inserir registros na tabela “cidade”.

DELIMITER $$DROP PROCEDURE IF EXISTS `inserir_cidade` $$CREATE PROCEDURE `inserir_cidade`(

IN _nome VARCHAR(45),IN _populacao BIGINT,IN pais INT )

BEGININSERT INTO cidade (nome,populacao, pais) VALUES (_nome,

_populacao, _pais);SELECT LAST_INSERT_ID();

END $$DELIMITER ;

A inserção segue a mesma formula das anteriores, mas como você pode perceber agora, nós trocamos o SELECT ROW_COUNT();

por

SELECT LAST_INSERT_ID();

Assim poderemos ter de volta o identificador (chave) da linha inserida o que muito útil na maioria dos sistemas.

texto