mysql 游标循环体中使用select into赋值可能导致提前退出

  Mysql存储过程的游标循环体中使用“select  【某个字段】into 【某个变量】 from 【某个表】”语句进行 赋值， 如果 select 出来没有记录，则会导致游标提前退出（即使游标没有遍历完也会退出）。

表结构如下：

1.部门表以及相关数据

[sql]  view plain  copy

1. <pre name="code" class="sql">create table department (  
2.     id int unsigned not null auto_increment,  
3.     name char(20) not null,  
4.     primary key id  
5. )engine=innobd default charset=utf8;  
6.   
7. insert department(id,name) values(1,'技术部'),(2,'行政部'),(3,'人力部'),(4,'运营部'),(5,'财务部'),(6,'法务部'),(7,'市场部'),(8,'商务部'),(9,'客服部');  

2.员工表即相关数据

[sql]  view plain  copy

1. <span style="font-family: Arial, Helvetica, sans-serif;"></span><pre name="code" class="sql">create table employee (  
2.     id int unsigned not null auto_increment,  
3.     name char(20) not null,  
4.     entry_date date not null,  
5.     department_id int unsigned not null,  
6.     contrainst fk_employee_department foreign key(department_id) references    department(id) on delete on action on update on action  
7. ) engine=innobd default charset=utf8;  
8.   
9. insert employee(id, name, department_id) values(1, '张三', '2013-05-10',1),(2, '李四','2013-06-10',1),(3, '赵六','2013-05-10',2),(4, '薛七','2015-05-10',3),(5, '王麻子','2010-05-10',4),(6, '小六子','2013-08-10',5),(7, '赵云','2013-06-10',5),(8, '张飞','2013-10-10',5),(9, '关羽','2015-05-10',5),(10, '郭芙蓉','2013-01-10',9),(11, '凤姐','2012-05-10',9),(12, '芙蓉街','2013-01-10',9),(13, '魏延','2014-12-10',9),(14, '周瑜','2012-05-18',9),(15, '兵丁1','2014-03-10',9),(16, '王五','2016-01-10',2);  

比如有一个需求：部门表中要增加最近一次员工入职时间，并要求从员工表中找出每个部门中最近入职时间的员工入职时间设置到部门表中，如果有部门没有员工的话，则不管。

下面用存储过程实现，大概思路：先定义一个所有部门的游标，然后遍历此游标，根据游标中的部门id去员工表中查找最近一次入职的员工时间，存在的话，就更新部门记录。

代码如下：

[sql]  view plain  copy

1. -- 部门表增加最近一次的员工入职时间字段  

[sql]  view plain  copy

1. alter table department  
2. add column last_employee_entry_date date default null after name;  

[sql]  view plain  copy

1. -- 找到每个部门最近一次员工的入职时间，并更行部门表，如果部门不存在员工，则不更新  
2. drop procedure pro_add_department_column if exists;  
3. delimiter //  
4. create procedure pro_add_department_column()  
5. begin  
6.     declare var_department_id int;  
7.     declare var_last_entry_date date;  
8.   
9.     declare no_more int default 0;  
10.   
11.     declare cur_department cursor for  
12.         select id from department;  
13.           
14.     declare continue handler for not found set no_more=1;  
15.   
16.     open cur_department  
17.   
18.     department_loop:LOOP  
19.         fetch cur_department into var_department_id;  
20.         if no_more = 1 then  
21.             leave cur_department;  
22.         end if;  
23.   
24.         select entry_date into var_last_entry_date from employee   
25.             where department_id = var_department_id order by entry_date desc limit 0,1;  
26.           
27.         if var_last_entry_date is not null then   
28.             update department set last_employee_entry_date = var_last_entry_date;  
29.         end if;  
30.           
31.     end loop;  
32.     close cur_department;  
33. end; //  
34.   
35. delimiter ;   
36.   
37. call drop procedure pro_add_department_column;  
38. drop procedure pro_add_department_column;  

在以上的代码中，如果有某个部门没有员工的话，游标就会直接退出，

原因是这个语句：select entry_date into var_last_entry_date from employee 
where department_id = var_department_id order by entry_date desc limit 0,1;

在游标使用select into语句进行赋值，如果select语句没有找到记录，则游标会直接退出！

所以为了防止遍历游标时，由于select into 导致游标提前退出，可以通过以下几种方法：

1. 采用set 赋值语句，比如上面的语句可以改成 set var_last_entry_date = (select entry_date from employee 
where department_id = var_department_id order by entry_date desc limit 0,1);

2.采用select count 语句，比如 select if (count(entry_date) > 0, entry_date, '') into var_last_entry_date from employee 
where department_id = var_department_id order by entry_date desc limit 0,1;

采用此方法的话，下面的判断条件if var_last_entry_date is not null 就需要与空字符串进行比较了，即改成if var_last_entry_date != '' then。

3. 因为当select· into赋值结果为null的时候，会自动修改循环触底的标志，所以手动在循环底部增加  set no_more = 0 这样也可以避免提前退出循环。

文章转自：https://blog.csdn.net/rj042/article/details/51263287