MySQL Cursor Operations Guide

This article is a detailed analysis of the specific use of MySQL cursors, the need for a friend reference under

Test table level

The code is as follows:

CREATE TABLE Test.level (name varchar (20));

Insert some data again;

Initialization

The code is as follows:

drop procedure if exists usecursor//

Create a stored procedure createThe code is as follows:

CREATE PROCEDURE usecursor () BEGIN

Definition of local variables declareThe code is as follows:

DECLARE tmpname varchar (+) default ';  DECLARE allname varchar (255) default ';  

MySQL cursor Catch after exception

and set the loop to use the variable tmpname null to jump out of the loop.

The code is as follows:

Declare CONTINUE HANDLER for SQLSTATE ' 02000 ' SET tmpname = null;

　　

Open cursorThe code is as follows:

OPEN Cur1;

　　

The cursor goes down one stepThe code is as follows:

FETCH cur1 into Tmpname;

　　

Loop body This obviously adds up the name queried by the MySQL cursor; Number separatedThe code is as follows:

While (Tmpname was not null) do set tmpname = CONCAT (Tmpname, ";"); Set allname = CONCAT (Allname, tmpname);

　　

The cursor goes down one stepThe code is as follows:

FETCH cur1 into Tmpname;

　　

End Loop Body:The code is as follows:

END while;

　　

Close CursorsThe code is as follows:

CLOSE Cur1;

　　

Select dataThe code is as follows:

Select Allname;

　　

End Stored ProcedureThe code is as follows:

end;//

　　

To call a stored procedure:The code is as follows:

Call Usecursor ()//

Loop loop cursors:The 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$$  

　　

Repeat Loop cursors:The code is as follows:

/* CREATE PROCEDURE */delimiter//drop PROCEDURE IF EXISTS test//create PROCEDURE Test () BEGIN    DECLARE done INT DEFAULT 0;    DECLARE a VARCHAR (+) DEFAULT ';    DECLARE c VARCHAR (+) 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 do then            SET c=concat (c,a);/* String Add */        END IF;    UNTIL done END REPEAT;    CLOSE MyCursor;    SELECT C; END//delimiter;

　　

The code is as follows:

/* CREATE PROCEDURE */delimiter//drop PROCEDURE IF EXISTS test//create PROCEDURE Test () BEGIN    DECLARE done INT DEFAULT 0;    DECLARE a VARCHAR (+) DEFAULT ';    DECLARE c VARCHAR (+) 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 do then            SET c=concat (c,a);/* String Add */        END IF;    UNTIL done END REPEAT;    CLOSE MyCursor;    SELECT C; END//delimiter;

　　

MySQL Cursor Operations Guide