Procédure stockée problème d'exécution

**mttwt9** · 18/12/2020, 11h09

Bonjour à tous,
J'écris une petite procédure stockée pour calculer automatiquement un classement et j'ai un problème que je ne comprends pas.
Lorsque j'exécute ma procédure par un CALL, une seule ligne est affectée et j'ai l'impression que mon curseur c_resParSerie ne renvoie plus rien après le premier parcours...
Alors que, lorsque j'exécute ma procédure avec le logiciel Debugger for MySQL, tout fonctionne correctement...

Si vous avez des idées, je suis preneur ...

Je vous mets la construction de mes tables et ma procédure dessous...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
 
CREATE TABLE Clubs(
    IdClub varchar(8) NOT NULL,
    NomClub varchar(50)NOT NULL,
    CONSTRAINT PK_Clubs PRIMARY KEY(IdClub)
);
 
CREATE TABLE Epreuves(
    IdEp int NOT NULL,
    CodeBat varchar(5)NOT NULL,
    NomEp varchar(50)NULL,
    DateEp date NULL, 
    ClubOrga varchar(5),
    CoId int NULL,
    NbInscrits int NULL,
    CONSTRAINT PK_Epreuves PRIMARY KEY(IdEp)
);
 
ALTER TABLE Epreuves ADD CONSTRAINT FK_Epreuve_ClubOrga FOREIGN KEY (ClubOrga)  
REFERENCES Clubs (IdClub);
 
CREATE TABLE Inscrits(
    Licence varchar(8)NOT NULL,
    Nom varchar(50)NULL,
    Prenom varchar(50)NULL,
    Club varchar(8) NULL,
    Sexe char(1)NULL,
    CONSTRAINT PK_Inscrits PRIMARY KEY(Licence)
);
 
ALTER TABLE Inscrits ADD CONSTRAINT FK_Club_Inscrits FOREIGN KEY(Club)
REFERENCES Clubs(IdClub);
 
CREATE TABLE Participe(
    Licence varchar(8)NOT NULL,
    NbEprCour int,
    CodeBat varchar(5)NOT NULL,
    CONSTRAINT PK_Participe PRIMARY KEY(Licence)
);
 
ALTER TABLE Participe ADD CONSTRAINT FK_Participe_Licence FOREIGN KEY(Licence)REFERENCES Inscrits(
    Licence
);
 
CREATE TABLE Resultats(
    Licence varchar(8)NOT NULL,
    IdEp int NOT NULL,
    Rang int NOT NULL
);
 
ALTER TABLE Resultats ADD CONSTRAINT Pk_Resultats PRIMARY KEY(Licence, IdEp);
 
ALTER TABLE Resultats ADD CONSTRAINT FK_Resultats_Licence FOREIGN KEY(Licence)REFERENCES Inscrits(
    Licence
);
 
ALTER TABLE Resultats ADD CONSTRAINT FK_Resultats_EpId FOREIGN KEY(IdEp)REFERENCES Epreuves(
    IdEp
);
 
CREATE TABLE GEN21(
    Licence varchar(8)NOT NULL,
    Rang int NOT NULL,
    CodeBat varchar(5)NOT NULL,
    Retrait int NOT NULL DEFAULT 0,
    CONSTRAINT PK_GEN21 PRIMARY KEY(Licence)
);
 
ALTER TABLE GEN21 ADD CONSTRAINT FK_GEN21_Licence FOREIGN KEY(Licence)REFERENCES Inscrits(
    Licence
);
 
INSERT INTO Epreuves(IdEp, NomEp, CodeBat)VALUES(1,'1', 'BAT');
INSERT INTO Epreuves(IdEp, NomEp, CodeBat)VALUES(2,'2', 'BAT');
INSERT INTO Epreuves(IdEp, NomEp, CodeBat)VALUES(3,'3', 'BAT');
INSERT INTO Epreuves(IdEp, NomEp, CodeBat)VALUES(4,'4', 'BAT');
INSERT INTO Epreuves(IdEp, NomEp, CodeBat)VALUES(5,'5', 'BAT');
INSERT INTO Epreuves(IdEp, NomEp, CodeBat)VALUES(6,'6', 'BAT');
INSERT INTO Epreuves(IdEp, NomEp, CodeBat)VALUES(7,'7', 'BAT');
 
