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 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).

score 45 · Accepted Answer · answered Mar 19 '12 at 15:32

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 ;

I think the second `IF proj_done THEN set proj_done = false;` should be change to `attribute` — tread, Aug 13 '14 at 17:46
Hi, I've some issue when following this code: https://stackoverflow.com/questions/57830576/nested-cursor-declare-issue-mysql — fudu, Sep 07 '19 at 04:31

score 11 · Answer 2 · answered Mar 14 '12 at 14:11

11

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.

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

answered Mar 14 '12 at 14:11

bhamby

15,112
1
45
66

For some more info on why cursors are usually considered a bad idea, see http://stackoverflow.com/questions/58141/why-is-it-considered-bad-practice-to-use-cursors-in-sql-server and http://stackoverflow.com/questions/287445/why-do-people-hate-sql-cursors-so-much – bhamby Mar 14 '12 at 14:15
1

It's really no contest. The cursor requires more code which will be way harder to comprehend than a simple join. And in the MySQL case, the performance is simply abysmal. There really, really is no reason for a cursor. – Roland Bouman Sep 20 '15 at 18:57
3

You sure you considered all uses of a nested cursor before you branded it a bad idea? I doubt. – Igbanam Jan 08 '16 at 12:35
4

I have to run queries on dynamic table names (massive WP multisite installation with 10s of 1000s of tables). An outer cursor gets table names and inner cursor is needed to deal with the dynamic queries on those tables. There might be a better way (without resorting to external code) but I'm not seeing it. – El Yobo Feb 18 '16 at 21:50
Cross join can't do a calculation which have a complicated logic – Taurus Silver Aug 25 '22 at 15:32
although the answer to the OP is a cross join. we shouldn't brand cursor as a tool a bad idea. in my case i am creating an ETL layer to migrate data from a 20 year old system to the new one, and in the process there is a case to simulate data that is missing in the old system, cursor has its space, it is upto the architects to know how to use the right tools at the right place to solve the problem at hand. – Kalpesh Popat Apr 26 '23 at 09:14

Milan · Answer 3 · 2018-01-04T13:34:15.213

After the first iteration within the inner cursor "attribute_done" variable is set to "true". And it remains "true" for every next iterations.

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;

Nested Cursors in Mysql

3 Answers3

Linked