MGzEvprM

1DROP PROCEDURE IF EXISTS coaching.retorno_espeado;
2CREATE DEFINER=`root`@`localhost` PROCEDURE `retorno_espeado`()
3BEGIN
4  DECLARE n INT DEFAULT 0;
5  DECLARE i INT DEFAULT 0;
6  DECLARE aux INT;
7  
8  DECLARE m INT DEFAULT 0;
9  DECLARE j INT DEFAULT 0;
10  DECLARE aux2 INT;
11
12  DROP TABLE IF EXISTS tmp_return;
13  CREATE TEMPORARY TABLE
14  tmp_return (
15      tipo CHAR(1) NOT NULL,
16      nome VARCHAR(255) NOT NULL,
17      autor VARCHAR(255) NULL
18  );
19
20  SELECT COUNT(*) FROM disciplinas INTO n;
21  SET i=0;
22  WHILE i<n DO
23    INSERT INTO tmp_return(tipo, nome) (SELECT "D", nome FROM disciplinas LIMIT i,1);
24    SELECT id FROM disciplinas LIMIT i,1 INTO aux;
25    
26    SELECT COUNT(*) FROM materials WHERE disciplina_id = aux INTO m;
27    SET j=0;
28    WHILE j<m DO
29      INSERT INTO tmp_return(tipo, nome, autor) (SELECT "M", nome, autor FROM materials WHERE disciplina_id = aux LIMIT j,1);
30  
31      SELECT id FROM materials WHERE disciplina_id = aux LIMIT j,1 INTO aux2;
32      INSERT INTO tmp_return(tipo, nome) (SELECT "A", tema FROM aulas WHERE material_id = aux2);
33  
34      SET j = j + 1;
35    END WHILE;
36
37    SET i = i + 1;
38  END WHILE;
39
40  SELECT * FROM tmp_return;
41End;