我一直在调试一个SQL存储过程,该过程必须根据表C中存在的值(ID)从表a中获取值(在我的代码ID和Numb中),然后将Numb平方并将其存储在表B中,即所有的东西ID, Numb和square。我无法解决下面代码中的问题
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$$
当我运行存储过程时,弹出的错误是"你的代码中似乎有一些语法错误,请参考MYSql指南。"
编辑:关于如何执行这个存储过程还有一个帮助。
有各种小错误;
需要一个参数列表,即使该过程为空;
CREATE PROCEDURE matlab.squaring()
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;
你忘记了一个分号;
SET @square= @Numb * @Numb;
你忘了@在变量用法上;
) values ( @ID , @Numb, @square);
你忘了在END IF上加分号
END IF;
作为概述,这里是更新的完整内容;
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//
- 您在PROCEDURE matlab…之后错过了()
- ;END IF 后同样,HANDLER声明应该在任何可执行代码和在CURSOR声明后
- SET @square= @Numb * @Numb是必需的
所以,查询应该是这样的:
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$$
缺少参数括号和分号。
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$$