Detailed usage of MySQL-based cursors

Test Table level;
Copy codeThe Code is as follows:
Create table test. level (name varchar (20 ));

Insert some data;

Code

Initialization
Copy codeThe Code is as follows:
Drop procedure if exists useCursor //

Create a stored procedure
Copy codeThe Code is as follows:
Create procedure useCursor ()

BEGIN

Declare
Copy codeThe Code is as follows:
Declare tmpName varchar (20) default '';
Declare allName varchar (255) default '';
Declare cur1 cursor for select name FROM test. level;

Capture when a MySQL cursor is abnormal

And set the loop to jump out of the loop with the variable tmpname null.
Copy codeThe Code is as follows:
Declare continue handler for sqlstate '20170101' SET tmpname = null;

Open cursor
Copy codeThe Code is as follows:
OPEN cur1;

Move the cursor down
Copy codeThe Code is as follows:
FETCH cur1 INTO tmpName;

Loop body, which obviously adds names queried by MySQL cursors and separates them with; numbers.
Copy codeThe Code is as follows:
WHILE (tmpname is not null) DO
Set tmpName = CONCAT (tmpName ,";");
Set allName = CONCAT (allName, tmpName );

Move the cursor down
Copy codeThe Code is as follows:
FETCH cur1 INTO tmpName;

End loop body:
Copy codeThe Code is as follows:
End while;

Close cursor
Copy codeThe Code is as follows:
CLOSE cur1;

Select data
Copy codeThe Code is as follows:
Select allName;

End Stored Procedure
Copy codeThe Code is as follows:
END ;//

Call the stored procedure:
Copy codeThe Code is as follows:
Call useCursor ()//

Running result:
Copy codeThe Code is as follows:
Mysql> call useCursor ()//

+ -------------------------------------- +

| AllName |

+ -------------------------------------- +

| F1; c3; c6; c5; c2; c4; c1; f1; f3; f4; f2; f5; |

+ -------------------------------------- +

1 row in set (0.00 sec)

Loop cursor:
Copy codeThe Code is as follows:
DELIMITER $

Drop procedure if exits cursor_example $
Create procedure cursor_example ()
READS SQL DATA
BEGIN
DECLARE l_employee_id INT;
DECLARE l_salary NUMERIC (8, 2 );
DECLARE l_department_id INT;
DECLARE done int default 0;
DECLARE cur1 cursor for select employee_id, salary, department_id FROM employees;
Declare continue handler for not found set done = 1;

OPEN cur1;
Emp_loop: LOOP
FETCH cur1 INTO l_employee_id, l_salary, l_department_id;
IF done = 1 THEN
LEAVE emp_loop;
End if;
End loop emp_loop;
CLOSE cur1;
END $
DELIMITER;

Repeat loop cursor:
Copy codeThe Code is as follows:
/* Creation process */
DELIMITER //
Drop procedure if exists test //
Create procedure test ()
BEGIN
DECLARE done int default 0;
DECLARE a VARCHAR (200) DEFAULT '';
DECLARE c VARCHAR (200) DEFAULT '';

DECLARE mycursor cursor for select fusername FROM uchome_friend;
Declare continue handler for not found set done = 1;

OPEN mycursor;

REPEAT
FETCH mycursor INTO;
If not done THEN
SET c = CONCAT (c, a);/* string addition */
End if;

UNTIL done end repeat;

CLOSE mycursor;

SELECT c;
END //
DELIMITER;

Copy codeThe Code is as follows:
/* Creation process */
DELIMITER //
Drop procedure if exists test //
Create procedure test ()
BEGIN
DECLARE done int default 0;
DECLARE a VARCHAR (200) DEFAULT '';
DECLARE c VARCHAR (200) DEFAULT '';

DECLARE mycursor cursor for select fusername FROM uchome_friend;
Declare continue handler for not found set done = 1;

OPEN mycursor;

REPEAT
FETCH mycursor INTO;
If not done THEN
SET c = CONCAT (c, a);/* string addition */
End if;

UNTIL done end repeat;

CLOSE mycursor;

SELECT c;
END //
DELIMITER;