Procedure MySQL

Version imprimable

Procedure MySQL

Bonsoir,
J'ai deja un procedure sql qui marche correctement, qui sert a prend des variables d'une page PHP, et les ajoutent dans une table sql (J'utilise Mysql Workbench 6.1).
Mais il, parfois, faire insert pour la meme "row" meme s'il existe. Alors j'ai re-ecrire le meme procedure avec la condition if not exists:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 -- -------------------------------------------------------------------------------- -- Routine DDL -- Note: comments before and after the routine body will not be stored by the server -- -------------------------------------------------------------------------------- DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `note_avg`(OUT numberOfStudents INT ,OUT avg_result decimal, IN courseId INT, IN semester INT, IN classOf VARCHAR(20)) IF NOT exists( select * from average where course_id=courseId AND semester=semester AND year=classOf ) BEGIN SELECT COUNT(*) INTO numberOfStudents FROM notes WHERE course_id=courseId AND semester=semester AND year=classOf; SELECT avg(final_note) INTO avg_result FROM notes WHERE course_id=courseId AND semester=semester AND year=classOf; INSERT INTO average(course_id, average, semester, classOf, numberOfStudents) VALUES (courseId, avg_result, semester, classOf, numberOfStudents); END
L'erreur:

Citation:

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN

J'ai obtenu une erreur sur BEGIN.
Est-ce quelqu'un peut savoir ou la probleme ? Et comment je peut utiliser un curseur avec IF NOT EXISTS.
Merci.

13/05/2014, 10h28
rawsrc

Salut,

et si tu descendais le test dans le BEGIN ?
Quelque chose comme ça : IF () THEN ... ELSE ... END IF;

Mr. Je veux ce procedure d'ajouter la requette si "row n'existe pas. Else, si elle exist, je veux faire un update de cette row.
Le procedure qui marche correctement est:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
 
CREATE DEFINER=`root`@`localhost` PROCEDURE `note_avg`(OUT numberOfStudents INT ,OUT avg_result decimal, IN courseId INT, IN semester INT, IN classOf VARCHAR(20))
BEGIN
SELECT COUNT(*) INTO numberOfStudents FROM notes WHERE course_id=courseId AND semester=semester AND year=classOf;
SELECT avg(final_note) INTO avg_result FROM notes WHERE course_id=courseId AND semester=semester AND year=classOf;
INSERT INTO average(course_id, average, semester, classOf, numberOfStudents) VALUES (courseId, avg_result, semester,  classOf, numberOfStudents); 
END

Mais lorsque j'ajoute les conditions IF NOT EXIST j'obtien l'erreur sur BEGIN.
Merci.

Quelque chose dans ce genre :

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 DEFINER=`root`@`localhost` PROCEDURE `note_avg`(
    OUT numberOfStudents INT, 
    OUT avg_result DECIMAL, 
    IN  courseId INT, 
    IN  semester INT, 
    IN classOf VARCHAR(20)
)
BEGIN
    IF EXISTS (
        SELECT 1 
          FROM average 
         WHERE course_id    = courseId 
               AND semester = semester 
               AND `year`   = classOf
    ) THEN
 
        UPDATE ...
 
    ELSE
 
        SELECT COUNT(*) INTO numberOfStudents 
          FROM notes 
         WHERE course_id    = courseId 
               AND semester = semester 
               AND `year`   = classOf;
 
 
        SELECT avg(final_note) INTO avg_result 
          FROM notes 
         WHERE course_id    = courseId 
               AND semester = semester 
               AND `year`   = classOf;
 
 
        INSERT INTO average (
            course_id, 
            average, 
            semester, 
            classOf, 
            numberOfStudents
        ) VALUES (
            courseId, 
            avg_result, 
            semester,  
            classOf, 
            numberOfStudents
        ); 
 
    END IF;
END

13/05/2014, 17h42
ali9090

Citation:

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELSE

SELECT COUNT(*) INTO numberOfStudents
FROM notes

:P
13/05/2014, 18h27
rawsrc

Poste la requête complète avec le UPDATE
13/05/2014, 18h34
ali9090

C'est la seule réponse, lorsque j'utilise IF EXISTS ... IF NOT EXISTS... j'obtiens la même erreur.

Attends, est ce que tu déclares tes variables (celles après INTO) quel que part dans le code ?

EDIT : chez moi, ce code compile sans erreur :

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
BEGIN
 
    DECLARE numberOfStudents INTEGER;
    DECLARE avg_result FLOAT(9, 3);
 
    IF EXISTS (
        SELECT 1 
          FROM average 
         WHERE course_id    = courseId 
               AND semester = semester 
               AND `year`   = classOf) 
	THEN
 
        SELECT 1;
 
    ELSE
 
        SELECT COUNT(*) INTO numberOfStudents 
          FROM notes 
         WHERE course_id    = courseId 
               AND semester = semester 
               AND `year`   = classOf;
 
 
        SELECT avg(final_note) INTO avg_result 
          FROM notes 
         WHERE course_id    = courseId 
               AND semester = semester 
               AND `year`   = classOf;
 
 
        INSERT INTO average (
            course_id, 
            average, 
            semester, 
            classOf, 
            numberOfStudents
        ) VALUES (
            courseId, 
            avg_result, 
            semester,  
            classOf, 
            numberOfStudents
        ); 
 
    END IF;
END

13/05/2014, 19h57
ali9090

Merci. Le nouveau code marche sans erreur. Mais maintenant aucun insert s'effectue.