如何在MySQL工作台中创建和执行过程 [英] How to create and execute procedures in MySQL workbench

问题描述

我在MySQL工作台中使用SQL编辑器创建了一个空间表点.要填写此表， 以下是我正在使用的代码.

I created a Spatial table Points using SQL Editor in MySQL workbench. To fill this table, the following is the code I am using.

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

错误代码:1064.您的SQL语法有错误；检查 对应于您的MySQL服务器版本的手册 在第1行的"END"附近使用正确的语法

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

执行语句

CALL fill_points(1000);

显示相同的错误

我什至不知道我的前进方式是否正确.

I even don't know whether the way I proceed is correct or not.

任何人都可以帮助我...

Can anybody help me...

推荐答案

您是否结束了整个查询?尝试设置定界符，并在END之后使用它，以便服务器知道您已完成命令.

Have you ended the entire query? Try setting a delimiter, and use it after the END so the server knows you finished the command.

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 ;

此外，by

DELETE FROM TABLE确实从表中删除了所有数据，而TRUNCATE table这样做的速度更快.除非您有充分的理由使用DELETE(它们存在)，否则TRUNCATE可能就是您想要的.

Also, by the by

While DELETE FROM TABLE does remove all data from the table, TRUNCATE table does so faster. Unless you have good reasons to use DELETE (they exist), TRUNCATE might be what you want.

这篇关于如何在MySQL工作台中创建和执行过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！