MySQL Stored Procedure Nested Cursors Error

Question

i am copying data for a Many to Many Relationship table making a Third Table. The stored procedure currently looks like this but it has some error

DELIMITER $$ 
CREATE PROCEDURE `test`.UpdateRelatedAccounts() 
BEGIN 
    DECLARE ssn_sel_id VARCHAR(255) DEFAULT 0;
    DECLARE id_sel_id CHAR(36) DEFAULT 0;
    DECLARE id_sel_rel CHAR(36) DEFAULT 0;
    DECLARE no_more_rows BOOLEAN;
    DECLARE num_rows INT DEFAULT 0;
    DECLARE no_more_rel_rows BOOLEAN;
    DECLARE rel_num_rows INT DEFAULT 0;

  DECLARE ssn_all_cur CURSOR FOR
   SELECT ssn, id FROM ssn WHERE ssn NOT IN ('','000-00-0000');

  DECLARE ssn_cur CURSOR FOR
   SELECT id FROM ssn WHERE id != id_sel_id AND ssn = ssn_sel_id;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
 -- DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rel_rows = TRUE;



 OPEN ssn_all_cur;
 SELECT FOUND_ROWS() INTO num_rows;
    the_loop: LOOP
        FETCH  ssn_all_cur
        INTO ssn_sel_id, id_sel_id;

        IF no_more_rows THEN
        CLOSE ssn_all_cur;
        LEAVE the_loop;
        END IF;



    OPEN ssn_cur;
    SELECT FOUND_ROWS() INTO rel_num_rows;
    the_rel_loop: LOOP
        FETCH  ssn_cur
        INTO id_sel_rel;

        IF no_more_rel_rows THEN
        CLOSE ssn_cur;
        LEAVE the_rel_loop;
        END IF;

      INSERT INTO `ssn_related` ( `ssn_primary`, `ssn_related` ) VALUES ( id_sel_id, id_sel_rel ), ( id_sel_rel, id_sel_id ); 

     END LOOP the_rel_loop;

  END LOOP the_loop;    
END$$ 
DELIMITER ;

How do i nest to use the values and insert into third table.

It does not run the insert query so that all the related records are inserted to the table — HardCode
– HardCode, Commented Feb 21, 2013 at 11:59

fancyPants · Accepted Answer · 2013-02-21 14:31:28Z

3

Cursors are slow and most of the time unnecessary. Nested cursors are slow² and unnecessary². Cursors shall only be used as the last resort, when there's really no other way.

What you want to do can be broken down to this:

INSERT INTO `ssn_related` ( `ssn_primary`, `ssn_related` )
SELECT
ssn_1.id,
ssn_2.id
FROM
ssn ssn_1
INNER JOIN ssn ssn_2 ON ssn_2.ssn = ssn_1.id
WHERE ssn_1.ssn NOT IN ('', '000-00-0000')
AND ssn_2.id != ssn_1.id;

Then you do the same again with swapped columns in the SELECT.

INSERT INTO `ssn_related` ( `ssn_primary`, `ssn_related` )
SELECT
ssn_2.id,
ssn_1.id
FROM
ssn ssn_1
INNER JOIN ssn ssn_2 ON ssn_2.ssn = ssn_1.id
WHERE ssn_1.ssn NOT IN ('', '000-00-0000')
AND ssn_2.id != ssn_1.id;

answered Feb 21, 2013 at 14:31

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

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

Collectives™ on Stack Overflow

MySQL Stored Procedure Nested Cursors Error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related