Artigos Banco de Dados Advanced Series - MySQL Stored Procedures

Olá pessoal,

19-11-2007pic01.JPG

Depois de algum tempo sem postar artigos aqui no portal SQL Magazine. Agradeço a todos os amigos que tem enviado e-mails com ótimas palavras e solicitando temas para abordarmos aqui na forma de artigo. Lembro a você que, para aqueles que buscam aprender MySQL com vídeos que trazem uma abordagem 92% prática, acesse a área de vídeos do Portal SQL Magazine onde tenho trabalhado junto com a equipe para trazer tudo o que você precisa aprender para manipular bem o SGBD MySQL.

Neste artigo (usarei um servidor SUPERION da SunSix, rodando Ubuntu 6.06 LTS com MySQL 5.0.37 Community Version), iniciaremos uma viagem interessante sobre todo o mundo dos procedimentos armazenados ou stored routines, cujo conceito principal é que são “programas armazenados no servidor, pré-compilados, chamados de forma explícita para executar alguma lógica de manipulação de dados, podendo retornar ou não algum valor”.

Mal começamos e já temos o conceito de stored procedure ou stored routines. No caso do MySQL, os procedimentos armazenados estão disponíveis exatamente desde a versão 5.0, que foi um marco na evolução do SGBD OpenSource mais utilizado no mundo.

Necessariamente, você precisará ter instalado na sua máquina, o MySQL 5++ e o MySQL Client (este é disponibilizado no momento da instalação do server) e um editor de textos como vi, emacs ou notepad. Poderemos utilizar também o MySQL Query Browser para tornar nossa experiência mais interessante com o MySQL e sair um pouco da linha de comando.

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;

Perceba que listamos todos os procedimentos armazenados (Stored Procedure e Functions), de todos os bancos de dados. Saliento que estamos listando somente Stored Procedure e Functions, pois, somente estas rotinas são gravadas na tabela ROUTINES do bancos de dados INFORMATION_SCHEMA. Triggers também são um tipo de procedimento armazenado, mas estão separadas em outra tabela do dicionário, chamada TRIGGERS.

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS;

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

CREATE PROCEDURE (tipo_param param_1 data_type, ...)
    
    [BEGIN]
    
          corpo_da_rotina;
    
    [END]

Explicando...

nome_procedure: 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!

param_1: esse é o parâmetro que enviaremos ao procedimento;

data_type: tipo de dados permitido para este parâmetro (INT, DECIMAL, CHAR [...]);

corpo_da_rotina: onde são definidos os comandos SQL que farão alguma manipulação e/ou defenderão alguma lógica, podendo retornar ou não algum resultado.

PRIMEIRO EXEMPLO!

Nesse primeiro exemplo, implementaremos um Stored Procedure bem simples, que nos devolverá um olá! Abra um terminal do seu Linux ou mesmo um prompt do seu Windows e entre no Mysql digitando:

shell> mysql -u nome_usuario -psenha,

Usarei neste artigo o banco de dados chamado test, que já vem criado desde a instalação do MySQL. Caso não conste no seu, use um banco de dados de sua preferência.

Caso prefira, podemos usar também o MySQL Query Browser (você poderá baixá-lo em http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-gui-tools-5.0r12-linux-i386.tar.gz/from/pick?done=0e56b35d372ca1 dentro de um pacote chamado MySQLGUITools). NO Windows basta instalar e no Linux basta descompactar o pacote TAR e executar.

Se você optou pelo MySQL Client, nesse momento estamos nesse ponto:

19-11-2007pic02.JPG

Se você tiver optado por trabalhar com o MySQL Query Browser, estamos nesse ponto:

19-11-2007pic03.JPG

Pronto! O Query Browser já nos deu quase tudo pronto para escrevermos somente os parâmetros que teremos no nosso procedimento e o corpo da rotina. Notem que, é utilizado o operador DELIMITER para mudar o delimitador de comandos, que por padrão é o “;”. no meio do procedimento, caso não mudemos, o procedimento será enviado pela metade e um erro será enviado por na sintaxe.

O DELIMITADOR no MySQL, em outras situações, por padrão também é chamado de terminador, Para verificar qual o delimitador da sessão corrente emita o comando STATUS, seguido por “;” ou o delimitador personalizado.

19-11-2007pic04.JPG

Bem, procedimento compilado basta executarmos o procedimento com o seguinte comando: 

CALL OLA();

OBS.: Após a versão 5.1.18, não é mais necessário os parênteses caso o procedimento não receba parâmetros.

A resposta do procedimento será “OLÁ!” como definimos.

19-11-2007pic05.JPG

