Utilizando Stored Functions no MySQL

No caso de aplicações que se conectam a servidores de bancos de dados mais robustos, como no caso do MySQL, administradores de bancos de dados e/ou desenvolvedores de sistemas já podem estar atentos em criar componentes modulares, armazenando programas que retornam ou não um valor em meio à operações do sistema.

Segundo Roger Pressman, modularidade é a independência funcional dos componentes do programa. Cada componente pode ser desenvolvido para desempenhar processamentos específicos, retornar um determinado resultado com base em entradas ou manipulações de dados, tratamento de determinados tipos de informação e até validar dados de acordo  com determinadas regras do negócio. Os componentes são desenvolvidos e estão disponíveis para serem aninhados dentro de outros novos.

A partir da versão 4.1 do MySQL já é possível utilizar as Stored Routines (Stored Procedures e Functions) para dividir o processamento de forma modular.

Neste artigo focaremos apenas na utilização de Stored Functions no MySQL (verifique se a sua versão é superior ou igual à versão 5.0). Mais especificamente, exibiremos como utilizar as funções definidas pelo usuário, as chamadas Stored Functions de maneira modular, buscando agilidade no desenvolvimento de sistemas, economia na escrita de código, colocando a lógica também por conta do banco de dados, dando mais segurança aos componentes que manipulam dados e produzem informações, facilitar a manutenção e minimizar o consumo de banda entre o servidor de banco de dados MySQL e a sua aplicação. Além disso, ao longo deste artigo, estruturaremos juntos, passo-a-passo, um estudo de caso para direcionar todos os exemplos práticos que teremos que explorar as Stored Routines (Stored Functions e Stored Procedures).

Stored Routines no MySQL

Stored Routines são programas armazenados dentro do servidor MySQL, obrigatoriamente vinculados a algum banco de dados que podem retornar ou não algum resultado. Stored Routines são as Stored Procedures e as Stored Functions, e podem ser verificadas através do dicionário de dados INFORMATION_SCHEMA, consultando a tabela ROUTINES, como mostra a Listagem 1.

Listagem 1. Exibindo as Stored Routines existentes em um servidor MySQL

mysql> SELECT ROUTINE_NAME
    -> FROM INFORMATION_SCHEMA.ROUTINES;
+-----------------------+
| ROUTINE_NAME          |
+-----------------------+
| fn_checa_categoria    |
| fn_data_converte      |
| fn_email              |
+-----------------------+
3 rows in set (0.03 sec)

Uma Stored Procedure pode retornar ou não um resultado. Quando invocada através da declaração CALL, executa manipulações em dados ou processa o retorno de dados de tabelas de um ou mais bancos de dados. Dentro destas, podemos utilizar de user variables e local variables para armazenar valores que podemos utilizar mais tarde, dentro do seu processamento. Assim como os tipos de variáveis citados, podemos utilizar também os comandos de transação (START TRANSACTION - COMMIT - ROLLBACK), estruturas condicionais como IF-THEN-ELSE e CASE e tratamento de erros.

Já uma Stored Function sempre e obrigatoriamente retornará um valor para a rotina que a invoca ou outras Stored Routines que a excuta. Sendo assim, uma Stored Function utilizada em meio a expressões podem ser consideradas como constantes, uma função do próprio MySQL ou uma referência a uma coluna de uma tabela. Uma Stored Function não pode ser invocada através da declaração CALL, assim como uma Stored Procedure também não poderá ser chamada em meio a uma expressão SELECT, por exemplo.

Imagine que em um sistema web precisemos receber uma data de nascimento e fazer o cálculo para retornar a idade atual de uma pessoa, mas que este procedimento seja requerido em várias páginas. Não precisamos escrever funções e métodos para obter este resultado diretamente na aplicação ou mesmo escrevê-lo várias vezes, em vários pontos do aplicativo. Basta criarmos ou definirmos uma função vinculada a um banco de dados utilizado pela aplicação para que esta possa ser utilizada em meio a uma declaração SELECT ou em outros casos, em meio a expressões como normalmente se utiliza uma built-in function ou uma função disponível na biblioteca do MySQL ou ainda, em qualquer outro tipo de Stored Routines.

A Listagem 2 exibe a criação de uma função que calcula a idade atual de uma pessoa a partir de uma data de nascimento informada como entrada, enviada como parâmetro. Em sistemas mais contemporâneos, que utilizam técnicas de AJAX (Assincronous JavaScript and XML), podemos com certa facilidade, desenvolver scripts para fazer tal cálculo para retornar o valor da idade de forma assíncrona, ou melhor, sem ter que dar aquele refresh na tela do sistema.

Listagem 2. Criando uma função para retorno da idade a partir da data de nascimento

mysql> DELIMITER //
mysql> CREATE FUNCTION f_age (in_dob datetime)

    -> RETURNS TINYINT
    ->  BEGIN
    ->    DECLARE l_age INT;
    ->    IF DATE_FORMAT(NOW(  ),'00-%m-%d') >= DATE_FORMAT(in_dob,'00-%m-%d')THEN
    ->      -- This person has had a birthday this year
    ->      SET l_age=DATE_FORMAT(NOW(  ),'%Y')-DATE_FORMAT(in_dob,'%Y');
    ->    ELSE
    ->      -- Ainda fará aniversário esse ano
    ->      SET l_age=DATE_FORMAT(NOW(  ),'%Y')-DATE_FORMAT(in_dob,'%Y')-1;
    ->    END IF;
    ->    RETURN(l_age);
    -> END;
    -> //
Query OK, 0 rows affected (0.06 sec)

Benefícios na utilização de Stored Routines no MySQL

Esta seção descreve alguns dos benefícios obtidos no gerenciamento de banco de dados a partir da utilização de Stored Routines no MySQL:

·         Maior Flexibilidade na Escrita do código SQL

Stored Routines podem ser escritas utilizando blocos de códigos com BEGIN ... END, estruturas condicionais IF...THEN...ELSE, de fluxo de controle (LOOP, WHILE, REPEAT), facilitando a composição de estruturas de lógica mais complexas. ...