MySQL 存储过程 - 游标

MySQL 游标简介

要处理存储过程中的结果集,需要使用游标。游标允许您迭代查询返回的一组行,并相应地处理每一行。

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

只读:您无法通过游标更新基础表中的数据。

不可滚动:您只能按SELECT语句确定的顺序获取行。您无法以相反的顺序获取行。此外,您不能跳过行或跳转到结果集中的特定行。

使用 MySQL 游标

首先,您必须使用以下DECLARE语句声明游标:

DECLARE cursor_name CURSOR FOR SELECT_statement; 

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

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

OPEN cursor_name; 

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

FETCH cursor_name INTO variables list; 

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

最后,调用CLOSE语句以停用游标并释放与其关联的内存,如下所示:

CLOSE cursor_name; 

当光标不再使用时,您应关闭它。

使用MySQL游标时,还必须声明NOT FOUND处理程序以在光标找不到任何行时处理情况。因为每次调用FETCH语句时,游标都会尝试读取结果集中的下一行。当光标到达结果集的末尾时,它将无法获取数据,并且会引发一个条件处理程序用于处理此情况。

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

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1; 

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

游标使用示例

遍历 classes 数据表,计算 id 总和:

DROP PROCEDURE IF EXISTS test;
DElIMITER $$
CREATE PROCEDURE test()
    BEGIN
        DECLARE finished INTEGER DEFAULT 0;
        DECLARE classid INT DEFAULT 0;
        DECLARE class_id_sum INT DEFAULT 0;
        DECLARE class_cursor CURSOR FOR SELECT class_id FROM classes;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
        OPEN class_cursor;
        get_id : LOOP 
            FETCH class_cursor INTO classid;
            IF finished = 1 THEN
                LEAVE get_id;
            END IF;
            set class_id_sum = class_id_sum + classid;
        END LOOP get_id;
        CLOSE class_cursor; 
        SELECT class_id_sum;
    END $$
DElIMITER;

CALL test();