Nesting cursors is possible, you just need to take care of a few things. You can either put the inner cursor in it's own BEGIN..END block, duplicating the code for the handler and the loop control, or you can reset your loop control variable inside the inner loop once the cursor is exhausted. I won't post an example here, as this would soon clutter the page. Just check it out here, it's got all the examples: http://rpbouman.blogspot.com/2005/10/nesting-mysql-cursor-loops.html

Posted by
Alexander Pelov
on
March 15, 2006

Hello everyone,

I comment the examples given on this page.

I had several working stored functions that when called from a "fetching" cursor loop didn't work (exited prematurely). The problem was that the functions were issuing SELECT statements which sometimes returned empty resultsets. This in turn executed the declared HANDLER, which interrupted the function(!) and set the variable 'done' to true -> after the first itteration the loop was acting as if the end of the data was hit.

The solution I found is somewhat cumbersome (but works!) - I've redeclared the HANDLER in each of the functions :

There are really helpful comments posted here and throughout the manual. PLEASE, everyone, when you post do not forget to mention the VERSION of MySQL you were using when you encountered your issue. Without the VERSION information, the report of a bug or the description of a technical feature or a problem is lessened in value to the community.

Thanks!

Posted by
Horga Marius
on
November 17, 2007

it is possible to nest 2 cursor an find the number of rows for each cursor. We can use SQL_CALC_FUND_ROWS in the cursor.See examples.

/*Write an SP that retrieves from your CS442 database all the departments and the majors offered by each department, and insert the results in the MESSAGES table. Here are detailed instructions:Store a string composed of the department ID and the major description in the MESSAGES table (separate the two with an asterisk (*) symbol by using the concat function). Your MESSAGES table should contain 16 records and look similar to the following: BUS*Marketing COM*Communications CS*Computer Networking */-- Drop ProcedureDROP PROCEDURE IF EXISTS get_major_info_for_all_dept;

-- Declare cursor for outside looping purpose to get all departments from the dept tableDECLARE getDept CURSOR FORSELECT deptid FROM dept ORDER BY deptid;--Declare cursor for getting each and every major description of each department.DECLARE getMajorsFromEachDept CURSOR FORSELECT description FROM major WHERE deptid = dept_id;

-- Record not found means for inner loop no more major record left for that dept_id and for outer loop no more record left for dept-id DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag1 = 'END';

-- Open cursorOPEN getDept;

-- loop to extract each major of each department and insert into messages table-- until no record left.

WHILE flag1<>'END' DO -- to get deptid FETCH getDept INTO dept_id;

IF flag1 <> 'END' THEN

OPEN getMajorsFromEachDept;

SET flag2= flag1; WHILE flag1 <> 'END' DO FETCH getMajorsFromEachDept INTO major_description; IF flag1 <> 'END' THEN SET concat_description = CONCAT(dept_id,'*',major_description); INSERT INTO messages VALUES (concat_description); END IF; END WHILE; -- free memory CLOSE getMajorsFromEachDept;

SET flag1 = flag2; END IF;END WHILE;-- Loop end.

-- free memoryCLOSE getDept;

--- Display all records of Messages Table---------SELECT * FROM messages;

END;//

DELIMITER ;

Purvi

Posted by
Aaron Diers
on
March 6, 2008

Just as a response to that last comment, wouldn't it be easier just to do this?

The problem is that when one of your SELECT INTO queries within the loop generates 0 results... it trips the CONTINUE HANDLER and sets "done = TRUE". The easiest way around this is to simply add the line "SET done = FALSE ;" immediately before the FETCH command(s) like so...

read_loop: LOOP -- This is the line that fixes the problem SET done = FALSE ;

FETCH cur1 INTO a, b; FETCH cur2 INTO c;

IF done THEN LEAVE read_loop; END IF; IF b < c THEN INSERT INTO test.t3 VALUES (a,b); ELSE INSERT INTO test.t3 VALUES (a,c); END IF;

-- This is the kind of line that would cause the problem. -- SELECT INTO returns 0 records, triggering the CONTINUE HANDLER and setting done to TRUE (which we don't want) SELECT id INTO a FROM test.t1 WHERE 1 = 2 ; END LOOP;

This way, "done" is reset to false from whatever else went on during the loop and the "IF done THEN" check could only be true when FETCH runs out of records.

Posted by
Brent Roady
on
May 9, 2012

It should be noted that the local variable names used in FETCH [cursor] INTO must be different than the variable names used in the SELECT statement defining the CURSOR. Otherwise the values will be NULL.

In this example,

DECLARE a VARCHAR(255);DECLARE cur1 CURSOR FOR SELECT a FROM table1;FETCH cur1 INTO a;

DECLARE a VARCHAR(255);/* you declare a variable "a" without a specified default value, a=NULL */

DECLARE cur1 CURSOR FOR SELECT a FROM table1;/* You declare a cursor that selects "a" FROM a table */

OPEN cur1;/* You execute your cursor query, a warning is raised because a is ambiguously defined but you don't see it */

FETCH cur1 INTO a;/* you put your unique field in your unique row into a (basically you do "SET a=a;") so a is still NULL */

There is no bug report, just a misunderstanding.

Posted by
Abraham Guyt
on
September 22, 2017

@Steffen Hirsch:

It is now (v 5.6.37) perfectly fine to close and (re)open a cursor after some rows involved in the first traverse have been modified. Closing and reopening just release the cursor resources and recreate a completely new cursor.

It should be noted though that a MySQL cursor is _not_ a snapshot of the database when it is opened; the data is only actually read from the database at each row's FETCH statement.