Procedimentos Armazenados e Variáveis do Usuário (User Variables)

Neste artigo, será apresentado ao usuário como trabalhar com variáveis criados por ele em meio à PROCEDURES, TRIGGERS e CURSORES ou mesmo, em meio a um SELECT em linha de comando. Este recurso é muito valioso quando é necessário guardarmos um determinado valor para usar em um processamento posterior.

Após ler este artigo você saberá como:

• Definir o conceito de variáveis do usuário;
• Criar variáveis para armazenar informações;
• Definir as propriedades das variáveis de usuário;
• Analisar o escopo das variáveis;
• Utilizar variáveis definidas pelo usuário em meio à procedimentos;

Introdução

Como já vimos em outros artigos, os procedimentos armazenados ou Stored Procedures são programas armazenados no servidor de banco de dados, no caso o MySQL, para que ao serem chamados executem alguma lógica, retornando ou não algum resultado. Estes aceitam parâmetros que por sua vez podem ser de entrada, saída ou entrada e saída. Mas, em alguns casos, é necessário armazenar alguma informação em variáveis ou mesmo executar uma conferência por exemplo de uma instrução INSERT. Digamos que desejamos efetuar um cálculo com base em um valor armazenado no banco de dados. Facilmente podemos criar ou definir uma variável em meio ao processamento e armazenar o valor de uma coluna de uma tabela em uma dessas variáveis definidas pela usuário.

Definição

Uma variável que é definida pelo usuário, também conhecida como user variables, é escrita precedida pelo símbolo @ (arroba) e pode receber através da declaração SET com os valores do tipo inteiro (INT), real (FLOAT), string ou um valor NULL, que representa um fragmento de dado sem definição e sem valor. Não vamos falar agora sobre NULL pois seria necessário escrever um outro artigo. Variáveis do usuário são diferentes de variáveis locais.

Podemos atribuir um valor a uma variável definida pelo usuário utilizando os sinais igual (=) ou o sinal de igual com notação Pascal (:=). Qualquer dos dois poderá ser usado levando em conta o contexto ao estamos atribuindo tal valor. Por exemplo, se somente queremos inicializar uma variável atribuindo a esta um valor para ser utilizado em meio a um processamento ou mesmo guardar um valor de um campo de uma tabela para uma utilização futura, podemos fazer como mostra a Figura 01.

Uma observação importante a fazer é que, caso venhamos a utilizar uma variável do usuário que não tenha sido inicializada e nem tenha recebido anteriormente um valor, o processamento que utilizar-se desta variável será prejudicado pois ela terá um valor NULL e uma comparação de qualquer valor, numérico ou string com NULL é igual a NULL, como mostra a Figura 02.

Naturalmente, caso um valor tivesse sido atribuído à @var_3 tivesse recebido um valor antes da comparação exibida, retornaria 0 para valores diferentes e 1 para valores iguais. Não podemos esquecer que NULL pode nos trazer resultados indesejados em meio a um processamento, poderá comprometer todo o trabalho.

Outro contexto o qual teremos que utilizar a notação de igualdade com padrões de Pascal é quando queremos atribuir um valor vindo de um SELECT a uma variável do usuário, como segue no seguinte exemplo:

Variáveis do usuário são específicas de uma conexão, não estando disponíveis ou visíveis para outras conexões. No MySQL 5.0++, variáveis do usuário não são Case Sensitive, ou seja, @VAR e @var são a mesma coisa.

Variáveis do Usuário + Procedimentos Armazenados

Agora que já sabemos lidar com a parte conceitual das variáveis do usuário, podemos trabalhar com estas em meio aos procedimentos armazenados, interagido com servidor de bancos de dados MySQL através da linha de comando.

Primeiramente, vamos criar um exemplo para efetuar a inserção de um registro, um valor então será enviado ao procedimento que o receberá como parâmetro de entrada (IN). Após a inserção do registro, utilizaremos uma estrutura condicional para checar o retorno da função LAST_INSERT_ID() que retornará seu valor em uma variável do usuário que criaremos internamente ao procedimento. Caso o valor retornado por esta função seja maior que zero, o cadastro foi efetuado com sucesso. A Figura 04 ilustra esta situação.

Com o procedimento compilado, podemos testá-lo, primeiro passando uma string vazia, como mostra a Figura 05:

Na Figura 06, o cadastro sendo efetuado com sucesso:

Este procedimento, ainda de forma básica, pode ser utilizado com UPDATE e DELETE também. Uma boa economia de código seria montar um procedimento para DELETE que receba como parâmetro o nome da tabela e o identificado do registro a excluir, utilizando também a mesma dinâmica na criação da variável do usuário para receber o último identificador inserido na coluna auto_increment daquela tabela e conformar a operação. O procedimento poderá falhar permanentemente caso a coluna banco_id da tabela tbl_banco seja alterada e seja configurada para existir sem a propriedade auto_increment.

Suponhamos que em determinado momento, temos o seguinte esquema de banco de dados:

O objetivo principal é ter um procedimento armazenado que faça o cadastro de uma cidade baseado em um estado. Receberemos como parâmetros a sigla do estado, o nome da nova cidade, após a verificação de consistência dos valores, selecionaremos o estado_id na tabela tbl_estado, armazenando este em uma variável do usuário e em seguida fazemos a inserção da cidade. Caso o estado não exista, devolvemos ao usuário uma mensagem informando que o estado desejado não foi localizado.

Após criar as tabelas, foram feitas as seguintes inserções de dados:

Em seguida, criamos o procedimento que executará a busca do estado com a sigla e o cadastro de uma nova cidade. O procedimento é apresentado na Figura 07.

Caso se trabalhe em um sistema, o desenvolvedor poderá facilmente retornar as mensagens do procedimento na interface do sistema ou site. Vamos em seguida, mostrar o teste do procedimento, informando uma sigla de um estado e uma cidade válida, mostrado na Figura 08.

Conclusão

Neste artigo verificamos com detalhes como iniciar, setar valor e utilizar variáveis do usuário em meio à procedimentos armazenados, mais especificamente Stored Procedures. No próximo artigo, apresentaremos uma abordagem específica com TRIGGERS no MySQL, que também é um tipo de procedimento armazenado e também veremos como declarar variáveis com escopo local.