mysqlstored-proceduresdatabase-cursor

Declaring 2 Cursors in a Stored Procedure | SQL Error [1338] [42000]: Cursor declaration after handler declaration


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;

Solution

  • 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;