[MySQL] Probleme de Select dans un REPEAT
Bonsoir,
Voila j utilise des procédure pour rechercher et tagger des reviews qui sont insérer dans ma base.
J ai une procédure qui est appeler lors de l insertion, et une autre lorsque la liste des tags change.
Mon problème est que ma procédure categorisation avorte sans raison avant d avoir parcouru toutes les catégories.
Le probleme vient de :
Code:
1
2
|
SELECT `id` INTO v_id FROM `Reviews` WHERE `CategoryID` = v_categoryid AND `FeedbackID` = v_feedbackid; |
car lorsque je le neutralise, la boucle parcourt bien toutes les occurrences.
Et la même requête dans recategorisation fonctionne parfaitement.
Merci de votre aide.
Code:
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
|
CREATE PROCEDURE `categorisation` (v_feedbackid INT,v_review TEXT)
BEGIN
DECLARE v_done BOOLEAN DEFAULT FALSE;
DECLARE v_categoryid INT;
DECLARE v_id INT;
DECLARE v_temp INT;
DECLARE v_result INT;
DECLARE v_list CURSOR FOR SELECT `id` FROM `Category`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
OPEN v_list;
REPEAT
FETCH v_list INTO v_categoryid;
IF NOT v_done THEN
SET v_result := AllKeywords(v_review,v_categoryid);
SELECT `id` INTO v_id FROM `Reviews` WHERE `CategoryID` = v_categoryid AND `FeedbackID` = v_feedbackid;
IF v_result = 2 THEN
IF v_id > 0 THEN
UPDATE `Reviews` SET `isCompletList` = TRUE WHERE `id` = v_id;
ELSE
INSERT INTO `Reviews` VALUES (NULL,v_feedbackid,v_categoryid,TRUE);
END IF;
ELSEIF v_result = 1 THEN
IF v_id > 0 THEN
UPDATE `Reviews` SET `isCompletList` = FALSE WHERE `id` = v_id;
ELSE
INSERT INTO `Reviews` VALUES (NULL,v_feedbackid,v_categoryid,FALSE);
END IF;
END IF;
END IF;
UNTIL v_done END REPEAT;
CLOSE v_List;
END;
$$ |
Code:
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
|
CREATE PROCEDURE `recategorisation` (v_categoryid INT)
BEGIN
DECLARE v_feedbackid INT;
DECLARE v_id INT;
DECLARE v_review TEXT;
DECLARE v_result INT;
DECLARE v_done INT DEFAULT 0;
DECLARE v_list CURSOR FOR SELECT `id`,`Review` FROM `Feedback`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
OPEN v_list;
REPEAT
FETCH v_list INTO v_feedbackid,v_review;
IF NOT v_done THEN
SET v_result := AllKeywords(v_review,v_categoryid);
SET v_id := 0;
IF v_result = 2 THEN
SELECT `id` INTO v_id FROM `Reviews` WHERE `CategoryID` = v_categoryid AND `FeedbackID` = v_feedbackid;
IF v_id > 0 THEN
UPDATE `Reviews` SET `isCompletList` = TRUE WHERE `id` = v_id;
ELSE
INSERT INTO `Reviews` VALUES (NULL,v_feedbackid,v_categoryid,TRUE);
END IF;
ELSEIF v_result = 1 THEN
SELECT `id` INTO v_id FROM `Reviews` WHERE `CategoryID` = v_categoryid AND `FeedbackID` = v_feedbackid;
IF v_id > 0 THEN
UPDATE `Reviews` SET `isCompletList` = FALSE WHERE `id` = v_id;
ELSE
INSERT INTO `Reviews` VALUES (NULL,v_feedbackid,v_categoryid,FALSE);
END IF;
END IF;
END IF;
UNTIL v_done END REPEAT;
CLOSE v_List;
END; |
