LearningMySQL 2019-04-08
代码如下:
DELIMITER $$ DROP PROCEDURE IF EXISTS getUserInfo $$ CREATE PROCEDURE getUserInfo(in date_day datetime) -- -- 实例 -- MYSQL存储过程名为:getUserInfo -- 参数为:date_day日期格式:2008-03-08 -- BEGIN declare _userName varchar(12); -- 用户名 declare _chinese int ; -- 语文 declare _math int ; -- 数学 declare done int; -- 定义游标 DECLARE rs_cursor CURSOR FOR SELECT username,chinese,math from userInfo where datediff(createDate, date_day)=0; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; -- 获取昨天的日期 if date_day is null then set date_day = date_add(now(),interval -1 day); end if; open rs_cursor; cursor_loop:loop FETCH rs_cursor into _userName, _chinese, _math; -- 取数据 if done=1 then leave cursor_loop; end if; -- 更新表 update infoSum set total=_chinese+_math where UserName=_userName; end loop cursor_loop; close rs_cursor; END$$ DELIMITER ;