Resumo : neste tutorial, você aprenderá como usar o cursor MySQL em procedimentos armazenados para iterar por meio de um conjunto de resultados retornado por uma SELECT
instrução.
Introdução ao cursor MySQL
No MySQL, um cursor é um objeto de banco de dados usado para iterar o resultado de uma instrução SELECT.
Normalmente, você usa cursores em procedimentos armazenados, gatilhos e funções onde precisa processar linhas individuais retornadas por uma consulta, uma de cada vez .
Aqui está a sintaxe básica de um cursor:
-- declare a cursor
DECLARE cursor_name CURSOR FOR
SELECT column1, column2
FROM your_table
WHERE your_condition;
-- open the cursor
OPEN cursor_name;
FETCH cursor_name INTO variable1, variable2;
-- process the data
-- close the cursor
CLOSE cursor_name;
Linguagem de código: PHP ( php )
Como funciona:
Primeiro, declare um cursor usando a DECLARE
instrução:
DECLARE cursor_name CURSOR FOR
SELECT column1, column2
FROM your_table
WHERE your_condition;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A declaração do cursor deve vir depois de qualquer declaração de variável . Se você declarar um cursor antes das declarações de variáveis, o MySQL irá emitir um erro. Além disso, um cursor deve sempre estar associado a uma SELECT
instrução.
A seguir, abra o cursor usando a OPEN
instrução:
OPEN cursor_name;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A OPEN
instrução inicializa o conjunto de resultados do cursor; portanto, você deve chamar a OPEN
instrução antes de buscar linhas do conjunto de resultados.
Em seguida, recupere a próxima linha apontada pelo cursor e avance o cursor para a linha subsequente no conjunto de resultados usando a FETCH
instrução:
FETCH cursor_name INTO variable1, variable2;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Depois disso, verifique se há alguma linha disponível antes de buscá-la.
Por fim, desative o cursor e libere a memória associada a ele usando a CLOSE
instrução:
CLOSE cursor_name;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
É uma boa prática sempre fechar um cursor quando ele não estiver mais em uso.
Ao trabalhar com o cursor MySQL, você também deve declarar um NOT FOUND
manipulador para gerenciar a situação quando o cursor não consegue encontrar nenhuma linha.
Cada vez que você chama a FETCH
instrução, o cursor tenta ler a próxima linha no conjunto de resultados. Quando o cursor atingir o final do conjunto de resultados, ele não será capaz de recuperar os dados e uma condição será levantada. O manipulador é usado para lidar com essa condição.
Para declarar um NOT FOUND
manipulador, você usa a seguinte sintaxe:
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
The finished
é uma variável para indicar que o cursor atingiu o final do conjunto de resultados.
Observe que a declaração do manipulador deve aparecer após as declarações de variável e cursor nos procedimentos armazenados.
O diagrama a seguir ilustra como funciona o cursor MySQL.
Exemplo de cursor MySQL
Desenvolveremos um procedimento armazenado que cria uma lista de e-mail de todos os funcionários da employees
tabela no banco de dados de exemplo :
O exemplo a seguir ilustra como usar um cursor para iterar todas as linhas da employees
tabela e concatenar o email em uma string:
DELIMITER $$
CREATE PROCEDURE create_email_list (
INOUT email_list TEXT
)
BEGIN
DECLARE done BOOL DEFAULT false;
DECLARE email_address VARCHAR(100) DEFAULT "";
-- declare cursor for employee email
DECLARE cur CURSOR FOR SELECT email FROM employees;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET done = true;
-- open the cursor
OPEN cur;
SET email_list = '';
process_email: LOOP
FETCH cur INTO email_address;
IF done = true THEN
LEAVE process_email;
END IF;
-- concatenate the email into the emailList
SET email_list = CONCAT(email_address,";",email_list);
END LOOP;
-- close the cursor
CLOSE cur;
END$$
DELIMITER ;
Linguagem de código: PHP ( php )
Como funciona.
Primeiro, declare a variável done
e inicialize seu valor como false
e email_address
e inicialize seu valor como ''
.
DECLARE done BOOL DEFAULT false;
DECLARE email_address VARCHAR(100) DEFAULT "";
Linguagem de código: PHP ( php )
Segundo, declare um cursor que irá buscar email
na employees
tabela:
DEClARE cur CURSOR FOR SELECT email FROM employees;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Terceiro, abra o cursor usando a OPEN
instrução:
OPEN cur;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Quarto, concatene cada e-mail na variável mail_list usando uma instrução de loop, separando os endereços de e-mail por ponto e vírgula:
process_email: LOOP
FETCH cur INTO email_address;
IF done = true THEN
LEAVE process_email;
END IF;
-- concatenate the email into the emailList
SET email_list = CONCAT(email_address,";",email_list);
END LOOP;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Finalmente, feche o cursor usando a CLOSE
instrução:
CLOSE email_cursor;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
O seguinte testa o createEmailList
procedimento armazenado:
CALL create_email_list(@email_list);
SELECT @email_list\G
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
*************************** 1. row ***************************
@email_list: [email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];
1 row in set (0.00 sec)
Linguagem de código: JavaScript ( javascript )
Resumo
- Use um cursor MySQL para processar um conjunto de resultados linha por linha individualmente.