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屋！