StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PONested Cursors in Mysql
text
Body
copied!<p>I have three tables.<br> <strong>Project(Id), attribute(Id), project_attribute(Id, project_id, attribute_id)</strong>.</p> <p>I want to create records in <strong>project_attribute</strong> table by using all attributes from <strong>attribute</strong> table to each project from <strong>project</strong> table.</p> <p>To create such records i am using following store procedure.</p> <pre><code>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 ; </code></pre> <p>But, this procedure is creating records only for 1 project in project_attribute table even though there are 50 projects in Project table. Expected record count is count(projectId)*count(attributeId).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload