Nested Cursors in Mysql

Question

I have three tables.
Project (Id), attribute (Id), project_attribute (Id, project_id, attribute_id) .

I want to create records in the project_attribute table using all the attributes of the attribute table for each project from the project table.

To create such records, I use the following storage procedure.

DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `proj_attr`() BEGIN DECLARE proj_done, attribute_done BOOLEAN DEFAULT FALSE; declare attributeId int(11) default 0; declare projectId int(11) default 0; DECLARE curProjects CURSOR FOR SELECT id FROM project order by id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET proj_done = TRUE; OPEN curProjects; cur_project_loop: LOOP FETCH FROM curProjects INTO projectId; IF proj_done THEN CLOSE curProjects; LEAVE cur_project_loop; END IF; BLOCK2: BEGIN DECLARE curAttribute CURSOR FOR SELECT id FROM attribute order by id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET attribute_done = TRUE; OPEN curAttribute; cur_attribute_loop: LOOP FETCH FROM curAttribute INTO attributeId; IF attribute_done THEN CLOSE curAttribute; LEAVE cur_attribute_loop; END IF; insert into project_attribute_value(project_id, attribute_id) values(projectId, attributeId); END LOOP cur_attribute_loop; END BLOCK2; END LOOP cur_project_loop; END$$ DELIMITER ; 

But this procedure creates records for only 1 project in the project_attribute table, although there are 50 projects in the Project table. The expected number of entries is count (projectId) * count (attributeId).

Answer 1

Try this, it will certainly solve your problem.

 DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `proj_attr`() BEGIN DECLARE proj_done, attribute_done BOOLEAN DEFAULT FALSE; declare attributeId int(11) default 0; declare projectId int(11) default 0; DECLARE curProjects CURSOR FOR SELECT id FROM project order by id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET proj_done = TRUE; OPEN curProjects; cur_project_loop: LOOP FETCH FROM curProjects INTO projectId; IF proj_done THEN CLOSE curProjects; LEAVE cur_project_loop; END IF; BLOCK2: BEGIN DECLARE curAttribute CURSOR FOR SELECT id FROM attribute order by id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET attribute_done = TRUE; OPEN curAttribute; cur_attribute_loop: LOOP FETCH FROM curAttribute INTO attributeId; IF proj_done THEN set proj_done = false; CLOSE curAttribute; LEAVE cur_attribute_loop; END IF; insert into project_attribute_value(project_id, attribute_id) values(projectId, attributeId); END LOOP cur_attribute_loop; END BLOCK2; END LOOP cur_project_loop; END$$ DELIMITER ; 

Answer 2

Quite frankly, nested cursors (usually) are a terrible idea. You can get what you want directly, without using the cursor, using the usual CROSS JOIN .

 INSERT INTO proj_attr (project, attribute) SELECT p.id AS projectid, a.id AS attributeid FROM project p CROSS JOIN attribute a; 

Answer 3

After the first iteration, the attribute_done variable is set to true inside the internal cursor. And it remains “true” for each subsequent iteration.

This leads to the fact that each subsequent iteration skips the inner loop.

The following is an example of a nested cursor.

---

 CREATE TABLE `parent` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB CREATE TABLE `child` ( `a` int(11) DEFAULT NULL, `b` varchar(20) DEFAULT NULL ) ENGINE=InnoDB insert into parent values (1),(2),(3); insert into child values (1,'a'),(1,'b'),(2,'a'),(2,'b'),(3,'a'),(3,'b'); ---------------------------------- drop procedure if exists nestedCursor; create procedure nestedCursor() BEGIN DECLARE done1, done2 BOOLEAN DEFAULT FALSE; DECLARE parentId,childId int; DECLARE childValue varchar(30); DECLARE cur1 CURSOR FOR SELECT a FROM parent; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE; open cur1; loop1: LOOP FETCH FROM cur1 INTO parentId; IF done1 THEN CLOSE cur1; LEAVE loop1; END IF; BLOCK1 : BEGIN DECLARE cur2 CURSOR FOR SELECT a,b FROM child where a = parentId; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = TRUE; open cur2; loop2 : LOOP FETCH FROM cur2 INTO childId,childValue; if done2 THEN CLOSE cur2; SET done2 = FALSE; LEAVE loop2; end if; select parentId,childId,childValue; END LOOP loop2; END BLOCK1; END loop loop1; END;