1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
| DELIMITER $$
CREATE
PROCEDURE `bdmschool`.`MajMoyennes`(IN v_idControle INT, IN v_Matricule VARCHAR)
BEGIN
DECLARE v_Note VARCHAR(5);
DECLARE v_Semestre INT DEFAULT 0;
DECLARE v_Cours INT DEFAULT 0;
DECLARE v_Moyenne VARCHAR(6);
DECLARE v_Max INT(2) default 0;
DECLARE v_Coef INT(2) default 0;
DECLARE v_Div INT(2) default 0;
DECLARE v_Matiere INT(2);
DECLARE v_Total DECIMAL(4,3) default 0;
DECLARE v_NbNC INT(2) default 0;
DECLARE v_NbEnrg INT(2) default 0;
DECLARE fincurs1 BOOLEAN DEFAULT 0;
SELECT idCours, idSemestre INTO v_Cours, v_Semestre
FROM controle WHERE idControle = v_idControle;
DECLARE curs1 CURSOR FOR
select a.Note, a.Max, c.coefficient
from notes a, controle b, type_controle c
where a.Matricule = 'v_Matricule'
and a.idControle = b.idControle
and b.idTypecontrole = c.idTypecontrole
and b.idSemestre = v_Semestre
and b.idCours = v_Cours;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fincurs1 := 1;
OPEN curs1;
FETCH curs1 INTO v_Note, v_Max, v_Coef;
WHILE (NOT fincurs) DO
IF (v_Note != 'NC') THEN
{
v_Div := v_Div + (v_Max*v_Coef)/20;
v_Total := v_Total + v_Note*v_Coef;
}
ELSE v_NbNC := v_NbNC + 1;
END IF;
v_NbEnrg := v_NbEnrg + 1;
FETCH curs1 INTO v_Note, v_Max, v_Coef;
END WHILE;
CLOSE curs1;
IF (v_NbNC = v_NbEnrg) THEN v_Moyenne := 'NC';
ELSEIF (v_Div = 0) v_Moyenne := 0;
ELSE v_Moyenne := v_Total/v_Div;
END IF;
SELECT idMatiere INTO v_Matiere
FROM cours WHERE idCours = v_Cours;
DELETE FROM moyenne_par_matiere
where idMatiere = v_Matiere
and Matricule = 'v_Matricule'
and idSemestre = v_Semestre;
INSERT INTO bdmschool.moyenne_par_matiere VALUES (v_Matiere, v_Matricule, v_Semestre, v_Moyenne);
END$$
DELIMITER ; |
Partager