Stored Procedures no MySQL

Introduç�o aos Stored Procedures

Quando desenvolvemos aplicaç�es que acessam banco de dados (boa parte delas), � comum executarmos rotinas complexas de manipulaç�o desses dados a partir da linguagem/ferramenta utilizada. Para isso, utilizamos v�rias instruç�es SQL em sequ�ncia para obter o resultado esperado.

Dependendo da rotina a ser executada, isso pode requerer v�rias consultas e atualizaç�es na base, o que acarreta um maior consumo de recursos pela aplicaç�o. No caso de aplicaç�es web, isso se torna ainda mais vis�vel, devido a maior quantidade de informaç�es que precisam trafegar pela rede e de requisiç�es ao servidor.

Uma boa forma de contornar ou ao menos atenuar esse consumo de recursos diretamente pela aplicaç�o � transferir parte do processamento direto para o banco de dados. Assim, considerando que as m�quinas servidoras geralmente t�m configuraç�es de hardware mais robustas, enquanto nada se pode garantir com relaç�o �s m�quinas clientes, essa pode ser uma �sa�da� a se considerar.

A quest�o em que se esbarra �: como executar v�rias aç�es no banco de dados a partir de uma �nica instruç�o? A resposta que trazemos neste artigo para essa pergunta �: Stored Procedures (ou Procedimentos Armazenados, em portugu�s).

Stored procedures s�o rotinas definidas no banco de dados, identificadas por um nome pelo qual podem ser invocadas. Um procedimento desses pode executar uma s�rie de instruç�es, receber par�metros e retornar valores.

A seguir � apresentada uma situaç�o hipot�tica para que possamos entender melhor o conceito e import�ncia dos procedimentos armazenados.

Usar ou N�o usar Stored Procedures

Para exemplificar o funcionamento dos Stored Procedures e comparar a execuç�o de uma rotina utilizando e n�o utilizando essa t�cnica, tomemos como base o seguinte contexto de uma aplicaç�o comercial.

O cliente faz um pedido, no qual s�o inseridos itens;
O pedido (bem como os itens) permanece com status �PENDENTE� at� ser confirmado;
O operador confirma o pedido, registrando o movimento no livro caixa.

At� o pedido ser confirmado, nenhum lançamento � feito no livro caixa, ent�o � preciso ter uma rotina de confirmaç�o do pedido, que deve executar as seguintes aç�es:

Atualizar o status do pedido;
Atualizar o status dos itens do pedido;
Lançar o valor do pedido no caixa.

Temos ent�o pelo menos tr�s instruç�es de atualizaç�o e/ou inserç�o. Poder�amos representar essa situaç�o graficamente pela Figura 1.

Figura 1: Execuç�o da rotina sem stored procedure

Por outro lado, poder�amos agrupar essas tr�s instruç�es no corpo de um procedimento e cham�-lo a partir da aplicaç�o uma �nica vez. As aç�es de update/insert/delete, a partir da�, ficariam por conta do servidor. A representaç�o gr�fica desse modelo � mostrada na Figura 2 (considerando um procedure teoricamente chamado de �CONFIRMAR PEDIDO�).

Figura 2: Execuç�o da rotina usando stored procedure

Ent�o, tendo entendido o funcionamento dos procedimentos armazenados, podemos citar as principais vantagens e desvantagens de seu uso: Pontos positivos:

Simplificaç�o da execuç�o de instruç�es SQLpela aplicaç�o;
Transfer�ncia de parte da responsabilidade de processamento para o servidor.
Facilidade na manutenç�o, reduzindo a quantidade de alteraç�es na aplicaç�o.

Pontos negativos:

Necessidade de maior conhecimento da sintaxe do banco de dados para escrita de rotinas em SQL;
As rotinas ficam mais facilmente acess�veis. Algu�m que tenha acesso ao banco poder� visualizar e alterar o c�digo.

Criando e invocando Stored Procedures no MySQL

Entrando no real foco deste artigo, ser� explicado a seguir como trabalhar com procedures no banco de dados MySQL, iniciando pela sintaxe utilizada para criaç�o desse tipo de objeto, que pode ser vista na Listagem 1.