INSERT INTO Inscrits(licence)VALUES('1');
INSERT INTO Inscrits(licence)VALUES('2');
INSERT INTO Inscrits(licence)VALUES('3');
 
INSERT INTO Resultats(idep, licence, rang)VALUES(1, '1', 1);
INSERT INTO Resultats(idep, licence, rang)VALUES(1, '2', 2);
INSERT INTO Resultats(idep, licence, rang)VALUES(1, '3', 3);
INSERT INTO Resultats(idep, licence, rang)VALUES(2, '1', 1);
INSERT INTO Resultats(idep, licence, rang)VALUES(2, '2', 2);
INSERT INTO Resultats(idep, licence, rang)VALUES(2, '3', 3);
INSERT INTO Resultats(idep, licence, rang)VALUES(3, '2', 1);
INSERT INTO Resultats(idep, licence, rang)VALUES(3, '3', 2);
INSERT INTO Resultats(IdEp, Licence, Rang) VALUES('4','1','1');
INSERT INTO Resultats(IdEp, Licence, Rang) VALUES('4','2','2');
INSERT INTO Resultats(IdEp, Licence, Rang) VALUES('4','3','3');
INSERT INTO Resultats(IdEp, Licence, Rang) VALUES('5','1','1');
INSERT INTO Resultats(IdEp, Licence, Rang) VALUES('5','2','2');
INSERT INTO Resultats(IdEp, Licence, Rang) VALUES('5','3','3');
INSERT INTO Resultats(IdEp, Licence, Rang) VALUES('6','1','1');
INSERT INTO Resultats(IdEp, Licence, Rang) VALUES('6','2','2');
INSERT INTO Resultats(IdEp, Licence, Rang) VALUES('6','3','3');
INSERT INTO Resultats(IdEp, Licence, Rang) VALUES('7','1','1');
INSERT INTO Resultats(IdEp, Licence, Rang) VALUES('7','2','2');
INSERT INTO Resultats(IdEp, Licence, Rang) VALUES('7','3','3');

Et la procédure

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
 
DELIMITER //
DROP PROCEDURE IF EXISTS classement//
CREATE PROCEDURE classement	(IN p_bateau varchar(50))
BEGIN
 
-- =============================================================
-- ======= DECLARATIONS ========================================
-- =============================================================
 
-- VARIABLES --
 
	DECLARE v_lic char(8);				/*Var licence*/
	DECLARE v_nbpart int;				/*Var nb épreuves courues*/
	DECLARE v_rang int;					/*Rang sans DNC*/
	DECLARE v_totalepr int;				/*nb d'épreuves total*/
	DECLARE v_message varchar(50);		
	DECLARE v_epreuve int;				-- Id Epreuve 
	DECLARE v_inscrits int;				-- nombre de coureurs par course
	DECLARE v_penalite int;				-- penalité pour DNC
	DECLARE v_pirecourse int;			-- Rang le plus grand dans la table résultats
	DECLARE v_pirepenalite int; 		-- Penalité la plus élevée
	DECLARE v_pirepenalite2 int; 		-- 2e penalité la plus élevée
	DECLARE v_retrait int;				-- Retrait précédent
	DECLARE done INT DEFAULT FALSE; 	-- Pour bouclage curseurs
 
 
