如何在MySQL工作台建表_如何在MySQL工作台中创建和执行过程

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

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

Executing the statement

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...

解决方案

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 ;

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.