Bom, agora que já temos uma noção básica de como é implementado um procedimento armazenado no MySQL, já podemos partir para aplicações do mundo real, tais como, manipular inserções de dados, exclusões de registros e atualizações de linhas de uma ou mais tabelas. Tudo isso nos levará a aportar lá na frente no conceito de transações, onde desenvolveremos um procedimento para simulação de transferência de valor entre contas bancárias de mesma agência.

Como seria então, um procedimento para inserir dados em uma tabela do banco de dados?

Bom, antes de prosseguirmos, criaremos uma tabela, de nome tbl_correntista, que terá os campos correntista_id do tipo INT, correntista_nome do tipo VARCHAR(60) e correntista_cpf do tipo VARCHAR(20).

CREATE TABLE tbl_correntista (
    correntista_id int auto_increment primary key,
    correntista_nome varchar(60) not null,
    correntista_cpf varchar(20) not null,
    dt_cadastro timestamp default current_timestamp(),
    unique(correntista_cpf)
    ) Engine =InnoDB;

Pronto! Após executarmos o script acima nossa tabela estará criada e já poderemos dar carga através de um procedimento armazenado, onde também utilizaremos em meio a este, estruturas condicionais, IF-THEN-ELSE!

Com a nossa tabela criada, criaremos nosso procedimento para efetuar o INSERT dos dados, ou seja, um procedimento para dar carga na tabela. 

DELIMITER //
    CREATE PROCEDURE mySp_correntistaInsert(v_nome VARCHAR(60), v_cpf VARCHAR(20))
    BEGIN
    IF ((v_nome != '') && (v_cpf != '')) THEN
    INSERT INTO tbl_correntista (correntista_nome, correntista_cpf)
    VALUES (v_nome, v_cpf);
    ELSE
    SELECT 'NOME e CPF devem ser fornecidos para o cadastro!' AS Msg;
    END IF; 
    END;
    //

Após compilarmos o procedimento, já poderemos chamá-lo através da declaração CALL, como se segue:

CALL mySp_correntistaInsert('Wagner Bianchi', '023.456.789-10');

...notem que utilizamos em meio ao nosso procedimento de inserção, a estrutura condicional para consistir o valor das variáveis. Caso os valores de ambas sejam vazios, a mensagem será disparada, como segue abaixo:

19-11-2007pic06.JPG

Após executarmos a linha de inserção com os valores, teremos um registro na tabela, da seguinte forma:

19-11-2007pic07.JPG

Temos mais dois procedimentos, fáceis, para complementar essa primeira parte do artigo. Faremos a seguir, um procedimento, com o mesmo formato para atualizarmos o registro da nossa tabela "tbl_correntista", que até o momento encontra-se com um registro. Serão três agora, o identificador do registro - v_id - , o novo nome do correntista - v_nome - e o novo cpf - v_cpf -. Vamos lá!

DELIMITER //
    CREATE PROCEDURE mySp_correntistaUpdate(v_id INT, v_nome VARCHAR(60), v_cpf VARCHAR(20))
    BEGIN 
    IF (((v_id > 0) && (v_id != '') ) && (v_nome != '') && (v_cpf != '')) THEN
    UPDATE tbl_correntista SET correntista_nome =v_nome,
    correntista_cpf =v_cpf
    WHERE correntista_id =v_id;
    ELSE
    SELECT 'O novos NOME e CPF devem ser informados!' AS Msg;
    END IF; 
    END;
    //

Já podemos, após compilarmos o procedimento de UPDATE, atualizarmos nosso registro na tabela de correntistas. Depois de compilado com sucesso, já podemos chamar nosso procedimento de atualização e passar os parâmetros para atualizar o registro que temos na tabela. 


    CALL mySp_correntistaUpdate(1, 'Wagner MySQL Bianchi', '123.123.111-11');

19-11-2007pic08.JPG

Note que este procedimento poderá facilmente ser adaptado em qualquer sistema que receba o identificador do registro a ser atualizado em uma tabela qualquer de um banco de dados. Sistemas web passam parâmetros com facilidade vi POST ou GET, de forma que poderão ser entregues como parâmetro ao procedimento.

Para finalizarmos, faremos um procedimento para excluir registros, que é o mais trivial de todos, basta mais uma vez enviarmos o identificador do registro como parâmetro e efetuarmos a exclusão após a conferência como estamos fazendo nos outros procedimentos.

Note que, em um sistema, você poderá implementar um único procedimento para exclusão de registros, que receberá alguns parâmetros como o identificador, o nome da tabela e o nome da coluna, mas, nesse momento, nos atentaremos para o simples, nos próximos artigos sofisticaremos um pouco mais nossos procedimentos.

DELIMITER //
    CREATE PROCEDURE mySp_correntistaDelete(v_id INT)
    BEGIN
    IF ((v_id > 0) && (v_id != '')) THEN
    DELETE FROM tbl_correntista WHERE correntista_id =v_id;
    ELSE 
    SELECT 'O identifiador do registro não foi informado!' AS Msg;
    END IF; 
    END;
    //

