存储过程在MySql中不能正常工作？
存储过程在MySql中不能正常工作？
提问于 2013-06-03 17:08:09
我一直在调试一个SQL存储过程，它必须根据表C中存在值( ID )从表A中获取值(在我的代码ID和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指南。”
编辑:还有一个帮助，请看如何执行这个存储过程。
回答 3
Stack Overflow用户
回答已采纳
发布于 2013-06-03 17:21:00
有各种各样的小错误；
你需要一个参数列表，即使这个过程是空的；
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;只是作为一个概述，这里是完整的更新；
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//Stack Overflow用户
发布于 2013-06-03 17:19:07
在过程matlab之后您已经错过了() ...
- 和；after END IF
- Also，处理程序声明应该在任何可执行代码之前和游标declaration
- Semicolon之后设置@square= @Numb * @Numb is
因此，查询应该是这样：
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$$Stack Overflow用户
发布于 2013-06-03 17:19:35
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$$页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/16893328
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号