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