Agora já podemos excluir o registro que inserimos e atualizamos! Segue a sintaxe para isso:

CALL mySp_correntistaDelete(1);

Bom, espero que este artigo, ainda na primeria fase, com aplicações de procedimentos muito básicos, possam trazer uma iniciação em stored procedure no MySQL a todos que buscam aprender a manejar este recurso que é de grande proveito, principalmente para sistemas que necessitam de modularidade e otimização quanto à performance.

Por outro lado, você poderá ficar preso ao MySQL (que não é mal negócio) ou qualquer outro SGBD que você vá usar para ser back-end, utilizando os procedimentos armazenados.

Na parte dois, avançaremos bem nos conceitos e já falaremos de transações com vários comandos dentro de uma stored procedure, trabalhando com criação de variáveis, iterações e savepoints, iniciando nossa aplicação de transferência de valores entre contas bancárias, que também necessitará uma pincelada nos conceitos de commit e rollback.

Em breve, os artigos serão escritos utilizando o MySQL 6.0. Recursos 1000, aguardem!

Um abração a todos e Happy MySQL'In!

PARTE II
Veja abaixo a segunda parte do artigo - Agora as partes I e II foram compiladas em um único artigo. Bons estudos :)

Stored procedures no MySQL - Parte 2

Atenção: por essa edição ser muito antiga não há arquivo PDF para download.
Os artigos dessa edição estão disponíveis somente através do formato HTML.

img

Clique aqui para ler todos os artigos desta edição

Stored procedures no MySQL - Parte 2

 Ian Gilfillan

Leitura obrigatória: SQL Magazine 31, Stored procedures no MySQL – Parte1.

 

Nesta segunda parte desta série sobre stored procedures no MySQL, analisamos em particular os manipuladores e cursores. Os manipuladores permitem executar declarações caso certa condição esteja presente, enquanto que os cursores permitem iterar através de um resultset, processando-o linha a linha. Se ainda não tiver lido a primeira parte da série, faça-o agora, especialmente se stored procedures são novidade para você.

Os manipuladores e o tratamento de erros

Com o advento das stored procedures, que permitem ao SGBD lidar com conceitos que anteriormente só eram resolvidos mediante programação, há uma clara necessidade de uma maneira mais elegante de controlar erros e exceções. Há dois tipos de manipuladores suportados pelo MySQL: manipuladores EXIT, que encerram imediatamente o bloco BEGIN/END corrente; e manipuladores CONTINUE, que permitem ao processo continuar depois que as ações do manipulador foram executadas (o manipulador UNDO, que pode ser familiar a usuários de outros SGBDs ainda não é suportado). A Listagem 1 mostra um exemplo. Lembre-se que ainda estamos usando o caractere ‘|’ como delimitador, como mencionado na primeira parte da série.

 

mysql>

CREATE procedure sp3()

 BEGIN

  DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';

  DECLARE EXIT HANDLER FOR 'unknown column'

   SELECT 'error error whoop whoop';

  SELECT aha;

  SELECT 'continuing';

 END;|

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp3()\G

*************************** 1. row ***************************

error error whoop whoop: error error whoop whoop

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Listagem 1. Exemplo de utilização do manipulador EXIT.

 

Então, o que acontece aqui? Nós declaramos uma condição chamada 'unknown column'. É uma condição que irá surgir quando for atingido o SQLSTATE 42S22, que ocorre quando encontramos uma coluna desconhecida (você poderá achar uma lista completa de códigos e mensagens de erro no site do MySQL). A seguir, declaramos um manipulador EXIT para a condição 'unknown column' declarada acima. O manipulador apenas exibe a mensagem error error whoop whoop. O corpo da procedure consiste em duas declarações: SELECT aha, que foi programada para ativar o SQLSTATE 42S22 e SELECT 'continuing', a qual de fato nunca será executada, pois sendo um manipulador exit, a procedure é encerrada assim que a condição estiver presente. Deste modo, quando chamamos sp3(), a declaração SELECT dispara a condição, e a mensagem é exibida.

Introduzindo um manipulador CONTINUE, vemos a diferença na Listagem 2.

 

mysql>

CREATE procedure sp4()

 BEGIN

  DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';

  DECLARE CONTINUE HANDLER FOR 'unknown column'

   SELECT 'error error whoop whoop';

  SELECT aha;

  SELECT 'continuing';

 END;

 

mysql> CALL sp4()\G

*************************** 1. row ***************************

error error whoop whoop: error error whoop whoop

1 row in set (0.00 sec)

*************************** 1. row ***************************

continuing: continuing

