Error creating a procedure: Cursor declaration after handler declaration

0

I'm trying to create a procedure

But I'm getting this message:

Cursor declaration after handler declaration

I'm trying to do 3 loops

DROP PROCEDURE IF EXISTS 'sp_curso_aprovados';

DELIMITER $$
    CREATE PROCEDURE 'sp_curso_aprovados'()
         BEGIN
            DECLARE done    INT DEFAULT FALSE;
           DECLARE done1   INT DEFAULT FALSE;
           DECLARE done2   INT DEFAULT FALSE;  


            DECLARE colaborador CURSOR FOR SELECT * FROM CONSULTA 1;    
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

            DECLARE aulas CURSOR FOR    SELECT * FROM CONSULTA 2;   
          DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;                                      




            CREATE TABLE #temp1 (
                id                INT(11) NOT NULL AUTO_INCREMENT,  
                cdfunc       INT(11),
                codigo        INT,
                cursomatriz  INT,
                datainclusao DATE,
                dataini       DATE,
                datafim       DATE,
                situacao      INT,
                aula              INT
                PRIMARY KEY (id)
            )   
           OPEN colaborador; -- VAMOS DAR UM LOOP NOS COLABORADORES
              BEGIN

                  loop0: LOOP
                        FETCH colaborador INTO @v_codfun; 
                        IF done THEN
                            LEAVE loop0;
                        END;




                        OPEN aulas; -- VAMOS DAR UM LOOP NAS AULAS
                            BEGIN

                               loop1: LOOP
                                  FETCH aulas INTO @v_codaula;  
                                    IF done1 THEN
                                        LEAVE loop1;
                                    END;

                                    DECLARE regtreina CURSOR FOR SELECT * FROM CONSULA3 ;
                                     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = TRUE;                                                                           


                                       OPEN regtreina; -- VAMOS DAR UM LOOP NOS REGTREINA
                                             BEGIN

                                                loop2: LOOP
                                                        FETCH regtreina INTO @v_regtreina, @v_cursomatriz, @v_dtinclusao, @v_dataini,@v_situacao, @v_aula ;  
                                                        IF done2 THEN
                                                            LEAVE loop2;
                                                        END;
                                                            INSERT INTO #temp1 
                                                            VALUES 
                                                            (NULL, @v_codfun, @v_regtreina, @v_dtinclusao, @v_dataini, @v_datafin, @v_situacao, @v_aula);

                                                END LOOP;   
                                             END          
                                        CLOSE regtreina;                                            





                                 END LOOP;
                             END                                
                        CLOSE aulas; 

                  END LOOP;           
              END;
           CLOSE colaborador; 

           SELECT * FROM #temp1;

          END $$      

DELIMITER ; 
    
asked by anonymous 16.08.2018 / 16:21

0 answers