Pb de requete SQL avec clauses WHERE dans une procédure stockée

**CocoLeNain** · 10/06/2010, 21h50

Bonjour à tous,

J'ai créé une procédure stockée qui permet de calculer les points gagnées entre deux relevés de points. Et je me suis rendu compte que lorsque je fais un SELECT avec des clauses WHERE, certaines ne sont pas pris en compte et notemment les paramètres d'entrées de ma procédure.
Voici le code de 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
CREATE PROCEDURE `CalculatePlayerPointsDelta` (IN season INTEGER,IN isOfficial BOOLEAN)
BEGIN
	DECLARE cSize1,cSize2,i,j INTEGER DEFAULT 0;
    	DECLARE r_numLicence, r_points,oldPoints,r_oldPoints INTEGER DEFAULT NULL;
	DECLARE r_resultDate DATE;
	DECLARE r_delta INTEGER DEFAULT NULL;
 
    	DECLARE cursor1 CURSOR FOR SELECT DISTINCT numLicence FROM `Points` p WHERE `season` = season AND `isOfficial` = isOfficial ORDER BY `numLicence`;
	DECLARE cursor2 CURSOR FOR SELECT `resultDate`,`points`,`delta` FROM `Points`p WHERE `season` = season AND `isOfficial` = isOfficial AND `numLicence` = r_numLicence ORDER BY `resultDate` ASC;
	DECLARE cursor3 CURSOR FOR SELECT `points` FROM `Points`p WHERE `isOfficial` = 1 AND `numLicence` = r_numLicence ORDER BY `resultDate` DESC; 
 
	IF isOfficial = 1 THEN
		...	
	ELSE
		OPEN cursor1;
			SET cSize1 = (SELECT FOUND_ROWS());
			SET i = 1;
 
			WHILE i <= cSize1 DO
				FETCH cursor1 INTO r_numLicence;
				OPEN cursor2;
					SET cSize2 = (SELECT FOUND_ROWS());
					SET j = 1;
					SET oldPoints  = NULL;
 
					WHILE j <= cSize2 DO
						FETCH cursor2 INTO r_resultDate,r_points,r_delta;					
						IF r_delta IS NULL THEN
							IF oldPoints IS NULL THEN
								UPDATE `Points` SET  `delta` = 0 WHERE `numLicence` = r_numLicence AND `resultDate` = r_resultDate; 										
							ELSE
								UPDATE `Points` SET  `delta` = (r_points - oldPoints) WHERE `numLicence` = r_numLicence AND `resultDate` = r_resultDate;
							END IF;
						END IF;
						SET oldPoints  = r_points;
						SET j = j+1;
					END WHILE;
				CLOSE cursor2;
				SET i= i+1;				
			END WHILE;    
		CLOSE cursor1;
	END IF;
END//

Voici la description de mes tables :

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
mysql> DESC Players;DESC Seasons;DESC Points;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| numLicence   | int(11)     | NO   | PRI | NULL    | auto_increment |
| gender       | varchar(1)  | NO   |     | NULL    |                |
| nationalRank | smallint(6) | YES  |     | NULL    |                |
| rank         | smallint(6) | YES  |     | NULL    |                |
| mutation     | date        | YES  |     | NULL    |                |
| publicInfo   | tinyint(1)  | NO   |     | 1       |                |
| category     | int(11)     | NO   | MUL | 0       |                |
| season       | int(11)     | NO   | PRI | NULL    |                |
| phase        | int(11)     | NO   | PRI | NULL    |                |
| team         | int(11)     | YES  | MUL | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)
 
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| libelle      | varchar(45) | NO   |     | NULL    |                |
| currentPhase | tinyint(1)  | NO   |     | 0       |                |
+--------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
 
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| numLicence | int(11)     | NO   | PRI | NULL    |       |
| resultDate | date        | NO   | PRI | NULL    |       |
| points     | smallint(6) | NO   |     | NULL    |       |
| delta      | smallint(6) | YES  |     | NULL    |       |
| season     | int(11)     | NO   | MUL | NULL    |       |
| isOfficial | tinyint(1)  | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

Voici les résultats de l'exécution

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
mysql> CALL CalculatePlayerPointsDelta(8,0);
+------------+------------+--------+-------+--------+------------+
| numLicence | resultDate | points | delta | season | isOfficial |
+------------+------------+--------+-------+--------+------------+
|    2917470 | 2002-09-01 |   1289 |  NULL |      1 |          1 |
|    2917470 | 2003-01-01 |   1281 |  NULL |      1 |          1 |
|    2917470 | 2003-09-01 |   1365 |  NULL |      2 |          1 |
|    2917470 | 2004-01-01 |   1388 |  NULL |      2 |          1 |
|    2917470 | 2010-01-01 |   1394 |  NULL |      8 |          1 |
|    2917470 | 2010-02-13 |   1392 |  NULL |      8 |          0 |
|    2917470 | 2010-03-18 |   1398 |  NULL |      8 |          0 |
|    2917470 | 2010-04-24 |   1408 |  NULL |      8 |          0 |
|    2917470 | 2010-05-17 |   1419 |  NULL |      8 |          0 |
+------------+------------+--------+-------+--------+------------+
9 rows in set (0.00 sec)
Query OK, 0 rows affected (0.35 sec)
 