1 row in set (0.06 sec)

Listagem 2. Adicionado manipulador CONTINUE ao exemplo anterior.

 

Como esperado, a procedure continua executando após o erro, e agora a declaração SELECT 'continuing' será executada.

A Listagem 3 mostra outro procedimento. O que você acha que acontecerá? Se quisermos exibir as mensagens “error error” e “still handling” como parte do manipulador depois de atingir a declaração “aha”, e então continuarmos com a declaração “continuing”, atingiremos este resultado?

 

CREATE procedure sp5()

 BEGIN

  DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';

  DECLARE CONTINUE HANDLER FOR 'unknown column'

   SELECT 'error error whoop whoop';

   SELECT 'still handling the error';

  SELECT aha;

  SELECT 'continuing';

 END;

 

mysql> CALL sp5()\G

*************************** 1. row ***************************

still handling the error: still handling the error

1 row in set (0.00 sec)

*************************** 1. row ***************************

error error whoop whoop: error error whoop whoop

1 row in set (0.00 sec)

*************************** 1. row ***************************

continuing: continuing

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Listagem 3. Outro exemplo de utilização dos manipuladores.

 

A resposta é: certamente que não. O SELECT ‘still handling the error’;, de fato faz parte do corpo da procedure principal e não do manipulador de erro. Desde que não existe nenhuma declaração BEGIN ou END que faça parte do manipulador, o manipulador possui apenas uma declaração. O código da Listagem 4 atinge os nossos objetivos.

 

mysql>

CREATE procedure sp6()

 BEGIN

  DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';

  DECLARE CONTINUE HANDLER FOR 'unknown column'

   BEGIN

    SELECT 'error error whoop whoop';

    SELECT 'still handling the error';

   END;

  SELECT aha;

  SELECT 'continuing';

 END;|

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp6()\G

*************************** 1. row ***************************

error error whoop whoop: error error whoop whoop

1 row in set (0.00 sec)

*************************** 1. row ***************************

still handling the error: still handling the error

1 row in set (0.00 sec)

*************************** 1. row ***************************

continuing: continuing

1 row in set (0.00 sec)

Listagem 4. Código correto que atinge o objetivo esperado.

 

Por fim, a Listagem 5 apresenta um exemplo de uma procedure onde o manipulador de erro não é chamado, e nenhuma das declarações do mesmo é executada.

 

mysql>

CREATE procedure sp7()

 BEGIN

  DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';

  DECLARE CONTINUE HANDLER FOR 'unknown column'

   BEGIN

    SELECT 'error error whoop whoop';

    SELECT 'still handling the error';

   END;

  SELECT 'continuing';

 END;|

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp7()\G

*************************** 1. row ***************************

continuing: continuing

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Listagem 5. Exemplo em que o manipulador não é chamado.

Variações e usos

Nos exemplos das listagens anteriores, declaramos uma condição para detectar um erro SQLSTATE. Além dessa abordagem, podemos ainda declarar manipuladores diretamente para tratar códigos de erro específicos. Desta forma, nem sempre precisaremos testar condições intermediárias, não havendo, portanto, necessidade alguma de referenciar uma lista de códigos de erro posteriormente.

O código de erro também pode ser tanto o número do erro SQLSTATE (como vimos anteriormente), quanto o código de erro MySQL. Pode ser também do tipo mais genérico SQLWARNING para todos os erros com um SQLSTATE iniciando com 01, ou NOT FOUNDED para todos os erros com um SQLSTATE iniciando com 02, ou ainda SQLEXCEPTION para todos os demais. Na Listagem 6 temos uma procedure que age de maneira semelhante a nossos exemplos anteriores. Agora usamos o código de erro 1054 do MySQL, que é quase equivalente ao SQLSTATE 42S22 e também pulamos a condição.

 

mysql>

CREATE procedure sp8()

 BEGIN

  DECLARE EXIT HANDLER FOR 1054

   BEGIN

    SELECT 'error error whoop whoop';

    SELECT 'still handling the error';

   END;

  SELECT aha;

 END;|

 

mysql> CALL sp8()\G

*************************** 1. row ***************************

error error whoop whoop: error error whoop whoop

1 row in set (0.00 sec)

*************************** 1. row ***************************

still handling the error: still handling the error

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Listagem 6. Outra forma de utilizar um manipulador.

 

Os manipuladores podem ainda conter declarações ROLLBACK, ou fazer um registro em uma tabela de erros. Além disto, as declarações poderiam ser tão complexas quanto forem necessárias, incorporando todas as repetições e condições que vimos no artigo anterior.

Cursores