-- CURSEURS --
	DECLARE c_part CURSOR FOR 			/*Curseur pour nb participation*/
		SELECT Licence, Count(Licence) As Particip FROM Resultats 
		INNER JOIN Epreuves
		On Resultats.IdEp=Epreuves.IdEp
		Where Epreuves.CodeBat=p_bateau
		Group By Licence;
 
	DECLARE c_resParSerie CURSOR FOR 			/*Curseur liste résultats par code bateau et somme par licence*/
		SELECT Licence, Sum(Rang) As Classement FROM Resultats 
		INNER JOIN Epreuves
		WHERE Resultats.IdEp=Epreuves.IdEp AND
			Epreuves.CodeBat=p_bateau
		Group By Licence
		Order by Classement ASC;
 
	DECLARE c_listeEpr CURSOR FOR 		/*Curseur liste des épreuves*/
		SELECT DISTINCT IdEp FROM Epreuves;
 
 
	DECLARE c_EprNonCourues CURSOR FOR		-- Curseur liste épreuves dans Epreuves mais pas dans Résultat(Licence)
		SELECT IdEp FROM Epreuves 
		WHERE CodeBat=p_bateau 
			AND IdEp NOT IN 
				(SELECT IdEp FROM Resultats 
				WHERE Licence=v_lic);	/*Sauf les id qui sont déjà dans résultats*/
 
 
	DECLARE c_LicGen CURSOR FOR 	-- Curseur licences du général /bateau
		SELECT Licence FROM GEN21 
		WHERE CodeBat=p_bateau;
 
 
 
	-- HANDLER --
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- Déclencheur pour sortir des boucles des curseurs
 
 
	DELETE FROM Participe WHERE CodeBat = p_bateau;
	DELETE FROM GEN21 WHERE CodeBat = p_bateau;
 
 
	/*==========================================--
	--  Inser. Nb participation dans Participe --
	--==========================================*/
 
	OPEN c_part;
 
    loopc_part: LOOP
		FETCH FROM c_part INTO v_lic, v_nbpart;
        IF done THEN 
			LEAVE loopc_part;
		END IF;
		INSERT INTO Participe(Licence, NbEprCour, CodeBat) VALUES (v_lic, v_nbpart, p_bateau);
	END LOOP;
	CLOSE c_part;
	SET done = FALSE;
 
	/*===================================--
	--Nombre de coureurs par épreuve--
	--===================================*/
 
	OPEN c_listeEpr;
    loopc_listeEpr: LOOP 
		FETCH FROM c_listeEpr INTO v_epreuve;
        IF done THEN 
			LEAVE loopc_listeEpr;
		END IF;
		UPDATE Epreuves
			SET NbInscrits = (SELECT Count(Licence) FROM Resultats WHERE IdEp = v_epreuve) WHERE IdEp = v_epreuve;
	END LOOP;
	CLOSE c_listeEpr;
	SET done = FALSE;
 
 
	-- =====================================
	-- Calcul général avec pénalités abs --
	-- =====================================
 
	OPEN c_resParSerie;
 
	loopc_resParSerie: LOOP
		FETCH FROM c_resParSerie INTO v_lic, v_rang;	-- Calcul général /lic (/bat)
        IF done THEN 
			LEAVE loopc_resParSerie;
		END IF;
		SELECT NbEprCour INTO v_nbpart FROM Participe WHERE Licence = v_lic;		/*Nombre d'épreuves courues /lic*/
		SELECT COUNT(IdEp) INTO v_totalepr FROM Epreuves WHERE CodeBat=p_bateau;	/*Nombre d'épreuves validées /bat*/
 
		-- ANCHOR remplace nbpart par totalepr si courses validées 							-- ======================== --
		IF v_nbpart = v_totalepr THEN	-- SANS PENALITE D'ABSCENCE --
										-- ======================== --
			INSERT INTO GEN21(Licence, Rang, CodeBat) VALUES (v_lic, v_rang, p_bateau); /*Pas de pénalité*/
			-- ----------------------- --
			-- DISCARDS SANS PENALITES --
			-- ----------------------- --
 
			IF v_nbpart >= 3 THEN	-- RETRAIT DE L'EPREUVE LA PLUS MAUVAISE
 
				SELECT Rang INTO v_pirecourse FROM Resultats WHERE Licence = v_lic AND 	-- Détermine et enregistre rang le plus grand
					IdEp IN (SELECT IdEp FROM Epreuves WHERE CodeBat=p_bateau)
					ORDER BY Rang DESC
					LIMIT 1; -- Limite le résulats à 1 ligne
 
				UPDATE GEN21 SET 
					Retrait = v_pirecourse
					WHERE Licence = v_lic AND CodeBat = p_bateau;
 
				UPDATE GEN21 SET 
					Rang = (v_rang - v_pirecourse)
					WHERE Licence = v_lic AND CodeBat = p_bateau;
 
			END IF;
			IF v_nbpart >= 5 THEN	-- RETRAIT DE LA 2e EPREUVE LA PLUS MAUVAISE 
 
				SELECT Rang INTO v_pirecourse FROM Resultats WHERE Licence = v_lic AND 	-- Détermine et enregistre rang le plus grand
					IdEp IN (SELECT IdEp FROM Epreuves WHERE CodeBat=p_bateau)
					ORDER BY Rang DESC
					LIMIT 1, 1; -- OFFSET 1 et LIMITE LE NB DE LIGNE à 1
 
				SELECT Retrait INTO v_retrait FROM GEN21 WHERE Licence = v_lic;
 
				UPDATE GEN21 SET 
					Retrait = (v_pirecourse + v_retrait)
					WHERE Licence = v_lic AND CodeBat = p_bateau;
 
				UPDATE GEN21 SET 
					Rang = (v_rang - (v_pirecourse + v_retrait))
					WHERE Licence = v_lic AND CodeBat = p_bateau;
			END IF;
									-- ======================================== --
        ELSE 						-- CALCUL RANG & PENALITES POUR LES ABSENTS --
			SET v_penalite = 0;		-- ======================================== --
			SET v_pirepenalite = 0;
			SET v_pirepenalite2 = 0;
 
			OPEN c_EprNonCourues;
			loopc_EprNonCourues: LOOP	-- Boucle sur les épreuves qui n'ont pas été courues /licence
				FETCH FROM c_EprNonCourues INTO v_epreuve;
                IF done THEN 
					LEAVE loopc_EprNonCourues;
				END IF;
 
				SELECT COUNT(Licence) INTO v_inscrits FROM Resultats WHERE IdEp = v_epreuve;	/*Nombre d'inscrits dans l'épreuve*/
				SET v_penalite = v_penalite + v_inscrits + 3;
 
				IF v_pirepenalite < (v_inscrits + 3) THEN
					SET v_pirepenalite = (v_inscrits + 3);
				END IF;
			END LOOP;
 
			Close c_EprNonCourues;
			SET done = FALSE;
 
			SELECT NbInscrits INTO v_pirepenalite2 FROM Epreuves 
			WHERE CodeBat='BAT' 
				AND IdEp NOT IN 
					(SELECT IdEp FROM Resultats 
					WHERE Licence='1')
			ORDER BY NbInscrits DESC 
			LIMIT 1,1;
 
			SET v_pirepenalite2 = v_pirepenalite2 + 3;
 
			SET v_rang=v_rang + v_penalite;
			INSERT INTO GEN21(Licence, Rang, CodeBat) VALUES (v_lic, v_rang, p_bateau); /*Ajout des penalité*/
 
				-- ================================== --
				-- Traitement DISCARDS avec pénalités --
				-- ================================== --
 
			IF v_nbpart >= 3 THEN	-- RETRAIT 1re EPREUVE PLUS MAUVAISE
 
				SELECT Rang INTO v_pirecourse FROM Resultats WHERE Licence = v_lic AND 	-- Détermine et enregistre rang le plus grand
					IdEp IN (SELECT IdEp FROM Epreuves WHERE CodeBat=p_bateau)
					ORDER BY Rang DESC
					LIMIT 1; -- Limite le résulats à 1 ligne
 
				IF v_pirecourse >= v_pirepenalite THEN	-- rang course > penalité 
					UPDATE GEN21 SET 
						Retrait = v_pirecourse
						WHERE Licence = v_lic AND CodeBat = p_bateau;
 
					UPDATE GEN21 SET 
						Rang = (v_rang - v_pirecourse)
						WHERE Licence = v_lic AND CodeBat = p_bateau;
 
				ELSE 								-- penalité > rang course
					UPDATE GEN21 SET 
						Retrait = v_pirepenalite
						WHERE Licence = v_lic AND CodeBat = p_bateau;
 
					UPDATE GEN21 SET 
						Rang = (v_rang - v_pirepenalite)
						WHERE Licence = v_lic AND CodeBat = p_bateau;
				END IF;
			END IF;
 
			IF v_nbpart >= 5 THEN	-- RETRAIT DE LA 2e EPREUVE LA PLUS MAUVAISE 
 
				SELECT Rang INTO v_pirecourse FROM Resultats WHERE Licence = v_lic AND 	-- Détermine et enregistre rang le plus grand
					IdEp IN (SELECT IdEp FROM Epreuves WHERE CodeBat=p_bateau)
					ORDER BY Rang DESC
					LIMIT 1, 1; -- OFFSET 1 et LIMITE LE NB DE LIGNE à 1
 
				SELECT Retrait INTO v_retrait FROM GEN21 WHERE Licence = v_lic;
 
				IF v_pirecourse >= v_pirepenalite2  OR v_pirepenalite2 = 3 THEN	-- rang course > penalité2 
					UPDATE GEN21 SET 
						Retrait = (v_pirecourse + v_retrait)
						WHERE Licence = v_lic AND CodeBat = p_bateau;
 
					UPDATE GEN21 SET 
						Rang = (v_rang - (v_pirecourse + v_retrait))
						WHERE Licence = v_lic AND CodeBat = p_bateau;
 
				ELSE 								-- penalité > rang course
					UPDATE GEN21 SET 
						Retrait = (v_retrait + v_pirepenalite2)
						WHERE Licence = v_lic AND CodeBat = p_bateau;
 
					UPDATE GEN21 SET 
						Rang = (v_rang - (v_retrait + v_pirepenalite2))
						WHERE Licence = v_lic AND CodeBat = p_bateau;
 
				END IF;
			END IF;
 
 
		END IF;
	END LOOP;
	Close c_resParSerie;
	SET done = FALSE;
 
