存储过程在MySql中无法正常工作？

I have been debugging a SQL stored procedure which has to take values (in my code ID and Numb) form table A based on the values (ID) present in the Table C, then square the Numb and store it in Table B i.e. all the things ID, Numb and Square. I am not able to figure out the problem in the below code

我一直在调试一个SQL存储过程,它必须根据表C中的值(ID)获取值(在我的代码ID和Numb中)表格A,然后对Numb进行平方并将其存储在表B中,即所有的事物ID,麻木和广场。我无法在下面的代码中找出问题所在

 DELIMITER $$
    CREATE PROCEDURE matlab.squaring
     BEGIN
       DECLARE finish BOOLEAN DEFAULT 0; # <- set up initial conditions
       DECLARE square BIGINT(10);
       DECLARE ID INT(10);
       DECLARE Numb INT (10);
       DECLARE id_cur CURSOR FOR
         SELECT ID, Numb FROM A WHERE EXISTS ( SELECT ID FROM c);
         SET @square= @Numb * @Numb

           INSERT INTO B
           (
             ID ,
             Numb ,
             square
           )     values ( ID , Numb, square);


       DECLARE CONTINUE HANDLER FOR NOT FOUND SET finish = TRUE;
       OPEN id_cur;

     the_loop : LOOP
        FETCH id_cur INTO ID;

        IF finish THEN
           CLOSE id_cur;
           LEAVE the_loop;
        END IF
    END LOOP the_loop;
    END$$

When I run the stored procedure the error that pops up is "there seems to be some syntax error in your code, please refer to MYSql guide. "

当我运行存储过程时弹出的错误是“您的代码中似乎存在一些语法错误,请参阅MYSql指南。”

edit: one more help please how to execute this stored procedure.

编辑:还有一个帮助请如何执行此存储过程。

3 个解决方案

#1

There are various minor errors;

有各种小错误;

You need a parameter list, even if empty for the procedure;

您需要一个参数列表,即使该过程为空;

CREATE PROCEDURE matlab.squaring()

The continue handler needs to be right below the other declarations;

continue处理程序需要在其他声明之下;

   DECLARE id_cur CURSOR FOR
     SELECT ID, Numb FROM A WHERE EXISTS ( SELECT ID FROM c);
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET @finish = TRUE;

You forgot a semicolon;

你忘记了一个分号;

SET @square= @Numb * @Numb;

You forgot @ on the variable usages;

你在变量用法上忘记了@;

   )     values ( @ID , @Numb, @square);

You forgot a semicolon on END IF

你忘记了END IF上的分号

    END IF;

Just as an overview, here's the complete thing updated;

正如概述一样,这里有完整的更新内容;

CREATE PROCEDURE matlab.squaring()
 BEGIN
   DECLARE finish BOOLEAN DEFAULT 0; # <- set up initial conditions
   DECLARE square BIGINT(10);
   DECLARE ID INT(10);
   DECLARE Numb INT (10);
   DECLARE id_cur CURSOR FOR
     SELECT ID, Numb FROM A WHERE EXISTS ( SELECT ID FROM c);
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET @finish = TRUE;
   SET @square= @Numb * @Numb;

   INSERT INTO B
   (
     ID ,
     Numb ,
     square
   )     values ( @ID , @Numb, @square);

 OPEN id_cur;

 the_loop : LOOP
    FETCH id_cur INTO ID;

    IF finish THEN
       CLOSE id_cur;
       LEAVE the_loop;
    END IF;
END LOOP the_loop;
END//

#2

• You have missed () after PROCEDURE matlab...

你在PROCEDURE matlab之后错过了()...

• And ; after END IF

而且;在END IF之后

• Also, HANDLER declaration should be before any executable code and after CURSOR declaration

此外,HANDLER声明应该在任何可执行代码之前和CURSOR声明之后

• Semicolon after SET @square= @Numb * @Numb is needed

SET @ square = @Numb * @Numb之后需要分号

So, query should be like this:

所以,查询应该是这样的:

    DELIMITER $$
CREATE PROCEDURE matlab.squaring ()
 BEGIN
   DECLARE finish BOOLEAN DEFAULT 0; # <- set up initial conditions
   DECLARE square BIGINT(10);
   DECLARE ID INT(10);
   DECLARE Numb INT (10);
   DECLARE id_cur CURSOR FOR
     SELECT ID, Numb FROM A WHERE EXISTS ( SELECT ID FROM c);
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET finish = TRUE;
     SET @square= @Numb * @Numb;

       INSERT INTO B
       (
         ID ,
         Numb ,
         square
       )     values ( ID , Numb, square);

   OPEN id_cur;

 the_loop : LOOP
    FETCH id_cur INTO ID;

    IF finish THEN
       CLOSE id_cur;
       LEAVE the_loop;
    END IF;
END LOOP the_loop;
END$$

#3

Missed parameter brackets and semicolon at endif.

在endif错过了参数括号和分号。

DELIMITER $$
    CREATE PROCEDURE squaring()
     BEGIN
       DECLARE finish BOOLEAN DEFAULT 0; # <- set up initial conditions
       DECLARE square BIGINT(10);
       DECLARE ID INT(10);
       DECLARE Numb INT (10);
       DECLARE id_cur CURSOR FOR
       SELECT ID, Numb FROM A WHERE EXISTS ( SELECT ID FROM c);
       DECLARE CONTINUE HANDLER FOR NOT FOUND SET finish = TRUE;
       SET @square= @Numb * @Numb;

           INSERT INTO B
           (
             ID ,
             Numb ,
             square
           )     values ( ID , Numb, square);


       OPEN id_cur;

     the_loop : LOOP
        FETCH id_cur INTO ID;

        IF finish THEN
           CLOSE id_cur;
           LEAVE the_loop;
        END IF;
    END LOOP the_loop;
    END$$