Os cursores constituem outra das características que foram altamente solicitadas e que agora são suportadas pelo MySQL 5. Para aqueles que estão familiarizados com outras implementações de SGBD, o MySQL 5 ainda não os suporta completamente. Na realidade, possui algumas restrições bastante severas. Cursores MySQL 5 são “não sensitivos” (portanto não devemos atualizar uma tabela enquanto estamos usando um cursor, caso contrário obteremos resultados imprevisíveis); são somente leitura (não podemos fazer atualizações usando a posição do cursor); e não rolantes (só podemos avançar para o próximo registro e não para traz e/ou para frente).

Em geral, cursores são usados para acessar um resultset que possa recuperar uma ou mais linhas. Eles também são usados para posicionar um ponteiro em uma linha específica e podem permitir atualizações para linhas com base na posição atual; embora como mencionado, o MySQL não suporte isto diretamente. Devemos fazer uso de alguns artifícios para obter este resultado.

O termo cursor é um acrônimo para CURrent Set Of Records (conjunto de registros corrente). Em nossos exemplos, usaremos a mesma tabela sp1, utilizada na primeira parte da série. Naquela ocasião, a tabela continha os registros descritos na Listagem 7. E, ainda na Listagem 7, utilizamos um cursor em uma stored procedure para manipular os dados da tabela.

 

mysql> SELECT * FROM sp1\G

*************************** 1. row ***************************

 id: 3

txt: 414243

*************************** 2. row ***************************

 id: 40

txt: 444546

2 rows in set (0.00 sec)

 

mysql>

CREATE PROCEDURE sp9 (OUT rx INT, OUT ry INT)

 BEGIN

  DECLARE x,y INT;

  DECLARE sp1_cursor CURSOR FOR SELECT id,txt FROM sp1;

  OPEN sp1_cursor;

   FETCH sp1_cursor INTO x,y;

  CLOSE sp1_cursor;

  SET rx = x;

  SET ry = y;

 END |

 

mysql> CALL sp9(@x,@y)\G

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x,@y\G

*************************** 1. row ***************************

@x: 3

@y: 414243

1 row in set (0.01 sec)

Listagem 7. Conteúdo da tabela sp1.

 

Aqui declaramos as variáveis (x e y e o cursor sp1_cursor). A ordem das declarações é importante: variáveis e condições antes, a seguir cursores e por fim os manipuladores.

Temos uma nova declaração: OPEN sp1_cursor, a qual ativa o cursor previamente declarado. A declaração FETCH faz toda a magia, retornando a próxima linha do resultset atual. Os resultados devem ser armazenados em algum lugar, sendo as duas variáveis x e y os recipientes das duas colunas retornadas pelo SELECT id,txt FROM sp1 que compõe o cursor. O cursor é então explicitamente fechado (embora o MySQL libere os recursos quando atingir o fim do bloco composto de declarações caso isto não seja explicitamente declarado), e os resultados são atribuídos às duas variáveis OUT. Ao chamar o procedimento e consultar as duas variáveis de sessão que recebem os resultados, podemos ver que as mesmas foram povoadas com a primeira linha da tabela sp1, tal como esperado.

Porém, retornar apenas uma linha não é de grande utilidade. Precisamos iterar pelo resultset inteiro e retornar todos os resultados. Podemos fazer isto usando um simples REPEAT UNTIL, como já tínhamos visto na primeira parte da série e ilustro novamente na Listagem 8.

 

mysql>

CREATE PROCEDURE sp10 (OUT rx INT, OUT ry INT)

 BEGIN

  DECLARE x,y,z INT;

  DECLARE sp1_cursor CURSOR FOR SELECT id,txt FROM sp1;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET z = 1;

  OPEN sp1_cursor;

  REPEAT

   FETCH sp1_cursor INTO x,y;

   UNTIL (z=1)

  END REPEAT;

  CLOSE sp1_cursor;

  SET rx = x;

  SET ry = y;

 END |

 

mysql> CALL sp10(@x,@y)|

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x,@y\G

*************************** 1. row ***************************

@x: 40

@y: 444546

1 row in set (0.00 sec)

Listagem 8. Utilização do REPEAT UNTIL para iterar em todo o cursor.

 

O manipulador é declarado para a condição NOT FOUND e atribui 1 à variável z, sendo justamente z=1 a condição testada pelo REPEAT UNTIL. Como mencionado anteriormente, a condição NOT FOUND inclui todos os erros com um SQLSTATE que começam com 02, um dos quais é o erro NO DATA TO FETCH. Isto só acontecerá quando recuperarmos todas as linhas da tabela. Como esperado, após chamar a procedure e consultar as variáveis de sessão, agora vemos a última linha de sp1table, pois na procedure, os dados de cada linha foram atribuídos a rx e ry, sobrescrevendo o conteúdo prévio.

O que são stored functions?