END
//
DELIMITER ;

**Artemus24** · 19/12/2020, 15h10

Salut mttwt9.

Il y a plusieurs choses qui ne vont pas avec votre procédure stockée.

1) les commentaires en mysql commencent par "--".
Pourquoi mettez-vous /* ... */ qui sont les commentaires en c ?

2) Une boucle ne s'écrit pas ainsi. C'est la vieille méthode avec des étiquettes, qui n'a plus lieu d'être.

On utilise pour cela la structure while do ... end while.
--> https://dev.mysql.com/doc/refman/8.0/en/while.html

3) En lisant votre procédure, je pense que ce n'est pas la bonne méthode pour modifier vos tables.
Je prends comme exemple ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
	DECLARE c_part CURSOR FOR 			/*Curseur pour nb participation*/
		SELECT Licence, Count(Licence) As Particip FROM Resultats 
		INNER JOIN Epreuves
		On Resultats.IdEp=Epreuves.IdEp
		Where Epreuves.CodeBat=p_bateau
		Group By Licence;

puis le traitement :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
	OPEN c_part;
 
	loopc_part: LOOP
		FETCH FROM c_part INTO v_lic, v_nbpart;
		IF done THEN
			LEAVE loopc_part;
		END IF;
		INSERT INTO Participe(Licence, NbEprCour, CodeBat) VALUES (v_lic, v_nbpart, p_bateau);
	END LOOP;
	CLOSE c_part;
	SET done = FALSE;