mysql> SELECT * FROM `Points`p WHERE `season` = 8 AND `isOfficial` = 0 AND `numLicence` = 2917470 ORDER BY `resultDate` ASC;
+------------+------------+--------+-------+--------+------------+
| numLicence | resultDate | points | delta | season | isOfficial |
+------------+------------+--------+-------+--------+------------+
|    2917470 | 2010-02-13 |   1392 |  NULL |      8 |          0 |
|    2917470 | 2010-03-18 |   1398 |  NULL |      8 |          0 |
|    2917470 | 2010-04-24 |   1408 |  NULL |      8 |          0 |
|    2917470 | 2010-05-17 |   1419 |  NULL |      8 |          0 |
+------------+------------+--------+-------+--------+------------+
4 rows in set (0.00 sec)

Ce que je comprend pas c'est que j'ai le bon résultat avec un copier coller du SELECT de la procédure stockée.

Je pense que ça vient de la déclaration de mes paramètres, mais je vois pas ce qui ne va pas.

Quelqu'un aurai une idée d'où cela vient ?

merci d'avance

**CocoLeNain** · 11/06/2010, 22h57

J'ai modifié ma procédure et j'ai fini par laisser un seul SELECT. Mais j'ai toujours le même résultat. Voici le code :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
DELIMITER $$
 
DROP PROCEDURE IF EXISTS `Test`.`CalculatePlayerPointsDelta`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `CalculatePlayerPointsDelta`(IN season INTEGER,IN isOfficial TINYINT)
BEGIN
 
	SELECT * FROM `Points`p WHERE `season` = 8 AND `isOfficial` = 0 AND `numLicence` = '2917470' ORDER BY `resultDate` ASC;
 
END$$
 
DELIMITER ;

Et le résultat de la commande :

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
mysql> Call CalculatePlayerPointsDelta(8,0);
+------------+------------+--------+-------+--------+------------+
| numLicence | resultDate | points | delta | season | isOfficial |
+------------+------------+--------+-------+--------+------------+
|    2917470 | 2002-09-01 |   1289 |     0 |      1 |          1 |
|    2917470 | 2003-01-01 |   1281 |    -8 |      1 |          1 |
|    2917470 | 2003-09-01 |   1365 |    84 |      2 |          1 |
|    2917470 | 2004-01-01 |   1388 |    23 |      2 |          1 |
|    2917470 | 2010-01-01 |   1394 |     6 |      8 |          1 |
|    2917470 | 2010-02-13 |   1392 |    -2 |      8 |          0 |
|    2917470 | 2010-03-18 |   1398 |     6 |      8 |          0 |
|    2917470 | 2010-04-24 |   1408 |    10 |      8 |          0 |
|    2917470 | 2010-05-17 |   1419 |    11 |      8 |          0 |
+------------+------------+--------+-------+--------+------------+
9 rows in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT `resultDate`,`points`,`delta` FROM `Points`p WHERE `season` = 8 AND `isOfficial` = 0 AND `numLicence` = '2917470' ORDER BY `resultDate` ASC;
+------------+--------+-------+
| resultDate | points | delta |
+------------+--------+-------+
| 2010-02-13 |   1392 |    -2 |
| 2010-03-18 |   1398 |     6 |
| 2010-04-24 |   1408 |    10 |
| 2010-05-17 |   1419 |    11 |
+------------+--------+-------+
4 rows in set (0.00 sec)

La je sèche vraiment. voici le code d'installation de mes tables :

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
DROP SCHEMA IF EXISTS `Test` ;
CREATE SCHEMA IF NOT EXISTS `Test` DEFAULT CHARACTER SET utf8 ;
USE `Test`;
 
-- -----------------------------------------------------
-- Table `Test`.`Seasons`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Test`.`Seasons` ;
 
CREATE  TABLE IF NOT EXISTS `Test`.`Seasons` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `libelle` VARCHAR(45) NOT NULL ,
  `currentPhase` TINYINT(1) NOT NULL DEFAULT 0 ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
 
 
-- -----------------------------------------------------
-- Table `Test`.`TeamDivisions`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Test`.`TeamDivisions` ;
 