Se a programação de stored procedures for desconhecida para você, estará se perguntando qual a diferença entre uma stored procedure e uma stored function. Realmente não muita. Uma função sempre retorna um resultado, e pode ser chamada de dentro de uma declaração SQL da mesma forma que procedures SQL normais. Um parâmetro de função é o equivalente ao parâmetro IN das procedures, enquanto que as functions usam a palavra chave RETURN para especificar o que será retornado. Stored functions também têm algumas pequenas limitações quanto às declarações SQL, que as stored procedures não possuem.

 

Um exemplo de stored function

Vejamos um exemplo de uma stored function na Listagem 9.

 

mysql> DELIMITER |

mysql>

 CREATE FUNCTION WEIGHTED_AVERAGE (n1 INT, n2 INT, n3 INT, n4 INT)

  RETURNS INT

   DETERMINISTIC

    BEGIN

     DECLARE avg INT;

     SET avg = (n1+n2+n3*2+n4*4)/8;

     RETURN avg;

    END|

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT WEIGHTED_AVERAGE(70,65,65,60)\G

*************************** 1. row ***************************

WEIGHTED_AVERAGE(70,65,65,60): 63

1 row in set (0.00 sec)

Listagem 9. Um exemplo de Stored Function.

 

Como mencionado na primeira parte da série, declaramos o símbolo "|" como delimitador, de forma que o corpo das funções poderão usar o caractere “;”. Esta função retorna uma média ponderada, que poderia ser usada para determinar um resultado global para um determinado assunto. Também fazemos uso das declarações DECLARE (declaração de variáveis) e DETERMINISTIC (instruindo o MySQL que, dada a mesma entrada, a função sempre retornará o mesmo resultado).

 

Acessando tabelas em stored functions

Stored functions em versões anteriores do MySQL 5.0 (anteriores à 5.0.10) tinham uma capacidade muito limitada para fazer referência a tabelas. Isso limitou enormemente a sua utilização. Versões mais novas podem fazê-lo agora, mas ainda não podem fazer uso de declarações que retornam um resultset. Entretanto, podemos contornar isto usando SELECT INTO. No exemplo da Listagem 10 criamos uma tabela que nos permite armazenar quatro marcas e um nome. A seguir, definiremos uma nova função WEIGHTED_AVERAGE para fazer uso dos dados dinâmicos da tabela.

 

mysql> CREATE TABLE sfdata(mark1 INT,mark2 INT,mark3 INT,mark4 INT,name VARCHAR(50))

mysql> INSERT INTO sfdata VALUES(70,65,65,60,'Mark')|

mysql> INSERT INTO sfdata VALUES(95,94,75,50,'Pavlov')|

mysql>

 CREATE FUNCTION WEIGHTED_AVERAGE2 (v1 VARCHAR(50))

  RETURNS INT

  DETERMINISTIC

   BEGIN

    DECLARE i1,i2,i3,i4,avg INT;

    SELECT mark1,mark2,mark3,mark4 INTO i1,i2,i3,i4 FROM sfdata WHERE name=v1;

    SET avg = (i1+i2+i3*2+i4*4)/8;

    RETURN avg;

   END|

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT WEIGHTED_AVERAGE2('Pavlov') AS Pavlov, WEIGHTED_AVERAGE2('Mark') AS Mark\G

*************************** 1. row ***************************

Pavlov: 67

  Mark: 63

1 row in set (0.00 sec)

Listagem 10. Interação de Stored Function com uma tabela através do SELECT INTO.

 

Ao selecionarmos o conteúdo das linhas mark1 até mark4 para as variáveis declaradas (INTO), não teremos necessidade de retornar um resultset e poderemos usar tranquilamente os resultados dentro da função. Todos os comportamentos habituais e condições se aplicam dentro da function.

Por fim, temos na Listagem 11 a sintaxe completa das stored functions.

 

CREATE FUNCTION sf_name ([parameter1 [...]])

    RETURNS type

    [

     LANGUAGE SQL

     | [NOT] DETERMINISTIC

     | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

     | SQL SECURITY { DEFINER | INVOKER }

     | COMMENT 'string'

    ]

    SQL statements

Listagem 11. Sintaxe completa das stored functions.

 

Manipulando tabelas

Tendo contornado a limitação de acesso a tabelas de dentro de uma function (como vimos anteriormente), podemos usá-la para fazer mudanças em uma tabela. Os próximos dois exemplos (Listagem 12 e 13) não representam um uso ideal de functions (no formato em que estão, seria melhor que fossem stored procedures). Não estamos interessados no resultado que será retornado - só na manipulação dos dados - porém os exemplos demonstram um pouco do poder das funções (uma função é mais bem utilizada quando quisermos retornar um resultado). Com base nestes exemplos, poderemos criar nossas próprias stored functions em que INSERTs, SELECTs e UPDATEs complexos serão executadas, e no final, um único resultado será retornado. Faremos primeiro um INSERT de um registro na tabela sfdata (ver Listagem 12).

 

