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
0
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
0
- You have missed () after PROCEDURE matlab...
- And ; after END IF
- Also, HANDLER declaration should be before any executable code and after CURSOR declaration
- Semicolon after SET @square= @Numb * @Numb is needed
你在PROCEDURE matlab之后错过了()...
而且;在END IF之后
此外,HANDLER声明应该在任何可执行代码之前和CURSOR声明之后
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
0
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$$
#1
0
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
0
- You have missed () after PROCEDURE matlab...
- And ; after END IF
- Also, HANDLER declaration should be before any executable code and after CURSOR declaration
- Semicolon after SET @square= @Numb * @Numb is needed
你在PROCEDURE matlab之后错过了()...
而且;在END IF之后
此外,HANDLER声明应该在任何可执行代码之前和CURSOR声明之后
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
0
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$$