Instances of cursors used in Mysql stored procedures _mysql

Copy Code code as follows:

DELIMITER $$

DROP PROCEDURE IF EXISTS getuserinfo $$

CREATE PROCEDURE GetUserInfo (in Date_day datetime)
--
--Example
--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;

--Defining cursors
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 yesterday's date
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; --Take 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;