MySQL 游标示例

摘要：在本教程中，您将学习如何在存储过程中使用MySQL 游标来迭代SELECT语句返回的结果集。

MySQL游标简介

要处理存储过程内的结果集，可以使用游标。游标允许您迭代查询返回的一组行并单独处理每一行。

MySQL 游标是只读的、不可滚动的且不敏感。

只读：不能通过游标更新底层表中的数据。
不可滚动：您只能按照SELECT语句确定的顺序获取行。您无法以相反的顺序获取行。此外，您无法跳过行或跳转到结果集中的特定行。
不敏感：有两种游标：不敏感游标和不敏感游标。不敏感游标指向实际数据，而不敏感游标则使用数据的临时副本。敏感游标比不敏感游标执行得更快，因为它不必创建数据的临时副本。但是，对来自其他连接的数据进行的任何更改都会影响敏感游标正在使用的数据，因此，如果不更新敏感游标正在使用的数据会更安全。 MySQL 游标不敏感。

使用 MySQL 游标

首先，使用DECLARE语句声明游标：

DECLARE cursor_name CURSOR FOR SELECT_statement;Code language: SQL (Structured Query Language) (sql)

游标声明必须位于任何变量声明之后。如果在变量声明之前声明游标，MySQL 将发出错误。游标必须始终与SELECT语句关联。

接下来，使用OPEN语句打开游标。 OPEN语句初始化游标的结果集，因此，在从结果集中获取行之前必须调用OPEN语句。

OPEN cursor_name;Code language: SQL (Structured Query Language) (sql)

然后，使用FETCH语句检索游标指向的下一行，并将游标移动到结果集中的下一行。

FETCH cursor_name INTO variables list;Code language: SQL (Structured Query Language) (sql)

之后，在获取之前检查是否有可用的行。

最后，使用CLOSE语句停用游标并释放与其关联的内存：

CLOSE cursor_name;Code language: SQL (Structured Query Language) (sql)

当游标不再使用时，最好将其关闭。

使用 MySQL 游标时，还必须声明NOT FOUND处理程序来处理游标找不到任何行的情况。

因为每次调用FETCH语句时，游标都会尝试读取结果集中的下一行。当游标到达结果集末尾时，它将无法获取数据，并且会引发一个条件。处理程序用于处理这种情况。

要声明NOT FOUND处理程序，请使用以下语法：

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;Code language: SQL (Structured Query Language) (sql)

finished是一个变量，指示光标已到达结果集的末尾。请注意，处理程序声明必须出现在存储过程内的变量和游标声明之后。

下图说明了 MySQL 游标的工作原理。

MySQL 游标示例

我们将开发一个存储过程，用于在示例数据库的employees表中创建所有员工的电子邮件列表。

首先，声明一些变量、一个用于循环查看员工电子邮件的游标和一个NOT FOUND处理程序：

	DECLARE finished INTEGER DEFAULT 0;
	DECLARE emailAddress varchar(100) DEFAULT "";

	-- declare cursor for employee email
	DEClARE curEmail 
		CURSOR FOR 
			SELECT email FROM employees;

	-- declare NOT FOUND handler
	DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;
Code language: SQL (Structured Query Language) (sql)

接下来，使用OPEN语句打开游标：

OPEN curEmail;Code language: SQL (Structured Query Language) (sql)

然后，迭代电子邮件列表，并将所有电子邮件连接起来，每封电子邮件之间用分号 (;) 分隔：

       getEmail: LOOP
		FETCH curEmail INTO emailAddress;
		IF finished = 1 THEN 
			LEAVE getEmail;
		END IF;
		-- build email list
		SET emailList = CONCAT(emailAddress,";",emailList);
	END LOOP getEmail;Code language: SQL (Structured Query Language) (sql)

之后，在循环内部，我们使用finished变量来检查列表中是否有电子邮件来终止循环。

最后，使用CLOSE语句关闭游标：

CLOSE email_cursor;Code language: SQL (Structured Query Language) (sql)

createEmailList存储过程如下：

DELIMITER $$
CREATE PROCEDURE createEmailList (
	INOUT emailList varchar(4000)
)
BEGIN
	DECLARE finished INTEGER DEFAULT 0;
	DECLARE emailAddress varchar(100) DEFAULT "";

	-- declare cursor for employee email
	DEClARE curEmail 
		CURSOR FOR 
			SELECT email FROM employees;

	-- declare NOT FOUND handler
	DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;

	OPEN curEmail;

	getEmail: LOOP
		FETCH curEmail INTO emailAddress;
		IF finished = 1 THEN 
			LEAVE getEmail;
		END IF;
		-- build email list
		SET emailList = CONCAT(emailAddress,";",emailList);
	END LOOP getEmail;
	CLOSE curEmail;

END$$
DELIMITER ;Code language: SQL (Structured Query Language) (sql)

您可以使用以下脚本测试createEmailList存储过程：

SET @emailList = ""; 
CALL createEmailList(@emailList); 
SELECT @emailList;Code language: SQL (Structured Query Language) (sql)

在本教程中，我们向您展示了如何使用 MySQL 游标来迭代结果集并相应地处理每一行。

本教程有帮助吗？