今天因项目需求要写存储过程,对查询的记录进行统计并生成报表返回给调用者。在编写过程中碰到一些问题记录于此。
整体思路如下。
- 拿到候选记录。
- 进行统计。
- 生成结果返回。
步骤1通过获取候选记录集合并创建游标即可。
步骤2需要对结果集进行遍历。
步骤3需要创建临时表,并将2遍历时的数据插入最后返回。
遍历最关键的是要判断什么时候循环结束。这里用到了
DECLARE … HANDLER[1],通过这条语句可以在某条件触发时候完成某个操作。
declare flag boolean DEFAULT true;
declare continue handler for not found set flag = false;
上面的代码声明了变量flag,默认值为true。下面创建了一个handler,当游标遍历完结果集时flag会被设置为false,因此最后用来最为循环条件。
通过遍历可以访问每条记录,接着需要将数据插入到一张临时表中,返回结果。这个功能通过create temporary table[2]来完成。
最后代码如下。其中student表的结构和临时表ss一致,这里为演示流程简化表结构与操作。
create procedure t()
begin
declare done boolean DEFAULT true;
declare id int(11);
declare tdate datetime;
declare cur CURSOR for select * from student;
declare continue handler for not found set done = false;
-- 若临时表已存在先删除
drop temporary table if exists ss;
create temporary table ss(
id int(11),
date datetime
);
open cur;
while done do
-- 获取数据
fetch cur into id,tdate;
-- TODO 需要执行的操作放在循环中,这里完成插入。
insert into ss value(id,tdate);
end while;
close cur;
-- 返回统计的记录
select * from ss;
end;
但执行后发现最后一条记录重复,最后一条记录出现了两次。在官网查询了fetch的意义,当fetch执行后游标会移动到下一条记录。
当时数据库中有4条记录,当下述代码的循环走到第4遍时,fetch执行后后游标移动到第4条记录,随后插入数据。而第4次仍能拿到数据,因此继续循环。在第5次时,结果集访问结束,此时变量中的值仍为上一次的数据。而第5次,没有获取任何记录done才被设置为false,这时仍在循环内,因此导致多插入一条数据(这个例子中没设置主键),最终结果多一条。
while done do
-- 获取数据
fetch cur into id,tdate;
-- TODO 需要执行的操作放在循环中,这里完成插入。
insert into ss value(id,tdate);
end while;
因此在插入时候要检查条件done,最终解决多插入一条数据的问题。
open cur;
while done do
fetch cur into id,tdate;
if done = true then
insert into ss value(id,tdate);
end if;
end while;
close cur;
参考:
[1]DECLARE … HANDLER 的用法
http://dev.mysql.com/doc/refman/5.6/en/declare-handler.html
[2] 创建临时表 http://blog.csdn.net/revivedsun/article/details/52854294