BitsCN.com
DELIMITER $
Drop procedure if exists getUserInfo $
Create procedure getUserInfo (in date_day datetime)
--
-- Instance
-- MYSQL stored procedure name: getUserInfo
-- Parameter: date_day date format: 2008-03-08
--
BEGIN
Declare _ userName varchar (12); -- user name
Declare _ chinese int; -- chinese
Declare _ math int; -- Mathematics
Declare done int;
-- Define a cursor
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;
-- Get the date of yesterday
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; -- get data
If done = 1 then
Leave cursor_loop;
End if;
-- Update table
Update infoSum set total = _ chinese + _ math where UserName = _ userName;
End loop cursor_loop;
Close rs_cursor;
END $
DELIMITER;
BitsCN.com