How to declare cursor in phpmyadmin?

Question

When I add this code I have an error and I can't able to repair it.

DECLARE c1 cursor FOR SELECT i FROM integers;
OPEN c1;
FETCH c1 INTO L1;
FETCH c1 INTO L2;
FETCH c1 INTO L3;
FETCH c1 INTO L4;
FETCH c1 INTO L5;
FETCH c1 INTO L6;
CLOSE c1;

Below is full code. Without the code, which is above, procedure executes and return six rows with no duplicate numbers from integer table, which I'd like to write to L1,L2,L3,L4,L5,L6 and later insert into table kupony. 'howMany' is IN procedure parameter.

DELIMITER $$

CREATE PROCEDURE addTickets (IN howMany INT)
BEGIN

DECLARE L1 INT default 0;
DECLARE L2 INT default 0;
DECLARE L3 INT default 0;
DECLARE L4 INT default 0;
DECLARE L5 INT default 0;
DECLARE L6 INT default 0;
DECLARE count1 INT;
set count1 = 0;


while count1 < howMany DO
DECLARE c1 FOR SELECT i FROM integers WHERE i BETWEEN 1 AND 49 ORDER BY RAND() LIMIT 6;
OPEN c1;
FETCH c1 INTO L1;
FETCH c1 INTO L2;
FETCH c1 INTO L3;
FETCH c1 INTO L4;
FETCH c1 INTO L5;
FETCH c1 INTO L6;
CLOSE c1;    
INSERT INTO kupony VALUES(NULL, 1, L1, L2, L3, L4, L5, L6, -1, '2018-01-01', -1);
set count1 = count1 + 1;
end while;

END$$

DELIMITER ;

Please share the nature of the error and/or the error message with us. — sticky bit, May 29 '18 at 20:38
#1064 - Something is wrong in your syntax next 'DECLARE c1 cursor FOR SELECT i FROM integers; OPEN c1; FETCH c1 INTO L1; FETC' in line 17 — gushy22, May 29 '18 at 21:15

sticky bit · Answer 1 · 2018-05-29T21:58:18.930

0

You have to put an BEGIN ... END; block around the nested cursor.

DELIMITER $$

CREATE PROCEDURE addTickets (IN howMany INT)
BEGIN

DECLARE L1 INT default 0;
DECLARE L2 INT default 0;
DECLARE L3 INT default 0;
DECLARE L4 INT default 0;
DECLARE L5 INT default 0;
DECLARE L6 INT default 0;
DECLARE count1 INT default 0;


while count1 < howMany DO
BEGIN
DECLARE c1 CURSOR FOR SELECT i FROM integers WHERE i BETWEEN 1 AND 49 ORDER BY RAND() LIMIT 6;
OPEN c1;
FETCH c1 INTO L1;
FETCH c1 INTO L2;
FETCH c1 INTO L3;
FETCH c1 INTO L4;
FETCH c1 INTO L5;
FETCH c1 INTO L6;
CLOSE c1;    
INSERT INTO kupony VALUES(NULL, 1, L1, L2, L3, L4, L5, L6, -1, '2018-01-01', -1);
set count1 = count1 + 1;
END;
end while;

END$$

DELIMITER ;

edited May 29 '18 at 21:58

answered May 29 '18 at 21:18

sticky bit

36,626
12
31
42

#1064 - Something is wrong in your syntax next 'DECLARE c1 FOR SELECT i FROM integers WHERE i BETWEEN 1 AND 49 ORDER BY RAND() L' in line 16 – gushy22 May 29 '18 at 21:34
@gushy22: Please edit you question and include the whole `CREATE PROCEDURE` statement. – sticky bit May 29 '18 at 21:37
@gushy22: Sorry, I guess I missed what you want to do here, so my previous edit was useless. I edited again. Hope that I understood you intentions right this time. – sticky bit May 29 '18 at 22:03
Thank You very much. It's working now. EDIT : Your previous comment was also good :) – gushy22 May 29 '18 at 22:03

How to declare cursor in phpmyadmin?

1 Answers1