I'm trying to declare two Cursors in a stored procedure but it shows
SQL Error [1338] [42000]: Cursor declaration after handler declaration Cursor declaration after handler declaration Cursor declaration after handler declaration
I've tried to run both cursor individually in a separate stored procedure and it works fine but when I combined them both it says otherwise. Mind helping me to solve this issue. Thanks in advance!
My Sample query as per below
BEGIN
DECLARE is_done INT DEFAULT 0;
DECLARE is_done2 INT DEFAULT 0;
DECLARE variables VARCHAR(16383) DEFAULT "";
DECLARE variables2 VARCHAR(255) ;
DECLARE cursor1 CURSOR FOR SELECT (statement);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_done = 1;
DECLARE cursor2 CURSOR FOR SELECT (statement);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_done2 = 1;
OPEN cursor1;
loop1: LOOP
FETCH cursor1 INTO variables;
IF is_done = 1 THEN
LEAVE loop1;
END IF;
(statement)
END LOOP loop1;
CLOSE cursor1;
OPEN cursor2;
loop2: LOOP
FETCH cursor2 INTO variables2;
IF is_done2 = 1 THEN
LEAVE loop2;
END IF;
(statement)
END LOOP loop2;
CLOSE cursor2;
END;
Somehow I edited this way and it worked.
BEGIN
DECLARE is_done INT DEFAULT 0;
DECLARE variables VARCHAR(16383) DEFAULT "";
DECLARE variables2 VARCHAR(255) ;
DECLARE cursor1 CURSOR FOR SELECT (statement);
DECLARE cursor2 CURSOR FOR SELECT (statement);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_done = 1;
OPEN cursor1;
loop1: LOOP
FETCH cursor1 INTO variables;
IF is_done = 1 THEN
LEAVE loop1;
END IF;
(statement)
END LOOP loop1;
CLOSE cursor1;
SET is_done = 0;
OPEN cursor2;
loop2: LOOP
FETCH cursor2 INTO variables2;
IF is_done = 1 THEN
LEAVE loop2;
END IF;
(statement)
END LOOP loop2;
CLOSE cursor2;
END;