Mysql存储过程的游标循环体中使用“select 【某个字段】into 【某个变量】 from 【某个表】”语句进行 赋值, 如果 select 出来没有记录,则会导致游标提前退出(即使游标没有遍历完也会退出)。
表结构如下:
1.部门表以及相关数据
<pre name="code" class="sql">create table department (
id int unsigned not null auto_increment,
name char(20) not null,
primary key id
)engine=innobd default charset=utf8;
insert department(id,name) values(1,'技术部'),(2,'行政部'),(3,'人力部'),(4,'运营部'),(5,'财务部'),(6,'法务部'),(7,'市场部'),(8,'商务部'),(9,'客服部');
2.员工表即相关数据
<span style="font-family: Arial, Helvetica, sans-serif;"></span><pre name="code" class="sql">create table employee (
id int unsigned not null auto_increment,
name char(20) not null,
entry_date date not null,
department_id int unsigned not null,
contrainst fk_employee_department foreign key(department_id) references department(id) on delete on action on update on action
) engine=innobd default charset=utf8;
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去员工表中查找最近一次入职的员工时间,存在的话,就更新部门记录。
代码如下:
-- 部门表增加最近一次的员工入职时间字段
alter table department
add column last_employee_entry_date date default null after name;
-- 找到每个部门最近一次员工的入职时间,并更行部门表,如果部门不存在员工,则不更新
drop procedure pro_add_department_column if exists;
delimiter //
create procedure pro_add_department_column()
begin
declare var_department_id int;
declare var_last_entry_date date;
declare no_more int default 0;
declare cur_department cursor for
select id from department;
declare continue handler for not found set no_more=1;
open cur_department
department_loop:LOOP
fetch cur_department into var_department_id;
if no_more = 1 then
leave cur_department;
end if;
select 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 then
update department set last_employee_entry_date = var_last_entry_date;
end if;
end loop;
close cur_department;
end; //
delimiter ;
call drop procedure pro_add_department_column;
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 这样也可以避免提前退出循环。