mysql>

 CREATE FUNCTION WEIGHTED_AVERAGE3 (n1 INT,n2 INT,n3 INT,n4 INT,v1 VARCHAR(50))

  RETURNS INT

  DETERMINISTIC

   BEGIN

    DECLARE i1,i2,i3,i4,avg INT;

    INSERT INTO sfdata VALUES(n1,n2,n3,n4,v1);

    RETURN 1;

   END|

Query OK, 0 rows affected (0.08 sec)

mysql> SELECT WEIGHTED_AVERAGE3(50,60,60,50,'Thoko')\G

*************************** 1. row ***************************

WEIGHTED_AVERAGE3(50,60,60,50,'Thoko'): 1

1 row in set (0.00 sec)

mysql> SELECT * FROM sfdata\G

*************************** 1. row ***************************

mark1: 70

mark2: 65

mark3: 65

mark4: 60

 name: Mark

*************************** 2. row ***************************

mark1: 95

mark2: 94

mark3: 75

mark4: 50

 name: Pavlov

*************************** 3. row ***************************

mark1: 90

mark2: NULL

mark3: 70

mark4: 60

 name: Isabelle

*************************** 4. row ***************************

mark1: 50

mark2: 60

mark3: 60

mark4: 50

 name: Thoko

4 rows in set (0.01 sec)

Listagem 12. Manipulando tabelas através de INSERTs dentro da função.

 

De modo semelhante, o exemplo da Listagem 13 faz um UPDATE de um registro com base nos parâmetros passados.

 

mysql> 

 CREATE FUNCTION WEIGHTED_AVERAGE_UPDATE (n1 INT,n2 INT,n3 INT,n4 INT,v1 VARCHAR(50))

  RETURNS INT

  DETERMINISTIC

   BEGIN

    DECLARE i1,i2,i3,i4,avg INT;

    UPDATE sfdata SET mark1=n1,mark2=n2,mark3=n3,mark4=n4 WHERE name=v1;

    RETURN 1;

   END|

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT WEIGHTED_AVERAGE_UPDATE(60,60,60,50,'Thoko')\G

*************************** 1. row ***************************

WEIGHTED_AVERAGE_UPDATE(60,60,60,50,'Thoko'): 1

1 row in set (0.00 sec)

mysql> SELECT * FROM sfdata\G

*************************** 1. row ***************************

mark1: 70

mark2: 65

mark3: 65

mark4: 60

 name: Mark

*************************** 2. row ***************************

mark1: 95

mark2: 94

mark3: 75

mark4: 50

 name: Pavlov

*************************** 3. row ***************************

mark1: 90

mark2: NULL

mark3: 70

mark4: 60

 name: Isabelle

*************************** 4. row ***************************

mark1: 60

mark2: 60

mark3: 60

mark4: 50

 name: Thoko

5 rows in set (0.01 sec)

Listagem 13. Manipulando tabelas através de UPDATEs dentro da função.

 

Informações a respeito de stored functions existentes

Assim como ocorre com stored procedures, existem diferentes maneiras de se obter metadados sobre stored functions existentes. Temos SHOW CREATE FUNCTION e SHOW FUNCTION STATUS. A primeira retorna a declaração CREATE aplicada à função fornecida, enquanto que a segunda retorna os metadados relativos a todas as functions existentes (ver Listagem 14).

 

mysql> SHOW CREATE FUNCTION WEIGHTED_AVERAGE\G

*************************** 1. row ***************************

       Function: WEIGHTED_AVERAGE

       sql_mode:

Create Function: CREATE FUNCTION 'WEIGHTED_AVERAGE'(n1 INT, n2 INT, n3 INT, n4 INT)

  RETURNS int(11)

    DETERMINISTIC

BEGIN

   DECLARE avg INT;

   SET avg = (n1+n2+n3*2+n4*4)/8;

   RETURN avg;

  END

1 row in set (0.01 sec)

mysql> SHOW FUNCTION STATUS\G

*************************** 1. row ***************************

           Db: test

         Name: WEIGHTED_AVERAGE

         Type: FUNCTION

      Definer: root@localhost

     Modified: 2005-12-07 13:21:37

      Created: 2005-12-07 13:21:37

Security_type: DEFINER

      Comment:

*************************** 2. row ***************************

           Db: test

         Name: WEIGHTED_AVERAGE2

         Type: FUNCTION

      Definer: root@localhost

     Modified: 2005-12-07 13:41:07

      Created: 2005-12-07 13:41:07

Security_type: DEFINER

      Comment:

