mysql存储过程

#判断游标是否存在，存在则删除
DROP PROCEDURE IF EXISTS statisticalMonthDetail;
#申明一个游标，并带输入参数
CREATE PROCEDURE statisticalMonthDetail (IN p_date varchar(100))

BEGIN

#申明一个游标的结束条件
declare done boolean;
#申明变量
DECLARE d_dateStatus date;
DECLARE d_userId VARCHAR(100);
DECLARE d_name VARCHAR(100);
DECLARE d_timeOvertime DOUBLE;
DECLARE d_subsidizedMeals int;

#申明游标
DECLARE
userCursor CURSOR FOR SELECT
d.dateStatus,
d.userId,
d.name,
SUM(d.timeOvertime),
SUM(d.lunch) + SUM(d.dinner)
FROM
a_status_detail d
WHERE
DATE_FORMAT(d.dateStatus, '%Y-%m') = DATE_FORMAT(p_date, '%Y-%m')
AND delFlag = 0
GROUP BY
d.userId ;

#每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

#打开游标
OPEN userCursor;

#循环游标
fetchIncomeLoop:LOOP
FETCH userCursor INTO d_dateStatus,d_userId,d_name,d_timeOvertime,d_subsidizedMeals;
#如果done为true，则游标结束
 if done then  
     leave fetchIncomeLoop;  
#如果done为false，则游标循环
 else       
    insert into a_status_detail_month (dateStatus,userId,name,timeOvertime,subsidizedMeals,dept,rest,lastRest,thisrest,delFlag,createTime,creator,updateTime,updator) 
values (d_dateStatus,d_userId,d_name,d_timeOvertime,d_subsidizedMeals,'运维开发部',0,0,0,0,SYSDATE(),'admin1',SYSDATE(),'admin1');
 end if; 

#游标循环结束
END LOOP;

#关闭游标
CLOSE userCursor;

END

－－备注：

这一个简单的存储过程，统计一个月内的考勤记录（加班时间，餐补费用等）