Si je reprends la même façon de faire, voici comment écrire la requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
INSERT INTO  `participe` (`Licence`,`nbEprCour`,`CodeBat`)
     SELECT      Licence,
                 Count(Licence) As Particip,
                 code_bateau
 
       FROM  Resultats
 
 INNER JOIN  Epreuves
         ON  Resultats.IdEp=Epreuves.IdEp
 
      Where  Epreuves.CodeBat=p_bateau
 
   Group By  Licence;

Il faut, bien sûr, renseigner sous forme de constante, le code bateau.
Comme vous le constatez, pas besoin de faire une procédure stockée pour gérer cela.

4) même remarque pour la requête "update". Voici un exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
    update  `contrat`    as c
inner join  `evolution`  as e
        on  e.CodContrat = c.CodContrat
 
       set  c.SalaireMensuel = e.SalaireActuel + e.EvolutionAjout
     where  c.CodContrat = 125;

Je m'arrête là pour l'instant.

@+

**mttwt9** · 19/12/2020, 16h01

Bonjour Artemus24,
Merci pour ta réponse, je ne savais pas que l'on pouvait faire une insertion avec une jointure. C'est super !
J'ai changé mon fusil d'épaule pour faire les calculs avec PHP.
Cela dit, le comportement lors de l'exécution sur le débuggeur reste un mystère...

