Use of MySQL Cursors

This is an example of how a cursor is used.

But there are a few points to note, which is why to join declare CONTINUE HANDLER for SQLSTATE ' 02000 ' SET tmpname = null;

If not added, will be directly error. No Data-zero rows fetched, selected, or processed

It has also been suggested that the experience is:

Experience:
Stored procedures in MySQL generally have to set a variable to track if not FOUND

DECLARE is_found INTEGER DEFAULT 1;
DECLARE CONTINUE HANDLER for not FOUND SET is_found=0;
* * The above line indicates that if no data is returned, the program continues, and the variable is_found is set to 0

This situation occurs when the Select XX to XXX from TableName, this time if XX is null there will be a problem. You can actually solve this.

Select IsNull (xxxx,0) to AAAA from TableName

This way, if you encounter null, it will be 0.

/* Initialize */
drop procedure if exists usecursor//

/* Create a stored procedure create */
CREATE PROCEDURE Usecursor ()
BEGIN
/* Definition of local variable declare*/
DECLARE tmpname varchar (+) default ';
DECLARE allname varchar (255) default ';

Declare cur1 CURSOR for SELECT name from Test.level;

/* MySQL does not know why to use the exception to add judgment?
* Please refer to the Official document 20.2.11. Cursor cursor
* This cursor is caught after the exception
* and set loop using variable tmpname to null out of loop.
*/
Declare CONTINUE HANDLER for SQLSTATE ' 02000 ' SET tmpname = null;


/* OPEN CURSOR */
OPEN Cur1;
/* Cursor down one step */
FETCH cur1 into Tmpname;

/* Loop body This obviously adds the name of the cursor query and uses it; separated by number */
While (Tmpname are not null) do
Set tmpname = CONCAT (Tmpname, ";");
Set allname = CONCAT (Allname, tmpname);
/* Cursor down one step */
FETCH cur1 into Tmpname;
END while;

CLOSE Cur1;

Select Allname;
end;//
Call Usecursor ()//

Turn from: 6150055

Use of MySQL Cursors