[PL/MySQL] - Compreenda as STORE PROCEDURES no MySQL

 

No cenário atual de desenvolvimento estamos cada vez mais preocupados com alguns princípios: A menor escrita de código possível (evitar redundância ao máximo), a centralização da regra de negócio e validação para facilitar a manutenção do projeto, a simplicidade e legibilidade no desenvolvimento do código e para finalizar a menor interdependência de situações no código. Dentro da evolução do desenvolvimento existe uma fase onde partes das regras de negócios estavam localizadas no banco de dados, isto foi possível devido ao recurso da STORE PROCEDURE que são um conjunto de instruções SQL armazenado no servidor. Os dois maiores fatores negativos deste cenário são:

- É o maior poder para o DBA (que agora também fica com a função de programador),

- Aumento na utilização dos recursos no servidor.

ATENÇÃO: Não utilize STORE PROCEDURE para implantação de bibliotecas para serem utilizadas na sua programação por que isto é trabalho para as linguagens de programação, além do mais teria um custo elevado no desempenho de sua aplicação.

De acordo com o manual do MySQL você pode utilizar STORE PROCEDURES neste cenário:
“

a) Quando várias aplicações clientes são escritas em diferentes linguagens ou funcionam em diferentes plataformas, mas precisam realizar as mesmas operações de banco de dados.

b) Quando a segurança é prioritária. Bancos, por exemplo, usam stored procedures para todas as operações comuns. Isto fornece um ambiente consistente e seguro, e procedures podem assegurar que cada operação seja registrada de forma apropriada. Neste tipo de condiguração, aplicações e usuários não conseguiriam nenhuma acesso as tabelas do banco de dados diretamente, mas apenas podem executar stored procedures específicas.

“

Vale ressaltar que há um tempo a Microsoft encorajava o desenvolvimento de suas aplicações com STORE PROCEDURES devido ao esquema de segurança.

A STORE PROCEDURE está presente desde a versão 5 do MySQL e segue a sintaxe do SQL:2003 o mesmo utilizado pelo DB2 da IBM. Após esta explicação, voltemos ao projeto da locadora de DVD e vejamos na prática como implantamos STORE PROCEDURES.

Abaixo o DER que iremos trabalhar:

DER Locadora de DVD

Sintaxe de uma procedure:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

CREATE PROCEDURE NOME_DA_PROCEDURE ( tipo_parametro1(Valores possíveis: [ IN | OUT | INOUT ]) parametro1 tipo_dado1(Qualquer tipo válido do mysql(varchar, char, int, date) , [ tipo_parametro2(Valores possíveis: [ IN | OUT | INOUT ]) parametro2 tipo_dado2(Qualquer tipo válido do mysql(varchar, char, int, date) ] ) BEGIN INSTRUCOES SQL; END

Tipos de Parâmetros:

a) IN => Parâmetro de entrada. É passado um valor que vai será utilizado no corpo da procedure;

b OUT =>Parâmetro de saída. Retorna um valor que pode fica armazenado na memória do servidor;

c) INOUT => Possui a funcionalidade de entrada e saída ao mesmo tempo.

Vamos fazer o procedimento de CRUD para a tabela tegenero (idGenero int(autoincremento), NmGenero, FgStatus smallint) da locadora:

Código de Inserção:

1 2 3 4 5 6

DELIMITER $$ CREATE PROCEDURE prInsereGenero (IN strNmGenero varchar(100), intFgStatus smallint) BEGIN INSERT INTO teGenero(NmGenero, FgStatus) VALUES (strNmGenero, intFgStatus); END $$ DELIMITER ;

Código de exclusão:

1 2 3 4 5 6

DELIMITER $$ CREATE PROCEDURE prExcluiGenero (IN intidGenero int) BEGIN DELETE FROM tegenero WHERE idGenero = intidGenero; END $$ DELIMITER ;

Código de Edição:

1 2 3 4 5 6 7 8

DELIMITER $$ CREATE PROCEDURE prEditaGenero (IN intIdGenero int, strNmGenero varchar(100), intFgStatus smallint) BEGIN UPDATE tegenero SET NmGenero = strNmGenero , FgStatus = intFgStatus WHERE idGenero = intIdGenero; END $$ DELIMITER ;

Código da Chamada:

1 2 3

CALL prInsereGenero ('Teste A', 0); CALL prEditaGenero (2, 'Teste B', 1); CALL prExcluiGenero (1);