How to debug a syntax error in my MySQL cursor code?

Question

I have an error with this cursor code:

delimiter//
CREATE FUNCTION updateOrder()
DETERMINISTIC
BEGIN
DECLARE done INT DEFAULT 0;
# déclare les variables qui vont accueillir les données retournées par la requête
DECLARE i_id_produit INTEGER;
DECLARE old_id_produit INTEGER;
DECLARE cpt INTEGER;
# déclare le curseur
DECLARE ccustomers CURSOR FOR SELECT id_produit
FROM produit_documentation order by id_produit;
# déclare un handler pour détecter la fin du jeu d'enregistrements
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
# ouvre le curseur
OPEN ccustomers;
REPEAT
FETCH ccustomers INTO i_id_produit;
IF i_id_produit = old_customer_id THEN
UPDATE produit_documentation SET ordre = cpt;
cpt++;
ELSE old_customer_id = i_id_produit;
END IF;
UNTIL done END REPEAT;
END
//

I'm getting this error:

Script line: 2  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 
'DETERMINISTIC
BEGIN
DECLARE done INT DEFAULT 0;
# déclare les variables qui ' at line 2

I use MySQL 5.5.

Ennael · Accepted Answer · 2011-04-21 15:57:38Z

1

You need a RETURNS clause after your CREATE FUNCTION, like this:

DELIMITER //
CREATE FUNCTION updateOrder() RETURNS varchar(255)
DETERMINISTIC
...

See this for details.

Don't forget to add a matching RETURN statement and to set your delimiter back to ; at the end. :)

ETA: Or make it a PROCEDURE instead, as in this example.

edited Apr 21, 2011 at 15:57

answered Apr 21, 2011 at 15:06

Ennael

8813 gold badges14 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to debug a syntax error in my MySQL cursor code?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related