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 指令.

You are missing custom delimiter instruction.

正如你所忽略的，引擎在找到第一个默认语句终止符时尝试编译语句，分号 ;.因此例外.

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屋！