Cursor MySQL – 03 – Acervo Lima Tutorials

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 SELECTinstruçã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 DECLAREinstruçã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 SELECTinstrução.

A seguir, abra o cursor usando a OPENinstrução:

OPEN cursor_name;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A OPENinstrução inicializa o conjunto de resultados do cursor; portanto, você deve chamar a OPENinstruçã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 FETCHinstruçã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 CLOSEinstruçã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 FOUNDmanipulador para gerenciar a situação quando o cursor não consegue encontrar nenhuma linha.

Cada vez que você chama a FETCHinstruçã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 FOUNDmanipulador, 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 employeestabela no banco de dados de exemplo :

O exemplo a seguir ilustra como usar um cursor para iterar todas as linhas da employeestabela 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 donee inicialize seu valor como falsee email_addresse 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 emailna employeestabela:

DEClARE cur CURSOR FOR SELECT email FROM employees;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Terceiro, abra o cursor usando a OPENinstruçã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 CLOSEinstrução:

CLOSE email_cursor;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O seguinte testa o createEmailListprocedimento armazenado:

CALL create_email_list(@email_list); 
SELECT @email_list\GLinguagem 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 )