CREATE  TABLE IF NOT EXISTS `Test`.`TeamDivisions` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `libelle` VARCHAR(45) NOT NULL ,
  `position` INT NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;
 
 
 
-- -----------------------------------------------------
-- Table `Test`.`Players`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Test`.`Players` ;
 
CREATE  TABLE IF NOT EXISTS `Test`.`Players` (
  `numLicence` INT NOT NULL AUTO_INCREMENT ,
  `gender` VARCHAR(1) NOT NULL ,
  `nationalRank` SMALLINT NULL ,
  `rank` SMALLINT NULL ,
  `mutation` DATE NULL DEFAULT NULL ,
  `publicInfo` TINYINT(1) NOT NULL DEFAULT 1 ,
  `category` INT NOT NULL DEFAULT 0 ,
  `season` INT NOT NULL ,
  `phase` INT NOT NULL ,
  `team` INT NULL DEFAULT NULL ,
  PRIMARY KEY (`numLicence`, `season`, `phase`) ,
  INDEX `fk_Players_1` (`category` ASC) ,
  INDEX `fk_Players_2` (`team` ASC) ,
  INDEX `fk_Players_3` (`season` ASC) ,
  CONSTRAINT `fk_Players_3`
    FOREIGN KEY (`season` )
    REFERENCES `Test`.`Seasons` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
 
 
-- -----------------------------------------------------
-- Table `Test`.`Points`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Test`.`Points` ;
 
CREATE  TABLE IF NOT EXISTS `Test`.`Points` (
  `numLicence` INT NOT NULL ,
  `resultDate` DATE NOT NULL ,
  `points` SMALLINT NOT NULL ,
  `delta` SMALLINT NULL ,
  `season` INT NOT NULL ,
  `isOfficial` TINYINT(1) NOT NULL ,
  PRIMARY KEY (`numLicence`, `resultDate`) ,
  INDEX `fk_Points_1` (`numLicence` ASC) ,
  INDEX `fk_Points_2` (`season` ASC) ,
  CONSTRAINT `fk_Points_1`
    FOREIGN KEY (`numLicence` )
    REFERENCES `Test`.`Players` (`numLicence` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Points_2`
    FOREIGN KEY (`season` )
    REFERENCES `Test`.`Seasons` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
 
INSERT INTO `Seasons` VALUES(1,'2002/2003',0),
			    (2,'2003/2004',0),
			    (3,'2004/2005',0),
			    (4,'2005/2006',0),
			    (5,'2006/2007',0),
			    (6,'2007/2008',0),
			    (7,'2008/2009',0),
			    (8,'2009/2010',2);
 
INSERT INTO `Players` VALUES(2917470,'H',NULL,13,'2010-06-16',1,2,8,2,NULL);
 
INSERT INTO `Points` VALUES(2917470,'2002-09-01',1289,0,1,1),
(2917470,'2003-01-01',  1281,   -8,     1,         1 ),
(2917470,'2003-09-01',  1365,   84,     2,         1 ),
(2917470,'2004-01-01',  1388,   23,     2,         1 ),
(2917470,'2010-01-01',  1394,    6,     8,         1 ),
(2917470,'2010-02-13',  1392,   -2,     8,         0 ),
(2917470,'2010-03-18',  1398,    6,     8,         0 ),
(2917470,'2010-04-24',  1408,   10,     8,         0 ),
(2917470,'2010-05-17',  1419,   11,     8,         0 );
 
DELIMITER $$
 
DROP PROCEDURE IF EXISTS `Test`.`CalculatePlayerPointsDelta`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `CalculatePlayerPointsDelta`(IN season INTEGER,IN isOfficial TINYINT)
BEGIN
 
	SELECT * FROM `Points`p WHERE `season` = 8 AND `isOfficial` = 0 AND `numLicence` = '2917470' ORDER BY `resultDate` ASC;
 
END$$
 
DELIMITER ;

**CocoLeNain** · 11/06/2010, 23h48

C'est bon je viens de trouver d'où vient mon problème. Cela vient bien des paramètres qui ont les même noms que celui des tables.
Voila la solution :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
DELIMITER $$
 
DROP PROCEDURE IF EXISTS `Test`.`CalculatePlayerPointsDelta`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `CalculatePlayerPointsDelta`(IN _season INTEGER,IN _isOfficial TINYINT)
BEGIN
 
	SELECT * FROM `Points`p WHERE `season` = _season AND `isOfficial` = _isOfficial AND `numLicence` = '2917470' ORDER BY `resultDate` ASC;
 
END$$
 
DELIMITER ;

Pb de requete SQL avec clauses WHERE dans une procédure stockée

SQL Procédural MySQL

Discussions similaires

Partager

Partager