Specific usage of MySQL cursors

The following articles mainly describe the specific usage of MySQL cursors. If you are interested in the actual operations of MySQL cursors, you can click and view the following articles, it is hoped that some help will be provided to you after your browsing.

Use MySQL 1.5 or above;

Test Table level;

 
 

  
  
create table test.level (name varchar(20)); 
 
 

Insert some data;

Code

Initialization

Drop procedure if exists useCursor //

Create a stored procedure

Create procedure useCursor ()

BEGIN

Declare

 
 

  
  
declare tmpName varchar(20) default '' ;  
  
  
declare allName varchar(255) default '' ;  
  
  
declare cur1 CURSOR FOR SELECT name FROM test.level ;  
 
 

MySQL does not know why to add an exception to the judgment?

Please refer to the official documentation for more information.

This captures MySQL cursor exceptions

And set the loop to jump out of the loop with the variable tmpname null.

 
 

  
  
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = null; 
 
 

Open cursor

OPEN cur1;

Move the cursor down

 
 

  
  
FETCH cur1 INTO tmpName; 
 
 

Loop body, which obviously adds names queried by MySQL cursors and separates them with; numbers.

 
 

  
  
WHILE ( tmpname is not null) DO  
  
  
set tmpName = CONCAT(tmpName ,";") ;  
  
  
set allName = CONCAT(allName ,tmpName) ;  
 
 

Move the cursor down

 
 

  
  
FETCH cur1 INTO tmpName;  
  
  
END WHILE;  
  
  
CLOSE cur1;  
  
  
select allName ;  
  
  
END;//  
  
  
call useCursor()//  
 
 

Running result:

Code

 
 

  
  
MySQL> call useCursor()//  
  
  
+--------------------------------------+  
  
  
| allName |  
  
  
+--------------------------------------+  
  
  
| f1;c3;c6;c5;c2;c4;c1;f1;f3;f4;f2;f5; |  
  
  
+--------------------------------------+  
  
  
1 row in set (0.00 sec)  
 
 

Code

 
 

  
  
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 ;   
 
 

Code

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 a;  
  
  
IF NOT done THEN  
  
  
SET c=CONCAT(c,a); 
 
 

String Addition

 
 

  
  
END IF;  
  
  
UNTIL done END REPEAT;  
  
  
CLOSE mycursor;  
  
  
SELECT c;  
  
  
END //  
  
  
DELIMITER ; 
 
 

The above content is an introduction to the usage of MySQL cursors. I hope you will gain some benefits.