MySQL SELECT语句中的存储过程变量

一尘不染

MySQL SELECT语句中的存储过程变量

mysql

我正在尝试创建一个存储过程。这是我到目前为止（不起作用）的内容：

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是我创建的可以正常工作的函数。如您所见，我正在尝试从“城市”表中获取城市的ID，然后将cityLat和cityLng设置为该记录的其他值。我显然在这里通过使用SELECTs做错了。

这有可能吗？看来应该如此。任何帮助将不胜感激。

阅读 685

2020-05-17

共1个答案

一尘不染

更正了一些问题，并添加了一个替代选择-适当删除。

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 ;

2020-05-17