*************************** 3. row ***************************

           Db: test

         Name: WEIGHTED_AVERAGE3

         Type: FUNCTION

      Definer: root@localhost

     Modified: 2005-12-07 15:51:16

      Created: 2005-12-07 15:51:16

Security_type: DEFINER

      Comment:

*************************** 4. row ***************************

           Db: test

         Name: WEIGHTED_AVERAGE_UPDATE

         Type: FUNCTION

      Definer: root@localhost

     Modified: 2005-12-07 16:03:26

      Created: 2005-12-07 16:03:26

Security_type: DEFINER

      Comment:

4 rows in set (0.00 sec)

Listagem 14. Visualização dos metadados e status da função.

 

Outro modo de obtermos as mesmas informações é consultando a tabela mysql.proc. Desde a versão 5 do MySQL, a tabela mysql.proc contém metadados a respeito de stored procedures e stored functions (Listagem 15).

 

mysql> SELECT * FROM mysql.proc\G

*************************** 1. row ***************************

              db: test

            name: WEIGHTED_AVERAGE

            type: FUNCTION

   specific_name: WEIGHTED_AVERAGE

        language: SQL

 sql_data_access: CONTAINS_SQL

is_deterministic: YES

   security_type: DEFINER

      param_list: n1 INT, n2 INT, n3 INT, n4 INT

         returns: int(11)

            body: BEGIN

   DECLARE avg INT;

   SET avg = (n1+n2+n3*2+n4*4)/8;

   RETURN avg;

  END

         definer: root@localhost

         created: 2005-12-07 13:21:37

        modified: 2005-12-07 13:21:37

        sql_mode:

         comment:

1 row in set (0.00 sec)

Listagem 15. Visualização dos metadados e status da função através de consulta à tabela mysql.proc.

 

Note que a consulta à tabela mysql.proc retorna dados mais completos que qualquer um dos dois métodos anteriores (de fato, retorna o conjunto de ambos os métodos).

Porém, quem estiver familiarizado com outros SGBDs aderentes ao padrão ANSI, pode não se sentir confortável com estes métodos específicos do MySQL. O modo padrão consiste em consultar o INFORMATION_SCHEMA. É um modo altamente flexível de obtermos o que desejamos, mas pode ser um pouco exagerado considerando os métodos SHOW mais simples disponibilizados pelo MySQL.. Por enquanto, basta dizermos que podemos consultar as INFORMATION_SCHEMA.ROUTINES para obtermos metadados semelhantes aos apresentados na Listagem 15 (ver Listagem 16).

 

mysql> SELECT * FROM INFORMATION_SCHEMA.ROUTINES\G

*************************** 1. row ***************************

     SPECIFIC_NAME: WEIGHTED_AVERAGE

   ROUTINE_CATALOG: NULL

    ROUTINE_SCHEMA: test

      ROUTINE_NAME: WEIGHTED_AVERAGE

      ROUTINE_TYPE: FUNCTION

    DTD_IDENTIFIER: int(11)

      ROUTINE_BODY: SQL

ROUTINE_DEFINITION: BEGIN

   DECLARE avg INT;

   SET avg = (n1+n2+n3*2+n4*4)/8;

   RETURN avg;

  END

     EXTERNAL_NAME: NULL

 EXTERNAL_LANGUAGE: NULL

   PARAMETER_STYLE: SQL

  IS_DETERMINISTIC: YES

   SQL_DATA_ACCESS: CONTAINS SQL

          SQL_PATH: NULL

     SECURITY_TYPE: DEFINER

           CREATED: 2005-12-07 13:21:37

      LAST_ALTERED: 2005-12-07 13:21:37

          SQL_MODE:

   ROUTINE_COMMENT:

           DEFINER: root@localhost

1 row in set (0.00 sec)

Listagem 16. Consultando INFORMATION_SCHEMA.ROUTINES.

Conclusão

Stored procedures e stored functions abrem um mundo completamente novo para o desenvolvedor MySQL. Embora a implementação no MySQL 5.0 ainda esteja crua (o MySQL 5.1 está agora na sua versão alfa, e continua avançando, assim como o MySQL 6.0), a maior parte das necessidades já estão disponíveis. Nem todas as minhas aplicações estão rodando em MySQL 5, porém a vontade de migra-las está se tornando mais forte, toda vez que desenvolvo e tenho que lidar novamente com lógica não desejada. Recentemente, me deparei com uma reclamação de um pobre desenvolvedor que lamentava a falta de funcionalidades no MySQL 3.23. Atualizar sistemas legados não é nada divertido, mas aqueles que são afortunados o bastante para começar com um quadro negro vazio, desfrutem o novo mundo das stored procedures e stored functions do MySQL 5!

 

Ian Gilfillan é especialista em MySQL.