Instances that use cursors in MYSQL stored procedures

MYSQL stored procedures can be used to implement many functions. The following describes an instance of using cursors in MYSQL stored procedures.

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;
 

The above is an example of using a cursor in the MYSQL stored procedure.

Mysql Stored Procedure Call Method

Use of mysql ifnull Functions

MySQL date functions

Learn more about mysql CONCAT () Functions

How to view the three MySQL character sets