Listagem 1: Sintaxe para criaç�o de stored procedures no MySQL


        DELIMITER $$
        CREATE PROCEDURE nome_procedimento (par�metros)
        BEGIN
        /*CORPO DO PROCEDIMENTO*/
        END $$
        DELIMITER ;

Onde consta �nome_procedimento�, deve-se informar o nome que identificar� o procedimento armazenado. Este nome segue as mesmas regras para definiç�o de vari�veis, n�o podendo iniciar com n�mero ou caracteres especiais (exceto o underline �_�).

Os �par�metros� s�o opcionais e, caso n�o sejam necess�rios, devem permanecer apenas os par�nteses vazios na declaraç�o do procedure. Para que um procedimento receba par�metros, � necess�rio seguir certa sintaxe (dentro dos par�nteses), apresentada abaixo (Listagem 2).

Listagem 2: Sintaxe de declaraç�o de par�metros em stored procedures

(MODO nome TIPO, MODO nome TIPO, MODO nome TIPO)

Aqui, � v�lido iniciar a explicaç�o pelos itens mais simples.

O �nome� dos par�metros tamb�m segue as mesmas regras de definiç�o de vari�veis.

O �TIPO� nada mais � que do tipo de dado do par�metro (int, varchar, decimal, etc).

O �MODO� indica a forma como o par�metro ser� tratado no procedimento, se ser� apenas um dado de entrada, apenas de sa�da ou se ter� ambas as funç�es. Os valores poss�veis para o modo s�o:

IN: indica que o par�metro � apenas para entrada/recebimento de dados, n�o podendo ser usado para retorno;
OUT: usado para par�metros de sa�da. Para esse tipo n�o pode ser informado um valor direto (como �teste�, 1 ou 2.3), deve ser passada uma vari�vel �por refer�ncia�;
INOUT: como � poss�vel imaginar, este tipo de par�metro pode ser usado para os dois fins (entrada e sa�da de dados). Nesse caso tamb�m deve ser informada uma vari�vel e n�o um valor direto.

Outro ponto que merece destaque na Listagem 1 � o uso do comando DELIMITER. Por padr�o o MySQL utiliza o sinal de ponto e v�rgula como delimitador de comandos, separando as instruç�es a serem executadas. No entanto, dentro do corpo do stored procedure ser� necess�rio separar algumas instruç�es internamente utilizando esse mesmo sinal, por isso � preciso inicialmente alterar o delimitador padr�o do MySQL (neste caso, para $$) e ao fim da criaç�o do procedimento, restaurar seu valor padr�o.

Tendo criado o procedure, cham�-lo � bastante simples. Para isso fazemos uso da palavra reservada CALL, como mostra o c�digo da Listagem 3.

Listagem 3: Sintaxe para chamar um stored procedure

CALL nome_procedimento(par�metros);

A seguir temos um exemplo de uso de cada tipo de par�metro.

Listagem 4: Usando par�metro de entrada


        DELIMITER $$

        CREATE PROCEDURE Selecionar_Produtos(IN quantidade INT)
        BEGIN
        SELECT * FROM PRODUTOS
        LIMIT quantidade;
        END $$
        DELIMITER ;

Esse procedimento tem por funç�o fazer um select na tabela PRODUTOS, limitando a quantidade de registros pela quantidade recebida como par�metro. Assim, caso desej�ssemos selecionar dois registros dessa tabela, poder�amos usar o procedure como mostra a Listagem 5.

Listagem 5: Chamando procedure com par�metro de entrada

CALL Selecionar_Produtos(2);

O pr�ximo c�digo mostra um exemplo de recebimento e retorno de par�metro de sa�da.

Listagem 6: Usando par�metro de sa�da


        DELIMITER $$

        CREATE PROCEDURE Verificar_Quantidade_Produtos(OUT quantidade INT)
        BEGIN
        SELECT COUNT(*) INTO quantidade FROM PRODUTOS;
        END $$
        DELIMITER ;

A funç�o desse procedimento � retornar a quantidade de registros da tabela PRODUTOS, passando esse valor para a vari�vel de sa�da �quantidade�. Para isso foi utilizada a palavra reservada INTO.

Para cham�-lo, usamos um s�mbolo de arroba (@) seguido do nome da vari�vel que receber� o valor de sa�da. Feito isso, a vari�vel poder� ser usada posteriormente, como vemos na Listagem 7.

