Mysql中的嵌套游标 [英] Nested Cursors in Mysql
问题描述
我有三个表格。
Project(Id),属性(Id),project_attribute(Id,project_id,attribute_id) b $ b
我要在 project_attribute 表中创建记录,方法是使用项目表中属性表中的每个项目的所有属性。 / p>
要创建此类记录,我使用以下存储过程。
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;
如果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;
但是,此过程仅在project_attribute表中为1个项目创建记录,即使有50个项目项目表。预期记录数为count(projectId)* count(attributeId)。
尝试这样做,这一定会解决您的问题。 / p>
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;
如果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;
I have three tables.
Project(Id), attribute(Id), project_attribute(Id, project_id, attribute_id).
I want to create records in project_attribute table by using all attributes from attribute table to each project from project table.
To create such records i am using following store 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 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).
Try this, this will surely solve your issue.
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 ;
这篇关于Mysql中的嵌套游标的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!