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 procedureCopy codeThe Code is as follows: create procedure useCursor ()
BEGIN
DeclareCopy 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 '000000' SET tmpname = null;
Open cursorCopy codeThe Code is as follows: OPEN cur1;
Move the cursor downCopy 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 downCopy codeThe Code is as follows: FETCH cur1 INTO tmpName;
End loop body:Copy codeThe Code is as follows: end while;
Close cursorCopy codeThe Code is as follows: CLOSE cur1;
Select dataCopy codeThe Code is as follows: select allName;
End Stored ProcedureCopy 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;