Description:
From reference manual:
12.8.6.3. LOOP Statement
[begin_label:] LOOP
statement_list
END LOOP [end_label]
12.8.6.4. LEAVE Statement
LEAVE label
The begin_label should be optional but source command complains when is not specified with ERROR 1064 (42000)
How to repeat:
Here is how to reproduce it:
create test_loop.sql script
delimiter !!
DROP PROCEDURE IF EXISTS test_loop;
CREATE PROCEDURE test_loop(IN p_param bigint unsigned)
BEGIN
DECLARE var int DEFAULT 0;
LOOP
IF var = 0 THEN
SET var := 1;
ELSE
LEAVE;
END IF;
END LOOP;
END;
!!
delimiter ;
mysql> source test_loop.sql;
Query OK, 0 rows affected (0.00 sec)
ERROR 1064 (42000): 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 ';
END IF;
END LOOP;
END' at line 9
Now I will add as LABEL LOOPING (here it works as expected):
create test_loop.sql script
delimiter !!
DROP PROCEDURE IF EXISTS test_loop;
CREATE PROCEDURE test_loop(IN p_param bigint unsigned)
BEGIN
DECLARE var int DEFAULT 0;
LOOPING: LOOP
IF var = 0 THEN
SET var := 1;
ELSE
LEAVE LOOPING;
END IF;
END LOOP LOOPING;
END;
!!
delimiter ;
mysql> source test_loop.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Also I got error if as LABEL I use the word LOOP:
create test_loop.sql script
delimiter !!
DROP PROCEDURE IF EXISTS test_loop;
CREATE PROCEDURE test_loop(IN p_usageId bigint unsigned)
BEGIN
DECLARE var int DEFAULT 0;
LOOP: LOOP
IF var = 0 THEN
SET var := 1;
ELSE
LEAVE LOOP;
END IF;
END LOOP LOOP;
END;
!!
delimiter ;
mysql> source test_loop.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)
ERROR 1064 (42000): 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 ': LOOP
IF var = 0 THEN
SET var := 1;
ELSE
LEAV' at line 5
Suggested fix:
As workaround define a LABEL other than LOOP reserver word.
Description: From reference manual: 12.8.6.3. LOOP Statement [begin_label:] LOOP statement_list END LOOP [end_label] 12.8.6.4. LEAVE Statement LEAVE label The begin_label should be optional but source command complains when is not specified with ERROR 1064 (42000) How to repeat: Here is how to reproduce it: create test_loop.sql script delimiter !! DROP PROCEDURE IF EXISTS test_loop; CREATE PROCEDURE test_loop(IN p_param bigint unsigned) BEGIN DECLARE var int DEFAULT 0; LOOP IF var = 0 THEN SET var := 1; ELSE LEAVE; END IF; END LOOP; END; !! delimiter ; mysql> source test_loop.sql; Query OK, 0 rows affected (0.00 sec) ERROR 1064 (42000): 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 '; END IF; END LOOP; END' at line 9 Now I will add as LABEL LOOPING (here it works as expected): create test_loop.sql script delimiter !! DROP PROCEDURE IF EXISTS test_loop; CREATE PROCEDURE test_loop(IN p_param bigint unsigned) BEGIN DECLARE var int DEFAULT 0; LOOPING: LOOP IF var = 0 THEN SET var := 1; ELSE LEAVE LOOPING; END IF; END LOOP LOOPING; END; !! delimiter ; mysql> source test_loop.sql; Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Also I got error if as LABEL I use the word LOOP: create test_loop.sql script delimiter !! DROP PROCEDURE IF EXISTS test_loop; CREATE PROCEDURE test_loop(IN p_usageId bigint unsigned) BEGIN DECLARE var int DEFAULT 0; LOOP: LOOP IF var = 0 THEN SET var := 1; ELSE LEAVE LOOP; END IF; END LOOP LOOP; END; !! delimiter ; mysql> source test_loop.sql; Query OK, 0 rows affected, 1 warning (0.00 sec) ERROR 1064 (42000): 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 ': LOOP IF var = 0 THEN SET var := 1; ELSE LEAV' at line 5 Suggested fix: As workaround define a LABEL other than LOOP reserver word.