User Comments

Hi all, as beat pointed out, LOOP is the only construct that I found useful for traversing cursors. Both WHILE and REPEAT lead to problems. See http://rpbouman.blogspot.com/2005/09/why-repeat-and-while-are-usually-not.html for examples:

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

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 :

/* Disable DATA NOT FOUND handlers from calling functions */
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' BEGIN END;

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!

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.

DELIMITER $$

DROP PROCEDURE IF EXISTS `igla`.`twoCursor` $$
CREATE PROCEDURE `igla`.`twoCursor` ()
BEGIN
declare idFact, idMaster, idSlave, indexM, indexS int default 0;

declare masterCursor cursor for Select SQL_CALC_FOUND_ROWS Factura_ID from facturi;
declare slaveCursor cursor for Select SQL_CALC_FOUND_ROWS iesire_id from iesiri where factura_ID =idFact;

open masterCursor;

set idMaster = (Select FOUND_ROWS());

while indexM<idMaster do
fetch masterCursor into idFact;

open slaveCursor;

set idSlave = (Select FOUND_ROWS());

while indexS<idSlave do
-- do some hear

set indexS= indexS+1;
end while;

close slaveCursor;
set indexM=indexM+1;
end while;

close masterCursor;

END $$

DELIMITER ;

How you can use nested loop,here each dept has one or more majors.

/*
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 Procedure
DROP PROCEDURE IF EXISTS get_major_info_for_all_dept;

DELIMITER //
-- Create procedure get_major_info_for_all_dept()
CREATE PROCEDURE get_major_info_for_all_dept()

BEGIN

DECLARE dept_id VARCHAR(3) ;
DECLARE major_description VARCHAR(50) ;
DECLARE concat_description VARCHAR(60);
DECLARE flag1 VARCHAR(5) DEFAULT 'START' ;
DECLARE flag2 VARCHAR(5) DEFAULT 'START' ;

-- Declare cursor for outside looping purpose to get all departments from the dept table
DECLARE getDept CURSOR FOR
SELECT deptid
FROM dept
ORDER BY deptid;
--Declare cursor for getting each and every major description of each department.
DECLARE getMajorsFromEachDept CURSOR FOR
SELECT 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 cursor
OPEN 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 memory
CLOSE getDept;

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

END;
//

DELIMITER ;

Purvi

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

CREATE PROCEDURE get_major_info_for_all_dept()
INSERT INTO messages
SELECT CONCAT(dept.deptid, '*', major.description)
FROM dept JOIN major ON dept.deptid = major.dept_id

hello folks,

you cannot reopen a cursor, if you changed rows hold by the cursor after scrolling through it for the first time!!

I'm using mysql 5.1.22-rc

regards,
s

Are you looking for an alternative cursor traversal code template in which there is no loop control variable (like "done") and no label (like "MyLabel:") ?

Take a look at "Simple Cursor Traversal 2"
http://forge.mysql.com/tools/tool.php?id=186

This method is stored function safe, even when nested into each other. (Like Function A calls Function B; both using cursors)

-- Initialise cursor
OPEN cursor40201;

-- USE BEGIN-END handler for cursor-control within own BEGIN-END block
BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;

-- Loop cursor throu users of group
LOOP

-- Get next value
FETCH cursor40201 INTO ;

-- Add update useracls
IF (SELECT libcrm_user_updateeffective(idUserU0204)) <=> NULL THEN

-- Return error
RETURN CONCAT('ERROR 404: iIDUser(', idUserU0204, '), unknown. - NOT FOUND');

END IF;

END LOOP;

END;

-- Release cursor
CLOSE cursorU0204;

Add your own comment.