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 ;