MySQL Mysql中的嵌套游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9699896/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Nested Cursors in Mysql
提问by Avinash T.
I have three tables.
Project(Id), attribute(Id), project_attribute(Id, project_id, attribute_id).
我有三张桌子。
项目(Id), attribute(Id), project_attribute(Id, project_id, attribute_id)。
I want to create records in project_attributetable by using all attributes from attributetable to each project from projecttable.
我想创建一个记录project_attribute通过使用来自所有属性表属性表，从每个项目的项目表。
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).
但是，即使 Project 表中有 50 个项目，此过程也仅为 project_attribute 表中的 1 个项目创建记录。预期的记录数为 count(projectId)*count(attributeId)。
回答by Nandkumar Tekale
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 ;
回答by bhamby
Quite bluntly, nested cursors are (usually) a terrible idea. You can get what you want directly, without using a cursor, by using a normal CROSS JOIN.
坦率地说，嵌套游标（通常）是一个糟糕的主意。您可以通过使用普通的CROSS JOIN.
INSERT INTO proj_attr (project, attribute)
SELECT p.id AS projectid, a.id AS attributeid
FROM project p CROSS JOIN attribute a;
回答by Milan
After the first iteration within the inner cursor "attribute_done" variable is set to "true". And it remains "true" for every next iterations.
在内部游标内的第一次迭代之后，“attribute_done”变量被设置为“true”。并且每次下一次迭代它都保持“真实”。
This causes every next iterations to skip the inner loop.
这会导致每次下一次迭代都跳过内部循环。
A sample nested cursor is illustrated below.
示例嵌套游标如下所示。
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;
