mysql存储过程之游标（DECLARE）原理与用法详解

本文实例讲述了mysql存储过程之游标（declare）原理与用法。分享给大家供大家参考，具体如下：

我们在处理存储过程中的结果集时，可以使用游标，因为游标允许我们迭代查询返回的一组行，并相应地处理每行。mysql的游标为只读，不可滚动和敏感三种模式，我们来看下：

• 只读：无法通过光标更新基础表中的数据。
• 不可滚动：只能按照select语句确定的顺序获取行。不能以相反的顺序获取行。 此外，不能跳过行或跳转到结果集中的特定行。
• 敏感：有两种游标：敏感游标和不敏感游标。敏感游标指向实际数据，不敏感游标使用数据的临时副本。敏感游标比一个不敏感的游标执行得更快，因为它不需要临时拷贝数据。但是，对其他连接的数据所做的任何更改都将影响由敏感游标使用的数据，因此，如果不更新敏感游标所使用的数据，则更安全。 mysql游标是敏感的。

我们可以在存储过程，存储函数和触发器中使用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是一个变量，指示光标到达结果集的结尾。请注意，处理程序声明必须出现在存储过程中的变量和游标声明之后。我们再来看下mysql游标的运行原理图：

咱们接下来，就要开发一个存储过程用来获取employees表中所有员工的电子邮件列表。我们先来声明一些变量，一个用于循环员工电子邮件的游标和一个not found处理程序：

declare finished integer default 0;
declare email varchar(255) default "";
-- declare cursor for employee email
declare email_cursor cursor for
 select email from employees;
-- declare not found handler
declare continue handler
for not found set finished = 1;
接下来，使用open语句打开email_cursor：
open email_cursor;

然后，迭代电子邮件列表，并使用分隔符(;)连接每个电子邮件：

get_email: loop
 fetch email_cursor into v_email;
 if v_finished = 1 then
 leave get_email;
 end if;
 -- build email list
 set email_list = concat(v_email,";",email_list);
end loop get_email;

之后，我们在循环中，使用v_finished变量来检查列表中是否有任何电子邮件来终止循环，完事呢，使用close语句关闭游标：

close email_cursor;

我们来看下build_email_list存储过程所有代码：

delimiter $$
create procedure build_email_list (inout email_list varchar(4000))
begin
 declare v_finished integer default 0;
    declare v_email varchar(100) default "";
 -- declare cursor for employee email
 declare email_cursor cursor for
 select email from employees;
 -- declare not found handler
 declare continue handler
    for not found set v_finished = 1;
 open email_cursor;
 get_email: loop
 fetch email_cursor into v_email;
 if v_finished = 1 then
 leave get_email;
 end if;
 -- build email list
 set email_list = concat(v_email,";",email_list);
 end loop get_email;
 close email_cursor;
end$$
delimiter ;

我们来使用以下脚本测试build_email_list存储过程：

set @email_list = "";
call build_email_list(@email_list);
select @email_list;

至于结果，咱就不赘述了哈。