mysql游标写在方法里面_mysql 存储过程 游标使用方法

耗费大半天的时间，终于可用，记录在案，留作纪念。

BEGIN

#Routine body goes here...

DECLARE userid INT(11);

DECLARE username varchar(200);

DECLARE userCur CURSOR for

select tbl_user.ID,tbl_user_info.`Name` from tbl_user left JOIN tbl_user_info on tbl_user.ID = tbl_user_info.UserID;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET @flag = 1;

set @rownum:=0;

set @rownum_plan:=0;

set @flag = 0 ;

drop table IF EXISTS tmp_tbl;

create TEMPORARY table tmp_tbl(

catalog varchar(200) ,

rownum INT (10),

project varchar(200),

workcontent varchar(2000),

executor VARCHAR(20),

comlete varchar(10),

trouble varchar(1000),

date datetime

);

OPEN userCur;

FETCH userCur INTO userid,username;

WHILE @flag<>1 DO

insert into tmp_tbl select '本周工作',@rownum:=@rownum+1 as rownum,'',t.`Name`,username,t.Comp,'',t.StartTime from tbl_project_task t where FIND_IN_SET(userid,t.Executors) and t.StartTime < curdate() and t.StartTime >= curdate()-7 order by t.StartTime;

insert into tmp_tbl select '下周计划',@rownum_plan:=@rownum_plan+1 as rownum,'',t.`Name`,username,t.Comp,'',t.StartTime from tbl_project_task t where FIND_IN_SET(userid,t.Executors) and t.StartTime < curdate()+7 and t.StartTime >= curdate() order by t.StartTime;

FETCH userCur INTO userid,username;

END WHILE;

CLOSE userCur;

select * from tmp_tbl;

drop table tmp_tbl;

END