Nested Cursor Into Mysql

Question

I am creating cursor within cursor and first cursor getting unique value and second cursor is generating details number of rows. I found second cursor is running one time and not populating rows against every row id. Please guide

DELIMITER $$
USE `dum_data`$$
DROP PROCEDURE IF EXISTS `sp_process_gen`$$
CREATE DEFINER=`test`@`%` PROCEDURE `sp_process_gen`()
BEGIN
DECLARE cur1_done,cur2_done INT DEFAULT 0;
DECLARE v_thread_id, v_id,v_tab,v_event_time INT;
DECLARE v_stime,v_etime DATETIME;
-- declaring cursor
DECLARE cur1 CURSOR FOR
    SELECT thread_id
    FROM general_log
    -- WHERE thread_id in (306710429,306711335)
    GROUP BY thread_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET cur1_done = 1;
OPEN cur1;
cur1_loop: LOOP
FETCH cur1 INTO v_thread_id;
-- INSERT INTO aa VALUES(NULL,v_thread_id,NULL,NULL);
     block2: BEGIN
        DECLARE cur2 CURSOR FOR
            SELECT id , event_time
            FROM general_log
            WHERE thread_id = v_thread_id
            ORDER BY id;
            
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET cur2_done = 1;
        OPEN cur2;
        cur2_loop: LOOP
        FETCH cur2 INTO v_id,v_stime;
        IF cur2_done THEN
            -- set cur1_done = 0;
            CLOSE cur2;
            LEAVE cur2_loop;
        END IF;
        -- INSERT INTO aa VALUES(v_id,v_thread_id,v_stime,v_stime);
        INSERT INTO aa(thread_id) VALUES(v_thread_id);
        -- FETCH cur2 INTO v_id,v_stime;
        END LOOP cur2_loop;
        
    END block2;
    
IF cur1_done THEN
    CLOSE cur1;
    LEAVE cur1_loop;
END IF;
        
INSERT INTO aa(thread_id) VALUES(999999);
-- FETCH cur1 INTO v_thread_id;     
END LOOP cur1_loop;
-- CLOSE cur;
    
END$$
DELIMITER ;

It would probably be better to join the two queries instead of using two cursors. — Barmar
– Barmar, Commented Oct 6, 2022 at 17:20
I assume this procedure has been simplified to post in this question, because the procedure makes no sense. It's just copying all the rows from one table to another in an inefficient manner. It could be done with a single INSERT ... SELECT. — Bill Karwin
– Bill Karwin, Commented Oct 6, 2022 at 18:09

MNB · Accepted Answer · 2022-10-06 17:25:28Z

1

I suspect your problem is the two CONTINUE HANDLER FOR NOT FOUND structures. The second cursor will get it's first NOT FOUND at the end its loop, trigger both handlers... causing both loops to exit.

The way around this is to have a second stored procedure that contains the inner loop, and call that procedure from the outer loop. That way your handlers are in separate contexts.

answered Oct 6, 2022 at 17:25

MNB

5476 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MNB Over a year ago

additionally - if you can, try to solve your problems with a single query. Cursors are inherently vastly slower way to achieve things that are essentially a join.

Collectives™ on Stack Overflow

Nested Cursor Into Mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related