MySQL SELECT语句中的存储过程变量 [英] MySQL Stored procedure variables from SELECT statements

问题描述

我正在尝试创建一个存储过程.这是我到目前为止(无法正常工作)的内容:

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

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.

这是否有可能.看来应该如此.任何帮助将不胜感激.

Is this even possible. It seems it should be. Any help whatsoever will be greatly appreciated.

推荐答案

更正了几件事，并添加了另一个选择-适当地删除.

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 ;

这篇关于MySQL SELECT语句中的存储过程变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！