如何在 MySQL Workbench 中创建和执行程序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10084458/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create and execute procedures in MySQL workbench
提问by Sirisha
I created a Spatial table Points using SQL Editor in MySQL workbench. To fill this table, the following is the code I am using.
我在 MySQL 工作台中使用 SQL 编辑器创建了一个空间表 Points。为了填写这张表，以下是我正在使用的代码。
CREATE PROCEDURE fill_points(
IN size INT(10)
)
BEGIN
DECLARE i DOUBLE(10,1) DEFAULT size;
DECLARE lon FLOAT(7,4);
DECLARE lat FLOAT(6,4);
DECLARE position VARCHAR(100);
-- Deleting all.
DELETE FROM Points;
WHILE i > 0 DO
SET lon = RAND() * 360 - 180;
SET lat = RAND() * 180 - 90;
SET position = CONCAT( 'POINT(', lon, ' ', lat, ')' );
INSERT INTO Points(name, location) VALUES ( CONCAT('name_', i), GeomFromText(position) );
SET i = i - 1;
END WHILE;
END
when I executed it, it shows the error
当我执行它时，它显示错误
Error Code: 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 'END' at line 1
错误代码：1064。您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 1 行的“END”附近使用的正确语法
Executing the statement
执行语句
CALL fill_points(1000);
CALL fill_points(1000);
shows the same error
显示相同的错误
I even don't know whether the way I proceed is correct or not.
我什至不知道我进行的方式是否正确。
Can anybody help me...
有谁能够帮助我...
回答by Konerak
Have you ended the entire query? Try setting a delimiter, and use it after the END so the server knows you finished the command.
您是否已结束整个查询？尝试设置定界符，并在 END 之后使用它，以便服务器知道您已完成命令。
delimiter //
CREATE PROCEDURE fill_points(
IN size INT(10)
)
BEGIN
DECLARE i DOUBLE(10,1) DEFAULT size;
DECLARE lon FLOAT(7,4);
DECLARE lat FLOAT(6,4);
DECLARE position VARCHAR(100);
-- Deleting all.
DELETE FROM Points;
WHILE i > 0 DO
SET lon = RAND() * 360 - 180;
SET lat = RAND() * 180 - 90;
SET position = CONCAT( 'POINT(', lon, ' ', lat, ')' );
INSERT INTO Points(name, location) VALUES ( CONCAT('name_', i), GeomFromText(position) );
SET i = i - 1;
END WHILE;
END //
delimiter ;
Also, by the by
此外，由
While DELETE FROM TABLEdoes remove all data from the table, TRUNCATE tabledoes so faster. Unless you have good reasons to use DELETE(they exist), TRUNCATEmight be what you want.
虽然DELETE FROM TABLE确实从表中删除了所有数据，TRUNCATE table但这样做的速度更快。除非您有充分的理由使用DELETE（它们存在），否则TRUNCATE可能就是您想要的。
