来自 SELECT 语句的 MySQL 存储过程变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10649748/
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
MySQL Stored procedure variables from SELECT statements
提问by Matt Harrison
I'm trying to create a stored procedure. Here's what I have so far (not working):
我正在尝试创建一个存储过程。这是我到目前为止所拥有的（不工作）：
DELIMITER |
CREATE PROCEDURE getNearestCities(IN cityID INT)
BEGIN
DECLARE cityLat FLOAT;
DECLARE cityLng FLOAT;
SET cityLat = SELECT cities.lat FROM cities WHERE cities.id = cityID;
SET cityLng = SELECT cities.lng FROM cities WHERE cities.id = cityID;
SELECT *, HAVERSINE(cityLat,cityLng, cities.lat, cities.lng) AS dist FROM cities ORDER BY dist LIMIT 10;
END |
HAVERSINE is a function I created which works fine. As you can see I'm trying to take the id of a city from the cities table and then set cityLat and cityLng to some other values of that record. I'm obviously doing this wrong here by using SELECTs.
HAVERSINE 是我创建的一个功能正常的函数。如您所见，我正在尝试从城市表中获取城市的 id，然后将 cityLat 和 cityLng 设置为该记录的其他一些值。我在这里使用 SELECT 显然是做错了。
Is this even possible. It seems it should be. Any help whatsoever will be greatly appreciated.
这甚至可能吗。好像应该是。任何帮助将不胜感激。
回答by Jon Black
Corrected a few things and added an alternative select - delete as appropriate.
更正了一些事情并添加了替代选择 - 酌情删除。
DELIMITER |
CREATE PROCEDURE getNearestCities
(
IN p_cityID INT -- should this be int unsigned ?
)
BEGIN
DECLARE cityLat FLOAT; -- should these be decimals ?
DECLARE cityLng FLOAT;
-- method 1
SELECT lat,lng into cityLat, cityLng FROM cities WHERE cities.cityID = p_cityID;
SELECT
b.*,
HAVERSINE(cityLat,cityLng, b.lat, b.lng) AS dist
FROM
cities b
ORDER BY
dist
LIMIT 10;
-- method 2
SELECT
b.*,
HAVERSINE(a.lat, a.lng, b.lat, b.lng) AS dist
FROM
cities AS a
JOIN cities AS b on a.cityID = p_cityID
ORDER BY
dist
LIMIT 10;
END |
delimiter ;
回答by eggyal
You simply need to enclose your SELECTstatements in parentheses to indicate that they are subqueries:
您只需将SELECT语句括在括号中以表明它们是子查询：
SET cityLat = (SELECT cities.lat FROM cities WHERE cities.id = cityID);
Alternatively, you can use MySQL's SELECT ... INTOsyntax. One advantage of this approach is that both cityLatand cityLngcan be assigned from a single table-access:
或者，您可以使用 MySQL 的SELECT ... INTO语法。这种方法的一个优点是可以从单个表访问中分配cityLat和cityLng：
SELECT lat, lng INTO cityLat, cityLng FROM cities WHERE id = cityID;
However, the entire procedure can be replaced with a single self-joined SELECTstatement:
但是，整个过程可以用单个自连接SELECT语句替换：
SELECT b.*, HAVERSINE(a.lat, a.lng, b.lat, b.lng) AS dist
FROM cities AS a, cities AS b
WHERE a.id = cityID
ORDER BY dist
LIMIT 10;
