复制代码 代码如下: DELIMITER $$ DROP PROCEDURE IF EXISTS getUserInfo $$ CREATE PROCEDURE getUserInfo(in date_day datetime) — — 实例 — MYSQL存储过程名为：getUserInfo — 参数为：date_day日期格式:2008-03-08 — BEGIN
复制代码 代码如下:DELIMITER $$
DROP PROCEDURE IF EXISTS getUserInfo $$
CREATE PROCEDURE getUserInfo(in date_day datetime)– — 实例– MYSQL存储过程名为：getUserInfo– 参数为：date_day日期格式:2008-03-08– BEGINdeclare _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 ;
