MySQL语法错误创建存储过程 [英] Mysql syntax error creating stored procedure
问题描述
这真让我发疯.
CREATE DEFINER=`root`@`localhost` PROCEDURE `CalcularCRTarea` (Id_Tarea INT, OUT crTarea decimal(12, 4))
DETERMINISTIC
BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE _id BIGINT UNSIGNED;
DECLARE cur CURSOR FOR SELECT Id FROM Tarea_Frente where Id_Item_Tarea = Id_Tarea;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;
OPEN cur;
testLoop: LOOP
FETCH cur INTO _id;
IF done THEN
LEAVE testLoop;
ELSE
CALL CalcularCRFrente(_id, @suma);
SET crTarea = crTarea + @suma;
END IF;
END LOOP testLoop;
CLOSE cur;
END;
phpmyadmin
返回我
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 '' at line 4
那将是线
DECLARE done BOOLEAN DEFAULT FALSE;
这是怎么了?我尝试了一些小的更改,但总是收到相同的错误.我检查了文档,但看不到语法错误. 我知道这是一个愚蠢的错误,但是我坚持了大约半个小时. 是一个游标,它迭代每一行并为每一行执行一个存储过程;我已经用存储函数完成了它(它的工作原理很像个魅力),但是在服务器上发布时,我看到我的共享主机不支持存储函数,只支持存储过程:(因此,这是我最好的方法找到了.
what's wrong here ? I tried with several minor changes and I'm getting always the same error. I checked the documentation and I'm not seeing what's the syntax error. I know this is a silly error but I'm stucked with this like a half an hour. Is a cursor that iterates each row and executes a stored procedure for each one; I've done it with stored functions (and it worked like a charm) but at the time to publish in the server I saw that my shared hosting doesn't suppor stored functions, only stored procedures :( So it's the best way I've found.
推荐答案
您缺少自定义delimiter
指令.
您错过了它,当引擎找到第一个默认语句终止符分号;
时,它尝试编译这些语句.因此是例外.
As you missed it, engine tried to compile the statements when it found the first default statement terminator, the semicolon ;
. And hence the exception.
将您的整个例程放在以下之间:
Place your entire routine in between:
-- define the delimiter
delimiter //
--- place here your stored procedure
-- and lastly following line
//
-- reset the delimiter
delimiter ;
这篇关于MySQL语法错误创建存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!