mysql dynamic cursor

Question

Here is the procedure I wrote- Cursors c1 & c2. c2 is inside c1, I tried declaring c2 below c1 (outside the c1 cursor) but then I is NOT taking the updated value :( Any suggestions to make it working would be helpful, Thanks

create table t1(i int); 

create table t2(i int, j int);

insert into t1(i) values(1), (2), (3), (4), (5); 

insert into t2(i, j) values(1, 6), (2, 7), (3, 8), (4, 9), (5, 10);

delimiter $

CREATE PROCEDURE p1() 
BEGIN 
DECLARE I INT; 
DECLARE J INT;
DECLARE done INT DEFAULT 0;

DECLARE c1 CURSOR FOR SELECT i FROM t1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN c1;
REPEAT 
    FETCH c1 INTO I;

    IF NOT done THEN

        select I;

        DECLARE c2 CURSOR FOR 
            SELECT j FROM t2 WHERE i = I;

        OPEN c2;
        REPEAT
            FETCH c2 into J;

            IF NOT done THEN
                SELECT J;
            END IF;
        UNTIL done END REPEAT;
        CLOSE c2;

        set done = 0;

    END IF;

UNTIL done END REPEAT;

CLOSE c1;

END$

delimiter ;

Answer 1

I don't understand what you are trying to do. When you select individual fields one at a time in a procedure, you're creating multiple result sets. I doubt that's what you want.

The following procedure is far simpler and gives you the same data in a single result set:

CREATE PROCEDURE p1()
BEGIN
  SELECT i, j FROM t1 JOIN t2 USING (i);
END

Perhaps you could edit your question and describe what result you want, or show an example of the desired output.