Discussion :

[ali9090]

Bonjour a tous,
J'ai ecriver un procedure:

Code sql :

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

1
2
CREATE DEFINER=`root`@`localhost` PROCEDURE `groups_fill`(IN country VARCHAR(45), IN nom_du_group VARCHAR(45))
...

Et il marche proprement avec select, mais je veux ajouter insert querry afin qu'il ajoute les resultats du select dans une table.
Cette table contient 4 pays, je veux que ce procedure prend automatiquement les noms de chaque pays de cette table est faire une execution, sans que je reecrire le nom de chaque pays a chaque fois.
Quelques idees ?
Merci.

[ali9090]

En d'autre termes, je peut utiliser un curseur a l'interieur du procedure pourqu'on passe le nom de chaque pays et faire le calcul ?

Code sql :

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
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
 
CREATE DEFINER=`root`@`localhost` PROCEDURE `groups_fill`(IN country VARCHAR(45))
BEGIN
	IF EXISTS(SELECT team FROM statistics WHERE `team`=country group by match_id)
	THEN
		SELECT 	team, 
				count(statistics.Round) AS 'Pld',
				(SELECT count(pts) FROM statistics WHERE pts=3 and team=country) AS 'W',
				(SELECT count(pts) FROM statistics WHERE pts=1 and team=country) AS 'D',
				(SELECT count(pts) FROM statistics WHERE pts=0 and team=country) AS 'L',
				SUM(statistics.Goals)AS 'GF', 
				(SUM(matches.`Goal Scored`) - SUM(statistics.Goals)) AS 'GA',
				(SUM(statistics.Goals)-(SUM(matches.`Goal Scored`) - SUM(statistics.Goals))) AS 'GD',
				(SELECT sum(pts) FROM statistics WHERE team=country) AS 'Pts'
		FROM statistics INNER JOIN matches
		WHERE 
		((statistics.team=matches.`Team 1` and matches.`Team 1`= country and matches.Round=statistics.Round)
		or (statistics.team=matches.`Team 2` and matches.`Team 2`= country and matches.Round=statistics.Round))
		;
	END IF;
END

En utilisant le curseur j'ai obtenu un nombre illimitee des query executer:

Code sql :

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
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
 
CREATE DEFINER=`root`@`localhost` PROCEDURE `groups_fill_with_cursor`()
BEGIN
	declare country VARCHAR(45);
	declare cur cursor for select name from teams;
	open cur;
	fetch cur into country;
 
	start_loop:loop
	-- IF EXISTS(SELECT team FROM statistics WHERE `team`=country group by match_id)
	-- THEN
		SELECT 	team, 
				count(statistics.Round) AS 'Pld',
				(SELECT count(pts) FROM statistics WHERE pts=3 and team=country) AS 'W',
				(SELECT count(pts) FROM statistics WHERE pts=1 and team=country) AS 'D',
				(SELECT count(pts) FROM statistics WHERE pts=0 and team=country) AS 'L',
				SUM(statistics.Goals)AS 'GF', 
				(SUM(matches.`Goal Scored`) - SUM(statistics.Goals)) AS 'GA',
				(SUM(statistics.Goals)-(SUM(matches.`Goal Scored`) - SUM(statistics.Goals))) AS 'GD',
				(SELECT sum(pts) FROM statistics WHERE team=country) AS 'Pts'
		FROM statistics INNER JOIN matches
		WHERE 
		((statistics.team=matches.`Team 1` and matches.`Team 1`= country and matches.Round=statistics.Round)
		or (statistics.team=matches.`Team 2` and matches.`Team 2`= country and matches.Round=statistics.Round))
		;
	-- else
		-- LEAVE start_loop;
	-- END IF;
END LOOP;
close cur;
END

Merci.