**Artemus24** · 20/12/2020, 06h23

Salut mttwt9.

Il y d'autres problèmes :

1) il faut utiliser une clef technique comme clef primaire, et non mettre un varchar(...).
Si l'information dans le varchar(...) est importante, alors créer une colonne de nom code.
Mais c'est par la clef technique qu'il faut accéder à la table.

2) il n'est pas nécessaire d'utiliser une procédure stockée pour gérer le traitement.
J'ai utilisé que des requêtes afin de te montrer que l'on peut faire autrement.

3) Dans mon exemple, le seul paramètre est @bateau.
Cela peut se gérer sans passer par une procédure stockée.
@bateau est une variable qui est local à la session.
Cela me permet de la passer à toutes les requêtes où ce paramètre est nécessaire.

4) je trouve que tes traitements sont complexes.
Je prends la dernière requête où la mise à jour des colonnes rang et retrait dépend de la colonne nbEprCour.
Est-ce judicieux de procéder ainsi ?

Voici ce que je te propose :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
--------------
START TRANSACTION
--------------
 
--------------
set session collation_connection = "latin1_general_ci"
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
CREATE TABLE `club`
( `idClub`    integer unsigned NOT NULL auto_increment primary key,
  `codeClub`  varchar(08)      NOT NULL,
  `nomClub`   varchar(50)      NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `club` (`codeClub`,`nomClub`) values
  ('C01', 'Club N° Un'),
  ('C02', 'Club N° Deux'),
  ('C03', 'Club N° Trois')
--------------
 
--------------
select * from `club`
--------------
 
+--------+----------+---------------+
| idClub | codeClub | nomClub       |
+--------+----------+---------------+
|      1 | C01      | Club N° Un    |
|      2 | C02      | Club N° Deux  |
|      3 | C03      | Club N° Trois |
+--------+----------+---------------+
--------------
CREATE TABLE `epreuve`
( `idEp`       integer unsigned NOT NULL auto_increment primary key,
  `codeBat`    varchar(5)       NOT NULL,
  `nomEp`      varchar(50)          NULL,
  `dateEp`     date                 NULL,
  `clubOrga`   integer unsigned     NULL,
  `coId`       integer unsigned     NULL,
  `nbInscrit`  integer unsigned     NULL,
  CONSTRAINT `FK_01` FOREIGN KEY (`clubOrga`) REFERENCES `club` (`idClub`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `epreuve` (`nomEp`,`codeBat`,`clubOrga`) VALUES
  ('nom 1', 'BAT', 1),  ('nom 2', 'BAT', 1),
  ('nom 3', 'BAT', 2),  ('nom 4', 'BAT', 2),
  ('nom 5', 'BAT', 3),  ('nom 6', 'BAT', 3),
  ('nom 7', 'BAT', 1)
--------------
 
--------------
select * from `epreuve`
--------------
 
+------+---------+-------+--------+----------+------+-----------+
| idEp | codeBat | nomEp | dateEp | clubOrga | coId | nbInscrit |
+------+---------+-------+--------+----------+------+-----------+
|    1 | BAT     | nom 1 | NULL   |        1 | NULL |      NULL |
|    2 | BAT     | nom 2 | NULL   |        1 | NULL |      NULL |
|    3 | BAT     | nom 3 | NULL   |        2 | NULL |      NULL |
|    4 | BAT     | nom 4 | NULL   |        2 | NULL |      NULL |
|    5 | BAT     | nom 5 | NULL   |        3 | NULL |      NULL |
|    6 | BAT     | nom 6 | NULL   |        3 | NULL |      NULL |
|    7 | BAT     | nom 7 | NULL   |        1 | NULL |      NULL |
+------+---------+-------+--------+----------+------+-----------+
--------------
CREATE TABLE `inscrit`
( `licence`   integer unsigned  NOT NULL auto_increment primary key,
  `nom`       varchar(50)           NULL,
  `prenom`    varchar(50)           NULL,
  `club`      integer unsigned      NULL,
  `sexe`         char(1)            NULL,
  CONSTRAINT `FK_02`FOREIGN KEY (`club`) REFERENCES `club` (`idClub`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `inscrit`(`nom`,`prenom`,`club`,`sexe`) values
  ('nom 1', 'prénom 1', 1, 'h'),
  ('nom 2', 'prénom 2', 2, 'f'),
  ('nom 3', 'prénom 3', 3, 'h')
--------------
 
--------------
select * from `inscrit`
--------------
 
+---------+-------+----------+------+------+
| licence | nom   | prenom   | club | sexe |
+---------+-------+----------+------+------+
|       1 | nom 1 | prénom 1 |    1 | h    |
|       2 | nom 2 | prénom 2 |    2 | f    |
|       3 | nom 3 | prénom 3 |    3 | h    |
+---------+-------+----------+------+------+
--------------
CREATE TABLE `participe`
( `licence`    integer unsigned  NOT NULL primary key,
  `nbEprCour`  integer unsigned  NOT NULL,
  `codeBat`    varchar(5)        NOT NULL,
  CONSTRAINT `FK_03` FOREIGN KEY (`licence`) REFERENCES `inscrit` (`licence`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
select * from `participe`
--------------
 
--------------
CREATE TABLE `resultat`
( `licence`  integer unsigned  NOT NULL,
  `idEp`     integer unsigned  NOT NULL,
  `rang`     integer unsigned  NOT NULL,
  primary key (`licence`,`idEp`),
  CONSTRAINT `FK_04` FOREIGN KEY (`licence`) REFERENCES `inscrit` (`licence`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_05` FOREIGN KEY (`idEp`)    REFERENCES `epreuve` (`idEp`)    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `resultat`(`idep`,`licence`,`rang`) VALUES
  (1, 1, 1), (1, 2, 2), (1, 3, 3),
  (2, 1, 1), (2, 2, 2), (2, 3, 3),
  (3, 2, 1), (3, 3, 2),
  (4, 1, 1), (4, 2, 2), (4, 3, 3),
  (5, 1, 1), (5, 2, 2), (5, 3, 3),
  (6, 1, 1), (6, 2, 2), (6, 3, 3),
  (7, 1, 1), (7, 2, 2), (7, 3, 3)
--------------
 
--------------
select * from `resultat`
--------------
 
+---------+------+------+
| licence | idEp | rang |
+---------+------+------+
|       1 |    1 |    1 |
|       1 |    2 |    1 |
|       1 |    4 |    1 |
|       1 |    5 |    1 |
|       1 |    6 |    1 |
|       1 |    7 |    1 |
|       2 |    1 |    2 |
|       2 |    2 |    2 |
|       2 |    3 |    1 |
|       2 |    4 |    2 |
|       2 |    5 |    2 |
|       2 |    6 |    2 |
|       2 |    7 |    2 |
|       3 |    1 |    3 |
|       3 |    2 |    3 |
|       3 |    3 |    2 |
|       3 |    4 |    3 |
|       3 |    5 |    3 |
|       3 |    6 |    3 |
|       3 |    7 |    3 |
+---------+------+------+
--------------
CREATE TABLE GEN21
( `licence`  integer unsigned  NOT NULL primary key,
  `rang`     integer unsigned  NOT NULL,
  `codeBat`  varchar(5)        NOT NULL,
  `retrait`  integer unsigned  NOT NULL DEFAULT 0,
  CONSTRAINT `FK_06` FOREIGN KEY (`licence`) REFERENCES `inscrit` (`licence`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
select * from `gen21`
--------------
 
--------------
SET @bateau = 'BAT'
--------------
 
--------------
DELETE FROM `participe` WHERE `codeBat` = @bateau
--------------
 
--------------
DELETE FROM `gen21`     WHERE `codeBat` = @bateau
--------------
 
--------------
insert into `participe` (`licence`,`nbEprCour`,`codeBat`)
    select  `licence`,
            count(`licence`) as nvEprCour,
            @bateau          as codeBat
 
      from  `resultat` as t1
 
INNER JOIN  `epreuve`  as t2
        on  t2.`idEp`= t1.`idEp`
 
     where  t2.codeBat = @bateau
  group by  `licence`
--------------
 
--------------
select * from `participe`
--------------
 
+---------+-----------+---------+
| licence | nbEprCour | codeBat |
+---------+-----------+---------+
|       1 |         6 | BAT     |
|       2 |         7 | BAT     |
|       3 |         7 | BAT     |
+---------+-----------+---------+
--------------
update      `epreuve`   as e
 
inner join  (   select  idEp,
                        count(licence) as qte
                  from  `resultat`
              group by  idEp
            ) as r
        on  r.idEp = e.idEp
 
       set  e.nbInscrit = r.qte
--------------
 
--------------
select * from `epreuve`
--------------
 
+------+---------+-------+--------+----------+------+-----------+
| idEp | codeBat | nomEp | dateEp | clubOrga | coId | nbInscrit |
+------+---------+-------+--------+----------+------+-----------+
|    1 | BAT     | nom 1 | NULL   |        1 | NULL |         3 |
|    2 | BAT     | nom 2 | NULL   |        1 | NULL |         3 |
|    3 | BAT     | nom 3 | NULL   |        2 | NULL |         2 |
|    4 | BAT     | nom 4 | NULL   |        2 | NULL |         3 |
|    5 | BAT     | nom 5 | NULL   |        3 | NULL |         3 |
|    6 | BAT     | nom 6 | NULL   |        3 | NULL |         3 |
|    7 | BAT     | nom 7 | NULL   |        1 | NULL |         3 |
+------+---------+-------+--------+----------+------+-----------+
--------------
INSERT INTO `gen21` (`licence`,`rang`,`codeBat`)
  select      licence,
              sum(rang) as rang,
              @bateau   as codBat
 
        from  `resultat` as r
 
  inner join  `epreuve`  as e
 
       where  e.idEp = r.idEp
         and  e.codeBat = @bateau
 
    group by  licence
    order by  rang asc
--------------
 
--------------
select * from `gen21`
--------------
 
+---------+------+---------+---------+
| licence | rang | codeBat | retrait |
+---------+------+---------+---------+
|       1 |    6 | BAT     |       0 |
|       2 |   13 | BAT     |       0 |
|       3 |   20 | BAT     |       0 |
+---------+------+---------+---------+
--------------
update `gen21` as g
 
inner join  (  select      r.licence,
                           r.rang
 
                     from  `resultat` as r
 
               inner join  `epreuve` as e
                       on  e.codeBat = @bateau
 
                    where  r.idEp = e.idEp
                      and  r.rang = (  select  max(rang)
                                         from  `resultat` as x
                                        where  x.idEp    = r.idEp
                                    )
            )  as r
        on  r.licence = g.licence
 
inner join  (  select  licence,
                       nbEprCour
 
                 from  `participe`
            )  as p
        on  p.licence = r.licence
 
       set  g.`rang`    = case when p.nbEprCour >= 3 and p.nbEprCour < 5 then g.`rang` - r.`rang`
                               when p.nbEprCour >= 5                     then g.`rang` - r.`rang` + g.`retrait`
                                                                         else g.`rang`                          end,
            g.`retrait` = case when p.nbEprCour >= 3 and p.nbEprCour < 5 then            r.`rang`
                               when p.nbEprCour >= 5                     then            r.`rang` + g.`retrait`
                                                                         else                       g.`retrait` end
 
     where  g.codeBat    = @bateau
--------------
 
--------------
select * from `gen21`
--------------
 
+---------+------+---------+---------+
| licence | rang | codeBat | retrait |
+---------+------+---------+---------+
|       1 |    6 | BAT     |       0 |
|       2 |   13 | BAT     |       0 |
|       3 |   17 | BAT     |       3 |
+---------+------+---------+---------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

A toi de continuer les modifications.
Si tu as des problèmes, tu peux me les soumettre.

@+

Procédure stockée problème d'exécution [MySQL-8.0]

SQL Procédural MySQL

Discussions similaires

Partager

Partager