Using a cursor in a stored procedure loop

Question

To dynamically use cursors using MySQL, is it possible to declare a cursor in a stored procedure loop? I tried and got an error:

increment: LOOP
DECLARE cur1 CURSOR FOR SELECT person_id, publication_id  FROM p_publication WHERE person_id = new_count;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
    FETCH cur1 INTO pub_id, per_id;
IF NOT done THEN
          INSERT INTO test.t2 VALUES (pub_id, per_id);
    END IF;
SET new_count = new_count + 1;
  UNTIL done END REPEAT;

     CLOSE cur1;
IF !(new_count < old_count ) THEN LEAVE increment;
END IF;
END LOOP increment;

You have an error in the SQL syntax; check the manual that matches your MySQL server for the correct syntax to use next to 'DECLARE cur1 CURSOR FOR SELECT person_id, publish_id FROM person_has_public' on line 12

thanks in advance

Answer 1

, , BEGIN ... END. . Roland Bouman" MySQL. , , , , .

, :

increment: LOOP
    block_cursor: BEGIN
        DECLARE cur1 CURSOR FOR SELECT person_id, publication_id  FROM p_publication WHERE person_id = new_count;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
        OPEN cur1;
        REPEAT
            FETCH cur1 INTO pub_id, per_id;
            IF NOT done THEN
                INSERT INTO test.t2 VALUES (pub_id, per_id);
            END IF;
            SET new_count = new_count + 1;
        UNTIL done END REPEAT;
        CLOSE cur1;
        IF !(new_count < old_count ) THEN
            LEAVE increment;
        END IF;
    END block_cursor;
END LOOP increment;