Listagem 7: Chamando procedure com par�metro de sa�da


        CALL Verificar_Quantidade_Produtos(@total);
        SELECT @total;

Ao executar a segunda linha, teremos como retorno o valor da vari�vel @total, que ser� preenchida no procedure.

O terceiro exemplo mostra um stored procedure chamado Elevar_Ao_Quadrado, que recebe uma vari�vel e a altera, definindo-a como o seu pr�prio valor elevado � segunda pot�ncia.

Listagem 8: Usando par�metro de entra e sa�da


        DELIMITER $$

        CREATE PROCEDURE Elevar_Ao_Quadrado(INOUT numero INT)
        BEGIN
        SET numero = numero * numero;
        END $$
        DELIMITER ;

Nesse caso, a mesma vari�vel � usada como entrada e sa�da, como vemos na chamada da Listagem 9.

Listagem 9: Chamando procedure com par�metro de entrada e sa�da


        SET @valor = 5;
        CALL Elevar_Ao_Quadrado(@valor);
        SELECT @valor;

Usando vari�veis no corpo do procedimento

� poss�vel declarar vari�veis no corpo dos stored procedures, para isso basta utilizar a seguinte sintaxe:

Listagem 10: Sintaxe de declaraç�o de vari�veis

DECLARE nome_vari�vel TIPO DEFAULT valor_padrao;

A palavra reservada DECLARE � obrigat�ria e � a respons�vel por indicar que uma vari�vel ser� declarada com o nome �nome_variavel� (que segue as mesmas regras de nomeaç�o de vari�veis). O TIPO � o tipo de dados da vari�vel (int, decimal, varchar, etc). A palavra reservada DEFAULT � opcional e deve ser usada quando se deseja definir um valor inicial (valor_padrao) para a vari�vel.

A declaraç�o das vari�veis deve ser feita logo no in�cio do corpo do procedure, para aquelas que ser�o utilizadas em todo o procedimento, ou dentro de um bloco BEGIN-END espec�fico que limite seu escopo.

Para definir um valor para uma vari�vel, usamos as palavras reservadas SET (no caso de passagem direta de valor, como na Listagem 8) ou INTO (no caso de associaç�o de valores dentro de consultas, como na Listagem 6).

Outro ponto importante de se citar � o ESCOPO das vari�veis, que define em que pontos elas s�o reconhecidas. Uma vari�vel definida dentro de um bloco BEGIN/END � v�lida somente dentro dele, ou seja, ap�s o END ela j� n�o � mais reconhecida. Assim, � poss�vel definir v�rias vari�veis com o mesmo nome, mas dentro de blocos BEGIN/END distintos.

Por sua vez, vari�veis cujo nome inicia com arroba (@), s�o chamadas vari�veis de sess�o, e s�o v�lidas enquanto durar a sess�o (exemplo da Listagem 9).

Excluindo procedures

Para excluir uma procedure no MySQL basta utilizar o comando abaixo:

Listagem 11: Excluindo procedure

DROP PROCEDURE Listar_Funcionarios;

H� tamb�m a alternativa a seguir:

Listagem 12: Excluindo procedure

DROP PROCEDURE IF EXISTS Listar_Funcionarios;

Ambos os comandos excluem uma stored procedure que tenha sido criada anteriormente.

Exibindo procedures

No MySQL temos um comando que exibe todas as stored procedures criadas:

Listagem 13: Listando procedures

SHOW PROCEDURE STATUS;

Ao executar o comando acima aparecer� uma lista com todas as stored procedures que foram criadas anteriormente.

Conclus�o

Nesse artigo vimos como funcionam os Stored Procedures em geral (para qualquer banco) e como trabalhar com essa estrutura no banco de dados MySQL, usando par�metros de entrada e sa�da, declarando vari�veis e invocando-os a partir da instruç�o CALL.

Vale ressaltar que o c�digo do corpo de um procedimento pode conter v�rias linhas com diversas instruç�es SQL, aqui apenas foram apresentados exemplos b�sicos a n�vel did�tico.

A seç�o de coment�rios, logo abaixo, est� aberta para cr�ticas, d�vidas e sugest�es.