How to repeate mysql cursor with parameter?

Question

I have following cursor with parameter. Now I need to set parameter during loop. Is their a short way to do this?

Cursor:

DECLARE curs1 CURSOR FOR 
     SELECT  USER_ID,affiliate_id
 FROM `wpmr_aff_referrals` WHERE affiliate_id=AFFILIATEID;

Cursor body part:

OPEN curs1;    
read_loop : LOOP    
FETCH curs1 INTO USERID,AFFILIATEID;
IF (vCNT>=3) THEN  
set AFFILIATEID=10;
ELSE
set new.affiliate_id= 5;
END IF;    
END LOOP read_loop;
CLOSE curs1;

At set AFFILIATEID=10; it should be re-opened like in Oracle we do open cursor as c_emp (23);

Answer 1

Try this with inner block and loop.

    OPEN curs1;    
        read_loop : LOOP    
        FETCH curs1 INTO USERID,AFFILIATEID;
        IF (vCNT>=3) THEN  
        set AFFILIATEID=10;
        ELSE
        set new.affiliate_id= 5;
        END IF;    

        BLOCK2: begin
                     DECLARE USERID1 varchar(50);
     DECLARE AFFILIATEID1 varchar(50) default
                    declare curs2 cursor for
                       SELECT  USER_ID,affiliate_id
         FROM `wpmr_aff_referrals` WHERE affiliate_id=AFFILIATEID;

                    open curs2;
                    LOOP2: loop
                        FETCH curs2 INTO USERID1,AFFILIATEID1;

                    end loop LOOP2;
                    CLOSE curs2;
                end BLOCK2;


        END LOOP read_loop;
        CLOSE curs1;